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?
Best Answer
There are a few problems.
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'