How to Unplug and Plug In Your Pluggable Database in Oracle 12c

By Chris Ruel, Michael Wessler

The activity covered here focuses on the actual pluggable part of the Pluggable Database (PDB) that you can use with Oracle 12c. You may want to move your PDBs around for a number of reasons. For example:

  • Upgrades

  • Patching

  • Relocation to a different Container Database (CDB)

  • Testing

  • The sky’s the limit!

How to unplug your PDB in Oracle 12c

A PDB is unplugged by connecting to the root CDB and issuing the ALTER PLUGGABLE DATABASE statement to specify an XML file that will contain metadata about the PDB after it is unplugged. The XML file contains the required information to enable a CREATE PLUGGABLE DATABASE statement on a target CDB to plug in the PDB.

  1. Log in to your CDB using SQL*Plus as SYSDBA. To make sure you’re in the correct location, type

    <show con_name>

    You should see something like this:

    CON_NAME
    ------------------------------
    CDB$ROOT
  2. Get a list of your PDBs by typing

    <show pdbs>

    You should see this:

    CON_ID CON_NAME            OPEN MODE RESTRICTED
    ------ ------------------------------ ---------- ----------
       2 PDB$SEED            READ ONLY NO
       3 DEVPDB1            READ WRITE NO
       4 DEVPDB2            READ WRITE NO
       5 DEVPDB3            READ WRITE NO

    Next you need to unplug DEVPDB3.

  3. Close DEVPDB3 by typing

    <alter pluggable database devpdb3 close immediate;>

    You should see this:

    Pluggable database altered.
  4. Run the ALTER PLUGGABLE DATABASE statement with the UNPLUG INTO clause and specify the PDB to unplug and the name and location of the PDB’s XML metadata file by typing

    <ALTER PLUGGABLE DATABASE devpdb3 UNPLUG INTO '/home/oracle/devpdb3.xml';>

    You should see this:

Pluggable database altered.

How to plug in your PDB in Oracle 12c

Before you plug in a PDB, you must meet some requirements:

  • The CDB must have the same endianness.

  • The CDB must have the same set of options installed.

  • The source CDB and the target CDB must have compatible character sets and national character sets.

For simplicity purposes, let’s show you how to unplug and replug the DEVPDB3 database into the same CDB root database:

  1. Log in to your CDB using SQL*Plus as SYSDBA.

    To make sure you are in the correct location, type

    <show con_name>

    You should see something like this:

    CON_NAME
    ------------------------------
    CDB$ROOT

    Next you need to drop the existing DEVPDB3 database.

  2. To drop DEVPDB3 in a manner that preserves the data files (because they’re needed to plug in database), type

    <drop pluggable database devpdb3 keep datafiles;>

    You should see this:

    Pluggable database dropped.
  3. Run the DBMS_PDB.CHECK_PLUG_COMPATIBILITY function to determine whether the unplugged PDB is compatible with the CDB. Type

    SET SERVEROUTPUT ON
    DECLARE
     compatible CONSTANT VARCHAR2(3) :=
      CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
          pdb_descr_file => '/home/oracle/devpdb3.xml')
      WHEN TRUE THEN 'YES'
      ELSE 'NO'
    END;
    BEGIN
     DBMS_OUTPUT.PUT_LINE(compatible);
    END;
    /

    If all requirements are met, you see this:

    YES
  4. Check the existing PDBs by typing

    <show pdbs>

    You should see something like this:

    CON_ID CON_NAME            OPEN MODE RESTRICTED
    ------ ------------------------------ ---------- ----------
       2 PDB$SEED            READ ONLY NO
       3 DEVPDB1            READ WRITE NO
       4 DEVPDB2            READ WRITE NO
  5. Plug in the database using the metadata file by typing

    <create pluggable database DEVPDB3 using
    '/home/oracle/devpdb3.xml' NOCOPY TEMPFILE REUSE;>

    You should see this:

    Pluggable database created.
  6. Check the status of your PDBs again by typing

    <show pdbs>

    You should see something like this:

    CON_ID CON_NAME            OPEN MODE RESTRICTED
    ------ ------------------------------ ---------- ----------
       2 PDB$SEED            READ ONLY NO
       3 DEVPDB1            READ WRITE NO
       4 DEVPDB2            READ WRITE NO
       5 DEVPDB3            MOUNTED
  7. Open your newly plugged in PDB by typing

    <alter pluggable database devpdb3 open;>

    You should see this:

Pluggable database altered.