Point in Time Recovery of a Pluggable Database

In this article I will explain how we can perform a point in time recovery for a single pluggable database. Point in time recovery of a single pluggable database would not have any effect on other pluggable databases in the same container database, or container database itself. In the following, a real time scenario of a pluggable database point in time recovery is explained. Following are the points to consider this scenario.

• I have a pluggable database “pdb1” in my CDB.
• Current time is 12:15 PM on 26^th May 2016.
• I want to perform a time/scn based recovery of my pdb1 until time 10:35 AM on 26^th May 2016.
• I have a backup taken yesterday (25^th May 2016), and have all archived logs available until current time.

Steps to perform point in time recovery

1)
I can perform either time based recovery or SCN based recovery. For SCN based recovery, I would need to have SCN noted already, or I need to extract it from the database using TIMESTAMP_TO_SCN function. Execute following statement to find out SCN of the time until which I will be doing recovery

SQL> select timestamp_to_scn(to_date('26-may-16 10:35:00','DD-MON-YY HH24:MI:SS')) as scn from dual;        SCN ----------    6547615

My example used SCN to perform the recovery as you will see later, but alternatively you can also use UNTIL TIME to perform the recovery; following is the example of command to specify the time for recovery if time is to be used.

RMAN> recover pluggable database pdb1 until time "to_date('26-may-16 10:55:00','DD-MON-YY HH24:MI:SS')";

2)
In RMAN recovery command, we can use clause AUXILIARY DESTINATION to specify destination for temporarily storing auxiliary files that are created during the recovery. If we don’t specify this clause, recovery process will use fast recovery area location by default. 4 steps are required for recovery.

• Connect to CDB using RMAN.
• Close pluggable database.
• Perform recovery .
• Open pluggable database using RESETLOGS.

In the following you can see how these steps performed are being performed and what is expected output of each step.

