RMAN FLASHBACK DATABASE

RMAN Flashback Database to a Specific SCN

Assume that you inserted corrupted rows in many tables at 5:00 p.m. on February 14. You connect SQL*Plus to the database and query the earliest SCN in the flashback window:

SQL> SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME
  2  FROM   V$FLASHBACK_DATABASE_LOG;

OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK
-------------------- ----------------
              411010 2013/02/14 16:49

You then open a new terminal, start the RMAN client, and connect to the target database and recovery catalog. You enter RMAN commands as follows (sample output for the FLASHBACK DATABASE is included):

RMAN> SHUTDOWN IMMEDIATE
RMAN> STARTUP MOUNT
RMAN> FLASHBACK DATABASE TO SCN 411010;
 
Starting flashback at 15-FEB-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=104 device type=DISK
 
 
starting media recovery
media recovery complete, elapsed time: 00:00:07
 
Finished flashback at 15-FEB-13
 
RMAN> ALTER DATABASE OPEN RESETLOGS;

Example 2-100 FLASHBACK DATABASE to a Restore Point

Assume that you are preparing to load a massive number of updates to the database. You create a guaranteed restore point before the performing the updates:

SQL> CREATE RESTORE POINT before_update GUARANTEE FLASHBACK DATABASE;

The bulk update fails, leaving the database with extensive corrupted data. You start an RMAN session, connect to the target database and recovery catalog, and list the guaranteed restore points:

RMAN> LIST RESTORE POINT ALL;

SCN              RSP Time  Type       Time      Name
---------------- --------- ---------- --------- ----
412742                     GUARANTEED 15-FEB-13 BEFORE_UPDATE

You mount the database, RMAN flashback database to the restore point (sample output included), and then open the database with the RESETLOGS option:

RMAN> SHUTDOWN IMMEDIATE
RMAN> STARTUP MOUNT
RMAN> FLASHBACK DATABASE TO RESTORE POINT 'BEFORE_UPDATE';
 
Starting flashback at 15-FEB-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=104 device type=DISK
 
 
starting media recovery
 
archived log for thread 1 with sequence 34 is already on disk as file /disk2/oracle/oradata/prod/arch/archive1_34_614598462.dbf
media recovery complete, elapsed time: 00:00:01
Finished flashback at 15-FEB-13
 
RMAN> ALTER DATABASE OPEN RESETLOGS;

FLASHBACK DATABASE for a PDB to a Guaranteed PDB Restore Point

Assume that you need to upgrade an application that performs DML operations on the tables in the PDB hr_pdb. Before you perform the application upgrade, you create a guaranteed PDB restore point in hr_pdb when connected to the PDB (the PDB is mounted):

SQL> CREATE RESTORE POINT hr_pdb_grp_before_upgrade GUARENTEE FLASHBACK DATABASE;

The application upgrade fails leaving the PDB with corrupted data. You want to rewind the PDB to its state before the upgrade failure. You start SQL*Plus, connect to the CDB as a common user with the SYSDBA or SYSBACKUP privilege, and then run the following command to view all the restore points:

SQL> SELECT name, guarantee_flashback_database, pdb_restore_point, con_id 
     FROM v$restore_point;

NAME                          GUARANTEE_FLASHBACK_DATABASE PDB_RESTORE_POINT  CON_ID
----------------------------- ---------------------------  -----------------  ------
CDB_GRP_BEFORE_PATCH                 YES                   		NO              0
HR_PDB_GRP_BEFORE_UPGRADE            YES                   		YES             1

The output indicates that the restore point HR_PDB_GRP_BEFORE_UPGRADE is a guaranteed PDB restore point. You can reverse the effects of data corruption by rewinding hr_pdb to this guaranteed PDB restore point. To perform a flashback operation for hr_pdb, this PDB must be closed. All other PDBs in the CDB can remain open and operational.

You place the CDB in mount mode, flash back the PDB to the guaranteed PDB restore point, and then open the PDB with resetlogs. In this example, the CDB uses shared undo and, therefore, an auxiliary instance is used to store temporary files during the flashback operation.

RMAN> SHUTDOWN IMMEDIATE;
RMAN> STARTUP MOUNT;
RMAN> FLASHBACK PLUGGABLE DATABASE hr_pdb TO RESTORE POINT hr_pdb_grp_before_upgrade 
      AUXILIARY DESTINATION '/temp/aux_dest';
RMAN> ALTER PLUGGABLE DATABASE hr_pdb OPEN RESETLOGS;

Also See:

Performing Flashback

Oracle RMAN