The Geek Diary

How to create restore points for PDB and perform flashback at PDB level

by

This post discusses how to create restore points for PDB and perform flashback at PDB level which is introduced from 12.2. For the purposes of this document, the following fictitious environment is used as an example to describe the procedure:

PDB Name: PDB1
Restore Point: TEST1
Restore Point (Guarantee) TEST2

1. Creating restore points at PDB level

Connect to the specific PDB and then run CREATE RESTORE POINT command:

SQL> alter session set container=PDB1;
SQL> create restore point TEST1;
SQL> create restore point TEST2 guarantee flashback database;

or

Connect to the CDB and use FOR PLUGGABLE DATABASE clause of CREATE RESTORE POINT command:

SQL> alter session set container=CDB$ROOT;
SQL> create restore point TEST1 for pluggable database PDB1;
SQL> create restore point TEST2 for pluggable database PDB1 guarantee flashback database;

Note: Starting from 12.2, we have the option to create Multitenant Databases with Local Undo. This means that each PDB uses it’s own UNDO tablespace. In such a PDB setup, a flashback operation at PDB level can rollback changes made to the PDB’s Undo tablespace similar to system, sysaux or user tablespaces of that PDB i.e. there is no dependency with the CDB or other PDB’s.

However, if your 12.2 Multitenant Database is still using Shared Undo, in such a setup, the UNDO tablespace is shared among all PDB’s. So, if a flashback operation is performed at PDB level, we need to use AUXILIARY DESTINATION clause along with FLASHBACK PLUGGABLE DATABASE command. This is used to create a auxiliary instance where CDB’s system,sysaux and undo are restored till the point-in-time of flashback to rollback the active transactions in that particular PDB whose flashback is being performed. To avoid having to create this auxiliary instance during flashback of PDB that uses Shared Undo, you can optionally create a clean restore point with the PDB closed. When doing PDB flashback to a clean restore point, auxiliary instance need not be created since there would be no active transactions in the PDB as of the time of restore point:

SQL> alter pluggable database PDB1 close;
SQL> create clean restore point Before_Upgrade for pluggable database PDB1;

2. Viewing restore-point information

View the restore-point information either via SQL*PLUS or RMAN:

SQL> select NAME,TIME,SCN,PDB_RESTORE_POINT,GUARANTEE_FLASHBACK_DATABASE from V$RESTORE_POINT;

NAME            TIME                                  SCN        PDB GUA
--------------- ------------------------------------- ---------- --- ---
TEST2 09-AUG-17 12.42.53.000000000 PM                 1451338    YES YES
TEST1 09-AUG-17 12.42.21.000000000 PM                 1451318    YES NO
RMAN> list restore point all;

SCN              RSP                  Time Type    Time                 Name
---------------- -------------------- ------------ -------------------- ----
1451318                               09-AUG-2017  12:42:21             TEST1
1451338          GUARANTEED           09-AUG-2017  12:42:53             TEST2

3. Performing flashback

a. If PDB uses local undo:

SQL> alter pluggable database PDB1 close;
SQL> flashback pluggable database PDB1 to restore point TEST1;
SQL> alter pluggable database PDB1 open resetlogs;

b. If PDB uses shared undo and restore point created when PDB was open:

RMAN> alter pluggable database PDB1 close;
RMAN> flashback pluggable database PDB1 to restore point TEST1 auxiliary destination '/o122/app/oracle/oradata/stage';
RMAN> alter pluggable database PDB1 open resetlogs;

c. If PDB uses shared undo and restore point created when PDB was closed (i.e. clean restore point)

SQL> alter pluggable database PDB1 close;
SQL> flashback pluggable database PDB1 to clean restore point TEST1;
SQL> alter pluggable database PDB1 open resetlogs;

You May Also Like