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

This error is received when the listener received a request to establish a connection to a database or other service but the connect descriptor received by the listener specified a SID for an instance (usually a database instance) that either has not yet dynamically registered with the listener or has not been statically configured for the listener therefore listener is not aware of the SID.

In Listener Static Registration the SID is mentioned in the listener.ora by which listener knows about the SID.

#Here orcl is the SID name which is registered in the listener.ora 

LISTENER=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=myserver.example.com)(PORT=1521))
(ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=orcl))

In Listener Dynamic Registration, SID is not mentioned in listener.ora instead its the job of PMON process to register the database to the listener and then the listener is made aware of the SID.

By default, the PMON process contact the listener running on port 1521 to perform the registration. To instruct PMON process to register to a listener running on different port, we have to change the LOCAL_LISTENER parameter. Since its dynamic LOCAL_LISTENER parameter can simply be altered using ALTER SYSTEM command.

#Following command will set local_listener to port 1522
alter system set local_listener='(ADDRESS = (PROTOCOL=TCP)(HOST=localhost.localdomain)(PORT=1522))';

#Following command will instruct PMON to register the SID on the listener defined by local_listener parameter
alter system register;

If the SID is registered with the listener, it should show the SID in the Services summary:-

> lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 21-DEC-2018 14:17:04
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=ISM01)))
STATUS of the LISTENER
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 10-AUG-2018 13:01:12
Uptime 133 days 2 hr. 15 min. 51 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /apps/network/admin/listener.ora
Listener Log File /apps/network/admin/listener.log
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=orcl)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=1522)))
Services Summary…
Service "orcl" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service…

The command completed successfully

If the service summary in lsnrctl status does not show the SID given in the client’s tnsnames.ora (CONNECT_DATA) then ensure that the same SID is either dynamically or statically registered with the listener.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.