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

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

We can use the Oracle Multitenant option to configure and manage a multitenant environment. The multitenant architecture enables an Oracle database to function as a multitenant container database (CDB) that includes zero, one, or many customer-created pluggable databases (PDBs).

A PDB is a portable collection of schemas, schema objects, and nonschema objects that appears to an Oracle Net client as a non-CDB. All Oracle databases before Oracle Database 12c were non-CDBs.

The multitenant option represents one of the biggest architectural changes in the history of the Oracle database. The option introduced the concepts of the Container Database (CDB) and Pluggable Database (PDB).

• Container Database (CDB): On the surface this seems very similar to a conventional Oracle database, as it contains most of the working parts you will be already familiar with (control files, data files, undo, temp files, redo logs etc.). It also houses the data dictionary for those objects that are owned by the root container and those that are visible to all PDBs.

• Pluggable Database (PDB): Since the CDB contains most of the working parts for the database, the PDB only needs to contain information specific to itself. It does not need to worry about control files, redo logs and undo etc. Instead it is just made up of data files and temp files to handle it's own objects. This includes it's own data dictionary, containing information about only those objects that are specific to the 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 we 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

You can connect to other common users in similar way.

SQL> CONN system/password

Connected.

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                              CDB$ROOT

orclXDB                       CDB$ROOT

pdborcl                        PDBORCL

5 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 10-JUL-2016 07:01:34

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))

Services Summary...

Service "orcl" has 1 instance(s).

  Instance "orcl", status READY, has 1 handler(s) for this service...

    Handler(s):

      "DEDICATED" established:0 refused:0 state:ready

         LOCAL SERVER

Service "orclXDB" 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

         (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=21196))

Service "pdborcl" has 1 instance(s).

  Instance "orcl", status READY, has 1 handler(s) for this service...

    Handler(s):

      "DEDICATED" established:0 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

Connected.

SQL> -- tnsnames.ora

SQL> CONN system/password@orcl

Connected.

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 = localhost)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)

    )

  )

Displaying the Current Container

The SHOW CON_NAME and SHOW CON_ID commands in SQL*Plus display the current container name and ID respectively.

SQL> SHOW CON_NAME

CON_NAME

------------------------------

CDB$ROOT

SQL> SHOW CON_ID

CON_ID

------------------------------

1

They can also be retrieved using the SYS_CONTEXT function.

SELECT SYS_CONTEXT('USERENV', 'CON_NAME')

FROM   dual;

SYS_CONTEXT('USERENV','CON_NAME')

--------------------------------------------------------------------------------

CDB$ROOT

SELECT SYS_CONTEXT('USERENV', 'CON_ID')

FROM   dual;

SYS_CONTEXT('USERENV','CON_ID')

--------------------------------------------------------------------------------

1

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 = pdborcl;

Session altered.

SQL> SHOW CON_NAME

CON_NAME

------------------------------

PDBORCL

SQL> ALTER SESSION SET container = cdb$root;

Session altered.

SQL> SHOW CON_NAME

CON_NAME

------------------------------

CDB$ROOT

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/pdborcl

Connected.

SQL> -- tnsnames.ora

SQL> CONN system/password@pdborcl

Connected.

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

PDBORCL =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = pdborcl)

    )

  )

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 CREATE SESSION privilege to enable connections.

Note:  In a manual 'startup' Oracle will only start the CDB and not the PDBs unless we have created a trigger to start them automatically. If we created the trigger then Oracle will also start the PDBs one at a time and, depending on how many we have, that can take a while.

In case of 12c when CDB (Container Database) is started/Open, All the Pluggable databases are not started automatically, instead all PDB's will be mount Status except SEED Database which will be Read only mode.

When connecting to PDB (Pluggable Database), we will receive the following error if PDB is not open

ERROR:

ORA-01033: ORACLE initialization or shutdown in progress

Process ID: 0

Session ID: 0

Serial number: 0

Let's check CDB and PDB database status

select instance_name,status from v$instance;

INSTANCE_NAME           STATUS

---------------- -----------------------------

