How to Unplug and Plug PDB(Pluggable DB) multiple Options- Copy,Nocopy & Move

How to Unplug and Plug PDB(Pluggable DB) multiple Options- Copy , Nocopy & Move

Oracle offers to unplug a PDB from one CDB and then plug into a different CDB. This will be another additional feature of PDB to give the highest availability and scalability the database systems may need in a cloud infrastructure environment. 

In this blog, We will unplug a PDB(Pluggable DB) from one container database and Plug them into another container database using all three option.

We have two Container database CDB1 and CDB2. CDB1 is having one PDB(Pluggable database) PDB1.

1) Unplug Pluggable database PDB1  from CBD1 Container database.

. oraenv [enter cdb1 at the prompt]

sqlplus / as sysdba

alter pluggable database pdb1 close immediate;

Unplug the closed PDB and then specify the path and name of the XML file:

alter pluggable database pdb1 unplug into '/u01/app/oracle/oradata/pdb1.xml';

Drop the closed PDB and keep the data files:

drop pluggable database pdb1 keep datafiles;

Verify the status of the unplugged PDB:
select pdb_name, status from cdb_pdbs where pdb_name in ('PDB1', 'PDB2');
[you should see no rows])

exit

2) Check the compatibility for plugging PDB1 to new Container databaseCDB2 useing the following plsql:

sqlplus / as sysdba [if cdb2 is not started up, start it up now.]

set serveroutput on
DECLARE
   compatible BOOLEAN := FALSE;
BEGIN  
   compatible := DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
        pdb_descr_file => '/u01/app/oracle/oradata/pdb1.xml');
   if compatible then
      DBMS_OUTPUT.PUT_LINE('Is pluggable PDB1 compatible? YES');
   else DBMS_OUTPUT.PUT_LINE('Is pluggable PDB1 compatible? NO');
   end if;
END;

/

NOTE: If the scripts returns NO, then check PDB_PLUG_IN_VIOLATIONS view

SQL> col cause for a10
col name for a10
col message for a35 word_wrapped
select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS where name='PDB1';

Resolve all the violation reported by the above command before proceeding.

3) Plug in copy method.

Now, We are going to plug-in Pluggable databas PDB1 to CDB2 container database  using copy method.

Create new directory for datafile of new pluggable database:

mkdir /u01/app/oracle/oradata/cdb2/pdb_plug_copy

Create pluggable database:

create pluggable database pdb_plug_copy using '/u01/app/oracle/oradata/pdb2.xml'
COPY
FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdb2','/u01/app/oracle/oradata/cdb2/pdb_plug_copy');

Verify new Pluggable database:

select pdb_name, status from cdb_pdbs where pdb_name='PDB_PLUG_COPY';
select open_mode from v$pdbs where name='PDB_PLUG_COPY';

To list the data files of the plugged PDB:
select name from v$datafile where con_id=3;

NOTE: In COPY: The datafiles of the PDB1 remain intact, and the PDB1 is copied to create a PDB2 at the new location and keep the original datafiles intact at the original location. 

4) Plug in nocopy method

Now, We are going to plug-in Pluggable databas PDB1 to CDB2 container database  using nocopy method.

create pluggable database pdb_plug_nocopy using '/u01/app/oracle/oradata/pdb1.xml'
NOCOPY TEMPFILE REUSE;

This operation lasts a few seconds. The original data files of the unplugged PDB now belong to the new plugged-in PDB in the new host CDB. A file with the same name as the temp file specified in the XML file exists in the target location. Therefore, the TEMPFILE_REUSE clause is required.

Verify new Pluggable database:

select pdb_name, status from cdb_pdbs where pdb_name='PDB_PLUG_NOCOPY';
select open_mode from v$pdbs where name='PDB_PLUG_NOCOPY';

List the data files of the plugged PDB:
select name from v$datafile where con_id=3;

5) Plug in move method.

Now, We are going to plug-in Pluggable databas PDB1 to CDB2 container database  using move method.

Create and define a destination for the new data files, use the data files of the unplugged PDB to plug the PDB into another CDB, and then move the data files to another location.

mkdir /u01/app/oracle/oradata/cdb2/pdb_plug_move

Create pluggable database:

create pluggable database pdb_plug_move using '/u01/app/oracle/oradata/pdb2.xml'
MOVE
FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdb2','/u01/app/oracle/oradata/cdb2/pdb_plug_move');

NOTE: Above command might fail with non-unique GUID message. This is a good example of using the AS CLONE clause. 

create pluggable database pdb_plug_move
AS CLONE using '/u01/app/oracle/oradata/pdb2.xml'
MOVE
FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdb2','/u01/app/oracle/oradata/cdb2/pdb_plug_move');

Verify new Pluggable database:

select pdb_name, status from cdb_pdbs where pdb_name='PDB_PLUG_MOVE';

select open_mode from v$pdbs where name='PDB_PLUG_MOVE';

List the data files of the plugged PDB:

select name from v$datafile where con_id=5;

6) Open and Verify connection to all three pluggable database.

alter pluggable database pdb_plug_nocopy open;

alter pluggable database pdb_plug_copy open;

alter pluggable database pdb_plug_move open;

Connect to the plugged-in PDBs and verify the container name that you are connected to. 

connect sys/oracle@localhost:1521/pdb_plug_nocopy AS SYSDBA
show con_name

connect sys/oracle@localhost:1521/pdb_plug_copy AS SYSDBA
show con_name

connect sys/oracle@localhost:1521/pdb_plug_move AS SYSDBA
show con_name

You can read more about Oracle PDB from below books:

Oracle Database 19c DBA By Examples: Installation and Administration

==========================================================

You can also , Visit our Blogs for related topic on Pluggable and Container database:

1)Oracle convert Non-Container database to Pluggable database

2)Manual Creation and Drop of Pluggable Database - Oracle 12c & 19c

3)How to Clone Pluggable database within same Container Database

4)How to clone Pluggable Database from one container to different Container Database