Oracle 12c Database: Flashback a CDB Database

Flashback technology was first introduced in 10g and is used to undo any accidental or unwanted logical change made to the database. The Flashback feature provides a convenient method to undo these mistakes, including reverting DML or dropping of a table. This allows recovering from the loss, a lot quicker without having to go through the lengthy process of an RMAN recovery.


Related Articles
SGA Instance Parameters in CDB and PDB
Managing CDB and PDB tablespaces
Managing Common and Local Users, Roles and Privileges for CDB and PDB
Create 12c CDB PDB Using OUI
Managing PDBs using Database Configuration Assistant

Flashback a CDB Database

Much of flashback technology features remains the same in the new multitenant architecture (in 12c), except for one specific scenario. In this article we will look at this Flashback feature for a CDB database where the workaround is required.


Prerequisite
For flashback database to work you must have off course enabled Flashback.

Flashback for a CDB

Typically, you can flashback the whole CDB database. If you Flashback the CDB to a previous point in time then this flashes back all the PDB’s as well.


In the multitentant world however, when trying to Flashback a CDB, you should make sure that the time for the Flashback is not beyond the time to when a particular PDB was recovered previously using point in time recovery. So for example, for a PDB database named pdb1 which was recovered previously to an SCN 12289, the CDB can now only be flashed back to a point where the SCN is greater than 12289 and not prior to it. If you try to do so you will receive the following error message.


$ sqlplus / as sysdba
FLASHBACK DATABASE TO SCN 12200;
ORA-39866: Data files for pluggable database pdb1 must be offline to flashback across a PDB point-in-time recovery


Here you can see that the error message hints to the solution! You can actually workaround this by to taking the datafiles for that particular pdb1 database offline! Follow the steps below to Flashback a CDB to a point in the past which is prior to the point in time to which a PDB was recovered.


1. Connect to Root with SYSDBA or SYSBACKUP privilege.

$ rman connect target /


2. Determine the target time/SCN to which CDB needs to be rewind. In our case it can be SCN anything less than 12289. Lets say it is 12200.


3. Take the datafiles of pdb1 offline using the following command.

ALTER PLUGGABLE DATABASE pdb1 DATAFILE ALL OFFLINE;


4. Shutdown the CDB and bring it to mount state.

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;


5. Flashback the CDB to desired SCN.
FLASHBACK DATABASE TO SCN 12200;


6. Open the CDB database and all PDB databases, except the pdb1.

ALTER DATABASE OPEN RESETLOGS;
ALTER PLUGGABLE DATABASE ALL EXCEPT pdb1 OPEN;


7. The pdb1 is now inconsistent with CDB. You will have to restore the PDB and recover it fully.

RESTORE PLUGGABLE DATABASE pdb1;
ALTER PLUGGABLE DATABASE pdb1 DATAFILE ALL ONLINE;
RECOVER PLUGGABLE DATABASE pdb1;
ALTER PLUGGABLE DATABASE pdb1 OPEN;

Flashback for a PDB

Although most of the other flashback features like version query and before drop are available for individual PDBs. Flashback database is not yet supported for individual PDBs. You can only flashback the entire CDB and cannot do it for individual PDBs. You will get the following error message if you try to do so.

SQL> alter session set container=pdb1;

Session altered.

SQL> flashback database to scn 2155326;
flashback database to scn 2155326
*
ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database


Alternatively you can use the point in time recovery if you want to revert individual PDB to a previous state.


References
Performing Complete Database Recovery