Mir Sayeed Hassan – Oracle Blog

How to connect Oracle 12c database (CDB and PDB) 

• Container Database (CDB)
• Pluggable Database (PDB)

[oracle@ora12c ~]$ !sq
sqlplus / as sysdba

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> select sys_context('USERENV','CON_NAME') from dual;

SYS_CONTEXT('USERENV','CON_NAME')
--------------------------------------------------------------------------------
CDB$ROOT

SQL> select name, cdb, con_id from v$database;

NAME      CDB     CON_ID
--------- --- ----------
ORA12C    YES          0

SQL> select instance_name, status, con_id from v$instance;

INSTANCE_NAME    STATUS           CON_ID
---------------- ------------ ----------
ora12c           OPEN                  0

 Find the list of PDB are created in database

SQL> select name,pdb from v$services order by 2;

NAME       PDB
---------------------------------------------
ora12cXDB CDB$ROOT
SYS$BACKGROUND CDB$ROOT
SYS$USERS CDB$ROOT
ora12c.local CDB$ROOT
pdb1.local PDB1
pdb2.local PDB2
6 rows selected.

Verify the listerner & services are available in database

[oracle@ora12c admin]$ pwd
/u02/app/oracle/product/12.1.0.2/db_1/network/admin

[oracle@ora12c admin]$ ls
listener.ora  samples  shrept.lst  tnsnames.ora

[oracle@ora12c admin]$ lsnrctl

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 10-OCT-2017 17:27:30
Copyright (c) 1991, 2014, Oracle.  All rights reserved.
Welcome to LSNRCTL, type "help" for information.

LSNRCTL> status

 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.20.*.**)(PORT=1521)))

STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                09-JUL-2017 12:00:25
Uptime                    93 days 6 hr. 27 min. 6 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u02/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/ora12c/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.20.*.**)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=ora12c..local)(PORT=5500))(Security=(my_wallet_directory=/u02/app/oracle/admin/ora12c/xdb_wallet))(Presentation=HTTP)(Session=RAW))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora12c..local)(PORT=5510))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "ora12c..local" has 1 instance(s).
Instance "ora12c", status READY, has 1 handler(s) for this service...
Service "ora12cXDB..local" has 1 instance(s).
Instance "ora12c", status READY, has 1 handler(s) for this service...
Service "pdb1..local" has 1 instance(s).
Instance "ora12c", status READY, has 1 handler(s) for this service...
Service "pdb2..local" has 1 instance(s).
Instance "ora12c", status READY, has 1 handler(s) for this service...
The command completed successfully

LSNRCTL> service

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.20.*.**)(PORT=1521)))
Services Summary...
Service "ora12c..local" has 1 instance(s).
Instance "ora12c", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:34418 refused:0 state:ready
LOCAL SERVER
Service "ora12cXDB..local" has 1 instance(s).
Instance "ora12c", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: ora12c..local, pid: 6988>
(ADDRESS=(PROTOCOL=tcp)(HOST=ora12c..local)(PORT=57454))
Service "pdb1..local" has 1 instance(s).
Instance "ora12c", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:34418 refused:0 state:ready
LOCAL SERVER
Service "pdb2..local" has 1 instance(s).
Instance "ora12c", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:34418 refused:0 state:ready
LOCAL SERVER
The command completed successfully

Connecting to PDB Databases from the CDB

CONNECT TO PDB1:

SQL> conn system/stmora12c@PDB1
Connected.

Status of PDB1 DB

SQL> select NAME, OPEN_MODE from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB1                           READ WRITE

SWITCH/CONNECT TO PDB2:

SQL> alter session set container=PDB2;
Session altered.

Status of PDB1 DB

SQL> select NAME, OPEN_MODE from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB2                           READ WRITE

SQL> select sys_context('USERENV','CON_NAME') from dual;

SYS_CONTEXT('USERENV','CON_NAME')
--------------------------------------------------------------------------------
PDB2

[oracle@ora12c admin]$ cat tnsnames.ora

ORA12C =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.20.*.**)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ora12c)
(UR = A)
)
)
PDB1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.20.*.**)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = pdb1..local)
(UR = A)
)
)
PDB2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.20.*.**)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = pdb2.local)
(UR = A)
)
)

======Hence connected to the CDB & PDB in our test env & verified=======