At times, we might need to duplicate a production pluggable database to a Past Point in Time without disturbing the production database itself, in order to:

  • View the data in the Production Database (PDB) as it appeared then
  • Perform testing
  • Generate reports, etc.

Duplication of a PDB slightly differs from that of a non-CDB. In order to duplicate the PDB, an auxiliary instance has to be created as a Container Database (CDB) on the same or different host. The PDB can be restored / recovered point-in-time using older backups of production database (CDB) and required archive logs so as not to affect the production database. On duplication of PDB(s), RMAN duplicates the root (CDB$ROOT) and the seed database (PDB$SEED) as well. The resulting duplicate database is a fully-functional CDB that contains the root, the seed database, and the duplicated PDBs. Subsequently, the PDB may be plugged into another CDB.

In this article, I will demonstrate the entire procedure for plugging a point-in-time recovered PDB into a CDB on a different host.

Current scenario:

Source:

  • Host: host01
  • Container Database 12.1.0.2c: cdb1 running in ARCHIVELOG mode.
  • Pluggable databases: PDB$SEED, pdb1

Destination:

  • Host: host02
  • Container Database 12.1.0.2c: destcdb
  • Pluggable database: PDB$SEED

Objective:

We need to perform Point-In-Time Restoration (PITR) on pdb1 and then plug it as pdb1_pitr into test CDB destcdb on host02 using backups and archive logs available on host01.

Overview:

  • Setup on Source PDB pdb1@cdb1 on host01
    • Create test table hr.emp with 14 records.
    • Note down current SCN# (SCN1) and timestamp.
    • Using RMAN, take a backup of CDB cdb1 and archive logs
    • Delete 7 records from hr.emp so that hr.emp now has 7 records
    • Note down current SCN# (SCN2) and timestamp. This will be the target System Change Number (SCN) / timestamp of PITR for PDB pdb1
    • Archive current online redo log
  • Duplicate point in time recovered pdb1 on host02
    • Move backup of CDB cdb1 and archive logs necessary for duplication until desired SCN to destination host host02
    • Start the auxiliary instance of CDB tempcdb in NOMOUNT mode using Pfile
    • Start the RMAN client and connect to the auxiliary instance as AUXILIARY.
    • Duplicate the source database cdb1 to the time corresponding to SCN2
    • Open the duplicate database with the RESETLOGS option
    • Verify that the database has been duplicated to desired point in time
  • Plug in PDB pdb1 as pdb1_pitr into test CDB destcdb on host02
    • Unplug and drop PDB pdb1 from CDB tempcdb while retaining its data files
    • Plug PDB pdb1 as pdb_pitr into test CDB destcdb using existing data files
    • Check that pdb1_pitr has been successfully plugged in.

Implementation

  • Set up on Source PDB pdb1@cdb1 on host01:
CDB1>sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO

CDB1>archive log list;

Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     36
Next log sequence to archive   38
Current log sequence           38
  • Create test table hr.emp with 14 records:
CDB1>alter session set container=pdb1;

Session altered.

CDB1>sho con_name

CON_NAME
------------------------------
PDB1

CDB1>create table hr.emp as select * from scott.emp;

Table created.

CDB1>select count(*) from hr.emp;

  COUNT(*)
----------
        14
  • Note down the current SCN# (SCN1):
CDB1>select current_scn from v$database;


CURRENT_SCN
-----------
    2263170
  • Using RMAN, take a backup of CDB cdb1 and archive logs:
[oracle@host01 root]$ export ORACLE_SID=cdb1
[oracle@host01 root]$ rman target /

RMAN> configure controlfile autobackup on;
RMAN> backup database plus archivelog;

RMAN> list backup of database;

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
51      Full    713.45M    DISK        00:01:44     07-JAN-17      
        BP Key: 51   Status: AVAILABLE  Compressed: NO  Tag: TAG20170107T103735
        Piece Name: /u01/app/oracle/fast_recovery_area/CDB1/3665F91A4F8724AEE053B7C909C001BB/backupset/2017_01_07/o1_mf_nnndf_TAG20170107T103735_d70xwqz0_.bkp
  List of Datafiles in backup set 51
  Container ID: 3, PDB Name: PDB1
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  8       Full 2263733    07-JAN-17 /u01/app/oracle/oradata/cdb1/pdb1/system01.dbf
  9       Full 2263733    07-JAN-17 /u01/app/oracle/oradata/cdb1/pdb1/sysaux01.dbf
  10      Full 2263733    07-JAN-17 /u01/app/oracle/oradata/cdb1/pdb1/SAMPLE_SCHEMA_users01.dbf
  11      Full 2263733    07-JAN-17 /u01/app/oracle/oradata/cdb1/pdb1/example01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
