Plugging and Unplugging in PDBs

Back to: Oracle DBA Tutorials

Plugging and Unplugging in PDBs

In this article, I am going to discuss Plugging and Unplugging in PDBs with Examples. Please read our previous article where we discussed Cloning Pluggable Database in Oracle with Examples.

Plugging and Unplugging in PDBs

Until now, we have learned how to create a pluggable database and clone pluggable databases. Now, let’s try to learn about plugging and unplugging pluggable databases.

What is Plugging and Unplugging?

Suppose, we have a server that consists of two container databases. Remember each container should have one Root database and one seed database. In CDB1 there is another pluggable database PDB1. We can unplug the PDB1 and plug the database into CDB2. When we take it off, we call it Unplug, and when we plug the database plugging. Unplugging a PDB is disassociating the PDB from its CDB. Plugging in a PDB is associating a PDB with a CDB.

Note: We can plug a PDB into the same or another CDB. So, we can remove the pluggable database from CDB1 and plug it back into CDB1 itself.

For example, let’s take a computer and a hard disk. A computer is taken as CDB1 and a hard disk is taken as PDB1. We can remove the hard disk from the computer and place it on another computer. Similarly, we can place the hard disk back on our computer as well. So, it’s similar to this.

Why do we need to Plug and unplug?

The DBA needs to test the performance of the container database with and without the pluggable databases. What would be the performance with the PDB and what is the performance without the PDB?

Steps to Perform Plugging and Unplugging:

1. Close the PDB1
2. Unplug PDB1 to XML
3. Drop the PDB1 But we keep the datafiles
4. Check compatibility
5. Plug the PDB1 using the XML

Close the PDB1: Before we unplug the database or we perform any operations we have to close the PDB.

Unplug PDB1 to XML: We will be generating the XML file from the pluggable database.

Drop the PDB1 but keep the data files: We will be dropping the pluggable database without dropping the data files because the XML contains only the structure of the database. It doesn’t contain any data associated with the database.

Check Compatibility: A compatibility check is checking if the PDB that we are going to plug into a container database is actually compatible with the container database or not. If we unplug and plug in the same database then we need to check the compatibility. We are going to unplug and plug in the same container database because we don’t have many container databases.

Plug the PDB1 using the XML: After we generate the XML, we will plug the database using the XML file which we will do as an exercise.

Example to understand Plugging and Unplugging in PDBs:

We have multiple pluggable databases like pdbseed, jayapdb, jayapdb1, pdb4, and JAYAPDB2. Remember that we have created pdb4 by cloning the database. We will unplug the pdb4 and plug the same with another name in the same container.

Note: Make sure the database is up and running fine and the listener is up and running fine. Open the SQL developer and connect to the database as sqlplus / as sysdba. Once connected to the database using the command “show con_name” to check the container name.

Pre-Steps Before Unplugging:

Step 1: Make sure all the pluggable databases are open.

Let us run a command to make all the pluggable databases open.

Command: alter pluggable database all open

Now, that the pluggable database is open let’s check whether all the pluggable databases are open.

Query: select name, open_mode from v$pdbs;

All the databases are up and running fine. Since we performing this on PDB4 the database is up and running. Now, let’s go ahead and check the data in PDB4.

Step 2: Connect to PDB4 and check the data

Let us connect to PDB4 and check the data in PDB4.

Command: alter session set container=PDB4;

Let’s go ahead and check the tables present in the pluggable database PDB4.

Query: select * from hr.employees;

We have so much data present in employees of hr user. Once the plugging is done, we have to make sure all the data is present in the new database. To close and unplug the database we have to connect to the root database

Command: alter session set container=CDB$ROOT

Steps to Plug and Unplug PDBs in Oracle:

Step 1: Close the pluggable database

The first step is to close the pluggable database. So, let’s go ahead and close the PDB4 database.

Command: alter pluggable database PDB4 close immediate;

Let’s check whether the database PDB4 is closed or not.

Query: select name, open_mode from v$pdbs;

So, the database PDB4 is in a mounted state. Let’s go ahead and apply the second step.

Step 2: Unplug PDB4 to XML

Let us go ahead and unplug the PDB4 to the XML file. The XML file that is altered from the database pdb4 will be stored in the path /u01/app/oracle/oradata/pdb4.xml. This path is mentioned by us. We can mention any path if we want.

