Oracle database 12c Release 2 provides flashback capability at the individual PDB level. Lets discuss the new feature.
we can learn about Flashback database in general from below links
Flashback Database explained and limitation
For the oracle database to be flashback, archive log mode and flashback mode should be on
How to put database in archive log mode
sqlplus “/ as sysdba”
SHUTDOWN IMMEDIATE
STARTUP MOUNT
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
How to turn on the flashback mode
ALTER DATABASE FLASHBACK ON;
–ALTER DATABASE FLASHBACK OFF;
We can check the status of flashback database using the following query.
SELECT flashback_on FROM v$database;
FLASHBACK_ON
YES
1 row selected.
SQL>
Now lets see how restore points can be created at the PDB level
Creating restore points at PDB level:
Connect to the specific PDB and then run CREATE RESTORE POINT command:
SQL> alter session set container=TECH1;
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 TECH1;
SQL> create restore point TEST2 for pluggable database TECH1 guarantee flashback database;
Creating Clean Restore Points
These are restore points taken when the pluggable database is down, with no outstanding transactions.
Clean restore points can be created while connected to the PDB as follows.
CONN / AS SYSDBA
ALTER PLUGGABLE DATABASE TECH1 CLOSE;
— Normal restore point.
CREATE CLEAN RESTORE POINT TEST1 FOR PLUGGABLE DATABASE TECH1;
DROP RESTORE POINT TEST1 FOR PLUGGABLE DATABASE TECH1;
— Guaranteed restore point.
CREATE CLEAN RESTORE POINT TEST1 FOR PLUGGABLE DATABASE TECH1 GUARANTEE FLASHBACK DATABASE;
DROP RESTORE POINT TEST1 FOR PLUGGABLE DATABASE TECH1;
ALTER PLUGGABLE DATABASE TECH1 OPEN;
How to view the Restore points
Viewing restore-point information. Either via SQL*PLUS or RMAN:
SQL> select NAME,TIME,SCN,PDB_RESTORE_POINT,GUARANTEE_FLASHBACK_DATABASE from V$RESTORE_POINT;
RMAN> list restore point all;
Important points about Flashback PDB for Oracle database 12c Release 2
With Oracle database 12.2, we have 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:
How to perform flashback at the PDB level
a. If PDB uses local undo:
SQL> alter pluggable database TECH1 close;
SQL> flashback pluggable database PDB1 to restore point TEST1;
SQL> alter pluggable database TECH1 open resetlogs;
b. If PDB uses shared undo and restore point created when PDB was open:
RMAN> alter pluggable database TECH1 close;
RMAN> flashback pluggable database TECH1 to restore point TEST1 auxiliary destination ‘/u122/oracle/oradata/stage’;
RMAN> alter pluggable database TECH1 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 TECH1 close;
SQL> flashback pluggable database TECH1 to clean restore point TEST1;
SQL> alter pluggable database TECH1 open resetlogs;
Related Articles
Local Undo in Oracle Database 12c R2(12.2)
Oracle Database Interactive Quick Reference Guide | 11g & 12c
Hot PDB cloning in Oracle database 12.2
Understand Oracle Database 12c – Multitenant Architecture