Working on a migration from non-CDB to Multitenant architecture, I found a curious problem: one implication you must consider when migrating to Multitenant is that you must switch some of your connection strings from using SID names to using Service names only. Remember, to connect to a Pluggable database you must use the service name (by default named as the PDB).
But what happened with old legacy applications that can use the SID name only? This is the case in this article; during the migration tests, I found an old legacy application that had the SID name hard-coded many times, and it was impossible to switch it to use the Service Name to connect to the newly migrated PDB.
This is not a new problem, and Oracle already implemented a solution since Version 12.2.0.1. Oracle added a new listener parameter called USE_SID_AS_SERVICE, and this is what the documentation says:
"The USE_SID_AS_SERVICE ORACLE CONNECTION MANAGER PARAMETER ENABLES THE SYSTEM IDENTIFIER (SID) IN THE CONNECT DESCRIPTOR TO BE INTERPRETED AS A SERVICE NAME WHEN A USER ATTEMPTS A DATABASE CONNECTION."
The solution is simple, add the parameter as follows to the listener.ora file:
$ cat /u01/app/oracle/network/admin/listener.ora
LISTENER =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = oel1vna)
(PORT = 1521)
)
)
USE_SID_AS_SERVICE_LISTENER = ON
Just remember, use the parameter as USE_SID_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 = oel1vna)(PORT = 1551))
(CONNECT_DATA =
(SID = PDB1)
)
)
The parameter works as expected. Let’s add one more variable, and this was my case. We are using Grid Infrastructure in a two-node ODA configuration, and we are using multiple scan networks, each with its own listener.
This is my current configuration, and I want to use network #2 and listener2 for the database that requires client connections using the SID:
$ srvctl config scan -all
SCAN name: vna-scan, Network: 1
Subnet IPv4: 10.225.20.0/255.255.255.128/pubnet, static
SCAN 1 IPv4 VIP: 10.225.20.106
SCAN VIP is enabled.
SCAN 2 IPv4 VIP: 10.225.20.108
SCAN VIP is enabled.
SCAN 3 IPv4 VIP: 10.225.20.110
SCAN VIP is enabled.
SCAN name: vna-vlan2-scan, Network: 2
Subnet IPv4: 10.225.40.0/255.255.255.0/btbond1.40, static
SCAN 1 IPv4 VIP: 10.225.40.106
SCAN VIP is enabled.
SCAN 2 IPv4 VIP: 10.225.40.108
SCAN VIP is enabled.
SCAN 3 IPv4 VIP: 10.225.40.110
SCAN VIP is enabled.
Oracle documentation doesn't say anything about USE_SID_AS_SERVICE in a Grid Infrastructure installation. I will explain how to configure it in a multiple network configuration.
Before we start, this is my current listener configuration for Network #2:
$ srvctl status scan_listener -netnum 2
SCAN Listener SCAN_LISTENER2_SCAN1_NET2 is enabled
SCAN listener SCAN_LISTENER2_SCAN1_NET2 is running on node vna-oda02
SCAN Listener SCAN_LISTENER2_SCAN2_NET2 is enabled
SCAN listener SCAN_LISTENER2_SCAN2_NET2 is running on node vna-oda02
SCAN Listener SCAN_LISTENER2_SCAN3_NET2 is enabled
SCAN listener SCAN_LISTENER2_SCAN3_NET2 is running on node vna-oda02
This is the PDB I want to connect to using the SID name:
$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jan 4 15:23:08 2024
Version 19.18.0.0.0
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.18.0.0.0
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDBVNA READ WRITE NO
And this is the TNS entry I want to use and the error I get:
PDBVNA_SID =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = vna-vlan2-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = PDBVNA)
)
)
$ sqlplus system/Password@pdbvna_sid
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jan 4 15:23:08 2024
Version 19.18.0.0.0
ERROR:
ORA-12505: TNS:listener does not currently know of SID given in connect descriptor
First step, 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
The listener.ora file should look like:
$ cat listener.ora
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
SCAN_LISTENER2_SCAN1_NET2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=SCAN_LISTENER2_SCAN1_NET2))))
SCAN_LISTENER2_SCAN3_NET2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=SCAN_LISTENER2_SCAN3_NET2))))
SCAN_LISTENER2_SCAN2_NET2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=SCAN_LISTENER2_SCAN2_NET2))))
LISTENER2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER2))))
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))
LISTENER_SCAN3=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN3))))
LISTENER_SCAN2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2))))
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1))))
ASMNET1LSNR_ASM=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=ASMNET1LSNR_ASM))))
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_ASMNET1LSNR_ASM=ON#lineaddedbyAgent
VALID_NODE_CHECKING_REGISTRATION_ASMNET1LSNR_ASM=SUBNET#lineaddedbyAgent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON#lineaddedbyAgent
VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN1=OFF#lineaddedbyAgent-
Second step, stop the scan listener and the local listener. Remember I’m using the network #2.
$ srvctl stop scan_listener -netnum 2
$ srvctl stop listener -listener listener2
You can check the status of the resources using:
$ crsctl stat res -t
…
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.SCAN_LISTENER2_SCAN1_NET2.lsnr
1 OFFLINE OFFLINE STABLE
ora.SCAN_LISTENER2_SCAN2_NET2.lsnr
1 OFFLINE OFFLINE STABLE
ora.SCAN_LISTENER2_SCAN3_NET2.lsnr
1 OFFLINE OFFLINE STABLE
And finally, start both scan listener and listener.
$ srvctl start listener -listener listener2
$ srvctl start scan_listener -netnum 2
$ crsctl stat res -t
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.SCAN_LISTENER2_SCAN1_NET2.lsnr
1 ONLINE ONLINE vna-oda02 STABLE
ora.SCAN_LISTENER2_SCAN2_NET2.lsnr
1 ONLINE ONLINE vna-oda02 STABLE
ora.SCAN_LISTENER2_SCAN3_NET2.lsnr
1 ONLINE ONLINE vna-oda02 STABLE
Now, we should be able to connect to the PDB using the SID name in the TNS entry:
$ sqlplus system/Password@pdbvna_sid
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jan 4 15:23:08 2024
Version 19.18.0.0.0
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.18.0.0.0
SQL> show con_name
CON_NAME
------------------------------
PDBVNA