Command: alter pluggable database pdb4 unplug into ‘/u01/app/oracle/oradata/pdb4.xml’

Go the same path as mentioned in the command ‘/u01/app/oracle/oradata/’ and there will be an XML file created.

We can see the XML file. This file contains the structure of the database. It doesn’t contain any data present in the pdb4 database. Let’s try to open the file and check what else we have in the file.

From the above image, we can see there is not much information in the XML file. It displays the version of the database, the path of the data files, and the name of the data files. We can see the datafile name as system01.dbf and if we scroll down a bit, we can see multiple datafile names. We do not see any information about the employees table in this file. Let us go ahead and check the state of PDB4.

Query: select name,open_mode from v$pdbs;

Step 3: Drop the pluggable database PDB4 but keep the data files.

We will be dropping the PDB4 database but we are not going to drop the data files present in the database PDB4. Let us go ahead and check if there are data files before dropping the database.

We can see there are data files present in the database pdb4. Now, let’s drop the database.

Command: drop pluggable database PDB4 keep datafiles;

From the output, we can see the PDB4 is dropped. Now, let’s go ahead and check if the data files are present or not.

So, the data files are present in the path. Let’s go ahead and check the list of pluggable databases whether the database pdb4 is present or not.

Query: select name,open_mode from v$pdbs;

From the list, we don’t see the pluggable database PDB4. So, the database PDB4 is dropped.

Step 4: Check the Compatibility

We have a script from oracle which determines if the XML file is compatible with the container database or not. This step is optional.

Code:

DECLARE
  status BOOLEAN;
BEGIN
  Status = DBMS_PDB.check_plug_compatibility(
                pdb_descr_file => '/u01/app/oracle/oradata/pdb4.xml',
                pdb_name => 'PDB4');

  IF status THEN
    DBMS_OUTPUT.PUT_LINE('compatible');
  ELSE
    DBMS_OUTPUT.PUT_LINE('incompatible');
  END IF;
END;

This code is a PLSQL code. PLSQL is a procedural language designed for oracle. It is an extension to SQL and has extension features like java, python. So, we can create if statement and any other loop statement.

This code starts with the DECLARE to declare a variable called status and BEGIN and it is the execution part of the code. We have declared a variable called status which is assigned to the package DBMS_PDB. check_plug_compatibility. If the result of the package is stored in the variable status. If the variable status is true then the XML file is compatible, else the XML file is not compatible. This is not a mandatory step. Our XML file is 100% compatible because we are plugging it into the same XML file.

Step 5: Plug the PDB using the XML

Let us go ahead and plug the database using the XML file. The process is to create a pluggable using the XML file.

Statement:

Create pluggable database pdbnew
Using ‘/u01/app/oracle/oradata/pdb4.xml’
FILE_NAME_CONVERT’(‘/u01/app/oracle/oradata/JAYADB/pdb4/’.
   ‘/u01/app/oracle/oradata/JAYADB/pdbnew/’);

The create statement will create the database using the XML file and the data files will be copied from the pdb4 folder to pdbnew folder. There is no pdbnew folder. Even though the folder is not present oracle will create the folder. Let’s go ahead and run the create statement.

We can see the pluggable database PDBNEW is created. Let’s go ahead and check the list of data files created in the pdbnew folder.

Let’s go ahead and check the list of pluggable databases again.

We can see the status of PDBNEW is mounted.

Step 6: Open the pluggable database and check the data

Let us go ahead and open the pluggable database and connect to the new pluggable database pdbnew.

Command: alter pluggable database pdbnew open;

Now, that the database is open let’s check the data present in the database of employees table.

Query: select * from hr.employees;

So, the data is present in employees table. Let’s go ahead and check the data files present in the database.

We can see the datafiles are present system, sysaux, undo, and temp. We understood how to plug and unplug the pluggable database. This is similar to cloning but we can unplug and plug into a different container database.

In the next article, I am going to discuss Plugging and Unplugging in PDBs Method-II in Oracle with Examples. Here, in this article, I try to explain Plugging and Unplugging in PDBs with Examples and I hope you enjoy this Plugging and Unplugging in PDBs article.