In an Oracle 12c container database, the client must specify a service name in order to connect to the PDB. Many Database clients with earlier releases of Oracle Database might have hard-coded connect descriptors to use SID to connect to the database. Now if they choose to plug in their non-CDB’s into a CDB, they will have to modify the connect descriptors in tnsnames.ora on all the client sites.
This problem can be resolved by the use of listener.ora parameter USE_SID_AS_SERVICE_listener_name. Setting this parameter to on instructs the listener to use the SID in the connect descriptor as a service name and connect the client to the specified database. The default value for this parameter is off and it has to be explicitly set to on to use this functionality.
Here is the demonstration :
Presently, I have a CDB called CDB1 into which I have plugged in a non-CDB (ncdb).
SQL> sho parameter db_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string cdb1 SQL> sho pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 NCDB READ WRITE NO
Currently connect descriptor in tnsnames.ora entry for ncdb points to SID as follows:
NCDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = host01.example.com)(PORT = 1521))
)
(CONNECT_DATA =
(SID = ncdb)
)
)
In listener.ora , since there is no entry for USE_SID_AS_SERVICE_listener_name, it defaults to off.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = host01.example.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
– If I try to connect to NCDB, I get following error :
[oracle@host01 ~]$ export ORACLE_SID=ncdb [oracle@host01 ~]$ sqlplus system/oracle@ncdb SQL*Plus: Release 12.1.0.1.0 Production on Fri May 30 15:24:00 2014 Copyright (c) 1982, 2013, Oracle. All rights reserved. ERROR: ORA-12505: TNS:listener does not currently know of SID given in connect descriptor
– I edited listener.ora to set USE_SID_AS_SERVICE_LISTENER to on
USE_SID_AS_SERVICE_LISTENER=on
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = host01.example.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
[oracle@host01 admin]$ lsnrctl reload [oracle@host01 admin]$ lsnrctl stat LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 30-MAY-2014 15:24:55 Copyright (c) 1991, 2013, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host01.example.com)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.1.0.1.0 - Production Start Date 29-MAY-2014 11:03:14 Uptime 1 days 4 hr. 21 min. 40 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/host01/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=host01.example.com)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=host01.example.com)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/cdb1/xdb_wallet))(Presentation=HTTP)(Session=RAW)) Services Summary... Service "cdb1" has 1 instance(s). Instance "cdb1", status READY, has 1 handler(s) for this service... Service "cdb1XDB" has 1 instance(s). Instance "cdb1", status READY, has 1 handler(s) for this service... Service "em12rep" has 1 instance(s). Instance "em12rep", status READY, has 1 handler(s) for this service... Service "em12repXDB" has 1 instance(s). Instance "em12rep", status READY, has 1 handler(s) for this service... Service "ncdb" has 1 instance(s). Instance "cdb1", status READY, has 1 handler(s) for this service... The command completed successfully
– Now I can connect to NCDB easily ..
[oracle@host01 ~]$ sqlplus system/oracle@ncdb
SQL> sho con_name
CON_NAME
------------------------------
NCDB
Hope this post was useful.
Your comments and suggestions are always welcome!!
References:
http://docs.oracle.com/cd/E16655_01/network.121/e17611/listener.htm#NETRF2090
http://www.freelists.org/post/oracle-l/DB12c-in-Production,21
Oracle 12c: OS authentication and Multitenant Architecture
—————————————————————————————-
Related Links:
12c : Connecting to CDB/PDB – Set Container vs Connect
12c : Connecting To PDB’s With Same Name
—————————————————————————
This article saved me countless hours of searching for a solution to my problem – Thanks a million! Here is the story:
(Adobe) Coldfusion (CF) server has an option to add Oracle data sources. Scores of CF developers – like myself – found themselves stuck when trying to connect to a CDB – nothing seemed to work. Adobe didn’t provide any answers or help on this topic… This article is a perfect solution to the problem and it worked “like a charm” for me. Thanks again!
Thanks Mitran for your feedback.
Your comments and suggestions are always welcome!
Regards
Anju
Nice post Anju, saved my time:) thanks for sharing!!