C:\>rman target / Recovery Manager: Release 12.1.0.2.0 - Production on Thu May 26 12:15:57 2016 Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved. connected to target database: SALMAN12 (DBID=3945497372) RMAN> alter pluggable database pdb1 close; using target database control file instead of recovery catalog Statement processed RMAN> run{ 2> set until scn 6547615; 3> restore pluggable database pdb1; 4> recover pluggable database pdb1; 5> } executing command: SET until clause Starting restore at 26-MAY-16 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=7 device type=DISK channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00014 to C:\APP\SALMQURE\ORADATA\SALMAN12\PDB1\SYSTEM01.DBF channel ORA_DISK_1: restoring datafile 00015 to C:\APP\SALMQURE\ORADATA\SALMAN12\PDB1\SYSAUX01.DBF channel ORA_DISK_1: restoring datafile 00016 to C:\APP\SALMQURE\ORADATA\SALMAN12\PDB1\PDB1_USERS01.DBF channel ORA_DISK_1: reading from backup piece C:\BACK_0RR6G6VD_1_1.BAK channel ORA_DISK_1: piece handle=C:\BACK_0RR6G6VD_1_1.BAK tag=TAG20160525T172832 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:35 Finished restore at 26-MAY-16 Starting recover at 26-MAY-16 using channel ORA_DISK_1 RMAN-05026: WARNING: presuming following set of tablespaces applies to specified Point-in-Time List of tablespaces expected to have UNDO segments Tablespace SYSTEM Tablespace UNDOTBS1 Creating automatic instance, with SID='biys' initialization parameters used for automatic instance: db_name=SALMAN12 db_unique_name=biys_pitr_pdb1_SALMAN12 compatible=12.1.0.2.0 db_block_size=8192 db_files=200 diagnostic_dest=C:\APP\SALMQURE _system_trig_enabled=FALSE db_domain=sg.oracle.com sga_target=2048M processes=200 #No auxiliary destination in use enable_pluggable_database=true _clone_one_pdb_recovery=true control_files=C:\APP\SALMQURE\FAST_RECOVERY_AREA\SALMAN12C\SALMAN12\CONTROLFILE\O1_MF_CNDSKKPK_.CTL #No auxiliary parameter file used starting up automatic instance SALMAN12 Oracle instance started Total System Global Area    2147483648 bytes Fixed Size                     3047720 bytes Variable Size                520097496 bytes Database Buffers            1610612736 bytes Redo Buffers                  13725696 bytes Automatic instance created contents of Memory Script: { # set requested point in time set until  scn 6547615; # restore the controlfile restore clone controlfile; # mount the controlfile sql clone 'alter database mount clone database'; } executing Memory Script executing command: SET until clause Starting restore at 26-MAY-16 allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=87 device type=DISK channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: restoring control file channel ORA_AUX_DISK_1: reading from backup piece C:\APP\SALMQURE\FAST_RECOVERY_AREA\SALMAN12C\SALMAN12\AUTOBACKUP\2016_05_25\O1_MF_S_912792665_CNBW6TYF_.BKP channel ORA_AUX_DISK_1: piece handle=C:\APP\SALMQURE\FAST_RECOVERY_AREA\SALMAN12C\SALMAN12\AUTOBACKUP\2016_05_25\O1_MF_S_912792665_CNBW6TYF_.BKP tag=TAG20160525T173105 channel ORA_AUX_DISK_1: restored backup piece 1 channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01 output file name=C:\APP\SALMQURE\FAST_RECOVERY_AREA\SALMAN12C\SALMAN12\CONTROLFILE\O1_MF_CNDSKKPK_.CTL Finished restore at 26-MAY-16 sql statement: alter database mount clone database contents of Memory Script: { # set requested point in time set until  scn 6547615; # switch to valid datafilecopies switch clone datafile  14 to datafilecopy  "C:\APP\SALMQURE\ORADATA\SALMAN12\PDB1\SYSTEM01.DBF"; switch clone datafile  15 to datafilecopy  "C:\APP\SALMQURE\ORADATA\SALMAN12\PDB1\SYSAUX01.DBF"; switch clone datafile  16 to datafilecopy  "C:\APP\SALMQURE\ORADATA\SALMAN12\PDB1\PDB1_USERS01.DBF"; # set destinations for recovery set and auxiliary set datafiles set newname for datafile  1 to  "C:\APP\SALMQURE\FAST_RECOVERY_AREA\SALMAN12C\SALMAN12\DATAFILE\O1_MF_SYSTEM_CNDSMHYN_.DBF"; set newname for datafile  5 to  "C:\APP\SALMQURE\FAST_RECOVERY_AREA\SALMAN12C\SALMAN12\DATAFILE\O1_MF_UNDOTBS1_CNDSMJ1C_.DBF"; set newname for datafile  3 to  "C:\APP\SALMQURE\FAST_RECOVERY_AREA\SALMAN12C\SALMAN12\DATAFILE\O1_MF_SYSAUX_CNDSMJ3S_.DBF"; set newname for datafile  6 to  "C:\APP\SALMQURE\FAST_RECOVERY_AREA\SALMAN12C\SALMAN12\DATAFILE\O1_MF_USERS_CNDSMRO6_.DBF"; set newname for datafile  10 to  "C:\APP\SALMQURE\FAST_RECOVERY_AREA\SALMAN12C\SALMAN12\DATAFILE\O1_MF_TEST_CNDSMROT_.DBF"; # restore the tablespaces in the recovery set and the auxiliary set restore clone datafile  1, 5, 3, 6, 10; switch clone datafile all; } executing Memory Script executing command: SET until clause datafile 14 switched to datafile copy input datafile copy RECID=3 STAMP=912855545 file name=C:\APP\SALMQURE\ORADATA\SALMAN12\PDB1\SYSTEM01.DBF datafile 15 switched to datafile copy input datafile copy RECID=4 STAMP=912855545 file name=C:\APP\SALMQURE\ORADATA\SALMAN12\PDB1\SYSAUX01.DBF datafile 16 switched to datafile copy input datafile copy RECID=5 STAMP=912855545 file name=C:\APP\SALMQURE\ORADATA\SALMAN12\PDB1\PDB1_USERS01.DBF executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME Starting restore at 26-MAY-16 using channel ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00001 to C:\APP\SALMQURE\FAST_RECOVERY_AREA\SALMAN12C\SALMAN12\DATAFILE\O1_MF_SYSTEM_CNDSMHYN_.DBF channel ORA_AUX_DISK_1: restoring datafile 00005 to C:\APP\SALMQURE\FAST_RECOVERY_AREA\SALMAN12C\SALMAN12\DATAFILE\O1_MF_UNDOTBS1_CNDSMJ1C_.DBF channel ORA_AUX_DISK_1: restoring datafile 00003 to C:\APP\SALMQURE\FAST_RECOVERY_AREA\SALMAN12C\SALMAN12\DATAFILE\O1_MF_SYSAUX_CNDSMJ3S_.DBF channel ORA_AUX_DISK_1: restoring datafile 00006 to C:\APP\SALMQURE\FAST_RECOVERY_AREA\SALMAN12C\SALMAN12\DATAFILE\O1_MF_USERS_CNDSMRO6_.DBF channel ORA_AUX_DISK_1: restoring datafile 00010 to C:\APP\SALMQURE\FAST_RECOVERY_AREA\SALMAN12C\SALMAN12\DATAFILE\O1_MF_TEST_CNDSMROT_.DBF channel ORA_AUX_DISK_1: reading from backup piece C:\BACK_0QR6G6U0_1_1.BAK channel ORA_AUX_DISK_1: piece handle=C:\BACK_0QR6G6U0_1_1.BAK tag=TAG20160525T172832 channel ORA_AUX_DISK_1: restored backup piece 1 channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:45 Finished restore at 26-MAY-16 datafile 1 switched to datafile copy input datafile copy RECID=11 STAMP=912855653 file name=C:\APP\SALMQURE\FAST_RECOVERY_AREA\SALMAN12C\SALMAN12\DATAFILE\O1_MF_SYSTEM_CNDSMHYN_.DBF datafile 5 switched to datafile copy input datafile copy RECID=12 STAMP=912855653 file name=C:\APP\SALMQURE\FAST_RECOVERY_AREA\SALMAN12C\SALMAN12\DATAFILE\O1_MF_UNDOTBS1_CNDSMJ1C_.DBF datafile 3 switched to datafile copy input datafile copy RECID=13 STAMP=912855653 file name=C:\APP\SALMQURE\FAST_RECOVERY_AREA\SALMAN12C\SALMAN12\DATAFILE\O1_MF_SYSAUX_CNDSMJ3S_.DBF datafile 6 switched to datafile copy input datafile copy RECID=14 STAMP=912855653 file name=C:\APP\SALMQURE\FAST_RECOVERY_AREA\SALMAN12C\SALMAN12\DATAFILE\O1_MF_USERS_CNDSMRO6_.DBF datafile 10 switched to datafile copy input datafile copy RECID=15 STAMP=912855654 file name=C:\APP\SALMQURE\FAST_RECOVERY_AREA\SALMAN12C\SALMAN12\DATAFILE\O1_MF_TEST_CNDSMROT_.DBF contents of Memory Script: { # set requested point in time set until  scn 6547615; # online the datafiles restored or switched sql clone "alter database datafile  1 online"; sql clone "alter database datafile  5 online"; sql clone "alter database datafile  3 online"; sql clone 'PDB1' "alter database datafile  14 online"; sql clone 'PDB1' "alter database datafile  15 online"; sql clone 'PDB1' "alter database datafile  16 online"; sql clone "alter database datafile  6 online"; sql clone "alter database datafile  10 online"; # recover pdb recover clone database tablespace  "SYSTEM", "UNDOTBS1", "SYSAUX", "USERS", "TEST" pluggable database  'PDB1'   delete archivelog; sql clone 'alter database open read only'; plsql <<<begin    add_dropped_ts; end; >>>; plsql <<<begin    save_pdb_clean_scn; end; >>>; # shutdown clone before import shutdown clone abort plsql <<<begin    pdbpitr_inspect(pdbname =>  'PDB1'); end; >>>; } executing Memory Script executing command: SET until clause sql statement: alter database datafile  1 online sql statement: alter database datafile  5 online sql statement: alter database datafile  3 online sql statement: alter database datafile  14 online sql statement: alter database datafile  15 online sql statement: alter database datafile  16 online sql statement: alter database datafile  6 online sql statement: alter database datafile  10 online Starting recover at 26-MAY-16 using channel ORA_AUX_DISK_1 starting media recovery archived log for thread 1 with sequence 588 is already on disk as file C:\APP\SALMQURE\FAST_RECOVERY_AREA\SALMAN12C\SALMAN12\ARCHIVELOG\2016_05_25\O1_MF_1_588_CNBW6QP9_.ARC archived log for thread 1 with sequence 589 is already on disk as file C:\APP\SALMQURE\FAST_RECOVERY_AREA\SALMAN12C\SALMAN12\ARCHIVELOG\2016_05_26\O1_MF_1_589_CNDRBP94_.ARC archived log file name=C:\APP\SALMQURE\FAST_RECOVERY_AREA\SALMAN12C\SALMAN12\ARCHIVELOG\2016_05_25\O1_MF_1_588_CNBW6QP9_.ARC thread=1 sequence=588 archived log file name=C:\APP\SALMQURE\FAST_RECOVERY_AREA\SALMAN12C\SALMAN12\ARCHIVELOG\2016_05_26\O1_MF_1_589_CNDRBP94_.ARC thread=1 sequence=589 media recovery complete, elapsed time: 00:00:01 Finished recover at 26-MAY-16 sql statement: alter database open read only Oracle instance shut down Removing automatic instance Automatic instance removed auxiliary instance file C:\APP\SALMQURE\FAST_RECOVERY_AREA\SALMAN12C\SALMAN12\DATAFILE\O1_MF_SYSAUX_CNDSMJ3S_.DBF deleted auxiliary instance file C:\APP\SALMQURE\FAST_RECOVERY_AREA\SALMAN12C\SALMAN12\CONTROLFILE\O1_MF_CNDSKKPK_.CTL deleted Finished recover at 26-MAY-16 RMAN> alter pluggable database pdb1 open resetlogs; Statement processed

Since the purpose of point in time recovery is to recover from user error (user mistakenly performed some database change, or dropped an object that is not required), you need to check your pluggable database if you have achieved your required goal, and have recovered from the user error.

If you are doing this in 12.1.0.2, the common users/roles present in the CDB, are not synchronized with the PDB, after a point in time recovery, and PDB lacks these common users/roles. Following is the way to synchronize the PDB with the CDB.

Pluggable Database not in sync with Container Database after recovery

If your pluggable database (PDB) is not in sync with the container database (CDB) – for example a common user/role exists in CDB, but not in CDB, it means PDB is not in sync with the PDB. There could be a reason for this, a bug, for example. Execute following steps to synchronize the PDB with the CDB.

SQL> alter session set container=pdb1; Session altered. SQL> exec DBMS_PDB.SYNC_PDB; PL/SQL procedure successfully completed.