Applies to:
Oracle Net Services - Version 12.1.0.1 and later
Information in this document applies to any platform.
Symptoms
As an example, Client connection string uses the "SID" value to connect to a <TEST> database instance.
So:
<my_alias> =
(DESCRIPTION =
(ADDRESS=(protocol = tcp)(HOST=<hostname.domain>)(port = 1521))
(CONNECT_DATA=(SERVER=DEDICATED)(SID = <TEST>))
)
However, the <TEST> database is changed to a pluggable database (PDB so Multi-Tenant functionality) and the client connection now fails with ORA-12505.
C:\Users\test>sqlplus <username>/<password>@<my_alias>
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 and not SID.
Cause
A PDB is not an instance.
So using a SID parameter in the connection string will not work unless the following listener.ora file setting is put 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 and not an Instance, with the Instance being the CDB (Container Database):
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<hostname.domain>)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
Services Summary...
...
Service "<TEST>" has 1 instance(s).
Instance "<cdb1>", status READY, has 1 handler(s) for this service...
The command completed successfully
Solution
Set the following control parameter in the listener.ora file and restart the listener:
USE_SID_AS_SERVICE_<listener_name> = ON
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=<hostname.domain>)(PORT=1521))
)
USE_SID_AS_SERVICE_LISTENER = ON
The connection will work after this change or you will progress to the next logical issue:
C:\Users\test>sqlplus <username>/<password>@<my _alias>
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>
The listener will interpret the value for SID=TEST as SERVICE_NAME=TEST and allow the connection.
2. Otherwise, modify the client connection string to use the a SERVICE_NAME field to match the actual PDB service name instead of the SID field :
<my_alias> =
(DESCRIPTION =
(ADDRESS=(protocol = tcp)(HOST=<hostname.domain>)(port = 1521))
(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME = <TEST>))
)
