Oracle12c

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

By September 26, 2015 September 12th, 2016 2 Comments

In Oracle 12c, a new architecture has been introduced called Multitenant Architecture. One Container Database (CDB) consisting of many Pluggable Databases (PDBs). So these Pluggable Databases can be unplugged from and plugged in to any other Container Database (CDB). Let us see how this can be done.

UnPlug and Plug in the PDB Database

To Unplug and Plug in the Pluggable Database database (PDB) from One Container Database (CDB)to another Container Database (CDB), the primary thing is we should be connected to the root CDB and to the PDB.

There are also some considerations to be taken care of when Unplugging and Plugging the PDB from one CDB to another CDB.

  • The Endianness of the CDB should be same.
  • The Options of the CDB installed should be same.
  • The source CDB and the target CDB must have compatible character sets and national character sets.

 

So let us see below steps which guides you to Unplug and Plug in the PDB to a Container Database.,

 

The below are the list of PDBs I have in my Container Database CDB.,

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

Now here we are going to unplug and plugin the Pluggable Database PDB1.

 

So for that the first step is to Close the Pluggable Database PDB1.

SQL> alter pluggable database pdb1 close immediate;

Pluggable database altered.

 

Run the ALTER PLUGGABLE DATABASE command with the UNPLUG INTO clause and specify the PDB to unplug. You should also enter the Name and Location of the PDB’s XML Metadata file. The XML file contains all the required information (metadata) to create the pluggable database PDB1 on a target CDB.

SQL> alter pluggable database pdb1 unplug into ‘/vol2/pdb1.xml’;

Pluggable database altered.

 

List the Current PDBs. You could see the Pluggable Database PDB1 is in the status MOUNTED.

SQL> show pdbs

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

 

Drop Pluggable Database PDB1 by preserving the datafiles of that respective PDB1. This is required for plugging in the PDB again to the CDB.

SQL> drop pluggable database pdb1 keep datafiles;

Pluggable database dropped.

keep datafiles – These keywords are used to retain or preserve the datafiles of the Pluggable Database PDB1. Later used when plug in the PDB1.

 

List the current PDBs and check if the Pluggable Database PDB1 has been unplugged or not.,

SQL> show pdbs

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

So from the output, we can clearly see that the Pluggable Database PDB1 has been unplugged from the Container Database CDB1.

 

Now let us Plugin the PDB1 to the Container Database.

 

Firstly, Run the DBMS_PDB.CHECK_PLUG_COMPATIBILITY function to determine whether the unplugged PDB is compatible with the CDB. Here the procedure should return the value ‘YES’.

SQL> SET SERVEROUTPUT ON
DECLARE
compatible CONSTANT VARCHAR2(3) :=
CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
pdb_descr_file => ‘/vol2/pdb1.xml’)
WHEN TRUE THEN ‘YES’
ELSE ‘NO’
END;
BEGIN
DBMS_OUTPUT.PUT_LINE(compatible);
END;
/SQL> 2 3 4 5 6 7 8 9 10 11

PL/SQL procedure successfully completed.

YES

 

Plugin the Pluggable Database PDB1 using the Metadata file (xml file) which we created during the Unplug of the same PDB1.

SQL> create pluggable database pdb1 using ‘/vol2/pdb1.xml’ nocopy tempfile reuse;

Pluggable database created.

 

List the PDBs and check if the PDB1 is created or not

SQL> show pdbs

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

So from the above output we can see that the Pluggable Database PDB1 has been Plugged in to the Container Database CDB1. The PDB will be created and it will be in the MOUNTED status.

 

 

Now open the PDB by issuing the below command,

SQL> alter pluggable database pdb1 open;

Pluggable database altered.

 

You could see the Pluggable Database PDB1 is 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
        4       PDB2                             READ WRITE         NO

 

Thanks for Visiting and Supporting 🙂

2 Comments

Leave a Reply

© 2020 ORACLE-SCN. All Rights Reserved.

error: Content is protected !!