When you create a PDB, the database automatically creates and starts a service inside the CDB.The service has the  same name as the PDB. It is possible that the name of the service will collide with an existing service name which is registered with the same listener. For example if two or more CDBs on the same computer system use the same  listener, and the newly established PDB has the same service name as another PDB in these CDBs, then a collision occurs.

You must not attempt to operate a PDB that causes a collision with an existing service name. To avoid incorrect connections, two methods are possible:

– configure a separate listener for each CDB on the computer system or

– ensure that all service names for PDBs are unique on the computer system .

Firstly, I will demonstrate that a connection that specifies the default service name of a PDB can connect randomly to any of the PDBs with the same service name.  Then I will demonstrate both the methods to  avoid incorrect connections.

Current scenario:

I have two CDB’s (CDB1 and CDB2) on the same computer system.
Pluggable database PDB1 exists in both the CDB’s  CDB1 and CDB2

There are two listeners running in database home
listener1 on port 1523
listener2 on port 1524

Overview:

— Register both the CDB’s (and hence PDB’s) with listener1 running on port 1523.
— Verify that if we repeatedly  connect to service PDB1, we are randomly connected to different pdb’s   (PDB1@CDB1 and PDB1@CDB2).
— Register PDB1@CDB2 with listener2 on port 1524 and verify that now we can connect to the right pdb

— Create service spdb11 for PDB1@CDB1 and spdb12 for PDB1@CDB2 and verify that now we can connect to the right pdb using respective service.

Implementation:

Method – I :  Configure a separate listener for each CDB on the computer system or

– Register both the CDB’s with listener1 running on port 1523

CDB1>alter system set local_listener='em12c.oracle.com:1523';

sho parameter local_listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string      em12c.oracle.com:1523

CDB2>alter system set local_listener='em12c.oracle.com:1523';

sho parameter local_listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string      em12c.oracle.com:1523

– check that both the CDB’s and PDB1 in both the CDB’s are registered with listener1 (port 1523)

[oracle@em12c ~]$ lsnrctl stat listener1
(output trimmed)
Listener Parameter File   /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/em12c/listener1/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=em12c.oracle.com)(PORT=1523)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1523)))
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 "cdb2" has 1 instance(s).
Instance "cdb2", status READY, has 1 handler(s) for this service...
Service "cdb2XDB" has 1 instance(s).
Instance "cdb2", status READY, has 1 handler(s) for this service...
Service "pdb1" has 2 instance(s).
 Instance "cdb1", status READY, has 1 handler(s) for this service...
  Instance "cdb2", status READY, has 1 handler(s) for this service...
The command completed successfully

It can be seen that  PDB PDB1 has same name in both the CDB’s (CDB1 and CDB2) and default service for both the PDB’s (PDB1@CDB1 and PDB1@CDB2) are registered with the listener  on the same port (1523).

– Verify that if we repeatedly  connect to service PDB1, we are randomly connected to different pdb’s   (PDB1@CDB1 and PDB1@CDB2)

CDB1>conn system/oracle@em12c:1523/pdb1
sho parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      cdb2

CDB1>conn system/oracle@em12c:1523/pdb1
sho parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      cdb1

CDB1>conn system/oracle@em12c:1523/pdb1
sho parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      cdb2

CDB1>conn system/oracle@em12c:1523/pdb1
sho parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      cdb1

Hence, to connect to the right PDB, we should register different PDB’s with listeners running on different ports.

– Let’s register PDB1@CDB2 with listener2 on port 1524

CDB2>alter system set local_listener='em12c.oracle.com:1524';

sho parameter local_listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string      em12c.oracle.com:1524

– check that  CDB1  and pdb1@CDB1  are registered with listener1 (port 1523)

[oracle@em12c ~]$ lsnrctl stat listener1

(output trimmed)

Listener Parameter File   /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/em12c/listener1/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=em12c.oracle.com)(PORT=1523)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1523)))
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 "pdb1" has 1 instance(s).
  Instance "cdb1", status READY, has 1 handler(s) for this service...
The command completed successfully

– check that  CDB2  and PDB1@CDB2  are registered with listener2 (port 1524)

[oracle@em12c ~]$ lsnrctl stat listener2

(output trimmed)

Listener Parameter File   /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/em12c/listener2/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=em12c.oracle.com)(PORT=1524)))
Services Summary...
Service "cdb2" has 1 instance(s).
Instance "cdb2", status READY, has 1 handler(s) for this service...
Service "cdb2XDB" has 1 instance(s).
Instance "cdb2", status READY, has 1 handler(s) for this service...
Service "pdb1" has 1 instance(s).
 Instance "cdb2", status READY, has 1 handler(s) for this service...
The command completed successfully

– Verify that now we can connect to the right pdb

– connect to PDB1@CDB1 (listener1, port 1523)

SQL> conn system/oracle@em12c:1523/pdb1
sho parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      cdb1

SQL> conn system/oracle@em12c:1523/pdb1
sho parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      cdb1

-- connect to PDB1@CDB2 (listener2, port 1524)

SQL> conn system/oracle@em12c:1524/pdb1
sho parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      cdb2

SQL> conn system/oracle@em12c:1524/pdb1
sho parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      cdb2

Hence,  to avoid  incorrect connections, one method is that we should configure a separate listener for each CDB on a computer system.

But this  approach is not easily transferable  to an Oracle RAC environment and hence it woule be better to use the second method i.e.  using services to make a PDB accessible in general, especially in RAC.

Method-II :Using Services to access a specific PDB

- Create and start service spdb1 for PDB1@CDB1

CDB1>alter session set container=pdb1;

PDB1@CDB1> exec dbms_service.create_service(‘spdb1′, ‘spdb1′);

exec dbms_service.start_service(‘spdb1′);

select name, network_name from dba_services;

NAME            NETWORK_NAME
————— —————
pdb1            pdb1
spdb1           spdb1

– Add the following entry to tnsnames.ora

NPDB11 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = em12c)(PORT = 1523))
)
(CONNECT_DATA =
(SERVICE_NAME = spdb1)
)
)

- Create and start service spdb12 for PDB1@CDB2

CDB2>alter session set container=pdb1;

PDB1@CDB2>exec dbms_service.create_service (‘spdb12′, ‘spdb12′);

exec dbms_service.start_service (‘spdb12′);

select name, network_name from dba_services;
NAME            NETWORK_NAME
————— —————
pdb1                pdb1
spdb12           spdb12

– Add the following entry to tnsnames.ora

NPDB12 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = em12c)(PORT = 1523))
)
(CONNECT_DATA =
(SERVICE_NAME = spdb12)
)
)

– Test connecting to specific PDB using services

– Connect to PDB1@CDB1

PDB1@CDB2>conn system/oracle@npdb11
sho con_name

CON_NAME
——————————
PDB1
PDB1@CDB2>sho parameter db_name

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_name                              string      cdb1

– Connect to PDB1@CDB2
PDB1@CDB2>conn system/oracle@npdb12
sho con_name

CON_NAME
——————————
PDB1
PDB1@CDB2>sho parameter db_name

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_name                              string      cdb2

References:

http://docs.oracle.com/cd/E16655_01/server.121/e17209/statements_6009.htm
http://docs.oracle.com/cd/E16655_01/server.121/e17633/cdblogic.htm#CIHDEDCC
————————————————————————————-

Related Links:

Home

Oracle 12c Index

12c : Connecting to CDB/PDB – Set Container vs Connect
12c: USE_SID_AS_SERVICE_listener_name