APPLIES TO:Oracle Net Services - Version 12.1.0.1 and later
Information in this document applies to any platform.
SYMPTOMS
Client connection string uses SID to connect to TEST database .For example :
net12c =
(DESCRIPTION =
(ADDRESS=(protocol = tcp)(HOST=test.oracle.com)(port = 1521))
(CONNECT_DATA=(SERVER=DEDICATED)(SID = TEST))
)
The TEST database is changed to a pluggable database and client connection fais with ORA-12505.
C:Users est>sqlplus sys/test_12c@net12c as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Wed Apr 16 18:15:25 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
ERROR:
ORA-12505: TNS:listener does not currently know of SID given in connect
descriptor
CHANGES
The TEST database is now a PDB. Connections to a pluggable database use service name .
CAUSE
PDB is not an instance, so using SID in the connection string will not work unless the following listener.ora file
setting is in place: USE_SID_AS_SERVICE_listener name=ON
When the database is an Oracle Database 12c container database, the client must specify a service name in order to connect to it.
Listener status shows TEST as only a service :
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test.oracle.com)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\.pipeEXTPROC1521ipc)))
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 "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service.
Service "TEST" has 1 instance(s).
Instance "cdb1", status READY, has 1 handler(s) for this service...
The command completed successfully
SOLUTION
Use USE_SID_AS_SERVICE_listener_name=on in listener.ora and restart the listener . This will enable the system identifier (SID) in the connect descriptor to be interpreted as a service name when a user attempts a database connection. Database clients with earlier releases of Oracle Database that have hard-coded connect descriptors can use this parameter to connect to a container or pluggable database.
Example of usage in listener.ora:
LISTENER=
(DESCRIPTION=
(ADDRESS_LIST= (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
)
USE_SID_AS_SERVICE_LISTENER=ON
The connection will work after this change:
C:Users est>sqlplus sys/test_12c@TNSNet12c as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Wed Apr 16 18:28:40 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt
ions
SQL>
OR
- Modify client connection string to use the actual PDB service name in the SID field :
TNSNet12c =
(DESCRIPTION =
(ADDRESS=(protocol = tcp)(HOST=test.oracle.com)(port = 1521))
(CONNECT_DATA=(SERVER=DEDICATED)(SID = TEST))
)
The listener will interpret the value for SID=TEST as SERVICE_NAME=TEST and allow the connection.
<wiz_tmp_tag id="wiz-table-range-border" contenteditable="false" style="display: none;">