Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a oracle 10g database in solaris 10 system. The SID=OTRS, I can connect to oracle database from local server itself. But I can not able 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 recognize the sid OTRS, What is solution to resolve this error ?

share|improve this question
    
Did you check if it is a problem related to upper/lower case letters? Do you have a file, in /u01/app/oracle/product/10.2.0/db_3/dbs, called lkotrs, or is it lkOTRS? –  eppesuig Dec 27 '12 at 7:52
    
Is that all of the ` listener.ora` file? The problem is because the database thinks it needs to auto-register with a listener called LISTENER_OTRS (I think). You'll need a SID_LIST_LISTENER_OTRS section in your listner.ora file, plus a LISTENER_OTRS section. –  Phil Dec 27 '12 at 8:07

1 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'

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.