In this Document

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  database instance.
So:

1
2
3
4
5
6
7
8
9
10
11
12
13
<my_alias> =  
  (DESCRIPTION =  
    (ADDRESS=(protocol = tcp)(HOST=<hostname.domain>)(port = 1521))  
    (CONNECT_DATA=(SERVER=DEDICATED)(SID = <TEST>))  
  )

AI写代码bash

* 1
* 2
* 3
* 4
* 5

However, the database is changed to a pluggable database (PDB so Multi-Tenant functionality) and the client connection now fails with ORA-12505.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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

AI写代码bash

* 1
* 2
* 3
* 4
* 5
* 6
* 7

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 = 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 as only a Service and not an Instance, with the Instance being the CDB (Container Database):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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 

AI写代码bash

* 1
* 2
* 3
* 4
* 5
* 6
* 7
* 8

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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
LISTENER =  
   (DESCRIPTION =  
    (ADDRESS_LIST = (ADDRESS=(PROTOCOL=tcp)(HOST=<hostname.domain>)(PORT=1521))  
   )

USE_SID_AS_SERVICE_LISTENER = ON

AI写代码bash

* 1
* 2
* 3
* 4
* 5
* 6

The connection will work after this change or you will progress to the next logical
issue
:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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>

AI写代码bash

* 1
* 2
* 3
* 4
* 5
* 6
* 7
* 8
* 9
* 10

The listener will interpret the value for SID=TEST as SERVICE_NAME=TEST and allow the connection.

  1. 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 :
1
2
3
4
5
6
7
8
9
10
11
12
13
<my_alias> =  
  (DESCRIPTION =  
    (ADDRESS=(protocol = tcp)(HOST=<hostname.domain>)(port = 1521))  
    (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME = <TEST>))  
  )

AI写代码bash

* 1
* 2
* 3
* 4
* 5