Plugging and Unplugging in PDBs METHOD -II

Back to: Oracle DBA Tutorials

Plugging and Unplugging in PDBs METHOD -II

Previously we unplugged pdb4 and plugged it into pdbnew. In this article, we will unplug the pdbnew and plug it into pdbtest. We will do the same exercise as the previous one but with small changes

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

Exercise:

We have multiple pluggable databases like pdbseed, jayapdb, jayapdb1, pdb4, pdbnew. 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 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 con_id, name, open_mode from v$pdbs;

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

Step 2: Connect to pdbnew and check the data

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

Command: alter session set container=pdbnew

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

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:

Step 1: Close the pluggable database

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

Command: alter pluggable database pdbnew close immediate;

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

Query: select con_id, name, open_mode from v$pdbs;

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

Step 2: Unplug pdbnew to XML

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

Command: alter pluggable database pdbnew unplug into ‘/u01/app/oracle/oradata/pdbnew.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 pdbnew database. Let’s try to open the file and check what else we have in the file. Let’s go ahead and check the state of PDBNEW.

Query: select con_id, name, open_mode from v$pdbs;

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

We will be dropping the PDBNEW database but we are not going to drop the data files present in the database PDBNEW.

Command: drop pluggable database PDBNEW keep datafiles;

From the output, we can see the PDBNEW is dropped. Let’s go ahead and check the list of pluggable databases whether the database pdb4 is present or not.

Query: select con_id, name, open_mode from v$pdbs;

From the list, we don’t see the pluggable database PDBNEW. So, the database PDBNEW 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 an if statement and any other loops.

This code starts with the DECLARE to declare a variable named 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 pdbtest
Using ‘/u01/app/oracle/oradata/pdbtest.xml’
NOCOPY TEMPFILE REUSE;

Previously we used FILE_NAME_CONVERT and now we are using NOCOPY TEMPFILE REUSE which indicates we will use the old location for datafiles and also, we will use the same old temp files. Let’s go ahead and run the statement.

We can see Pluggable database PDBTEST created. Let’s go ahead and check the file system to check the folder for pdbtest.

But we don’t find the folder pdbtest because it will re-use the older datafiles and tempfiles and create a new database. Let’s go ahead and check the list of pluggable databases present.

We can see the status of PDBTEST 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 but the datafiles are reading from pdbnew folder. So, oracle reusing the unplugged database folder for new pluggable database

Note: we don’t recommend this method because maintaining each folder for each pluggable database is good practice. Each pluggable database needs to have a naming convention.