Oracle Technology Sharing!!!: Connecting to Container Databases (CDB) and Pluggable Databases (PDB) in Oracle Database 12c Release 1

Connecting to a Container Database (CDB)

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.

$ export ORACLE_SID=orcl
$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Mon Aug 26 15:29:49 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>

You can connect to other common users in similar way.

SQL> CONN system/password
Connected.
SQL>

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

COLUMN name FORMAT A30

SELECT name, pdb
FROM   v$services
ORDER BY name;

NAME          PDB
------------------------------ ------------------------------
SYS$BACKGROUND         CDB$ROOT
SYS$USERS         CDB$ROOT
orcl.testbd.com                CDB$ROOT
cdb1XDB          CDB$ROOT
pdb1.testbd.com                               PDB1
pdb2.testbd.com                               PDB2

6 rows selected.

SQL>

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

$ lsnrctl service

LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 26-AUG-2013 16:00:35

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
Services Summary...
Service "orcl.testbd.com" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:45 refused:0 state:ready
         LOCAL SERVER
Service "orclXDB.testbd.com" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: 192.xxx.xxx.100, pid: 22812>
         (ADDRESS=(PROTOCOL=tcp)(HOST=192.xxx.xxx.100)(PORT=1521))
Service "pdb1.testbd.com" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:45 refused:0 state:ready
         LOCAL SERVER
Service "pdb2.testbd.com" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:45 refused:0 state:ready
         LOCAL SERVER
The command completed successfully
$

Connections using services are unchanged from previous versions.

SQL> -- EZCONNECT
SQL> CONN system/password@//localhost:1521/orcl.testbd.com
Connected.
SQL>

SQL> -- tnsnames.ora
SQL> CONN system/password@cdb1
Connected.
SQL>

The connection using a TNS alias requires an entry in the "$ORACLE_HOME/network/admin/tnsnames.ora" file, such as the one shown below.

orcl =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.xxx.xxx.100)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl.testbd.com)
    )
  )

Displaying the Current Container

The SHOW CON_NAME command in SQL*Plus displays the current container name.

SQL> SHOW CON_NAME

CON_NAME
------------------------------
CDB$ROOT
SQL>

It can also be retrieved using the SYS_CONTEXT function.

SELECT SYS_CONTEXT('USERENV', 'CON_NAME')
FROM   dual;

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

SQL>

Switching Between Containers

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.

SQL> ALTER SESSION SET container = pdb1;

Session altered.

SQL> SHOW CON_NAME

CON_NAME
------------------------------
PDB1
SQL> ALTER SESSION SET container = cdb$root;

Session altered.

SQL> SHOW CON_NAME

CON_NAME
------------------------------
CDB$ROOT
SQL>

Connecting to a Pluggable Database (PDB)

Direct connections to pluggable databases must be made using a service. Each pluggable database automatically registers a service with the listener. This is how any application will connect to a pluggable database, as well as administrative connections.

SQL> -- EZCONNECT
SQL> CONN system/password@//localhost:1521/pdb1.testbd.com
Connected.
SQL>

SQL> -- tnsnames.ora
SQL> CONN system/password@pdb1
Connected.
SQL>

The connection using a TNS alias requires an entry in the "$ORACLE_HOME/network/admin/tnsnames.ora" file, such as the one shown below.

PDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.xxx.xxx.100)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdb1.testbd.com)
    )
  )

PDB users with the SYSDBA, SYSOPER, SYSBACKUP, or SYSDG privilege can connect to a closed PDB. All other PDB users can only connect when the PDB is open. As with regular databases, the PDB users require the CONNECT SESSION privilege to enable connections.