Monday, February 2, 2009

The listener supports no services

Problem Description
Whenever I issue lsnrctl status command it says "The listener supports no services" and no one from outside could not connect to database through listener.

$lsnrctl status

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 02-FEB-2009 16:22:01

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 02-FEB-2009 05:07:47
Uptime 0 days 11 hr. 14 min. 14 sec
Trace Level support
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /var/opt/oracle/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File /var/opt/oracle/oracle/product/10.2.0/db_1/network/log/listener.log
Listener Trace File /var/opt/oracle/oracle/product/10.2.0/db_1/network/trace/listener.trc
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
The listener supports no services
The command completed successfully

Below is my listener.ora file entry,

$ cat /var/opt/oracle/oracle/product/10.2.0/db_1/network/admin/listener.ora
# listener.ora Network Configuration File: /usr/oracle/product/11.1.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST =127.0.0.1)(PORT = 1521))
)
)


Cause of the Problem
A listener is configured with one or more listening protocol addresses, information about supported services, and parameters that control its run time behavior. Upon database startup, oracle process PMON register with listener service. If after database startup if a listener is created then database has no idea about the listener because inside listener.ora there is no entry of SID_LIST_{listener_name} where {listener_name} need to be replaced by your listener name.

Suppose if you have a listener named LISTENER then you should have a corresponding SID_LIST_LISTENER entry while will define $ORACLE_HOME and ORACLE_SID of the listener.

Problem Solution
I edited the listener.ora file as below.


$ cat /var/opt/oracle/oracle/product/10.2.0/db_1/network/admin/listener.ora
# listener.ora Network Configuration File: /usr/oracle/product/11.1.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /var/opt/oracle/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
(SID_DESC=
(GLOBAL_DBNAME=thdb)
(ORACLE_HOME=/var/opt/oracle/oracle/product/10.2.0/db_1)
(SID_NAME=thdb)
)
)
SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST =THAI_DB)(PORT = 1521))
)
)



Then bounce the listener by,
$lsnrctl stop
$lsnrctl start


And now listener supports service and from outside world you can connect to database.

$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 02-FEB-2009 16:49:06

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 02-FEB-2009 16:43:16
Uptime 0 days 0 hr. 5 min. 49 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /var/opt/oracle/oracle/product/10.2.0/db_1/network/adm in/listener.ora
Listener Log File /var/opt/oracle/oracle/product/10.2.0/db_1/network/log /listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=THAI_HP)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "thdb" has 1 instance(s).
Instance "thdb", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
Related Documents

16 comments:

bokal said...

Thanks - you just saved my day ;-)

Anonymous said...

Thanks for the useful post. It's interesting that I used netca to set up listener.ora and it didn't put any sid_list_listener* information in my listener.ora. On the one hand they say don't edit your listener.ora, on the other hand netca doesn't do a complete job.

Anonymous said...

Hi. Something else to try for those using 11g.
1. export $ORACLE_HOME_LISTENER=[the value of ADR_BASE_LISTENER in listener.ora]
2. dbstart $ORACLE_HOME
3. If need be in sqlplus as sysdba run 'startup'

Веско said...

Very good info. I've dig a lot for simple explanation of a listener.ora problem, and found this. Oracle Tech docs are just bulk of info.

Anonymous said...

Thanks for this!

-David

Anonymous said...

Thank you so much for posting this paper, it really saved my day. I don't understand why netca did not get listener.ora configured. you are great, I bookmarked this site, will visit it frequently.
Wendy

Anonymous said...

Thank you so much for saving me extra headache. After about 5 attempts to deinstall and then reinstall 11GR1 and getting the same error you were with the listener, I am now able to fix my issue using your solution.

For windows users, you can use this example.

# Example listner.ora file using
# SID name = orcl
# Global DB Name = orcl.mydomain.com #(your's will be different here)
# Oracle_Home = C:\Oracle\11GR1\OraHome_1\bin

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC = (SID_NAME = PLSExtProc)
(ORACLE_HOME=C:\Oracle\11GR1\OraHome_1\bin)
(PROGRAM = extproc) )

(SID_DESC=
(GLOBAL_DBNAME=orcl.mydomain.com)
(ORACLE_HOME=C:\Oracle\11GR1\OraHome_1\bin)
(SID_NAME=orcl) ) )

SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST =127.0.0.1)(PORT = 1521)) ) )

Anonymous said...

Muchas gracias.

Anonymous said...

This solution just saved the day for me too. Wonder why netca doesn't write this configuration! Oracle 11G R2 - you'd think by now they would have fixed this.

Anonymous said...

When I have this problem, I wait and after a while it registers the services... lol but true!

Anonymous said...

Checklist for single Oracle instance on Windows:

1) Universal Installer
2) Oracle Net Configuration Assistant
- Local Naming
- Easy Connect Naming
3) Environment variables
- ORACLE_HOME
- PATH
4) Listener.ora

<> =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = <>))
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
)

SID_LIST_<> =
(SID_LIST=
(SID_DESC =
(SID_NAME=<>)
(ORACLE_HOME=<>\bin)
(PROGRAM=extproc)
)
)

Note that extproc is an executable which exists in the BIN subfolder of your ORACLE_HOME.

The default value of <> is LISTENER.

5) Getting things started
In Services, "OracleService<>" should be running.
At the command prompt
lsnrctl stop
lsnrctl start
lsnrctl status
You should have 1 instance attached to your listener.

Anonymous said...

Thanks!
My first time here, and also the best solution!

Anonymous said...

Thanks!
My first time here, you are best!

Lydia said...

Something so ridiculously simple was giving me such head aches...thank you so much for this post!

Anonymous said...

Thanks for this! (gui)

Anonymous said...

Thank you ! You made my day !