Wednesday, August 24, 2011

TNS Listener supports no services

While using Oracle 11g database, we encountered an issue where the database listener was unable to connect to any DB service although the associated database server was started, up and running. No matter how many times we issued lsnrctl start commands, the listener was unable to tie to the running database service. The listener reported the following on startup;

LSNRCTL for Linux: Version 11.2.0.1.0 - Production

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                11-AUG-2011 07:16:06
Uptime                    12 days 19 hr. 24 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/dbhome/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /oracle/dbhome/app/oracle/diag/tnslsnr/soabpm-vm/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
The listener supports no services
The command completed successfully

To overcome this issue, leave the listener started and reboot the 11g database. After 11g DB restart, issue the lsnrctl status command and see that the services are now registered successfully with the listener.

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 24-AUG-2011 02:40:07

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                11-AUG-2011 07:36:00
Uptime                    0 days 0 hr. 24 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/dbhome/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /oracle/dbhome/app/oracle/diag/tnslsnr/soabpm-vm/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
Services Summary...
Service "orcl.local" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB.local" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully

15 comments:

  1. this blog helped me.

    ReplyDelete
  2. You can also go with this one to register the database with the listener.
    Alter system register;

    ReplyDelete
  3. i am still getting the same error.
    Where do i update entry (tnsnames or sqlnet)? or how do i dynamically registered the listener?

    SQLNET.ORA has
    SQLNET.AUTHENTICATION_SERVICES= (NTS)
    NAMES.DIRECTORY_PATH= (TNSNAMES,EZCONNECT,HOSTNAME)

    This is my home laptop....
    and listener.ora

    # listener.ora Network Configuration File: C:\oracle\product\11.2.0\dbhome_1\NETWORK\ADMIN\listener.ora
    # Generated by Oracle configuration tools.

    SID_LIST_LISTENER =
    (SID_LIST = (SID_DESC = (PROGRAM = extproc)
    (SID_NAME = CLRExtProc)
    (ORACLE_HOME = C:\oracle\product\11.2.0\dbhome_1)
    )
    (SID_DESC =
    (GLOBAL_DBNAME = orcl.192.168.2.10)
    (ORACLE_HOME = C:\oracle\product\11.2.0\dbhome_1)
    (SID_NAME = ORCL)
    )
    )
    LISTENER =
    (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.10)(PORT = 1521))
    )
    )
    ADR_BASE_LISTENER = C:\oracle

    Thanks in advance,
    Dev

    ReplyDelete
  4. Thanks for the alter system register command. This solved one problem for me. A question for you though - lsnrctl status shows the listener has handlers for SIDs not in this oracle home (they do exist under a 10G home on the server). How is it finding these?

    ReplyDelete
    Replies
    1. Did you check your listener.ora file?

      Delete
  5. The extra handlers I am seeing are not referenced in the listener or tnsnames files at all under this oracle home. There are also no references to the 10G home either (like IFLES). It is a puzzler.

    ReplyDelete
  6. it works for me...many thanks!!

    ReplyDelete
    Replies
    1. Your comment much appreciated. Thanks for letting me know.

      Delete
  7. Sathya, thank you ever so much! such a simple solution but it WORKS :-) I kept restarting listener :-(

    ReplyDelete
  8. Alter System Register didn't work.. but bouncing db did the charm!! thank you!

    ReplyDelete
  9. restart always do magic!

    ReplyDelete