Connecting to Container Databases (CDB) and Pluggable Databases (PDB) in Oracle Database 12c

Connecting to the root of a container database is the same as that of any previous database instance. On the database server you can use OS Authentication

[oracle@vmdb12c ~]$ sqlplus / as sysdbaSQL*Plus: Release 12.1.0.1.0 Production on Fri Sep 6 22:06:34 2013Copyright (c) 1982, 2013, Oracle.  All rights reserved.Connected to:Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit ProductionWith the Partitioning, OLAP, Advanced Analytics and Real Application Testing optionsSQL> show con_nameCON_NAME------------------------------CDB$ROOT

The V$SERVICES views can be used to display available services from the database.

SQL> select name,pdb from v$services order by 2;NAME                 PDB-------------------- ----------zwcXDB               CDB$ROOTSYS$BACKGROUND       CDB$ROOTSYS$USERS            CDB$ROOTzwc                  CDB$ROOTzhongwc1             ZHONGWC1zhongwc2             ZHONGWC26 rows selected.

The lsnrctl utility allows you to display the available services from the command line.

[oracle@vmdb12c ~]$ lsnrctl statusLSNRCTL for Linux: Version 12.1.0.1.0 - Production on 06-SEP-2013 22:10:41Copyright (c) 1991, 2013, Oracle.  All rights reserved.Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=vmdb12c)(PORT=1521)))STATUS of the LISTENER------------------------Alias                     LISTENERVersion                   TNSLSNR for Linux: Version 12.1.0.1.0 - ProductionStart Date                06-SEP-2013 19:32:46Uptime                    0 days 2 hr. 37 min. 54 secTrace Level               offSecurity                  ON: Local OS AuthenticationSNMP                      OFFListener Parameter File   /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.oraListener Log File         /u01/app/oracle/diag/tnslsnr/vmdb12c/listener/alert/log.xmlListening Endpoints Summary...  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vmdb12c)(PORT=1521)))  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))Services Summary...Service "zhongwc1" has 1 instance(s).  Instance "zwc", status READY, has 1 handler(s) for this service...Service "zhongwc2" has 1 instance(s).  Instance "zwc", status READY, has 1 handler(s) for this service...Service "zwc" has 1 instance(s).  Instance "zwc", status READY, has 1 handler(s) for this service...Service "zwcXDB" has 1 instance(s).  Instance "zwc", status READY, has 1 handler(s) for this service...The command completed successfully

[oracle@vmdb12c ~]$ lsnrctl serviceLSNRCTL for Linux: Version 12.1.0.1.0 - Production on 06-SEP-2013 22:10:45Copyright (c) 1991, 2013, Oracle.  All rights reserved.Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=vmdb12c)(PORT=1521)))Services Summary...Service "zhongwc1" has 1 instance(s).  Instance "zwc", status READY, has 1 handler(s) for this service...    Handler(s):      "DEDICATED" established:5 refused:0 state:ready         LOCAL SERVERService "zhongwc2" has 1 instance(s).  Instance "zwc", status READY, has 1 handler(s) for this service...    Handler(s):      "DEDICATED" established:5 refused:0 state:ready         LOCAL SERVERService "zwc" has 1 instance(s).  Instance "zwc", status READY, has 1 handler(s) for this service...    Handler(s):      "DEDICATED" established:5 refused:0 state:ready         LOCAL SERVERService "zwcXDB" has 1 instance(s).  Instance "zwc", status READY, has 1 handler(s) for this service...    Handler(s):      "D000" established:0 refused:0 current:0 max:1022 state:ready         DISPATCHER <machine: vmdb12c, pid: 4294>         (ADDRESS=(PROTOCOL=tcp)(HOST=vmdb12c)(PORT=34716))The command completed successfully

Connections using services are unchanged from previous versions.

easy connect

[oracle@vmdb12c ~]$ sqlplus system/oracle@192.168.1.14:1521/zhongwc1SQL*Plus: Release 12.1.0.1.0 Production on Fri Sep 6 22:13:37 2013Copyright (c) 1982, 2013, Oracle.  All rights reserved.Last Successful login time: Fri Sep 06 2013 22:13:31 +08:00Connected to:Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit ProductionWith the Partitioning, OLAP, Advanced Analytics and Real Application Testing optionsSQL> show pdbs    CON_ID CON_NAME                       OPEN MODE  RESTRICTED---------- ------------------------------ ---------- ----------         3 ZHONGWC1                       READ WRITE NOSQL> show con_nameCON_NAME------------------------------ZHONGWC1

