12c: USE_SID_AS_SERVICE_listener_name

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

——————————————————————————————

Related Links:

Home

Database 12c Index

12c : Connecting to CDB/PDB – Set Container vs Connect
12c : Connecting To PDB’s With Same Name

—————————————————————————

 

 

 

Your comments and suggestions are welcome!