Sunday, September 18, 2016

Remote Cloning of a PDB

Similar to non-CDB, PDB too could cloned over a remote link. In this case both source and remote DBs are CDBs and one PDB is cloned on the local DB. As the first step create a TNS entry and a link on the local DB. The remote PDB is called PDB1K
PDB1KTNS =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.88)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdb1k)
    )
  )

SQL> create database link pdb1k_link connect to  system identified by system using 'PDB1KTNS';
Validate the link by querying a view on the remote PDB
SQL> select name from v$pdbs@pdb1k_link;

NAME
------------------------------
PDB1K
If OMF is used nothing else is needed and PDB could be cloned. However in this case a data files of the remotely cloned PDBs are stored separately. To achieve that set the db_create_dest parameter to desired location with scope set to memory.
SQL> alter system set db_create_file_dest='/opt/app/oracle/oradata/remoteclones' scope=memory;

SQL> show parameter db_create

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      /opt/app/oracle/oradata/remoteclones


Put the source PDB into read only mode. Refer oracle doc for full list of pre-reqs. Create the PDB, the new PDB is named PDB1KRMT. 
SQL> create pluggable database pdb1krmt from pdb1k@pdb1k_link;

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 PDB1KRMT                       MOUNTED
Finally open the PDB
SQL> alter pluggable database pdb1krmt open;

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 PDB1KRMT                       READ WRITE NO
Verify the PDB data files are created in the intended location
SQL>  select name from v$datafile;

NAME
------------------------------------------------------------------------------------------------------------------------
/opt/app/oracle/oradata/CGCDB/datafile/o1_mf_undotbs1_cvchzywd_.dbf
/opt/app/oracle/oradata/remoteclones/CGCDB/3B5F12ED0B6F4762E0536300A8C0A85F/datafile/o1_mf_system_cwfq6dd5_.dbf
/opt/app/oracle/oradata/remoteclones/CGCDB/3B5F12ED0B6F4762E0536300A8C0A85F/datafile/o1_mf_sysaux_cwfq6ddt_.dbf
/opt/app/oracle/oradata/remoteclones/CGCDB/3B5F12ED0B6F4762E0536300A8C0A85F/datafile/o1_mf_pdb1ktbs_cwfq6ddv_.dbf
Using the USER_TABLESPACES clause available on 12.1.0.2 it is possible to clone the new PDB only with a subset of tablespaces. Assume that original PDB has 3 application specific tablespaces.
SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
TEMP                           ONLINE
APP1                           ONLINE
APP2                           ONLINE
APP3                           ONLINE
Only 2 of them are wanted in the newly cloned PDB. It is possible to include just these two tablespaces in the user_tablespaces clause excluding all other tablespaces.
create pluggable database pdb1krmt from pdb1k@pdb1k_link USER_TABLESPACES=('APP1','APP3');
Tablespace name exists but status will be offline with data file missing as well.
SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
TEMP                           ONLINE
APP1                           ONLINE
APP2                           OFFLINE
APP3                           ONLINE

SQL> select tablespace_name,status,file_name from dba_data_files;

TABLESPACE_NAME                STATUS    FILE_NAME
------------------------------ --------- ----------------------------------------------------------------------------------------------------
SYSTEM                         AVAILABLE /opt/app/oracle/oradata/CGCDB/3BED1E0F3E63672CE0536300A8C0356F/datafile/o1_mf_system_cx0bpkwo_.dbf
SYSAUX                         AVAILABLE /opt/app/oracle/oradata/CGCDB/3BED1E0F3E63672CE0536300A8C0356F/datafile/o1_mf_sysaux_cx0bpkwy_.dbf
APP1                           AVAILABLE /opt/app/oracle/oradata/CGCDB/3BED1E0F3E63672CE0536300A8C0356F/datafile/o1_mf_app1_cx0bpkwz_.dbf
APP3                           AVAILABLE /opt/app/oracle/oradata/CGCDB/3BED1E0F3E63672CE0536300A8C0356F/datafile/o1_mf_app3_cx0bpkx1_.dbf
APP2                           AVAILABLE /opt/app/oracle/product/12.1.0/dbhome_2/dbs/MISSING00122
Offline tablespace could be dropped to clean up the new PDB
SQL> drop tablespace app2 including contents and datafiles cascade constraints;
Same could be done when plugging non-CDB as PDBs as well.
SQL> create pluggable database stdpdb from std12c1@std_link USER_TABLESPACES=('APP1','APP3');
Run the post cloning steps and verify the tablespace list
SQL> select tablespace_name,status from dba_tablespaces order by 2,1;

TABLESPACE_NAME                STATUS
------------------------------ ---------
APP2                           OFFLINE
TOOLS                          OFFLINE
UNDOTBS1                       OFFLINE
USERS                          OFFLINE
APP1                           ONLINE
APP3                           ONLINE
SYSAUX                         ONLINE
SYSTEM                         ONLINE
TEMP                           ONLINE

SQL> select tablespace_name,status,file_name from dba_data_files;

TABLESPACE_NAME                STATUS    FILE_NAME
------------------------------ --------- ----------------------------------------------------------------------------------------------------
SYSTEM                         AVAILABLE /opt/app/oracle/oradata/CGCDB/3BEC00E8AA7862ECE0536300A8C0B5F7/datafile/o1_mf_system_cx060v17_.dbf
SYSAUX                         AVAILABLE /opt/app/oracle/oradata/CGCDB/3BEC00E8AA7862ECE0536300A8C0B5F7/datafile/o1_mf_sysaux_cx060v18_.dbf
USERS                          AVAILABLE /opt/app/oracle/product/12.1.0/dbhome_2/dbs/MISSING00114
TOOLS                          AVAILABLE /opt/app/oracle/product/12.1.0/dbhome_2/dbs/MISSING00115
APP1                           AVAILABLE /opt/app/oracle/oradata/CGCDB/3BEC00E8AA7862ECE0536300A8C0B5F7/datafile/o1_mf_app1_cx060v1b_.dbf
APP2                           AVAILABLE /opt/app/oracle/product/12.1.0/dbhome_2/dbs/MISSING00117
APP3                           AVAILABLE /opt/app/oracle/oradata/CGCDB/3BEC00E8AA7862ECE0536300A8C0B5F7/datafile/o1_mf_app3_cx060v1c_.dbf
Undo tablespace within the PDB cannot be removed (2067414.1).
SQL> drop tablespace UNDOTBS1 including contents and datafiles cascade constraints;
drop tablespace UNDOTBS1 including contents and datafiles cascade constraints
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use
Offline undo tablespace in this case is the undo tablespace on non-CDB. This is because the CDB undo tablespace name and cloned non-CDB tablespace name is the same and undo is not local to PDB but common to entire CDB. It was not possible to get rid of the undotbs1 offline status even after switching the default undo tablespace of the CDB to a different undo tablespace.
This clause does not apply to the SYSTEM, SYSAUX, or TEMP tablespaces.

Related Posts
Move a PDB Between Servers
Plugging a SE2 non-CDB as an EE PDB Using File Copying and Remote Link
Posted by at
Labels: , ,