Thursday, September 1, 2016

Move a PDB Between Servers

This post list the steps for moving a PDB from it's current CDB to another CDB on a different server. The detonation CDB and the PDB are patched to the same level.
Destination CDB patches
SQL> select con_id,patch_id,action,status,description from cdb_registry_sqlpatch where con_id = 1 order by action_time;

    CON_ID   PATCH_ID ACTION          STATUS          DESCRIPTION
---------- ---------- --------------- --------------- ----------------------------------------------------------------------------------------------------
         1   21555660 APPLY           SUCCESS         Database PSU 12.1.0.2.5, Oracle JavaVM Component (Oct2015)
         1   21359755 APPLY           SUCCESS         Database Patch Set Update : 12.1.0.2.5 (21359755)
         1   21555660 ROLLBACK        SUCCESS         Database PSU 12.1.0.2.5, Oracle JavaVM Component (Oct2015)
         1   22674709 APPLY           SUCCESS         Database PSU 12.1.0.2.160419, Oracle JavaVM Component (Apr2016)
         1   22291127 APPLY           SUCCESS         Database Patch Set Update : 12.1.0.2.160419 (22291127)
PBD patches
SQL> select con_id,patch_id,action,status,description from cdb_registry_sqlpatch where con_id = 5 order by action_time;

    CON_ID   PATCH_ID ACTION          STATUS          DESCRIPTION
---------- ---------- --------------- --------------- ----------------------------------------------------------------------------------------------------
         5   21555660 APPLY           SUCCESS         Database PSU 12.1.0.2.5, Oracle JavaVM Component (Oct2015)
         5   21359755 APPLY           SUCCESS         Database Patch Set Update : 12.1.0.2.5 (21359755)
         5   21555660 ROLLBACK        SUCCESS         Database PSU 12.1.0.2.5, Oracle JavaVM Component (Oct2015)
         5   22674709 APPLY           SUCCESS         Database PSU 12.1.0.2.160419, Oracle JavaVM Component (Apr2016)
         5   22291127 APPLY           SUCCESS         Database Patch Set Update : 12.1.0.2.160419 (22291127)
As seen from above outputs both destination CDB and PDB has gone through the same set of patches and are currently at the same patch level.
The source CDB has three PDBs and the PDB called "PDBTHREE" will be moved.
SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ONEPDB                         READ WRITE NO
         4 TWOPDB                         READ WRITE NO
         5 PDBTHREE                       READ WRITE NO
Close the PBD and unplug it from the source CDB by specifying the metadata xml file.
SQL> alter pluggable database pdbthree close immediate;
Pluggable database altered.

SQL> alter pluggable database pdbthree unplug into '/home/oracle/backup/pdbthree_desc.xml';
Pluggable database altered.

SQL> select pdb_name, status from cdb_pdbs;

PDB_NAME   STATUS
---------- ---------
PDBTHREE   UNPLUGGED
PDB$SEED   NORMAL
ONEPDB     NORMAL
TWOPDB     NORMAL

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ONEPDB                         READ WRITE NO
         4 TWOPDB                         READ WRITE NO
         5 PDBTHREE                       MOUNTED
Drop the PDB with keep file option (default)
SQL> drop pluggable database pdbthree keep datafiles;
Pluggable database dropped.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ONEPDB                         READ WRITE NO
         4 TWOPDB                         READ WRITE NO
Copy the files over to the destination server. The location of the file is not the same as source location.
[oracle@hpc5 datafile]$ pwd
/opt/app/oracle/oradata/APCDB/3AE6245AE81713AFE0536300A8C02D00/datafile

[oracle@hpc5 datafile]$ scp -C * hpc1:/home/oracle/backup/pdbthree/
oracle@192.168.0.66's password:
o1_mf_pdbthree_cvy3w4f7_.dbf                                                                                                                                100%   10MB  10.0MB/s   00:00
o1_mf_sysaux_cvxvnvnw_.dbf                                                                                                                                  100%  580MB  24.2MB/s   00:24
o1_mf_system_cvxvnvnl_.dbf                                                                                                                                  100%  260MB  18.6MB/s   00:14
On the destination CDB run the compatibility check. In this case the output says that PDB is not compatible.
SQL> SET SERVEROUTPUT ON
DECLARE
SQL>   2  compatible CONSTANT VARCHAR2(3) :=
  3   CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(  pdb_descr_file => '/home/oracle/backup/pdbthree_desc.xml',  pdb_name => 'PDBTHREE')
  4    WHEN TRUE THEN 'YES'
  5    ELSE 'NO'
  6    END;
  7     BEGIN
  8     DBMS_OUTPUT.PUT_LINE(compatible);
  9     END;
 10     /
NO
Checking the plugin violation view shows the following (last two lines has been shortened)
SQL> select name,cause,status,type,action from PDB_PLUG_IN_VIOLATIONS;