52      Full    1.17G      DISK        00:03:18     07-JAN-17      
        BP Key: 52   Status: AVAILABLE  Compressed: NO  Tag: TAG20170107T103735
        Piece Name: /u01/app/oracle/fast_recovery_area/CDB1/backupset/2017_01_07/o1_mf_nnndf_TAG20170107T103735_d70y1z3q_.bkp
  List of Datafiles in backup set 52
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 2264336    07-JAN-17 /u01/app/oracle/oradata/cdb1/system01.dbf
  3       Full 2264336    07-JAN-17 /u01/app/oracle/oradata/cdb1/sysaux01.dbf
  4       Full 2264336    07-JAN-17 /u01/app/oracle/oradata/cdb1/undotbs01.dbf
  6       Full 2264336    07-JAN-17 /u01/app/oracle/oradata/cdb1/users01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
53      Full    594.33M    DISK        00:00:56     07-JAN-17      
        BP Key: 53   Status: AVAILABLE  Compressed: NO  Tag: TAG20170107T103735
        Piece Name: /u01/app/oracle/fast_recovery_area/CDB1/3665DFDEE5151F39E053B7C909C03076/backupset/2017_01_07/o1_mf_nnndf_TAG20170107T103735_d70ydkyx_.bkp
  List of Datafiles in backup set 53
  Container ID: 2, PDB Name: PDB$SEED
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  5       Full 1610184    29-JUN-16 /u01/app/oracle/oradata/cdb1/pdbseed/system01.dbf
  7       Full 1610184    29-JUN-16 /u01/app/oracle/oradata/cdb1/pdbseed/sysaux01.dbf

RMAN> list backup of controlfile;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
55      Full    17.20M     DISK        00:00:03     07-JAN-17      
        BP Key: 55   Status: AVAILABLE  Compressed: NO  Tag: TAG20170107T104822
        Piece Name: /u01/app/oracle/fast_recovery_area/CDB1/autobackup/2017_01_07/o1_mf_s_932640502_d70yk12j_.bkp
  Control File Included: Ckp SCN: 2264535      Ckp time: 07-JAN-17
  • Delete 7 records from hr.emp so that hr.emp now has 7 records:
CDB1>sho con_name

CON_NAME
------------------------------
PDB1

CDB1>delete from hr.emp where rownum < 8;

7 rows deleted.

CDB1>commit;

Commit complete.

CDB1>select count(*) from hr.emp;

  COUNT(*)
----------
         7
  • Note down current SCN# (SCN2) and timestamp. This will be the target SCN / timestamp of the PITR for PDB pdb1:
CDB1>select current_scn from v$database;

CURRENT_SCN
-----------
    2265169

CDB1>select current_timestamp from dual;

CURRENT_TIMESTAMP
----------------------------------------------------------------------
07-JAN-17 10.54.01.000000 AM +05:30
  • Archive the current online redo log:
CDB1>select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
            39

CDB1>alter system archive log current;

System altered.

