Flashback Database  (Alternative to Point-In-Time Recovery)

Oracle Flashback Database, lets you quickly recover the entire database from logical data corruptions or user errors.

To enable Flashback Database, you set up a flash recovery area, and set a flashback retention target, to specify how far back into the past you want to be able to restore your database with Flashback Database.

Once you set these parameters, From that time on, at regular intervals, the database copies images of each altered block in every datafile into flashback logs stored in the flash recovery area. These Flashback logs are use to flashback database to a point in time.

Enabling Flash Back Database

Step 1. Shutdown the database if it is already running and set the following parameters

           DB_RECOVERY_FILE_DEST=/d01/ica/flasharea
          DB_RECOVERY_FILE_DEST_SIZE=10G
          DB_FLASHBACK_RETENTION_TARGET=4320

(Note: the db_flashback_retention_target is specified in minutes here we have specified  3 days i.e. 3x24x60=4320)

Step 2. Start the instance and mount the Database.

            SQL>startup mount;

Step 3. Now enable the flashback database by giving the following command

            SQL>alter database flashback on;

Now Oracle start writing Flashback logs to recovery area.

To how much size we should set the flash recovery area.

After you have enabled the Flashback Database feature and allowed the database to generate some flashback logs, run the following query:

SQL> SELECT ESTIMATED_FLASHBACK_SIZE FROM V$FLASHBACK_DATABASE_LOG;

This will show how much size the recovery area should be set to.

 How far you can flashback database.

To determine the earliest SCN and earliest Time you can Flashback your database,  give the following query:

SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME

      FROM V$FLASHBACK_DATABASE_LOG;

Example: Flashing Back Database to a point in time

Suppose, a user erroneously drops a schema at 10:00AM. You as a DBA came to know of this at 5PM. Now since you have configured the flashback area and set up the flashback retention time to 3 Days, you can flashback the database to 9:50AM by following the given procedure

1. Start RMAN

$ rman target /

2. Run the FLASHBACK DATABASE command to return the database to  9:59AM by typing the following command

RMAN>FLASHBACK DATABASE TO TIME
        timestamp('2007-06-21 09:59:00');

or, you can also type this command.

RMAN> FLASHBACK DATABASE TO TIME (SYSDATE-8/24);

3.  When the Flashback Database operation completes, you can evaluate the results by opening the database read-only and run some queries to check whether your Flashback Database has returned the database to the desired state.

 RMAN> SQL 'ALTER DATABASE OPEN READ ONLY';

               At this time, you have several options

4. Since in our example only a schema is dropped and the rest of database is good, third option is relevant for us.

Now, come out of  RMAN and run export utility to export the whole schema

$ exp  userid=system/manager file=scott.dmp  owner=SCOTT

5.  Now Start RMAN and recover database to the present time

$rman target /

RMAN> RECOVER DATABASE;

6.  After database is recovered shutdown and restart the database in normal mode and import the schema by running IMPORT utility

$imp userid=system/manager file=scott.dmp