CDB and PDB concepts in Oracle 12c

Oracle introduced PDB from 12c to minimize following drawbacks in 11g

 

In 11g,sometimes we need to create numerous databases in single host which resulted below side effects and eventually slowed down whole system.

1.Too many background processes.

2.High shared memory.

3.Many copies of oracle metadata.

Now We will delve into more deeper what Oracle 12c PDB concepts bring to us

1.Multiple databases in centralized managed platform.

a.Less instance overhead.

b.Less storage.

2.Fast and easy provisioning.

3.Time savings for patch and upgrade.

4.Separation of duties.

5.Ensure full backward compatibility with Non-CDB.

6.Fully operate on RAC.

Naming the containers

Multi-tenant Architecture

1.Redo logs are common.Redo log contains annotated information pertaining to PDB.

2.Control files are common.

3.UNDO tablespace is common.

4.Temporary tablespaces are common, but each PDB can contains temp tablespaces.

5.Each container have it’s own dictionary in SYSTEM and SYSAUX tablespaces.

6.Each PDB will have it’s own tablespace and datafiles.

Concepts of Containers

 

A CDB has new characteristics compared to non-CDBs:

• Two containers:

– The root (CDB$ROOT)

– The seed PDB (PDB$SEED)

• Several services: one per container

– Name of root service = name of the CDB (cdb1)

• Common users in root and seed: SYS,SYSTEM …

Common privileges granted to common users

• Pre-defined common roles
• Tablespaces and data files associated to each container:

– root:

— SYSTEM: system-supplied metadata and no user data

— SYSAUX

– seed: SYSTEM, SYSAUX Global

 

Root container can be connected from OS authentication or using root service name.

PDB can only be connected using service name by TNS names.Each PDB by default will create it’s own service.

Automatic Diagnostic repository

 

Manual creation of CDB .I do not recommend it.I recommend to create using DBCA.

oracle@cdv1proccmdbf01:XCORMAN[/u01/app/oracle/product/12.1.0/db_1/dbs]$

The following parameters are basic .

a.Please create pfile

cat initXCORMAN.ora

control_files=’+DATA/XCORMAN/control01.ctl’

DB_NAME=XCORMAN

ENABLE_PLUGGABLE_DATABASE=TRUE

DB_CREATE_FILE_DEST=’+DATA’

 

b.Create the database

 

Export ORACLE_SID=XCORMAN

sqlplus / as sysdba

SQL>Create database XCORMAN;

c.Close and open seed PDB

Set the session with a new parameter:

alter session set “_oracle_script”=true;

1. Close and open the seed PDB:

 

alter pluggable database pdb$seed close;

alter pluggable database pdb$seed open;

d.Please execute post database creation scripts

1. Execute catalog.sql and other post-creation scripts.

?/rdbms/admin/catalog.sql

?/rdbms/admin/catblock.sql

?/rdbms/admin/catproc.sql

?/rdbms/admin/catoctk.sql

?/rdbms/admin/owminst.plb

?/sqlplus/admin/pupbld.sql

e.Register into listener

SYS@XCORTST1> alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=10.XX.11.XXX)(PORT=1590))’;

System altered.

SYS@XCORTST1> alter system register;

System altered.

f.Please check in listener status now.

Creation of CDB from DBCA

PDB creation overview

Copies the data files from

PDB$SEED data files

• Creates tablespaces SYSTEM,

SYSAUX

• Creates a full catalog including

metadata pointing to Oracle-

supplied objects

• Creates common users:

– Superuser SYS

• SYSTEM

• Creates a local user (PDBA)

granted local PDB_DBA role

• Creates a new default service SYSAUX Global

Command line PDB creation

SYS@XCORTST1> CREATE PLUGGABLE DATABASE my_new_pdb

ADMIN USER my_pdb_admin IDENTIFIED BY my_pdb_admin

ROLES = (dba)

DEFAULT TABLESPACE my_tbs

DATAFILE ‘+DATA’ SIZE 50M AUTOEXTEND ON  2    3    4    5  ;

Pluggable database created.

SYS@XCORTST1> alter pluggable database my_new_pdb open;

Pluggable database altered.

The datafiles will be created in below directory.

ASMCMD> pwd

+DATA/XCORTST/5F2CCEBE3E091BD4E053850B330AF8E1/DATAFILE

ASMCMD> ls -ltr

Type      Redund  Striped  Time             Sys  Name

DATAFILE  UNPROT  COARSE   NOV 30 01:00:00  Y    MY_TBS.445.961378753

