Unplugging and Plugging in of a Pluggable Database

In a multitenant environment we can unplug a pluggable database (PDB) from a container database (CDB), and then plug it into another CDB. After we unplug a PDB, it needs to be dropped from dba CDB as it become unusable in this CDB. We can plug the same PDB back into the same CDB as well. In this article I will explain how we perform this operation. I will unplug a database and plug it back in the same CDB, method of plugging it into a different CDB is essentially the same.

Unplugging a pluggable database

1)
Log into the PDB using sqlplus and list the location of all the datafiles of the database that is being unplugged. I will use my pluggable database “pdb2” for this example.

C:\>sqlplus "/ as sysdba" SQL*Plus: Release 12.1.0.2.0 Production on Thu May 26 13:07:31 2016 Copyright (c) 1982, 2014, Oracle.  All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> alter session set container=pdb2; SQL> select file_name from dba_data_files; FILE_NAME -------------------------------------------------------------------------------- C:\APP\SALMQURE\ORADATA\SALMAN12\PDB2\USERS01.DBF C:\APP\SALMQURE\ORADATA\SALMAN12\PDB2\SYSTEM01.DBF C:\APP\SALMQURE\ORADATA\SALMAN12\PDB2\SYSAUX01.DBF

2)
Close the pluggable database.

SQL> alter pluggable database pdb2 close immediate; Pluggable database altered.

3)
Unplug the database. While executing the command to unplug the database, we need to specify path for the xml file that would get created during this process, and would contain the information about this pluggable database. Same xml file will be used during plugging this database into a CDB.

SQL> alter pluggable database pdb2 unplug into 'c:\pdb2_db.xml'; Pluggable database altered.

4)
After the above step, we need to copy the datafiles listed in step, one to the host of CDB where we want to plug in this database. Since I will be plugging in this PDB back into the same CDB, so I will not copy datafile anywhere. Also copy the xml file along with the datafiles

5)
For this example, I will drop the existing  pdb2 so that I could plug in this database back. I am using clause “KEEP DATAFILES” with DROP command so that datafiles don’t get dropped and can be reused from same location while plugging in to the CDB. If you are plugging in database on a different host, you can skip this step.

SQL> drop pluggable database pdb2 keep datafiles; Pluggable database dropped.

Following steps will be performed in the destination container database in order to plug in pdb2.

Plugging in a pluggable database in a container database

6)

In the destination CDB, compatibility of pluggable database should be checked against CDB where it is being plugged in. I simply copied following code from Oracle’s documentation to perform this compatibility test. Highlighted text should be modified to match the name/path of our xml ('c:\pdb2_db.xml') file as well as PDB name (pdb2). The output of this script should be “YES” to proceed further.

SET SERVEROUTPUT ON  DECLARE compatible CONSTANT VARCHAR2(3) := CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY( pdb_descr_file => 'c:\pdb2_db.xml', pdb_name       => 'PDB2') WHEN TRUE THEN 'YES' ELSE 'NO' END; BEGIN DBMS_OUTPUT.PUT_LINE(compatible); END;  / YES PL/SQL procedure successfully completed.

7)
Now we will plug in pdb2 in the destination CDB. Datafiles location on the destination host/CDB can be same or different. If location is same, we don’t need to do anything, otherwise we will need to use FILE_NAME_CONVERT option of CREATE PLUGGABLE DATABASE command to specify the new location of the datafiles. By default, the command assumes location of the datafiles as it is recorded in the xml file.
For this example, I am changing the location of the datafiles from 'C:\app\salmqure\oradata\salman12\pdb2'to 'C:\app\salmqure\oradata\pdb2'. Datafiles will be copied to the new location during plugging in phase.

SQL> create pluggable database pdb2 using 'c:\pdb2_db.xml'  copy file_name_convert=('C:\app\salmqure\oradata\salman12\pdb2','C:\app\salmqure\oradata\pdb2'); Pluggable database created. SQL> alter pluggable database pdb2 open; Pluggable database altered.

COPY keyword is default to be used to copy files to the new destination. If destination is same as source, specify NOCOPY.

Important Points to note

• By default source and destination location of the datafiles is same and that is mentioned in the .xml file. Before plugging in to the destination CDB, the datafiles should be copied to same location as these were on source because same location is mentioned in the .xml file and datafiles will be searched on the same location.
• If you have copied datafiles on a different location on the destination, you will need to edit .xml file to specify the location where you have copied the datafiles so that during plugging in phase, datafiles can be searched at this location.