12c: CONNECTING TO PDB'S WITH SAME NAME

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.

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. To avoid incorrect connections, we should configure a separate listener for each CDB on a computer system.

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.
-- Verify that now we can connect to the right pdb

Implementation:

-- 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, it can be concluded that to avoid incorrect connections, we should configure a separate listener for each CDB on a computer system.
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
-------------------------------------------------------------------------------------