Skip to content

Create and Configure a Pluggable Database (PDB)

    Database Configuration Assistant (DBCA)

    The DBCA includes a new option on the opening “Database Operation” screen that allows you to manage the pluggable databases of an existing container database. Select the “Manage Pluggable Databases” option and click the “Next” button.

    PDB DBCA - Database Operation

    You can see from the resulting screen what operations are possible with pluggable databases.

    PDB DBCA - Manage Pluggable Databases

    The following sections describe some of these options.

     The screens look a little different between Oracle 12.1 and 12.2, but not enough to cause any confusion.

    Create a Pluggable Database (PDB) using the DBCA

    On the “Manage Pluggable Databases” screen shown previously, select the “Create a Pluggable Database” option and click the “Next” button. On the resulting screen, select the container database to house the new pluggable database and click the “Next” button.

    PDB DBCA - Database List

    Select the “Create a new Pluggable Database” option and click the “Next” button. If you were plugging in a previously unplugged database, you would select the PDB Archive or PDB File Set options to match the format of the files containing the unplugged PDB.

    PDB DBCA - Create Pluggable Database

    Enter the pluggable database name, database location and admin credentials, then click the “Next” button.

    PDB DBCA - Pluggable Database Options

    If you are happy with the summary information, click the “Finish” button.

    PDB DBCA - Summary

    Wait while the pluggable database is created. Once complete, click the “OK” button on the message dialog and the “Close” button on the main screen.

    PDB DBCA - Summary

    The new pluggable database has been created as a clone of the seed database.

    Unplug a Pluggable Database (PDB) using the DBCA

    On the “Manage Pluggable Databases” screen shown previously, select the “Unplug a Pluggable Database” option and click the “Next” button. On the resulting screen, select the container database that houses the pluggable database to be unplugged and click the “Next” button.

    PDB DBCA - Database List

    Select the PDB to unplug, decide whether to use a pluggable database archive or a file set and enter the appropriate location details. Click the “Next” button.

    PDB DBCA - Unplug Pluggable Database

    If you are happy with the summary information, click the “Finish” button.

    PDB DBCA - Summary

    Wait while the pluggable database is unplugged. Once complete, click the “OK” button on the message dialog and the “Close” button on the main screen.

    PDB DBCA - Summary

    The pluggable database has now been unplugged.

    Plugin a Pluggable Database (PDB) using the DBCA

    On the “Manage Pluggable Databases” screen shown previously, select the “Create a Pluggable Database” option and click the “Next” button. On the resulting screen, select the container database to house the new pluggable database and click the “Next” button.

    PDB DBCA - Database List

    Select the “Create Pluggable Database From PDB Archive” or “Create Pluggable Database using PDB File Set” option and enter the location of the required files. You can browse for the files using the “Browse” button.

    PDB DBCA - Create Pluggable Database

    Enter the pluggable database name, database location and admin credentials, then click the “Next” button.

    PDB DBCA - Pluggable Database Options

    If you are happy with the summary information, click the “Finish” button.

    PDB DBCA - Summary

    Wait while the pluggable database is created. Once complete, click the “OK” button on the message dialog and the “Close” button on the main screen.

    PDB DBCA - Summary

    The pluggable database has been plugged into the container database.

    Delete a Pluggable Database (PDB) using the DBCA

    On the “Manage Pluggable Databases” screen shown previously, select the “Delete a Pluggable Database” option and click the “Next” button. On the resulting screen, select the container database that houses the pluggable database to be deleted and click the “Next” button.

    PDB DBCA - Database List

    Select the PDB to delete and click the “Next” button.

    PDB DBCA - Delete Pluggable Database

    If you are happy with the summary information, click the “Finish” button.

    PDB DBCA - Summary

    Wait while the pluggable database is deleted. Once complete, click the “OK” button on the message dialog and the “Close” button on the main screen.

    PDB DBCA - Summary

    The pluggable database has been deleted from the container database.

    Configure a Pluggable Database (PDB) using the DBCA

    On the “Manage Pluggable Databases” screen shown previously, select the “Configure a Pluggable Database” option and click the “Next” button. On the resulting screen, select the container database that houses the pluggable database to be configured and click the “Next” button.

    PDB DBCA - Database List

    Select the PDB to configure and click the “Next” button.

    PDB DBCA - Pluggable Database List

    Select any additional options you would like to configure, then click the “Next” button.

    PDB DBCA - Pluggable Database Options

    If you are happy with the summary information, click the “Finish” button.

    PDB DBCA - Summary

    Wait while the pluggable database is configured. Once complete, click the “OK” button on the message dialog and the “Close” button on the main screen.

    PDB DBCA - Progress

    The pluggable database has been configured.

    Manual (SQL*Plus)

    We can do this using one of three methods. If we are using Oracle Managed Files (OMF) we don’t need to worry about the file placement. Oracle will handle it for us.

    ALTER SYSTEM SET db_create_file_dest = '/u02/oradata';

    From 12.1.0.2 onward there is an inline variation of this using the CREATE_FILE_DEST clause. The path set in this clause will be used as the OMF location for the new PDB.

    CREATE PLUGGABLE DATABASE pdb2 ADMIN USER pdb_adm IDENTIFIED BY Password1
      CREATE_FILE_DEST='/u01/app/oracle/oradata';

    The second method uses the FILE_NAME_CONVERT clause in the CREATE PLUGGABLE DATABASE statement.

    CONN / AS SYSDBA
    
    CREATE PLUGGABLE DATABASE pdb2 ADMIN USER pdb_adm IDENTIFIED BY Password1
      FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdbseed/','/u01/app/oracle/oradata/cdb1/pdb2/');

    Alternatively, we can specify the PDB_FILE_NAME_CONVERT initialization parameter before calling the command without using the FILE_NAME_CONVERT clause.

    CONN / AS SYSDBA
    
    ALTER SESSION SET PDB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/cdb1/pdbseed/','/u01/app/oracle/oradata/cdb1/pdb3/';
    
    CREATE PLUGGABLE DATABASE pdb3 ADMIN USER pdb_adm IDENTIFIED BY Password1;

    Every time there is a need to convert file locations, either of these two methods will work. For the remainder of the article I will stick to using the FILE_NAME_CONVERT method to cut down on the variations I have to display.

    We can see the PDBs are present by querying the DBA_PDBS and V$PDBS views.

    COLUMN pdb_name FORMAT A20
    
    SELECT pdb_name, status
    FROM   dba_pdbs
    ORDER BY pdb_name;
    
    PDB_NAME	     STATUS
    -------------------- -------------
    PDB$SEED	     NORMAL
    PDB1		     NORMAL
    PDB2		     NEW
    PDB3		     NEW
    
    SQL>
    
    COLUMN name FORMAT A20
    
    SELECT name, open_mode
    FROM   v$pdbs
    ORDER BY name;
    
    NAME			       OPEN_MODE
    ------------------------------ ----------
    PDB$SEED		       READ ONLY
    PDB1			       MOUNTED
    PDB2			       MOUNTED
    PDB3			       MOUNTED
    
    SQL>

    You can also use the SHOW PDBS command from SQL*Plus.

    SQL> SHOW PDBS
    
        CON_ID CON_NAME			  OPEN MODE  RESTRICTED
    ---------- ------------------------------ ---------- ----------
    	 2 PDB$SEED			  READ ONLY  NO
    	 3 PDB1 			  MOUNTED
    	 4 PDB2 			  MOUNTED
    	 5 PDB3 			  MOUNTED
    SQL>

    The PDBs are created with the status of ‘NEW’. They must be opened in READ WRITE mode at least once for the integration of the PDB into the CDB to be complete.

    ALTER PLUGGABLE DATABASE pdb2 OPEN READ WRITE;
    ALTER PLUGGABLE DATABASE pdb3 OPEN READ WRITE;
    
    SELECT pdb_name, status
    FROM   dba_pdbs
    ORDER BY pdb_name;
    
    PDB_NAME	     STATUS
    -------------------- -------------
    PDB$SEED	     NORMAL
    PDB1		     NORMAL
    PDB2		     NORMAL
    PDB3		     NORMAL
    
    SQL>
    
    SELECT name, open_mode
    FROM   v$pdbs
    ORDER BY name;
    
    NAME			       OPEN_MODE
    ------------------------------ ----------
    PDB$SEED		       READ ONLY
    PDB1			       MOUNTED
    PDB2			       READ WRITE
    PDB3			       READ WRITE
    
    SQL>

     Depending on the syntax used, you may need to grant the PDB_DBA role to the local admin users for the PDB.

    Unplug a Pluggable Database (PDB) Manually

    Before attempting to unplug a PDB, you must make sure it is closed. To unplug the database use the ALTER PLUGGABLE DATABASE command with the UNPLUG INTO clause to specify the location of the XML metadata file.

    ALTER PLUGGABLE DATABASE pdb2 CLOSE;
    ALTER PLUGGABLE DATABASE pdb2 UNPLUG INTO '/u01/app/oracle/oradata/cdb1/pdb2/pdb2.xml';

    The pluggable database is still present, but you shouldn’t open it until the metadata file and all the datafiles are copied somewhere safe.

    SELECT name, open_mode
    FROM   v$pdbs
    ORDER BY name;
    
    NAME			       OPEN_MODE
    ------------------------------ ----------
    PDB$SEED		       READ ONLY
    PDB1			       MOUNTED
    PDB2			       MOUNTED
    PDB3			       READ WRITE
    
    SQL>

    You can delete the PDB, choosing to keep the files on the file system.

    DROP PLUGGABLE DATABASE pdb2 KEEP DATAFILES;
    
    SELECT name, open_mode
    FROM   v$pdbs
    ORDER BY name;
    
    NAME			       OPEN_MODE
    ------------------------------ ----------
    PDB$SEED		       READ ONLY
    PDB1			       MOUNTED
    PDB3			       READ WRITE
    
    SQL>
    Plugin a Pluggable Database (PDB) Manually

    Plugging in a PDB into the CDB is similar to creating a new PDB. First check the PBD is compatible with the CDB by calling the DBMS_PDB.CHECK_PLUG_COMPATIBILITY function, passing in the XML metadata file and the name of the PDB you want to create using it.

    SET SERVEROUTPUT ON
    DECLARE
      l_result BOOLEAN;
    BEGIN
      l_result := DBMS_PDB.check_plug_compatibility(
                    pdb_descr_file => '/u01/app/oracle/oradata/cdb1/pdb2/pdb2.xml',
                    pdb_name       => 'pdb2');
    
      IF l_result THEN
        DBMS_OUTPUT.PUT_LINE('compatible');
      ELSE
        DBMS_OUTPUT.PUT_LINE('incompatible');
      END IF;
    END;
    /
    compatible
    
    PL/SQL procedure successfully completed.
    
    SQL>

    If the PDB is not compatible, violations are listed in the PDB_PLUG_IN_VIOLATIONS view. If the PDB is compatible, create a new PDB using it as the source. If we were creating it with a new name we might do something like this.

    CREATE PLUGGABLE DATABASE pdb5 USING '/u01/app/oracle/oradata/cdb1/pdb2/pdb2.xml'
      FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdb2/','/u01/app/oracle/oradata/cdb1/pdb5/');

    Instead, we want to plug the database back into the same container, so we don’t need to copy the files or recreate the temp file, so we can do the following.

    CREATE PLUGGABLE DATABASE pdb2 USING '/u01/app/oracle/oradata/cdb1/pdb2/pdb2.xml'
      NOCOPY
      TEMPFILE REUSE;
    
    ALTER PLUGGABLE DATABASE pdb2 OPEN READ WRITE;
    
    SELECT name, open_mode
    FROM   v$pdbs
    ORDER BY name;
    
    NAME			       OPEN_MODE
    ------------------------------ ----------
    PDB$SEED		       READ ONLY
    PDB1			       MOUNTED
    PDB2			       READ WRITE
    PDB3			       READ WRITE
    
    SQL>
    Clone a Pluggable Database (PDB) Manually

    Cloning an existing local PDB is similar to creating a new PDB from the seed PDB, except now we are using non-seed PDB as the source, which we have to identify using the FROM clause.


    -- Setting the source to read-only is not necessary for Oracle 12cR2. ALTER PLUGGABLE DATABASE pdb3 CLOSE; ALTER PLUGGABLE DATABASE pdb3 OPEN READ ONLY; CREATE PLUGGABLE DATABASE pdb4 FROM pdb3 FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdb3/','/u01/app/oracle/oradata/cdb1/pdb4/'); ALTER PLUGGABLE DATABASE pdb4 OPEN READ WRITE; -- Switch the source PDB back to read/write if you made it read-only. ALTER PLUGGABLE DATABASE pdb3 CLOSE; ALTER PLUGGABLE DATABASE pdb3 OPEN READ WRITE;
    Delete a Pluggable Database (PDB) Manually

    When dropping a pluggable database, you must decide whether to keep or drop the associated datafiles. The PDBs must be closed before being dropped.

    ALTER PLUGGABLE DATABASE pdb2 CLOSE;
    DROP PLUGGABLE DATABASE pdb2 KEEP DATAFILES;
    
    ALTER PLUGGABLE DATABASE pdb3 CLOSE;
    DROP PLUGGABLE DATABASE pdb3 INCLUDING DATAFILES;
    
    ALTER PLUGGABLE DATABASE pdb4 CLOSE;
    DROP PLUGGABLE DATABASE pdb4 INCLUDING DATAFILES;
    
    SELECT name, open_mode
    FROM   v$pdbs
    ORDER BY name;
    
    NAME			       OPEN_MODE
    ------------------------------ ----------
    PDB$SEED		       READ ONLY
    PDB1			       MOUNTED
    
    SQL>
    SQL Developer

    The DBA section of SQL Developer includes tree node called “Container Database”.

    PDB SQL Developer

    Right-clicking on the “Container Database” node produces a popup menu showing you what operations are available.

    PDB SQL Developer Popup 1

    Right-clicking on a specific PDB node produces a popup menu showing only those operations that are relevant to that PDB.

    PDB SQL Developer Popup 2

    If you understand the DBCA and SQL*Plus approach to managing PDBs, these SQL Developer screens are very straight forward.

    Cloud Control

    Cloud Control 12cR3 onward supports pluggable database functionality. Once you click on the container database, the “Oracle Database > Control > Open/Close Pluggable Database” menu option allows you to control the state of the PDBs owned by the CDB.

    PDB Cloud Control - Control

    The “Oracle Database > Provision > Provision Pluggable Database” menu option allows you to perform other operations PDBs owned by the CDB, including cloning, unplugging amongst other things.

    PDB Cloud Control - Provisioning