CDB1>select max(sequence#) from v$archived_log;


MAX(SEQUENCE#)
--------------
            40

Duplicate point in time recovered PDB pdb1 on host02

  • Move the backup of CDB cdb1 and archive logs necessary for duplication until desired SCN to destination host host02:
[oracle@host02 fast_recovery_area]$ mkdir -p /u01/app/oracle/fast_recovery_area/CDB1
[oracle@host01 fast_recovery_area]$ scp -r /u01/app/oracle/fast_recovery_area/CDB1 host02:/u01/app/oracle/fast_recovery_area/
  • Start the auxiliary instance of CDB tempcdb in NOMOUNT mode using a Pfile that includes the declaration enable_pluggable_database=TRUE:
[oracle@host02 CDB1]$ cat /u01/app/oracle/inittempcdb.ora
*.compatible='12.1.0.2.0'
*.control_files='/u01/app/oracle/oradata/tempcdb/control01.ctl'
*.db_name='tempcdb'
*.db_block_size=8192
*.dispatchers='(PROTOCOL=TCP) (SERVICE=tempcdbXDB)'
*.enable_pluggable_database=true
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.db_create_file_dest='/u01/app/oracle/oradata'
*.diagnostic_dest='/u01/app/oracle'

[oracle@host02 CDB1]$ mkdir -p /u01/app/oracle/oradata/tempcdb

[oracle@host01 datafile]$ export $ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/db_1

[oracle@host02 trace]$ export ORACLE_SID=tempcdb

[oracle@host02 trace]$ sqlplus / as sysdba

TEMPCDB> startup nomount pfile='/u01/app/oracle/inittempcdb.ora';
ORACLE instance started.

Total System Global Area  301989888 bytes
Fixed Size                  2923680 bytes
Variable Size             243270496 bytes
Database Buffers           50331648 bytes
Redo Buffers                5464064 bytes
  • Start the RMAN client and connect to the auxiliary instance as AUXILIARY:
[oracle@host02 CDB1]$ export ORACLE_SID=tempcdb

[oracle@host02 CDB1]$ rman auxiliary sys/oracle

RMAN>
  • Duplicate the source database cdb1 to the time corresponding to SCN2:
RMAN>Duplicate database to 'tempcdb' UNTIL TIME
"TO_DATE ('07-JAN-17 10:54:01', 'DD-MON-YY hh:mi:ss')" noopen backup location '/u01/app/oracle/fast_recovery_area/CDB1';
  • Open the duplicate database with the RESETLOGS option:
TEMPCDB>select status from v$instance

STATUS
------------
MOUNTED

TEMPCDB>alter database open resetlogs;

TEMPCDB> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           MOUNTED
  • Verify that the database has been duplicated to the desired time:
TEMPCDB>alter pluggable database pdb1 open;

Pluggable database altered.

TEMPCDB>alter session set container=pdb1;

Session altered.

TEMPCDB>sho con_name

CON_NAME
------------------------------
PDB1

TEMPCDB>select count(*) from hr.emp;

  COUNT(*)
----------
         7

Plug in PDB pdb1 as pdb1_pitr into test CDB destcdb on host02

  • Unplug and drop PDB pdb1 from CDB tempcdb while retaining its data files:
TEMPCDB>ALTER PLUGGABLE DATABASE pdb1 CLOSE;
TEMPCDB>ALTER PLUGGABLE DATABASE pdb1 UNPLUG INTO '/u01/app/oracle/oradata/CDB1/pdb1.xml';
TEMPCDB>DROP PLUGGABLE DATABASE pdb1 KEEP DATAFILES;
  • Plug PDB pdb1 as pdb_pitr into the test CDB destcdb using existing data files:
DESTCDB>sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO

DESTCDB>CREATE PLUGGABLE DATABASE pdb1_pitr USING '/u01/app/oracle/oradata/CDB1/pdb1.xml'
  NOCOPY TEMPFILE REUSE;   

Pluggable database created.

DESTCDB>sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1_PITR                      MOUNTED
  • Check that pdb1_pitr has been successfully plugged in:
DESTCDB>alter pluggable database pdb1_pitr open;

Pluggable database altered.

DESTCDB>alter session set container = pdb1_pitr;

Session altered.

DESTCDB>sho con_name

CON_NAME
------------------------------
PDB1_PITR

DESTCDB>select count(*) from hr.emp;

  COUNT(*)
----------
         7

Summary:

  • In order to duplicate a PDB, an auxiliary instance has to be created as a CDB on the same or different host. 
  • The PDB can be restored / recovered point-in-time using older backups of a production database and the required archive logs so as not to affect the production database.
  • On duplication of PDB(s), RMAN duplicates the root (CDB$ROOT) and the seed database (PDB$SEED) as well.
  • The resulting duplicate database is a fully-functional CDB that contains the root, the seed database, and the duplicated PDBs.

Tags: