listener does not currently know of SID given in connect descriptor in oracle 10g and solaris 10 env

Question

I have an Oracle 10g database on a Solaris 10 system. The SID=OTRS, I can connect to the Oracle database from the local server itself. But I am not able to connect from another client. It is returning an error

ORA-12505 TNS:listener does not currently know of SID given in connect descriptor 

If I execute the command show parameter listener, it shows

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string      LISTENER_OTRS
remote_listener                      string

But my listener.ora file is

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = otrs)
      (SID_NAME = otrs)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_3)
    )
  )
ADR_BASE_LISTENER = /u01/app/oracle

and my tnsnames.ora file is

otrs =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ticket)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = otrs)
    )
  )

CONNECT_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID= otrs)
      (PRESENTATION = RO)
    )
  )

If I run the command lsnrctl services , It shows

LSNRCTL for Solaris: Version 10.2.0.1.0 - Production on 27-DEC-2012 10:42:23

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=TICKET)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
The command completed successfully

The listener is not recognizing the sid OTRS. What is solution to resolve this error?

Answer 1

There are a few problems.

1. given the fact that you specified local_listener, you want your database instance to register to a non default listener for which the tnsalias should be LISTENER_OTRS
2. the tnsalias LISTENER_OTRS is missing
3. the tnsalias otrs is misformed (it has multiple addresses but not the ADDRESS_LIST parameter (in this case, the last piece will be ignored))

fix: remove the local_listener parameter and your instance will register perfectly fine on the listener that sits on port 1521 (the default port). The instance will register during startup or, if the listener is started later, once every few minutes or after issuing 'alter system register;'.

If you did this, lsnrctl status will show the instance name. Also: don't forget that on *nix, the ORACLE_SID is case sensitive.

The otrs tnsalias could be used to connect to the instance, once it has registered itself to the listener. You can test this using 'tnsping otrs'