Creating PDB from Seed PDB (From Scratch)

 When you create a new PDB from scratch, Oracle copies all DB files from SEED PDB (PDB$SEED) to the datafile directory of the new PDB. Then the metadata information is also copied from the Seed PDB to the new PDB. (See the diagram below)

The steps are very straightforward. You use only a single command which starts with “Create Pluggable Database” command. You can provide the database name and other optional clause to this command to complete the PDB creation.

When you are creating a PDB from scratch, one of the most important information is where the DB files will be stored. The new PDB file location is determined by using one of the below 4 parameters (Listed in the order of precedence).

Let us create a new PDB from scratch using the below simple command with no optional clause.

CREATE PLUGGABLE DATABASE pdb19c1 ADMIN USER pdb19c1adm IDENTIFIED BY pdb19c1pwd;

But first check the values of db_create_file_dest and pdb_file_name_convert parameters.

SQL> show parameter db_create_file_dest
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string     
 
SQL> show parameter pdb_file_name_convert
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pdb_file_name_convert                string

As you can see, both of the parameters "db_create_file_dest" and "pdb_file_name_convert" are not set to any value and our "CREATE PLUGGABLE DATABASE" command does not have any clause to specify the file path.

Let us see what the output will be:

SQL> CREATE PLUGGABLE DATABASE pdb19c1 ADMIN USER pdb19c1adm IDENTIFIED BY pdb19c1pwd;
CREATE PLUGGABLE DATABASE pdb19c1 ADMIN USER pdb19c1adm IDENTIFIED BY pdb19c1pwd
                                                                               *
ERROR at line 1:
ORA-65016: FILE_NAME_CONVERT must be specified

As you can see above, Oracle throws the error because it does not know how to set the datafile path. From the table above, The file path is set from the above 2 parameter or the optional clause "FILE_NAME_CONVERT" and "CREATE_FILE_DEST". Let's set the parameter "db_create_file_dest" and execute the create command again.

SQL> alter system set db_create_file_dest='/u02/oradata' scope=both;

 System altered.

 SQL> show parameter db_create_file_dest                                              

 NAME                           TYPE    VALUE

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

db_create_file_dest             string  /u02/oradata

SQL> CREATE PLUGGABLE DATABASE pdb19c1 ADMIN USER pdb19c1adm IDENTIFIED BY pdb19c1pwd;

Pluggable database created.

SQL> show pdbs

    CON_ID CON_NAME                OPEN MODE  RESTRICTED

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

        2 PDB$SEED                 READ ONLY  NO

        3 PDB19C1                  MOUNTED

By Default, the pluggable database will be in Mounted state. You need to open it.

SQL> alter pluggable database pdb19c1 open;
Pluggable database altered.

SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB19C1                        READ WRITE NO

SQL>

Let us look at the datafile path and name for this new PDB (pdb19c1). first we need to change the current container for the session to the new PDB (PDB19C1).

SQL> alter session set container=PDB19C1;

Session altered.

SQL> set lines 300

SQL> col file_name format A95

SQL> col tablespace_name format A16

SQL> select tablespace_name, file_name from dba_data_files order by tablespace_name;

TABLESPACE_NAME  FILE_NAME

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

SYSAUX           /u02/oradata/CDB19C1/F2B9B34F1AF14EF9E055000000000001/datafile/o1_mf_sysaux_kwp1qzwz_.dbf

SYSTEM           /u02/oradata/CDB19C1/F2B9B34F1AF14EF9E055000000000001/datafile/o1_mf_system_kwp1qzpx_.dbf

UNDOTBS1         /u02/oradata/CDB19C1/F2B9B34F1AF14EF9E055000000000001/datafile/o1_mf_undotbs1_kwp1qzx3_.dbf

USERS            /u02/oradata/CDB19C1/F2B9B34F1AF14EF9E055000000000001/datafile/o1_mf_users_kynld2vo_.dbf

SQL>

As you can see from the above output, The data files names are in OMF (Oracle Managed File) format and the directory path is taken from initialization parameter db_create_file_dest (/u02/oradata), followed by the CDB Unique name and PDB GUID and string "datafile".

let's get the PDB detail for this new PDB:

SQL> col pdb_name format A20

SQL> select pdb_id, pdb_name, dbid, guid, status, con_id from dba_pdbs;

    PDB_ID PDB_NAME                   DBID GUID                             STATUS         CON_ID

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

         3 PDB19C1              3731800278 F2B9B34F1AF14EF9E055000000000001 NORMAL              3