DATAFILE  UNPROT  COARSE   NOV 30 01:00:00  Y    SYSAUX.443.961378749

DATAFILE  UNPROT  COARSE   NOV 30 01:00:00  Y    SYSTEM.442.961378749

Creation of pluggable database using SQL Developer and manage from sql developer

Once a PDB is created using seed PDB or plugging or cloning methods, or even closed, you can view the status of the new or closed PDB by querying the STATUS column of the CDB_PDBS view.

If common users and roles had been previously created, the new or closed PDB must be synchronized to retrieve the new common users and roles from the root. The synchronization is automatically performed if the PDB is opened in read write mode.

If you open the PDB in read only mode, an error is returned.

When a PDB is opened, Oracle Database checks the compatibility of the PDB with the CDB.Each compatibility violation is either a warning or an error. If a compatibility violation is a warning, then the warning is recorded in the alert log, but the PDB is opened normally without displaying a warning message. If a compatibility violation is an error, then an error message is displayed when the PDB is opened, and the error is recorded in the alert log. You must correct the condition that caused each error. When there are errors, access to the PDB is limited to users with RESTRICTED SESSION privilege so that the compatibility violations can be addressed. You can view descriptions of violations by querying PDB_PLUG_IN_VIOLATIONS view.

Clone PDB from Existing PDB using command line

—clone pdb to another pdb—

SYS@XCORTST1> alter  PLUGGABLE DATABASE “PDB_ORCL” open read only;

Pluggable database altered.

SYS@XCORTST1> create PLUGGABLE DATABASE “PDB_ORCL_CLONE” from  “PDB_ORCL”;

Pluggable database created.

SYS@XCORTST1> alter pluggable database PDB_ORCL_CLONE close;

Pluggable database altered.

SYS@XCORTST1> alter pluggable database PDB_ORCL_CLONE open;

Pluggable database altered.

SYS@XCORTST1> alter pluggable database PDB_ORCL_CLONE open read only;

 

SYS@XCORTST1> ALTER PLUGGABLE DATABASE pdb_orcl close;

 

Pluggable database altered.

Plug and unplug PDB to another CDB using command line

In Source CDB

SYS@XCORTST1> ALTER PLUGGABLE DATABASE pdb_orcl UNPLUG INTO  ‘/tmp/pdb_orcl.xml’;

Pluggable database altered.

SYS@XCORTST1> drop PLUGGABLE DATABASE pdb_orcl;

Pluggable database dropped.

Please check the compatibility of unplugged PDB can be plugged to new CDB

 

SET SERVEROUTPUT ON
DECLARE
compatible CONSTANT VARCHAR2(3) :=
CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(pdb_descr_file=> ‘/tmp/pdb_orcl.xml’,pdb_name => ‘pdb_orcl’)
WHEN TRUE THEN ‘YES’
ELSE ‘NO’
END;
BEGIN
DBMS_OUTPUT.PUT_LINE(compatible);
END;
/

In Target CDB

SYS@XCLONEST1> create PLUGGABLE DATABASE “PDB_ORCL” using ‘/tmp/pdb_orcl.xml’ nocopy;

Pluggable database created.

Possible way to Convert non-CDB to PDB of another CDB

There are three possible methods to plug a non-CDB database into a CDB.

Whichever method is used, you have to get the non-CDB into a transitionally-consistent state

and open it in restricted mode.

• Either use transportable tablespace (TTS) or full conventional export / import or

transportable database (TDB) provided that in the last one any user-defined object

resides in a single user-defined tablespace.

• Or use DBMS_PDB package to construct an XML file describing the non-CDB data files to

plug the non-CDB into the CDB as a PDB. This method presupposes that the non-CDB is

an Oracle 12c database.

• Or use replication with GoldenGate

Convert non-CDB to PDB of another CDB example

In non-cdb

Export ORACLE_SID=ORCL

sqlplus / as sysdba

Shutdown immediate;

Startup mount;

Alter database open read only;

Exec edbms_pdb.describe(‘/tmp/orcl.xml’);

In target CDB

sqlplus / as sysdba

Create pluggable database pdb1 using ‘/tmp/orcl.xml’;

Run the ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql script to delete

unnecessary metadata from PDB SYSTEM tablespace. This script must be run before the

PDB can be opened for the first time. This script is required for plugging non-CDBs only.

Sqlplus / as sysdba

Connect sys/xxx@pdb2 as sysdba

@$ORACLE_HOME/rdbms/admin/noncdb_to_pdb;