Create Pluggable Database (PDB) in Oracle 12c

We have gone through features of 12c Multitenant database. Now we will have discussion on create pluggable databases (PDB). There are different ways to create pluggable database

We will discuss on below methods

1. Using SEED pdb

2. Using XML file

3. By Cloning

     a. Local PDB

     b. Remote PDB

Using seed PDB PDB$SEED

Below are steps to create PDB using PDB$SEED, first Check the PDB's available in container database

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 PDB_1                          READ WRITE NO

Now create PDB using below command, note that we have provided options to create admin user, default tablespaces and roles. FILE_NAME_CONVERT is required in case mount point or diskgroups are different, for understanding purpose we used in below example though diskgroup is same.

SQL> create pluggable database pdb_techon admin user techon_admin identified by techon#123 roles = (DBA)

default tablespace techon_tbs datafile '+DATA01' size 100M

FILE_NAME_CONVERT=('+DATA01','+DATA01');  2    3

Pluggable database created.

We can see PDB is default created in MOUNTED state. we need to open the same with alter pluggable database command as shown below

SQL>  show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 PDB_1                          READ WRITE NO

         4 PDB_TECHON                     MOUNTED

SQL>

SQL> alter pluggable database PDB_TECHON open;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 PDB_1                          READ WRITE NO

         4 PDB_TECHON                     READ WRITE NO

We can check datafile created for tablespace techon_tbs

SQL> select name from v$datafile where name like '%tech%';

NAME

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

+DATA01/DEV12C1/TECHON_PDB/DATAFILE/techon_tbs.363.994818875

Using XML file

For carrying out this activity, I have created .xml file of our previous PDB, techno_pdb and dropped it using keep datafile option and generated xml file for the same

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 PDB_1                          READ WRITE NO

         4 PDB_TECHON                     READ WRITE NO

SQL> alter pluggable database PDB_TECHON close;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 PDB_1                          READ WRITE NO

         4 PDB_TECHON                     MOUNTED

SQL> alter pluggable database PDB_TECHON unplug into '/u01/pdb_backup/pdb_techon.xml';

Pluggable database altered.

SQL> drop pluggable database PDB_TECHON keep datafiles;

Pluggable database dropped.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 PDB_1                          READ WRITE NO

SQL>

Now lets see command to create PDB from xml file, as we have kept datafiles as it is during drop no file_name_convert or transfer of any datafile is required

SQL> Create pluggable database PDB_TECHON using '/u01/pdb_backup/pdb_techon.xml’;

Pluggable database created.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 PDB_1                          READ WRITE NO

         4 PDB_TECHON                     MOUNTED

SQL>

SQL> alter pluggable database PDB_TECHON open;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 PDB_1                          READ WRITE NO

         4 PDB_TECHON                     READ WRITE NO

Using clone from local or remote PDB

Using Local PDB

Now we will discuss to clone PDB from local PDB, Below are the steps discussed,

Open source PDB in read only mode using below method

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 PDB_1                          READ WRITE NO

         4 PDB_TECHON                     READ WRITE NO

SQL> alter pluggable database PDB_TECHON close;

Pluggable database altered.

SQL> alter pluggable database PDB_TECHON open read only;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 PDB_1                          READ WRITE NO

         4 PDB_TECHON                     READ ONLY  NO

Below are commands to create PDB from local PDB, we have created PDB techon_new and open both PDB's

SQL> create pluggable database techon_new from PDB_TECHON FILE_NAME_CONVERT=( '+DATA01', '+DATA01');

Pluggable database created.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 PDB_1                          READ WRITE NO

         4 PDB_TECHON                     READ ONLY  NO

         5 TECHON_NEW                     MOUNTED

SQL> alter pluggable database PDB_TECHON close;

Pluggable database altered.

SQL> alter pluggable database PDB_TECHON open;

Pluggable database altered.

SQL> alter pluggable database TECHON_NEW open;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 PDB_1                          READ WRITE NO

         4 PDB_TECHON                     READ WRITE NO

         5 TECHON_NEW                     READ WRITE NO

Using Remote PDB

For cloning remote follow the steps mentioned here Clone Remote PDB using DB link

You can also read (12c Features )