Duplicate Point-In-Time Recovered PDB Using Backup

Comments 0

Share to social media

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:

  • Create test table hr.emp with 14 records:

  • Note down the current SCN# (SCN1):

  • 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 SCN / timestamp of the PITR for PDB pdb1:

  • Archive the current online redo log:

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:

  • Start the auxiliary instance of CDB tempcdb in NOMOUNT mode using a Pfile that includes the declaration enable_pluggable_database=TRUE:

  • 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 the desired 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 the test CDB destcdb using existing data files:

  • Check that pdb1_pitr has been successfully plugged in:

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.

About the author