12c Database : CDB Overview

From the release 12c Database, the new architecture has been introduced similar to MS-SQL concepts a master database and the multiple databases attached to that master database shares a single SGA.

In same way Oracle Introduced multitenant architecture where the multiple databases share same SGA and all databases are interlinked to master database called Root (CDB$root) databases which the whole things called as CDB.

The CDB components are:-

• Root
  The root, named CDB$ROOT, stores Oracle-supplied metadata and common users. An example of metadata is the source code for Oracle-supplied PL/SQL packages. A common user is a database user known in every container. A CDB has exactly one root.
• 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.
• PDBs
  A PDB appears to users and applications as if it were a non-CDB. For example, a PDB can contain the data and code required to support a specific application. A PDB is fully backward compatible with Oracle Database releases before Oracle Database 12c.

The all above is called a container, Therefore, the root is a container, the seed is a container, and each PDB is a container. Each container has a unique container ID and name within a CDB

When you create the database with multitenant option as like in below screenshot when using DBCA

  or Using Create Database Command with option ‘enable pluggable database’ as like below

CREATE DATABASE newcdb
  USER SYS IDENTIFIED BY sys_password
  USER SYSTEM IDENTIFIED BY system_password
  LOGFILE GROUP 1 ('/u01/logs/my/redo01a.log','/u02/logs/my/redo01b.log')
             SIZE 100M BLOCKSIZE 512,
          GROUP 2 ('/u01/logs/my/redo02a.log','/u02/logs/my/redo02b.log')
             SIZE 100M BLOCKSIZE 512,
          GROUP 3 ('/u01/logs/my/redo03a.log','/u02/logs/my/redo03b.log')
             SIZE 100M BLOCKSIZE 512
  MAXLOGHISTORY 1
  MAXLOGFILES 16
  MAXLOGMEMBERS 3
  MAXDATAFILES 1024
  CHARACTER SET AL32UTF8
  NATIONAL CHARACTER SET AL16UTF16
  EXTENT MANAGEMENT LOCAL
  DATAFILE '/u01/app/oracle/oradata/newcdb/system01.dbf'
    SIZE 700M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
  SYSAUX DATAFILE '/u01/app/oracle/oradata/newcdb/sysaux01.dbf'
    SIZE 550M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
  DEFAULT TABLESPACE deftbs
     DATAFILE '/u01/app/oracle/oradata/newcdb/deftbs01.dbf'
     SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
  DEFAULT TEMPORARY TABLESPACE tempts1
     TEMPFILE '/u01/app/oracle/oradata/newcdb/temp01.dbf'
     SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
  UNDO TABLESPACE undotbs1
     DATAFILE '/u01/app/oracle/oradata/newcdb/undotbs01.dbf'
     SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
  ENABLE PLUGGABLE DATABASE
    SEED
    FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/newcdb/',   '/u01/app/oracle/oradata/pdbseed/')
    SYSTEM DATAFILES SIZE 125M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
    SYSAUX DATAFILES SIZE 100M
  USER_DATA TABLESPACE usertbs
    DATAFILE '/u01/app/oracle/oradata/pdbseed/usertbs01.dbf'
    SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

So this is how to create a container database, now some important clauses here

The above command creates a two databases with one SGA, A root database and the Seed database for Future Pluggable databases.

A fresh CDB Database could like this.

It contains two databases initially, A Root and PDB$Seed which is a seed template database.

Notes for root Database:-

1. A CDB (root) database with no PDB’s inside is just like a normal database only.

2. Each CDB (root) has its own datafiles, (System,Sysaux,Tempfiles, Undo)

3. Representation of databases will be managed by CON_ID column in all the dynamic views or base tables, and the root database is always 0

Notes for PDB$SEED database:-

1. This does contains structures that can be used as a template for the new pluggable databases created ahead.

2. No data should be resided on created on PDB$SEED Database

When you create a pluggable databases in a CDB the diagram show how it could be

Creating PDB Example:-

Example:1 , Simple PDB without any clauses

SQL> CREATE PLUGGABLE DATABASE racpdb ADMIN USER racpdb IDENTIFIED BY password;

Example: 2. Simple PDB without any clauses except the new role should granted to admin user

SQL> CREATE PLUGGABLE DATABASE racpdb ADMIN USER racpdb IDENTIFIED BY password ROLES=(DBA);

Example 3: Simple PDB with all clauses,

• This PDB tablespaces should not grow more than 2g
• Should use the temp tablespace of ROOT of 100m only
• A default tablespaces racpdb is created
• PATH Prefix , any directory prefixes to make this database files to go in one folder
• FILE_NAME_CONVERT, as there is no OMF and no PDB_FILE_NAME_CONVERT set at parameter , file_name_convert parameter should specify, here the example to create the pdb from the see, so we have to give the seed database the destination directory

SQL> CREATE PLUGGABLE DATABASE racdb ADMIN USER racadm IDENTIFIED BY password
  STORAGE (MAXSIZE 2G MAX_SHARED_TEMP_SIZE 100M)
  DEFAULT TABLESPACE racdb 
    DATAFILE '/disk1/oracle/dbs/racpdb/sales01.dbf' SIZE 250M AUTOEXTEND ON
  PATH_PREFIX = '/disk1/oracle/dbs/racpdb/'
  FILE_NAME_CONVERT = ('/disk1/oracle/dbs/pdbseed/', '/disk1/oracle/dbs/racdb/');

Notes for PDB databases:-

1. Well this is pretty straight ( I would like to call them schemas rather databases) which contains its own set of datafiles (System, Sysaux, Temp, Users)

2. They share rest of structures from the CDB root. (BG Process, datadictionary for awr etc, User profiles etc)

3. A link between root to pdb database will be created called metalink which used to communicate between root and pdb (for example querying a user from local PDB to root database cdb_users)

4. If you create a object on pdb that is local to pdb not for rest of PDB’s

In my instance, this is how it looks like with Container of Root, Seed and a PDB

Viewing CDB and all containers

Viewing PDB Status Viewing Datafiles for all CDB & PDB

COLUMN PDB_ID FORMAT 999
COLUMN PDB_NAME FORMAT A8
COLUMN FILE_ID FORMAT 9999
COLUMN TABLESPACE_NAME FORMAT A10
COLUMN FILE_NAME FORMAT A45

SELECT p.PDB_ID, p.PDB_NAME, d.FILE_ID, d.TABLESPACE_NAME, d.FILE_NAME
  FROM DBA_PDBS p, CDB_DATA_FILES d
  WHERE p.PDB_ID = d.CON_ID
  ORDER BY p.PDB_ID;

Some facts about Container databases:-

1) Parameter , Redo, Undo is only one for all databases

2) Common users and local users can be created for ease of use

3) DBA’s can be further segregated into PDB_DBA or CDB_DBA

4) All dictionary objects have been added a column called con_id to distinguish the databases

Lot more to discuss

Next:- Understanding how data dictionary works in view of multiple databases sharing single SGA

Sources: The great blogs in the internet, (see left side of my blog roll)

Source: Oracle Online Documentation

-Thanks

Geek DBA