SQL>

(** Please note the GUID in the data file directory Path)

Create PDB with clause for File Path

You can also use the clause FILE_NAME_CONVERT or CREATE_FILE_DEST to specify directory path for your PDB's database files. Below is an example with FILE_NAME_CONVERT clause:

CREATE PLUGGABLE DATABASE PDB19C2 ADMIN USER pdb19c2adm IDENTIFIED BY pdb19c2pwd
ROLES=(DBA)
FILE_NAME_CONVERT = ('/u02/oradata/CDB19C1/pdbseed', '/u02/oradata/PDB19C2');

** ROLES=(DBA) : If you want to assign any specific role to the PDB admin user (in this case, pdb19c2adm), you use this clause. By default, only PDB_DBA role is assigned to the ADMIN user.

** FILE_NAME_CONVERT: As we know that the new PDB will be created by copying the necessary database files from the seed PDB. But Oracle needs to know the destination directory of the copied files. FILE_NAME_CONVERT clause or pdb_file_name_convert parameter specifies the rule to replace the source directory string with an alternate string (If the destination directory path does not exist, Oracle will create it automatically).

Let's check the data file directory for the seed PDB:

SQL> alter session set container=PDB$SEED;
 
Session altered.
 
SQL> set lines 300
SQL> col file_name format A70
SQL> col file_name format A50
SQL> select tablespace_name, file_name from dba_data_files;
 
TABLESPACE_NAME  FILE_NAME
---------------- --------------------------------------------------
SYSTEM           /u02/oradata/CDB19C1/pdbseed/system01.dbf
SYSAUX           /u02/oradata/CDB19C1/pdbseed/sysaux01.dbf
UNDOTBS1         /u02/oradata/CDB19C1/pdbseed/undotbs01.dbf
 
SQL>

We will try to replace the directory string from "/u02/oradata/CDB19C1/pdbseed" to "/u02/oradata/PDB19C2". So, we will use the clause below to set the database file directory path:

FILE_NAME_CONVERT = ('/u02/oradata/CDB19C1/pdbseed', '/u02/oradata/PDB19C2')

Let us create a PDB using the above clause:

SQL> CREATE PLUGGABLE DATABASE PDB19C2 ADMIN USER pdb19c2adm IDENTIFIED BY pdb19c2pwd
ROLES=(DBA)
FILE_NAME_CONVERT = ('/u02/oradata/CDB19C1/pdbseed', '/u02/oradata/PDB19C2');
Pluggable database created.
 
SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB19C1                        MOUNTED
         5 PDB19C2                        MOUNTED
 
SQL> alter pluggable database PDB19C2 open;
 
Pluggable database altered.
 
SQL> show pdbs
 
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB19C1                        MOUNTED
         5 PDB19C2                        READ WRITE NO

SQL> alter session set container=PDB19C2;
 
Session altered.

SQL> select tablespace_name, file_name from dba_data_files;
 
TABLESPACE_NAME  FILE_NAME
---------------- --------------------------------------------------
SYSTEM           /u02/oradata/PDB19C2/system01.dbf
SYSAUX           /u02/oradata/PDB19C2/sysaux01.dbf
UNDOTBS1         /u02/oradata/PDB19C2/undotbs01.dbf
 
SQL>

As you can see above output, the datafiles for the new PDBs are created in the new destination directory as per the FILE_NAME_CONVERT clause.

Below is another example of CREATE PLUGGABLE DATABASE command which creates a default tablespace for the new PDB.

SQL> CREATE PLUGGABLE DATABASE PDB19C3
ADMIN USER pdb19c3adm IDENTIFIED BY pdb19c3pwd
STORAGE (MAXSIZE 2G)
DEFAULT TABLESPACE sales
DATAFILE '/u02/oradata/PDB19C3/sales01.dbf' SIZE 100M
AUTOEXTEND ON
FILE_NAME_CONVERT = ('/u02/oradata/CDB19C1/pdbseed', '/u02/oradata/PDB19C3');  2    3    4    5    6    7 
 
Pluggable database created.
 
SQL> alter pluggable database PDB19C3 open;
Pluggable database altered.

SQL> show pdbs
 
    CON_ID CON_NAME                   OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
       2 PDB$SEED               READ ONLY  NO
       3 PDB19C1                READ WRITE NO
       4 PDB19C2                READ WRITE NO
       5 PDB19C3                READ WRITE NO

Hope you learned something. 
Leave me a comment if you want to add something to this topic.