How to Enable Clients Using SID to Connect to Oracle PDB in a Oracle Home and Oracle Grid Infrastructure Installation
Connect to a Oracle PDB using SID
--------------------------------------------
To connect to a PDB, you have to use the service name of the pluggable database. But what when there is an older legacy application that can only use an SID to connect to a database? And what if we just migrated an older legacy database to a PDB? When starting the application, you might see something like:
ORA-12505: TNS:listener does not currently know of SID given in connect descriptor
There is a parameter called USE_SID_AS_SERVICE_listener that can be used to tell the listener to handle SIDs like service names. So the listener will interpret the SID given in the connection as a service name and will then connect you to the database.
To implement this change, add the following line to your listener.ora configuration (where “listener” is the name of your listener) and restart the TNS listener:
USE_SID_AS_SERVICE_listener=on
Just remember, use the parameter as USE_SID_AS_SERVICE_<LISTENER_NAME>.
Then, restart the listener, and you will be able to use a string connection from your legacy application as:
PDB1=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = nizamappsdba.blogspot.com)(PORT = 1521))
(CONNECT_DATA =
(SID = PDB1)
)
)
if you are using Oracle Grid Infrastructure, then we need add the USE_SID_AS_SERVICE_<LISTENER_NAME> on the Grid Scan Listnerers as well
Add the listener parameter to the listener.ora file located in the GI Home.
Important: add the parameter using the LISTENER name and the SCAN LISTENER names.
USE_SID_AS_SERVICE_LISTENER2=ON
USE_SID_AS_SERVICE_SCAN_LISTENER2_SCAN1_NET2=ON
USE_SID_AS_SERVICE_SCAN_LISTENER2_SCAN2_NET2=ON
USE_SID_AS_SERVICE_SCAN_LISTENER2_SCAN3_NET2=ON
$ srvctl stop scan_listener -netnum 2
$ srvctl stop listener -listener listener2
You can check the status of the resources using:
$ crsctl stat res -t
And finally, start both scan listener and listener.
$ srvctl start listener -listener listener2
$ srvctl start scan_listener -netnum 2
$ crsctl stat res -t
Important To Note:
For a Oracle Grid Infrastructure RAC or a two-node Oracle Grid Infrastructur installation, you must change the listener.ora file for both nodes and restart the local listener for each.
SID names must match a service name in the PDB; otherwise, the connection won't work.
It is not possible to see the SID as an available service using the lscncrtl status command.
Remember to change the LOCAL_LISTENER, REMOTE_LISTENER, and LISTENER_NETWORKS parameters to use the desired network configuration for your database.