Flashback PDB in Oracle Database 12c Release 2

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;

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:

FLASHBACK_ON

Related Posts

Leave a Comment Cancel Reply