Oracle11g: Flashback database

Flashback database allows a user to rewind an entire database in the past.
Flashback database uses specific flashback logs that contains blocks of database that have been modified. These logs are stored in the Fast Recovery area.

Configuring a database for flashback database

1.  The database must be in archivelog
   

   SQL> ALTER DATABASE ARCHIVELOG;

2.  The target retention policy must be configured
   

   SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=3000 SCOPE=BOTH;

   In this example, the retention is configure for 3000 minutes. There is no guarantee on the retention target; it is depending on the amount of data stores in the flashback logs and the size of the fast recovery area. If space is needed for the fast recovery area, flashback logs could be lost.
   


3.  Flashback Database must be explicitly activated
   

   SQL> ALTER DATABASE FLASHBACK ON;

   note: This activation must be done when the database is mounted in an exclusive mode (NOT OPENED!).
   

How to flashback a database

To perform a flashback, the database must be in MOUNT EXCLUSIVE state :

SQL> FLASHBACK DATABASE TO TIMESTAMP (sysdate - interval '1' DAY);
SQL> FLASHBACK DATABASE TO SCN #;
SQL> FLASHBACK DATABASE TO RESTORE POINT <restore_point>;
SQL> FLASHBACK DATABASE TO SEQUENCE #;

Once flashback done, the database must be reopen with RESETLOGS option.

Flashback opérations could be followed through the V$SESSION_LONGOPS view.

note : Flashback database could not be used if

•  The control file has been restored or re-created
•  A tablespace has been dropped
•  The size of a file has be reduced
•  The restoration is before a previous use of RESETLOGS (in this case use TO BEFORE RESETLOGS)

How to monitor a flashback database
To monitor disk space usage :

SQL> SELECT ESTIMATED_FLASHBACK_SIZE, FLASHBACK_SIZE FROM V$FLASHBACK_DATABASE_LOG;

To monitor the SCN window :

SQL> SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME FROM V$FLASHBACK_DATABASE_LOG;

To monitor flashback statistics :

SQL>SELECT BEGIN_TIME, END_TIME, FLASHBACK_DATA, DB_DATA, REDO_DATA, ESTIMATED_FLASHBACK_SIZE FROM V$FLASHBACK_DATABASE_STAT;

The v$flashback_database_stat contains 24 hours of statistics collection. Each line correspond to one hour of flashback activity. This view is usefull to calculate the size requiered by flashback logs in relation to the retention target defined by the administrator.
FLASHBACK_DATA is the number of bytes of flashback data and REDO_DATA the number of bytes of redo log generated for the period.
DB_DATA is the number of bytes of data block read and write.

The V$RECOVERY_FILE_DEST gives information on fast recovery disk usage (size quota, used space, reclamable space and number of files for each location of the fast recovery area).

Guaranted restore point
With flashback database, it is possible to defined a guaranted restore point that ensure that the flash recovery area maintains all information used to restore the database at this specific restore point (In this case no lost of flashback logs).
To define a guaranted restore point, you can issue:

SQL> CREATE RESTORE POINT <restore_point> GUARANTEE FLASHBACK DATABASE;

To create this kind of restore point, the following pre-requisite are mandatory:

•  The COMPATIBLE parameter must be greater than or equals to 10.2
•  The database must be in archivelog
•  Archive logs must be available before the creation of the guaranted restore point
•  The Fast recovery area must be configured and available