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
at
Labels:

15 comments:

  1. AnonymousApril 10, 2012 at 4:23 PM

    this blog helped me.

    ReplyDelete
  2. VikashMay 15, 2012 at 12:22 PM

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

    ReplyDelete
  3. Firdous Ahmed BhatSeptember 26, 2012 at 3:24 AM

    this blog helped me too. thanks a lot

    ReplyDelete
  4. AnonymousOctober 26, 2012 at 8:06 PM

    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
  5. AnonymousNovember 19, 2012 at 6:39 PM

    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
  6. AnonymousNovember 20, 2012 at 9:34 AM

    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
  7. AnonymousMarch 13, 2013 at 1:04 AM

    it works for me...many thanks!!

    ReplyDelete
    Replies
    1. Sathya AGMarch 14, 2013 at 2:35 AM

      Your comment much appreciated. Thanks for letting me know.

      Delete
  8. AnonymousMarch 13, 2013 at 10:27 PM

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

    ReplyDelete
  9. AnonymousJuly 26, 2013 at 12:56 PM

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

    ReplyDelete
  10. AnonymousFebruary 23, 2016 at 3:03 PM

    restart always do magic!

    ReplyDelete

Subscribe to: Post Comments (Atom)