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
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.
ReplyDeleteHi. Something else to try for those using 11g.
ReplyDelete1. 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'
Thanks for this!
ReplyDelete-David
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.
ReplyDeleteWendy