12c: Plugging unplugging Database

1- Unplugging the PDB

To unplug a PDB, you first close it and then generate an XML manifest file. The XML file contains information about the names and the full paths of the tablespaces, as well as data files of the unplugged PDB. The information will be used by the plugging operation.

a) connect to CDB and close PDB
sqlplus / as sysdba
SQL> alter pluggable database PDB1OR12C close immediate;

Pluggable database altered.
b) Unplug the closed PDB and then specify the path and name of the XML file.

SQL> alter pluggable database PDB1OR12C unplug into 'D:\APP\INAM\ORADATA\OR12C\PDBOR12C\PDB1OR12C.xml';

Pluggable database altered.

c) Drop the closed PDB and keep the data files. 
SQL> drop pluggable database PDB1OR12C keep datafiles;

Pluggable database dropped.

d) Verify the status of the unplugged PDB.
SQL> select pdb_name, status from cdb_pdbs where pdb_name = 'PDB1OR12C';

no rows selected

The unplugging operation makes changes in the PDB data files to record that the PDB was properly and successfully unplugged. Because the PDB is still part of the CDB, you can back it up in Oracle Recovery Manager (Oracle RMAN). This backup provides a convenient way to archive the unplugged PDB. After backing it up, you then remove it from the CDB catalog. But, of course, you must preserve the data files for the subsequent plugging operation.

2- Plugging the PDB into the Same or another CDB
NOCOPY Method

a) Before starting the plugging operation, make sure that the to-be-plugged-in PDB is compatible with the new host CDB. Execution of the PL/SQL block raises an error if it is not compatible. 

SQL> set serveroutput on
SQL> DECLARE
  2     compatible BOOLEAN := FALSE;
  3  BEGIN
  4     compatible := DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
  5          pdb_descr_file => 'D:\app\Inam\oradata\or12c\pdbor12c\PDB1OR12C.xml');
  6     if compatible then
  7        DBMS_OUTPUT.PUT_LINE('Is pluggable PDB1OR12C compatible? YES');
  8     else DBMS_OUTPUT.PUT_LINE('Is pluggable PDB1OR12C compatible? NO');
  9     end if;
 10  END;
 11  /
Is pluggable PDB1OR12C compatible? YES

PL/SQL procedure successfully completed.


b) Use the data files of the unplugged PDB to plug the PDB into another CDB without any copy.

SQL> create pluggable database pdb_plug_nocopy using  'D:\APP\INAM\ORADATA\OR12C\PDBOR12C\PDB1OR12C.xml'
  2  NOCOPY
  3  TEMPFILE REUSE;

Pluggable database created.

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

select pdb_name, status from cdb_pdbs;

PDB_NAME	STATUS
PDB$SEED	NORMAL
PDB_PLUG_NOCOPY	NEW

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

OPEN_MODE
MOUNTED

SQL> alter pluggable database PDB_PLUG_NOCOPY open;
Pluggable database altered.

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

COPY Method

a) Close the PDB

SQL> alter pluggable database pdb_plug_nocopy close immediate;
b) Create and define a destination for the new data files, plug the unplugged PDB into the CDB, and then copy the data files of the unplugged PDB.
eg; D:\app\Inam\oradata\or12c\pdb_plug_copy

c) Unplug PDB

 SQL> alter pluggable database pdb_plug_nocopy unplug into 'D:\APP\INAM\ORADATA\OR12C\PDBOR12C\pdb_plug_nocopy.xml';

Pluggable database altered.
c) Drop PDB keeping datafiles

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

e) Use the data files of the unplugged PDB to plug the PDB into the CDB and copy the data files to a new location.
SQL> create pluggable database pdb_plug_copy using  'D:\APP\INAM\ORADATA\OR12C\PDBOR12C\pdb_plug_nocopy.xml'
  2  COPY
  3  FILE_NAME_CONVERT=('D:\app\Inam\oradata\or12c\pdbor12c','D:\app\Inam\oradata\or12c\pdb_plug_copy');

Pluggable database created.

f) Verify the status and open mode of the plugged PDB.
select pdb_name, status from cdb_pdbs where pdb_name='PDB_PLUG_COPY';

PDB_NAME	STATUS
PDB_PLUG_COPY	NEW

SQL> alter pluggable database PDB_PLUG_COPY open;

Pluggable database altered.

select name from v$datafile where con_id=3

NAME
D:\APP\INAM\ORADATA\OR12C\PDB_PLUG_COPY\SYSTEM01.DBF
D:\APP\INAM\ORADATA\OR12C\PDB_PLUG_COPY\SYSAUX01.DBF
D:\APP\INAM\ORADATA\OR12C\PDB_PLUG_COPY\SAMPLE_SCHEMA_USERS01.DBF
D:\APP\INAM\ORADATA\OR12C\PDB_PLUG_COPY\EXAMPLE01.DBF
D:\APP\INAM\ORADATA\OR12C\PDB_PLUG_COPY\LDATA01.DBF

MOVE Method

a) Close PDB
SQL> alter pluggable database pdb_plug_copy close immediate; 

Pluggable database altered.

b) Create and define a destination for the new data files, plug the unplugged PDB into the CDB, and then copy the data files of the unplugged PDB.
eg;D:\app\Inam\oradata\or12c\pdb_plug_move



c) Unplug PDB
SQL> alter pluggable database pdb_plug_copy unplug into 'D:\APP\INAM\ORADATA\OR12C\PDBOR12C\pdb_plug_copy.xml';

Pluggable database altered.



d) Drop PDB keeping datafiles
SQL> drop pluggable database pdb_plug_copy keep datafiles;

Pluggable database dropped.
e) Plug the PDB into the CDB and move the data files to a new location.
SQL> create pluggable database pdb_plug_move using 'D:\app\Inam\oradata\or12c\pdbor12c\pdb_plug_copy.xml'
  2  MOVE
  3  FILE_NAME_CONVERT=('D:\app\Inam\oradata\or12c\pdb_plug_copy','D:\app\Inam\oradata\or12c\pdb_plug_move
');

Pluggable database created. 

You can use AS CLONE clause also.
create pluggable database pdb_plug_move
AS CLONE using 'D:\app\Inam\oradata\or12c\pdbor12c\pdb_plug_copy.xml'
MOVE
FILE_NAME_CONVERT=('D:\app\Inam\oradata\or12c\pdb_plug_copy','D:\app\Inam\oradata\or12c\pdb_plug_move');
f) Verify the status and open mode of the plugged PDB.

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

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