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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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 <span style="color: red;">Archive Mode</span> 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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
CDB1>alter session set container=pdb1; Session altered. CDB1>sho con_name CON_NAME ------------------------------ <span style="color: red;">PDB1</span> CDB1>create table hr.emp as select * from scott.emp; Table created. CDB1>select count(*) from hr.emp; COUNT(*) ---------- <span style="color: red;">14</span> |
- Note down the current SCN# (SCN1):
|
1 2 3 4 5 6 |
CDB1>select current_scn from v$database; CURRENT_SCN ----------- 2263170 |
- Using RMAN, take a backup of CDB cdb1 and archive logs:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 |
[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: <span style="color: red;">/u01/app/oracle/fast_recovery_area/CDB1/3665F91A4F8724AEE053B7C909C001BB/backupset/2017_01_07/o1_mf_nnndf_TAG20170107T103735_d70xwqz0_.bkp</span> 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: <span style="color: red;">/u01/app/oracle/fast_recovery_area/CDB1/backupset/2017_01_07/o1_mf_nnndf_TAG20170107T103735_d70y1z3q_.bkp</span> 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: <span style="color: red;">/u01/app/oracle/fast_recovery_area/CDB1/3665DFDEE5151F39E053B7C909C03076/backupset/2017_01_07/o1_mf_nnndf_TAG20170107T103735_d70ydkyx_.bkp</span> 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: <span style="color: red;">/u01/app/oracle/fast_recovery_area/CDB1/autobackup/2017_01_07/o1_mf_s_932640502_d70yk12j_.bkp</span> 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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
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(*) ---------- <span style="color: red;"><strong>7</strong></span> |
- Note down current SCN# (SCN2) and timestamp. This will be the target SCN / timestamp of the PITR for PDB pdb1:
|
1 2 3 4 5 6 7 8 9 10 11 |
CDB1>select current_scn from v$database; CURRENT_SCN ----------- <span style="color: red;">2265169</span> CDB1>select current_timestamp from dual; CURRENT_TIMESTAMP ---------------------------------------------------------------------- <span style="color: red;">07-JAN-17 10.54.01.000000 AM +05:30</span> |
- Archive the current online redo log:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
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:
|
1 2 |
[oracle@<strong>host02</strong> 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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
[oracle@host02 CDB1]$ cat <span style="color: red;"><strong>/u01/app/oracle/inittempcdb.ora</strong></span> *.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)' *.<span style="color: red;"><strong>enable_pluggable_database=true</strong></span> *.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> <span style="color: red;"><strong>startup nomount pfile='/u01/app/oracle/inittempcdb.ora';</strong></span> 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:
|
1 2 3 4 5 |
[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:
|
1 2 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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 <span style="color: red;"><strong>3 PDB1 MOUNTED</strong></span> |
- Verify that the database has been duplicated to the desired time:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
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(*) ---------- <span style="color: red;"><strong>7</strong></span> |
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:
|
1 2 3 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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 <span style="color: red;"><strong>PDB1_PITR MOUNTED</strong></span> |
- Check that pdb1_pitr has been successfully plugged in:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
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(*) ---------- <span style="color: red;"><strong>7</strong></span> |
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.
Load comments