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
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 220.127.116.11.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
Version TNSLSNR for Linux: Version 18.104.22.168.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
Listener Parameter File /apps/network/admin/listener.ora
Listener Log File /apps/network/admin/listener.log
Listening Endpoints 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.