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 container 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 the required directory for the new pluggable database.

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

Use the below query to create a 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 the 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 the 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 the simplest way to create a 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 a 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 use 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 the comment box.