How to Create Pluggable Database Oracle

In this article, we are going to learn how to create pluggable database Oracle in oracle 12c or 19c. Here I’m going to create a pluggable database using PDB seed (PDB$SEED).

How to set Oracle Pluggable Database Automatic startup

Create Pluggable Database

Step 1. Check current database details with existing pdbs.

SQL> select name,open_mode,con_id from v$database;

NAME      OPEN_MODE                CON_ID
--------- -------------------- ----------
SHRIPAL   READ WRITE                    0

In my case the database name is SHRIPAL which is open in read write mode.

SQL> show pdbs

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

In the above window, you can see we have only one pluggable database which name is PDB1 and it is open in read write mode.

Let me check the all containers details.

SQL> select name,con_id,dbid from v$containers;

NAME                               CON_ID       DBID
------------------------------ ---------- ----------
CDB$ROOT                                1 3438567718
PDB$SEED                                2 2409276147
PDB1                                    3 3075487488

Check the PDB$SEED datafile details.

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

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

Tempfile details of pdbseed.

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

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/shripal/pdbseed/pdbseed_temp012021-04-22_03-37-44-PM.dbf

Step to create pluggable database

Step 2. Now create the new pluggable database using pdb$seeds details.

Create required directory for new pluggable database.

$mkdir -p /u01/app/oracle/oradata/shripal/pdb2

Use below query to create pluggable database.

SQL> create pluggable database PDB2 admin user pdb2dba identified by oracle
default tablespace PDB2_USERS
datafile '/u01/app/oracle/oradata/shripal/pdb2/pdb2_users01.dbf' size 250m autoextend on
file_name_convert=('/u01/app/oracle/oradata/shripal/pdbseed/','/u01/app/oracle/oradata/shripal/pdb2/');

Check pdbs details.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB2                           MOUNTED

Open PDB2 database in read write mode.

SQL> alter pluggable database pdb2 open;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB2                           READ WRITE NO

Check datafiles details for newly create pluggable database.

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

NAME
-----------------------------------------------------
/u01/app/oracle/oradata/shripal/pdb2/system01.dbf
/u01/app/oracle/oradata/shripal/pdb2/sysaux01.dbf
/u01/app/oracle/oradata/shripal/pdb2/pdb2_users01.dbf

SQL> select name from v$tempfile where con_id=4;

NAME
------------------------------------------------------------------------------
/u01/app/oracle/oradata/shripal/pdb2/pdbseed_temp012021-04-22_03-37-44-PM.dbf

SQL> select name,con_id,dbid from v$containers;

NAME                               CON_ID       DBID
------------------------------ ---------- ----------
CDB$ROOT                                1 3438567718
PDB$SEED                                2 2409276147
PDB1                                    3 3075487488
PDB2                                    4 3901914943

Creating pluggable Database (PDB) using no Clause

In this example we are going to create a simplest way to create pluggable database (pdb).

Either OMF (Oracle Managed Files) is enabled for the CDB, or the PDB_FILE_NAME_CONVERT initialization parameter is set. The files associated with the PDB (pluggable database) seed or application seed will be copied to a new location based on the OMF configuration or the initialization parameter setting.

SQL> CREATE PLUGGABLE DATABASE pdb3 ADMIN USER admin1 IDENTIFIED BY admin1;

• There is no file with the same name as the new temp file that will be created in the target location. Therefore, the TEMPFILE REUSE clause is not required.

• No predefined Oracle roles need to be granted to the PDB_DBA role.

Creating a pluggable database and Granting Predefined Oracle Roles to the PDB Administrator

In this example we are going to uses the ROLES parameter to grant a predefined role.

SQL> CREATE PLUGGABLE DATABASE pdb4 
     ADMIN USER admin1 IDENTIFIED BY admin1
     ROLES=(DBA);

I hope you completely understand how to Create Pluggable Database Oracle step by step. Please tell us your fillings in comment box.