Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | Linux

Multitenant : Connecting to Container Databases (CDB) and Pluggable Databases (PDB) in Oracle Database 12c Release 1 (12.1)

The multitenant option introduced in Oracle Database 12c allows a single container database (CDB) to host multiple separate pluggable databases (PDB). This article describes how to connect to container databases (CDB) and pluggable databases (PDB).

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=cdb1
$ 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
cdb1.localdomain	       CDB$ROOT
cdb1XDB 		       CDB$ROOT
pdb1.localdomain	       PDB1
pdb2.localdomain	       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 "cdb1.localdomain" has 1 instance(s).
  Instance "cdb1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:45 refused:0 state:ready
         LOCAL SERVER
Service "cdb1XDB.localdomain" has 1 instance(s).
  Instance "cdb1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: ol6-121.localdomain, pid: 22812>
         (ADDRESS=(PROTOCOL=tcp)(HOST=ol6-121.localdomain)(PORT=39902))
Service "pdb1.localdomain" has 1 instance(s).
  Instance "cdb1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:45 refused:0 state:ready
         LOCAL SERVER
Service "pdb2.localdomain" has 1 instance(s).
  Instance "cdb1", 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/cdb1.localdomain
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.

CDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ol6-121.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cdb1.localdomain)
    )
  )

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.localdomain
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 = ol6-121.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdb1.localdomain)
    )
  )

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.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.