Multi-tenant Architecture

Oracle Database 12c introduced a new feature called “Multitenant.”

The multitenant feature provides the ability for a single instance to manage multiple databases. The multitenant architecture enables an Oracle database to function as a multitenant container database (CDB).

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 non-schema objects that appears to an Oracle Net client as a non-CDB. 

Oracle Multitenant offers the ability to have up to 252 PDBs per multitenant container database. The multitenant architecture with one user-created pluggable database (single tenant) is available in all editions without the multitenant option.

A CDB includes the following components:

Root Container (CDB$ROOT)

The root, named CDB$ROOT, stores Oracle-supplied metadata and common users. This is the database that is created when that database supports Oracle’s multitenant option.

The root container is a collection of schemas, schema objects, and non-schema objects to which all PDBs belong. Every CDB has one root container, which stores the system metadata which is required to manage PDBs. All PDBs belongs to the root.

A CDB has exactly one root.

The root does not store user data. Thus, you must not add user data to the root or modify system supplied schemas in the root.

Seed (PDB$SEED)

The seed, named PDB$SEED, is a template that you can use to create new PDBs. You cannot add objects to or modify objects in the seed. A CDB has exactly one seed.

You can add or modify objects in PDB$SEED.

Pluggable Database

A pluggable database (PDB) is a portable collection of schemas, schema objects, and nonschema objects that appears to an Oracle Net client as a non-CDB.

PDBs can be plugged into to CDBs. A CDB can contain multiple PDBs. Each PDB appears on the network as a separate database.

Examples of container data objects are Oracle-supplied views whose names begin with V$ and CDB_.

IMPORTANT FILES

CONTROL FILE: It is common for CDB and all PDBs.

REDOLOG FILE: Online redologs are common for both CDB and PDB. But make sure to keep the size of logs are more.

ARCHIVELOG: This mode itself is done at the level of the CDB. You cannot opt to disable ARCHIVELOG mode for individual PDBs.

SYSTEM TABLESPACE: There is a separate SYSTEM tablespace for the root and for each PDB.

SYSAUX TABLESPACE: There is a separate SYSAUX tablespace for the root and for each PDB.

TEMP TABLESPACE: A PDB can either have its owner temporary tablespace, or if it is created without a temporary tablespace, it can share the temporary tablespace with the CBD.

UNDO TABLESPACE: PDB can’t have an undo tablespace. Instead, it uses the undo tablespace belonging to the CDB. If we connect to a PDB, we can see no undo tablespace is visible.

Upto Oracle 12cR1- Each PDB uses shared UNDO ( undo space from CDB) and From 12cR2 onwards each PDB has it’s own UNDO and temp tablespaces.

CHARACTERSET: There will only one characterset for the complete CDB. All the PDBs will use same characterset. For new multitenant database installation, Oracle recommends to user AL32UTF8.

ALERT LOG: There will only one alert log for the container database. All log info about the PDBs will be written to the same alert log.

PASSWORD FILE: There will be only one password file for the container database. All PDBs will use the same password file.

Find the list of PDBs present in multitenant database:

SQL> select CON_ID, NAME, OPEN_MODE from V$PDBS;

CON_ID  NAME  OPEN_MODE
------------------------
2 PDB$SEED READ ONLY
3 PDB1 READ WRITE
4 PDB2 READ WRITE

What is this CON_ID?

CON_ID – 0 = The data pertains to the entire CDB.
CON_ID – 1= The data pertains to the root container (CDB$ROOT)
CON_ID – 2= The data pertains to the seed ( PDB$SEED)
CON_ID – 3 onwards = The data pertains to a PDB, Each PDB has its own container ID.

Find Number of PDBs:

SQL> show pdbs;

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ----------------------------------------
	 2 PDB$SEED			  READ ONLY  NO
	 3 ORCL 			  READ WRITE NO

Connect to PDB & find to which PDB we are currently connected:

[oracle@dg11 ~]$ sqlplus username/password@pdbname

SQL> 
SQL> show pdbs;
    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 ORCL 			  READ WRITE NO
SQL> 
SQL> 
SQL> ALTER SESSION SET CONTAINER=ORCL;
Session altered.

SQL> SHOW CON_NAME
CON_NAME
------------------------------
ORCL

SQL> SHOW CON_ID
CON_ID
------------------------------
3
SQL> 
SQL> select sys_context ( 'Userenv', 'Con_Name') "Container DB" from dual;
Container DB
--------------------------------------------------------------------------------
ORCL

How to connect to container database:(CDB)

SQL> alter session set container=CDB$ROOT;
Session altered.

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

SQL> 
SQL> SHOW CON_ID
CON_ID
------------------------------
1