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. 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

 

2 thoughts on “12c: CONNECTING TO PDB’S WITH SAME NAME

  1. Hello Anju,

    I have created cdb (twco) using dbca no pluggable databases this time.I have read that creating databases in 12c also creates the services of the same name too which I can see using dba_services but it is not registered with the listener do I need to tinker with listener.ora for the same.
    My listener look like

    LSNR121 =
    (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = uoe12c.cloudapp.net)(PORT = 2626))
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC2626))
    )
    )

    and on twco it look like this:

    SQL> set linesize 1000
    SQL> /

    SERVICE_ID NAME PDB
    ———- —————————————————————- ——————————————————————————————————————————–
    1 SYS$BACKGROUND CDB$ROOT
    2 SYS$USERS CDB$ROOT
    5 twcoXDB CDB$ROOT
    6 twco CDB$ROOT
    SQL> show parameter service

    NAME TYPE VALUE
    ———————————— ———– ——————————
    service_names string twco
    SQL> show parameter local_listener

    NAME TYPE VALUE
    ———————————— ———– ——————————
    local_listener string
    SQL>

    Do I need to set the local_listener in the twco for the listener to show the service for twco registered.
    LISTERNER121

    status no servies at the moment:

    [oracle@uoe12c ~]$ lsnrctl status LSNR121

    LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 16-JAN-2016 03:50:12

    Copyright (c) 1991, 2014, Oracle. All rights reserved.

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=uoe12c.cloudapp.net)(PORT=2626)))
    STATUS of the LISTENER
    ————————
    Alias LSNR121
    Version TNSLSNR for Linux: Version 12.1.0.2.0 – Production
    Start Date 15-JAN-2016 18:27:30
    Uptime 0 days 9 hr. 22 min. 42 sec
    Trace Level off
    Security ON: Local OS Authentication
    SNMP OFF
    Listener Parameter File /app/oracle/db/121/network/admin/listener.ora
    Listener Log File /app/oracle/diag/tnslsnr/uoe12c/lsnr121/alert/log.xml
    Listening Endpoints Summary…
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=uoe12c.cloudapp.net)(PORT=2626)))
    (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC2626)))
    The listener supports no services
    The command completed successfully
    [oracle@uoe12c ~]$

    Please help

    Thanks
    Rishi

    1. Hello Rishi,

      By default the services are registered with the listener on default port 1521 only. If listener is running on non default port, You need to explicitly registerthe services with the corresponding listener.

      Hope it helps
      Regards
      Anju

Your comments and suggestions are welcome!