Unplug a PDB from source and plug into Target Database

=====================================================
TO CREATE A PLUGGABLE DATABASE USING NOCOPY METHOD
=====================================================
The PDB to be unplugged need to be closed on all instances where it is up else when unplugging will get an error as below.

SQL> ALTER PLUGGABLE DATABASE orclpdb2 UNPLUG INTO 'D:\oracle\oracle_objects\orclpdb2.xml';
ALTER PLUGGABLE DATABASE orclpdb2 UNPLUG INTO 'D:\oracle\oracle_objects\orclpdb2.xml'
*
ERROR at line 1:
ORA-65025: Pluggable database ORCLPDB2 is not closed on all instances.

To close and unplug the database. The unplugged database metadata information will be save in an xml file in a path specified when unplugging the database

SQL> alter pluggable database orclpdb2 close;

Pluggable database altered.

SQL> ALTER PLUGGABLE DATABASE orclpdb2 UNPLUG INTO 'D:\oracle\oracle_objects\orclpdb2.xml';

Pluggable database altered.

The status of the unplugged database can be checked with the command below

SQL> SELECT name, open_mode  FROM v$pdbs ORDER BY name;
----------------------------------
ORCLPDB            READ WRITE
ORCLPDB2           MOUNTED

SQL> select pdb_name, status from dba_pdbs;
-------------------------------------
ORCLPDB            NORMAL
ORCLPDB2           UNPLUGGED

Once the database is unplugged it can be dropped with command below

SQL> DROP PLUGGABLE DATABASE orclpdb2  ;

For checking the compatibility of the unplugged database to the destination database, following block of code can be run on the target database

SET SERVEROUTPUT ON
DECLARE
  l_result BOOLEAN;
BEGIN
  l_result := DBMS_PDB.check_plug_compatibility(
                pdb_descr_file => 'D:\oracle\oracle_objects\orclpdb2.xml',
                pdb_name       => 'pdb2');

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

The unplugged database can be plugged to the target database with the below command. Once the database is created and opened, it can use the alter session command to connect to the new plugged in database. --on target database

SQL> CREATE PLUGGABLE DATABASE PDB_PLUG_NOCOPY USING 'D:\ORACLE\ORACLE_OBJECTS\ORCLPDB2.XML'
     NOCOPY
     TEMPFILE REUSE;

SQL> alter pluggable database pdb_plug_nocopy open;

SQL> ALTER SESSION SET CONTAINER = pdb_plug_nocopy;

SQL> sho con_id con_name;

To verify the location of the datafiles on the target database.

SQL> select name from v$datafile where con_id=5;

============================================================
TO UNPLUG AND PLUG A PLUGGABLE DATABASE USING COPY METHOD
============================================================

SQL> create pluggable database orclpdb4 
admin user PDBADM 
identified by PDBADM
FILE_NAME_CONVERT=('D:\app\oracle\oradata\ORCL\pdbseed','D:\app\oracle\oradata\ORCL\orclpdb4');

SQL> alter pluggable database orclpdb4 open;
SQL> ALTER SESSION SET CONTAINER = orclpdb4;

Create a table PDB4HR.PDB_INFO on source database and insert rows. On target database you can verify that all the data in new plugged in database is accessible.

SQL>CREATE USER PDB4HR IDENTIFIED BY PDB4HR CONTAINER=CURRENT;
SQL>grant create session to PDB4HR;
SQL>ALTER USER PDB4HR QUOTA UNLIMITED ON SYSTEM;
SQL>GRANT CREATE TABLE TO PDB4HR;

SQL>CREATE TABLE PDB4HR.PDB_INFO 
   (	ID NUMBER, 
	PDB_NAME VARCHAR2(100 BYTE), 
	SCHEMA_NAME VARCHAR2(100 BYTE), 
	RMKS VARCHAR2(100 BYTE)
   ) 
TABLESPACE SYSTEM;
  
SQL> Insert into PDB_INFO (ID,PDB_NAME,SCHEMA_NAME,RMKS) values (1,'ORCLPDB4','PDB4HR','TESTING PLUG UNPLUG'); 

SQL> alter pluggable database orclpdb4 close immediate;

SQL> alter pluggable database ORCLPDB4 unplug into 'D:\oracle\oracle_objects\orclpdb4.xml';

SQL> drop pluggable database ORCLPDB4 keep datafiles;

The unplugged database can be plugged into the target database using the COPY command as below

SQL> create pluggable database alphapdb4_plug_copy using 'D:\oracle\oracle_objects\orclpdb4.xml'
COPY
FILE_NAME_CONVERT=('D:\app\oracle\oradata\ORCL\ORCLPDB4','D:\app\oracle\oradata\ALPHA\pdb4_plug_copy');

Pluggable database created.

SQL> alter pluggable database ALPHAPDB4_PLUG_COPY open;

To verify the stautus of the new plugged in database

SQL> select pdb_name, status from cdb_pdbs where pdb_name='ALPHAPDB4_PLUG_COPY';

SQL> select open_mode from v$pdbs where name='ALPHAPDB4_PLUG_COPY';

Run the select command on PDB4HR.PDB_INFO to verfiy that data from source database is copied to the target database.

SQL> select * from PDB4HR.PDB_INFO;

------------------------------------------------------------------------------------------------------------------------
========================================================================
TO UNPLUG AND PLUG A PLUGGABLE DATABASE USING USING ARCHIVE .PDB FILE --MOVE METHOD
========================================================================