NAME       CAUSE           STATUS    TYPE      ACTION
---------- --------------- --------- --------- --------------------------------------------------
PDBTHREE   SQL patch error PENDING   ERROR     Call datapatch to reinstall
PDBTHREE   SQL patch error PENDING   ERROR     Call datapatch to reinstall
PDBTHREE   SQL Patch       PENDING   ERROR     Call datapatch to install in the PDB or the CDB
PDBTHREE   SQL Patch       PENDING   ERROR     Call datapatch to install in the PDB or the CDB

SQL> select status,message from PDB_PLUG_IN_VIOLATIONS;

STATUS    MESSAGE
--------- ------------------------------------------------------------------------------------------------------------------------------------------------------
PENDING    (PSU bundle patch 160419 (Database Patch Set Update : 12.1.0.2.160419 (22291127)): APPLY SUCCESS):  with status  in the PDB.
PENDING    (SQL patch ID/UID 22674709/20057886 (Database PSU 12.1.0.2.160419, Oracle JavaVM Component (Apr2016)): APPLY SUCCESS):  with status  in the PDB.
PENDING   ... Update : 12.1.0.2.160419 (22291127)): Installed in the CDB but not in the PDB.
PENDING   ... Oracle JavaVM Component (Apr2016)): Installed in the CDB but not in the PDB.
Since it has been confirmed that both destination CDB and PDB being plugged in are both patched to same level this violation is ignored.



The destination uses OMF but different paths for PDBs and the root container files. This path is also different from the OMF path at the source CDB. Login to the CDB and set the db_create_file_dest used for PDBs (if a separate OMF path is used for PDBs) and create the PDB with clone and move clause. source_file_name_convert has been used to specify the file mapping where ('/opt/app/oracle/oradata/APCDB/3AE6245AE81713AFE0536300A8C02D00/datafile') refers to PDB data file path at source and ('/home/oracle/backup/pdbthree') refers to the location where data files were copied into.
SQL> alter system set db_create_file_dest='/ssdoracle/oradata/pdbs' scope=memory;
System altered.

SQL> create pluggable database pdbthree as clone using '/home/oracle/backup/pdbthree_desc.xml' 
source_file_name_convert=('/opt/app/oracle/oradata/APCDB/3AE6245AE81713AFE0536300A8C02D00/datafile','/home/oracle/backup/pdbthree') move;
Pluggable database created.

SQL> select pdb_name, status from cdb_pdbs;

PDB_NAME        STATUS
--------------- ---------
PDBTHREE        NEW
PDB$SEED        NORMAL
ONEPDB          NORMAL
TWOPDB          NORMAL

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ONEPDB                         READ WRITE NO
         4 TWOPDB                         READ WRITE NO
         5 PDBTHREE                       MOUNTED

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

    CON_ID NAME
---------- ----------------------------------------------------------------------------------------------------
         5 /ssdoracle/oradata/pdbs/APCDB/3AE70A7E4DC630F7E0534200A8C0F281/datafile/o1_mf_system_cvxzo517_.dbf
         5 /ssdoracle/oradata/pdbs/APCDB/3AE70A7E4DC630F7E0534200A8C0F281/datafile/o1_mf_sysaux_cvxzo529_.dbf
         5 /ssdoracle/oradata/pdbs/APCDB/3AE70A7E4DC630F7E0534200A8C0F281/datafile/o1_mf_pdbthree_cvxzo52b_.dbf
Finally open the PDB
SQL> alter pluggable database pdbthree open;
Pluggable database altered.

SQL>  select pdb_name, status from cdb_pdbs;

PDB_NAME        STATUS
--------------- ---------
PDBTHREE        NORMAL
PDB$SEED        NORMAL
ONEPDB          NORMAL
TWOPDB          NORMAL

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ONEPDB                         READ WRITE NO
         4 TWOPDB                         READ WRITE NO
         5 PDBTHREE                       READ WRITE NO
The CDB doesn't list any more plugin violations
SQL> select name,cause,status,type,action from PDB_PLUG_IN_VIOLATIONS;
no rows selected
Verify the patch levels are reflected
SQL> alter session set container=pdbthree;
Session altered.

SQL> select con_id,patch_id,action,status,description from cdb_registry_sqlpatch where con_id = 5 order by action_time;

    CON_ID   PATCH_ID ACTION     STATUS          DESCRIPTION
---------- ---------- ---------- --------------- ----------------------------------------------------------------------------------------------------
         5   21555660 APPLY      SUCCESS         Database PSU 12.1.0.2.5, Oracle JavaVM Component (Oct2015)
         5   21359755 APPLY      SUCCESS         Database Patch Set Update : 12.1.0.2.5 (21359755)
         5   21555660 ROLLBACK   SUCCESS         Database PSU 12.1.0.2.5, Oracle JavaVM Component (Oct2015)
         5   22674709 APPLY      SUCCESS         Database PSU 12.1.0.2.160419, Oracle JavaVM Component (Apr2016)
         5   22291127 APPLY      SUCCESS         Database Patch Set Update : 12.1.0.2.160419 (22291127)
This concludes the steps for moving a PDB between servers.

Related Posts
Remote Cloning of a PDB
Plugging a SE2 non-CDB as an EE PDB Using File Copying and Remote Link