12c New Feature - Container and Pluggable Databases (Multitenant Option)

Container and Pluggable databases is one of the main new features introduced in 12c nd is referred to as the Multitenant Option.
A Container database (CDB) is made up of the following containers:
There is one root container which stores the Oracle supplied metadata like the PL/SQL data dictionary packages and the common users. This root container is referred to as CDB$ROOT.
One seed Pluggable Database (PDB) which is a system supplied template which can be used to create new PDB’s. This seed PDB is called PDB$SEED.
None or more user created PDBs.
In one of my earlier posts, we had seen how to create a container database (CDB) using the 12c DBCA tool.
A container database can also be created via the SQL statement
CREATE DATABASE ... ENABLE PLUGGABLE DATABASE
Lets take a look at some of the characteristics of a CDB

• There is a separate SYSTEM and SYSAUX tablespace for the root container of the CDB and each PDB
• Thee is only one UNDO tablespace for the entire CDB
• There is only one set of control files and online redo logs files for the entire CDB. Individual PDB’s have their own data files (which contain the user data), but do not have distinct redo log or control files.
• We can create on default temporary tablespace for the entire CDB or each PDB can have its own additional temporary tablespaces
• There is single network administration files like listener.ora, tnsnames.ora, and sqlnet.ora file for an entire CDB. All of the PDBs in the CDB use the same files.
• There is only one set of background processes shared by the root and all PDBs
• There is a single SGA shared by all  PDB’s
• When the CDB is shut down then all PDB’s are also automatically shut down
• When the CDB is open then we can change the open mode of individual PDBs via the ALTER PLUGGABLE DATABASE SQL statement.

Let us look at some examples of using CDBs and PDBs.
In this case we have a CDB called cdb12c and the PDB name is testdb1
Connect to the CDB – by default we are connected to the root container – the container id is 1

[oracle@kens-orasql-001-dev ~]$ sqlplus sys/password@localhost:1525/cdb12c as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Mon Jul 1 11:07:52 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> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> show con_id

CON_ID
------------------------------
1

Now connect to the PDB testdb1 – note the container id is 3 ( 2 is the container id for PDB$SEED)

[oracle@kens-orasql-001-dev ~]$ sqlplus sys/password@localhost:1525/testdb1 as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Mon Jul 1 11:09:47 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> show con_name

CON_NAME
------------------------------
TESTDB1

SQL> show con_id

CON_ID
------------------------------
3

When the container database is started up, by default the pluggable databases are not automatically opened but remain in a mount state

SQL> select name, open_mode from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
TESTDB1                        MOUNTED

SQL> alter pluggable database testdb1 open;

Pluggable database altered.

SQL> select name, open_mode from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
TESTDB1                        READ WRITE

Note the datafiles shown by V$DATAFILE view when we are connected to the root container.
We see the datafiles for the root container CDB$ROOT, the PDB$SEED PDB and the testdb1 PDB

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/cdb12c/system01.dbf
/u01/app/oracle/oradata/cdb12c/sysaux01.dbf
/u01/app/oracle/oradata/cdb12c/undotbs01.dbf
/u01/app/oracle/oradata/cdb12c/pdbseed/system01.dbf
/u01/app/oracle/oradata/cdb12c/users01.dbf
/u01/app/oracle/oradata/cdb12c/pdbseed/sysaux01.dbf
/u01/app/oracle/oradata/cdb12c/testdb1/system01.dbf
/u01/app/oracle/oradata/cdb12c/testdb1/sysaux01.dbf
/u01/app/oracle/oradata/cdb12c/testdb1/SAMPLE_SCHEMA_users01.dbf
/u01/app/oracle/oradata/cdb12c/testdb1/example01.dbf

Connected to the root container, we can see the datafiles of the seed PDB

SQL> select name from v$datafile where con_id=2;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/cdb12c/pdbseed/system01.dbf
/u01/app/oracle/oradata/cdb12c/pdbseed/sysaux01.dbf

Now when connected to the PDB testdb1, the V$DATAFILE only displays the datafiles belonging to that individual PDB

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/cdb12c/undotbs01.dbf
/u01/app/oracle/oradata/cdb12c/testdb1/system01.dbf
/u01/app/oracle/oradata/cdb12c/testdb1/sysaux01.dbf
/u01/app/oracle/oradata/cdb12c/testdb1/SAMPLE_SCHEMA_users01.dbf
/u01/app/oracle/oradata/cdb12c/testdb1/example01.dbf

Each of the CDB and PDBs have their own temp files

SQL> select con_id,name from v$tempfile;

CON_ID NAME
---------- --------------------------------------------------------------------------------
         1 /u01/app/oracle/oradata/cdb12c/temp01.dbf
         2 /u01/app/oracle/oradata/cdb12c/pdbseed/pdbseed_temp01.dbf
         3 /u01/app/oracle/oradata/cdb12c/testdb1/testdb1_temp01.dbf

Note – there is only one SGA!

[oracle@kens-orasql-001-dev ~]$ sqlplus sys as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Tue Jul 9 10:25:23 2013

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

Enter password:

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> show sga

Total System Global Area 4275781632 bytes
Fixed Size                  2296576 bytes
Variable Size            2936014080 bytes
Database Buffers         1325400064 bytes
Redo Buffers               12070912 bytes

Connect to the PDB – SGA is the same

SQL> ALTER SESSION SET CONTAINER =testdb1;

Session altered.

SQL> show sga

Total System Global Area 4275781632 bytes
Fixed Size                  2296576 bytes
Variable Size            2936014080 bytes
Database Buffers         1325400064 bytes
Redo Buffers               12070912 bytes

Controlfiles and Online Redo Log files are only one for CDB as well as PDB

[oracle@kens-orasql-001-dev ~]$ sqlplus sys as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Tue Jul 9 11:04:10 2013

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

Enter password:

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> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/cdb12c/control01.ctl
/u01/app/oracle/fast_recovery_area/cdb12c/control02.ctl

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/cdb12c/redo03.log
/u01/app/oracle/oradata/cdb12c/redo02.log
/u01/app/oracle/oradata/cdb12c/redo01.log

SQL>  ALTER SESSION SET CONTAINER =testdb1;

Session altered.

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/cdb12c/control01.ctl
/u01/app/oracle/fast_recovery_area/cdb12c/control02.ctl

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/cdb12c/redo03.log
/u01/app/oracle/oradata/cdb12c/redo02.log
/u01/app/oracle/oradata/cdb12c/redo01.log