[oracle@vmdb12c ~]$ sqlplus system/oracle@192.168.1.14:1521/zhongwc2SQL*Plus: Release 12.1.0.1.0 Production on Fri Sep 6 22:13:48 2013Copyright (c) 1982, 2013, Oracle.  All rights reserved.Last Successful login time: Fri Sep 06 2013 21:26:00 +08:00Connected to:Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit ProductionWith the Partitioning, OLAP, Advanced Analytics and Real Application Testing optionsSQL> show pdbs    CON_ID CON_NAME                       OPEN MODE  RESTRICTED---------- ------------------------------ ---------- ----------         4 ZHONGWC2                       READ WRITE NOSQL> show con_nameCON_NAME------------------------------ZHONGWC2

TNS

[oracle@vmdb12c ~]$ sqlplus system/oracle@zhongwc1SQL*Plus: Release 12.1.0.1.0 Production on Fri Sep 6 22:15:42 2013Copyright (c) 1982, 2013, Oracle.  All rights reserved.Last Successful login time: Fri Sep 06 2013 22:13:37 +08:00Connected to:Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit ProductionWith the Partitioning, OLAP, Advanced Analytics and Real Application Testing optionsSQL> show pdbs    CON_ID CON_NAME                       OPEN MODE  RESTRICTED---------- ------------------------------ ---------- ----------         3 ZHONGWC1                       READ WRITE NOSQL> show con_nameCON_NAME------------------------------ZHONGWC1

[oracle@vmdb12c ~]$ sqlplus system/oracle@zhongwc2SQL*Plus: Release 12.1.0.1.0 Production on Fri Sep 6 22:15:47 2013Copyright (c) 1982, 2013, Oracle.  All rights reserved.Last Successful login time: Fri Sep 06 2013 22:13:48 +08:00Connected to:Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit ProductionWith the Partitioning, OLAP, Advanced Analytics and Real Application Testing optionsSQL> show con_nameCON_NAME------------------------------ZHONGWC2SQL> show pdbs    CON_ID CON_NAME                       OPEN MODE  RESTRICTED---------- ------------------------------ ---------- ----------         4 ZHONGWC2                       READ WRITE NO

tnsnames.ora

ZWC =  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = vmdb12c)(PORT = 1521))    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = zwc)    )  )zhongwc1 =  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = vmdb12c)(PORT = 1521))    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = zhongwc1)    )  )zhongwc2 =  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = vmdb12c)(PORT = 1521))    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = zhongwc2)    )  )LISTENER_ZWC =  (ADDRESS = (PROTOCOL = TCP)(HOST = vmdb12c)(PORT = 1521))

When logged in to the CDB as an appropriately privileged user, the ALTER SESSION command can be used to switch between containers within the container database.

[oracle@vmdb12c ~]$ sqlplus / as sysdbaSQL*Plus: Release 12.1.0.1.0 Production on Fri Sep 6 22:17:52 2013Copyright (c) 1982, 2013, Oracle.  All rights reserved.Connected to:Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit ProductionWith the Partitioning, OLAP, Advanced Analytics and Real Application Testing optionsSQL> alter session set container=zhongwc1;Session altered.SQL> show pdbs    CON_ID CON_NAME                       OPEN MODE  RESTRICTED---------- ------------------------------ ---------- ----------         3 ZHONGWC1                       READ WRITE NOSQL> alter session set container=zhongwc2;Session altered.SQL> show pdbs    CON_ID CON_NAME                       OPEN MODE  RESTRICTED---------- ------------------------------ ---------- ----------         4 ZHONGWC2                       READ WRITE NOSQL> alter session set container=cdb$root;Session altered.SQL> show pdbs    CON_ID CON_NAME                       OPEN MODE  RESTRICTED---------- ------------------------------ ---------- ----------         2 PDB$SEED                       READ ONLY  NO         3 ZHONGWC1                       READ WRITE NO         4 ZHONGWC2                       READ WRITE NO

SQL> select sys_context('USERENV','CON_NAME') from dual;SYS_CONTEXT('USERENV','CON_NAME')--------------------------------------------------------------------------------CDB$ROOTSQL> alter session set container=zhongwc1;Session altered.SQL> select sys_context('USERENV','CON_NAME') from dual;SYS_CONTEXT('USERENV','CON_NAME')--------------------------------------------------------------------------------ZHONGWC1SQL> alter session set container=zhongwc2;Session altered.SQL> select sys_context('USERENV','CON_NAME') from dual;SYS_CONTEXT('USERENV','CON_NAME')--------------------------------------------------------------------------------ZHONGWC2