orcl                                    OPEN

select name,con_id,open_mode from v$pdbs;

NAME                      CON_ID         OPEN_MODE

—————————— ———-—————————— ———-

PDB$SEED                        2            READ ONLY

PDBORCL                          3            MOUNTED

So the PDBORCL is only mounted not open although the instance is up and the container CDB is open.

To find the name of the service we will use v$active_services view. In most of the cases oracle software creates database and its service with the same name.

SELECT name FROM v$active_services WHERE con_id = 3;

So we need to open the PDB:

As DBA we need to open the Pluggable database PDBORCL to allow access to users

1)    sqlplus  /  as  sysdba

2)    ALTER  SESSION  SET  CONTAINER  =  pdborcl;

3)    SHOW  con_name;

4)    ALTER  PLUGGABLE  DATABASE  open;

   OR

 ALTER PLUGGABLE DATABASE PDBORCL OPEN;

  OR

 ALTER PLUGGABLE DATABASE ALL OPEN;

5)    Let's check PDB database status

select name,con_id,open_mode from v$pdbs;

NAME           CON_ID         OPEN_MODE

—————————— ———-—————————— ———-

PDB$SEED                            2            READ ONLY

PDBORCL                              3            READ WRITE

6)   CONN system/system@pdborcl

   OR

CONN system/system@//localhost:1521/pdborcl

Connected.

Unlock the HR user on PDB

ALTER USER hr IDENTIFIED BY hr ACCOUNT unlock;

Login to PDB database using HR user

conn  hr/hr@pdborcl

To avoid starting PDB every time when Container database is started, we can write a trigger to open all the pluggable databases.

Create or replace trigger OPEN_PDBS

AFTER STARTUP ON DATABASE

Begin

Execute immediate ‘ALTER PLUGGABLE DATABASE ALL OPEN';

End;

/

We get different results when we query dba_data_files depending on whether connected to the CDB or PDB:

1)    sqlplus  /  as  sysdba

2)    select file_name from dba_data_files;

CDB

FILE_NAME                                

------------------------------------------

/u01/app/oracle/oradata/orcl/system01.dbf

/u01/app/oracle/oradata/orcl/sysaux01.dbf

/u01/app/oracle/oradata/orcl/undotbs01.dbf

/u01/app/oracle/oradata/orcl/users01.dbf

3)         CONN system/system@pdborcl

            Connected.

4)         select file_name from dba_data_files;

PDB

FILE_NAME                                                     

--------------------------------------------------------------

/u01/app/oracle/oradata/orcl/pdborcl/system01.dbf            

/u01/app/oracle/oradata/orcl/pdborcl/sysaux01.dbf            

/u01/app/oracle/oradata/orcl/pdborcl/SAMPLE_SCHEMA_users01.dbf

/u01/app/oracle/oradata/orcl/pdborcl/example01.dbf

JDBC Connections to PDBs

It has already been mentioned that you must connect to a PDB using a service. This means that by default many JDBC connect strings will be broken. Valid JDBC connect strings for Oracle use the following format.

# Syntax

jdbc:oracle:thin:@[HOST][:PORT]:SID

jdbc:oracle:thin:@[HOST][:PORT]/SERVICE

# Example

jdbc:oracle:thin:@localhost:1521:pdborcl

jdbc:oracle:thin:@localhost:1521/pdborcl

When attempting to connect to a PDB using the SID format, we will receive the following error.

ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

Ideally, we would correct the connect string to use services instead of SIDs, but if that is a problem the USE_SID_AS_SERVICE_listener_name listener parameter can be used.

Edit the "$ORACLE_HOME/network/admin/listener.ora" file, adding the following entry, with the "listener" name matching that used by our listener.

USE_SID_AS_SERVICE_listener=on

Reload or restart the listener.

$ lsnrctl reload

Now both of the following connection attempts will be successful as any SIDs will be treated as services.

jdbc:oracle:thin:@localhost:1521:pdborcl

jdbc:oracle:thin:@localhost:1521/pdborcl

  

I hope you all have enjoyed reading this article. Comments are welcome....