Friday, May 10, 2013

HowTO: Oracle the listener supports no services error

The following are the detailed RCA report for the Oracle database issue :

ERROR:
The listener supports no services

ROOT CAUSE:
The database has no idea about the listener because inside the file 'listener.ora', there is no entry of SID_LIST_{listener_name} where {listener_name} need to be replaced by your listener name.

CORRECTIVE ACTION:
Edited the 'listener.ora' file as explained below:

SID_LIST_LISTENER =
 (SID_LIST =
   (SID_DESC =
     (SID_NAME = PLSExtProc)
     (ORACLE_HOME = /home/oracle/app/oracle/product/11.2.0/dbhome_1/)
     (PROGRAM = extproc)
   )
 (SID_DESC=
       (GLOBAL_DBNAME=orcl)
       (ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_1/)
       (SID_NAME=orcl)
  )
 )
SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF
LISTENER =
 (DESCRIPTION_LIST =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
     (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
   )
 )

And fixed net service names map in 'tnsnames.ora' file
ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
        (SID_NAME = ORCL)
      (SERVER = DEDICATED)
    )
  )

$ lsnrctl status
$ lsnrctl stop
$ lsnrctl start