How to connect Oracle 12c database (CDB and PDB)
Posted by Mir Sayeed Hassan on October 14th, 2017
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=======