SQL> create pluggable database orclpdb5 
     admin user PDBADM 
     identified by PDBADM
     FILE_NAME_CONVERT=('D:\app\oracle\oradata\ORCL\pdbseed','D:\app\oracle\oradata\ORCL\orclpdb5');

SQL> alter pluggable database orclpdb5 open;
SQL> ALTER SESSION SET CONTAINER = orclpdb5;

SQL> CREATE USER PDB5HR IDENTIFIED BY PDB5HR CONTAINER=CURRENT;
	grant create session to PDB5HR;
	ALTER USER PDB5HR QUOTA UNLIMITED ON SYSTEM;
	GRANT CREATE TABLE TO PDB5HR;

Create a table PDB5HR.PDB_INFO on source database and insert rows. On target database you can verify that all the data in new plugged in database is accessible.

SQL> CREATE TABLE PDB5HR.PDB_INFO 
	   (	ID NUMBER, 
		PDB_NAME VARCHAR2(100 BYTE), 
		SCHEMA_NAME VARCHAR2(100 BYTE), 
		RMKS VARCHAR2(100 BYTE)
	   ) 
	  TABLESPACE SYSTEM ;
  
SQL> Insert into PDB_INFO (ID,PDB_NAME,SCHEMA_NAME,RMKS) values (1,'ORCLPDB5','PDB5HR','TESTING PLUG UNPLUG'); 

To unplug to .pdb file

SQL> alter pluggable database orclpdb5 close immediate;

SQL> alter pluggable database orclpdb5 unplug into 'D:\oracle\oracle_objects\orclpdb5.pdb';

Verify the staus of the unplugged database

SQL> SELECT name, open_mode  FROM v$pdbs ORDER BY name;

SQL> select pdb_name, status from dba_pdbs;

SQL> drop pluggable database orclpdb5 keep datafiles;

check compatibility on target database

SET SERVEROUTPUT ON
DECLARE
  l_result BOOLEAN;
BEGIN
  l_result := DBMS_PDB.check_plug_compatibility(
                pdb_descr_file => 'D:\oracle\oracle_objects\orclpdb5.pdb',
                pdb_name       => 'pdb5_plug');

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

The unplugged .pdb file can now be created on the target database as below

SQL> CREATE PLUGGABLE DATABASE pdb5_plug USING 'D:\oracle\oracle_objects\orclpdb5.pdb'
SQL> CREATE_FILE_DEST='D:\app\oracle\oradata\ALPHA\pdb5_plug_pdb';

SQL> alter pluggable database pdb5_plug open;

The file system becomes OMF in below path

D:\app\oracle\oradata\ALPHA\pdb5_plug_pdb\ALPHA\F6168B3C18554A4CADFACAA8BF8CC82E\DATAFILE

==================================================================
TO UNPLUG AND PLUG A PLUGGABLE DATABASE USING USING MOVE METHOD
==================================================================

SQL> create pluggable database orclpdb6 
	admin user PDBADM 
	identified by PDBADM
	FILE_NAME_CONVERT=('D:\app\oracle\oradata\ORCL\pdbseed','D:\app\oracle\oradata\ORCL\orclpdb6');

SQL> alter pluggable database orclpdb6 open;
SQL> ALTER SESSION SET CONTAINER = orclpdb6;

Create a table PDB6HR.PDB_INFO on source database and insert rows. On target database you can verify that all the data in new plugged in database is accessible.

SQL> CREATE USER PDB6HR IDENTIFIED BY PDB6HR CONTAINER=CURRENT;
	grant create session to PDB6HR;
	ALTER USER PDB6HR QUOTA UNLIMITED ON SYSTEM;
	GRANT CREATE TABLE TO PDB6HR;

Create a table PDB6HR.PDB_INFO on source database and insert rows. On target database you can verify that all the data in new plugged in database is accessible.

SQL> CREATE TABLE PDB6HR.PDB_INFO 
	   (	ID NUMBER, 
		PDB_NAME VARCHAR2(100 BYTE), 
		SCHEMA_NAME VARCHAR2(100 BYTE), 
		RMKS VARCHAR2(100 BYTE)
	   ) 
	  TABLESPACE SYSTEM ;
  
SQL> Insert into PDB_INFO (ID,PDB_NAME,SCHEMA_NAME,RMKS) values (1,'ORCLPDB6','PDB6HR','TESTING PLUG UNPLUG'); 

SQL> alter pluggable database orclpdb6 close immediate;

SQL> alter pluggable database orclpdb6 unplug into 'D:\oracle\oracle_objects\orclpdb6.xml';

SQL> SELECT name, open_mode  FROM v$pdbs ORDER BY name;

SQL> select pdb_name, status from dba_pdbs;

SQL> drop pluggable database orclpdb6 keep datafiles;

On target database

DECLARE
  l_result BOOLEAN;
BEGIN
  l_result := DBMS_PDB.check_plug_compatibility(
                pdb_descr_file => 'D:\oracle\oracle_objects\orclpdb6.xml',
                pdb_name       => 'pdb6_plug_move');

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

SQL> create pluggable database pdb6_plug_move using 'D:\oracle\oracle_objects\orclpdb6.xml'
	MOVE
	FILE_NAME_CONVERT=('D:\app\oracle\oradata\ORCL\orclpdb6','D:\app\oracle\oradata\ALPHA\pdb6_plug_move');

SQL> alter pluggable database pdb6_plug_move open;