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

Oracle11g : Flashback Data Archive [Oracle Total Recall]

Oracle total recal register all tables modification in dedicated tablespaces associated to FLASHBACK ARCHIVE area.

To create a flashback archive you need the FLASHBACK ARCHIVE ADMINISTER priviledge. With this priviledge you can craete a flashback archive :

SQL> CREATE FLASHBACK ARCHIVE fda TABLESPACE fda_tbs QUOTA 100M RETENTION 5 YEARS;

To register a table with a flashback archive you need the FLASHBACK ARCHIVE privilege.

SQL> GRANT FLASHBACK ARCHIVE on fda to <user>;

With this priviledge you can reister a table with a specific flashback archive :

SQL> ALTER TABLE my_table FALSHBACK ARCHIVE fda;

With flashback archive registered for a specific table you can query the table in the past

SQL> SELECT * FROM my_table AS OF TIMESTAMP to_timestamp( '05/12/2001 10:41:00', 'DD/MM/YYYY HH24:MI:SS');

note : To make Flashback Data Archive fda the default Flashback Data Archive:

SQL> ALTER FLASHBACK ARCHIVE fda SET DEFAULT;

note : to modify the retention policy

SQL> ALTER FLASHBACK ARCHIVE fda MODIFY RETENTION 2 YEARS;

note : to purge data older then a specific time

SQL> ALTER FLASHBACK ARCHIVE fda PURGE BEFORE TIMESTAMP (SYSTIMESTAMP - INTERVAL '5' DAY);

note : to drop an archive

SQL> DROP FLASHBACK ARCHIVE fda;

note : common DDL requests are supported by flashback archive but for complete ones it is necessary to disassociate the table before to procceed any modification. Total recall provides the DISASSOCIATED_FBA & REASSOCIATE_FBA procedure of the DBMS_FLASHBACK_ARCHIVE package for this purpose.

note :

•  Automatic undo management must be enabled.
•  The tablespace in which flashback data archive is created must have Automatic Segment Space Management activated.
•  You can not drop a table that is managed by total recall but you can truncate it.
  

Oracle11g: Flashback Transaction Query, Flashback Transaction

Flashback transaction allows the user to rollback an entire transaction.
This technology is based on undo data and redo logs availables.

Pre-requisite
You need to :

1.  Activate supplemental log data
2.  Activate supplemental log data primary key
3.  Grant execute on DBMS_FLASHBACK package
4.  Grant SELECT ANY TRANSACTION priviledge to user

These can be done like this :

SQL> alter database add supplemental log data;
SQL> alter database add supplemental log data (primary key) columns;
SQL> grant execute on DBMS_FLASHBACK to <user>;
SQL> grant SELECT ANY TRANSACTION to <user>;

Transaction back-out
See the excellent example from this oracle blog to perform a transaction backout.
For Oracle documentation.

Oracle 11g: Flashback Query, Flashback Query Version, Flashback Table

The flashback Technology uses undo data to retrieve information (except for flashback database). Ensure that you have the correct UNDO_RETENTION parameter definition to guarantee your flashback opération through a specific period of time.


FlashBack Query
Flashback query allows user to query the database and see what were the data as a specific point in the past.

To perform a query on the past you can write :

SQL> SELECT employee_id, salary from HR.employees AS OF TIMESTAMP <time_stamp in past>;

or using a specific SCN number :

SQL> SELECT employee_id, salary from HR.employees AS OF SCN #;

note: It is not possible to flashback query a table if a DDL request has been performed after your targeted SCN or TIMESTAMP.


FlashBack Query Version
Flashback query version allows the user to display all modifications performed on a set of data between two timestamps or to SCN.

SQL> select VERSIONS_XID, employee_id, salary from HR.employees VERSIONS BETWEEN TIMESTAMP <T1> AND <T2>;

or using SCN

SQL> select VERSIONS_XID, employee_id, salary from HR.employees VERSIONS BETWEEN SCN # AND #;

VERSIONS_XID is a pseudo-column that defines the transaction identifier responsible of the line modification (Deleted lines are also considered).

note: Only commited lines are taken into account.
note: Do not use the query version to query :

•  External tables
•  Temporary tables
•  Views
•  V$ views

note: It is not possible to perform a flashback query version if a DDL request has been performed between the two targeted timstamps or SCN.


FlashBack Table
Flashback table restore a table at a specific time or SCN.
You must :

1.  be granted FLASHBACK TABLE or FLASHBACK ANY TABLE
2.  have SELECT , UPDATE, DELETE and ALTER priviledges on the targeted table
3.  enable ROW MOVEMENT on the targeted tables

   SQL> ALTER TABLE <table_name> ENABLE ROW MOVEMENT;

To flashback a table at a specific time in the past :

SQL> FLASHBACK TABLE <table_name> TO TIMESTAMP to_timestamp( '01/12/2011 17:35:00', 'DD/MM/YYYY HH24:MI:SS');

note: The flashback table could not be performed on a tables in the SYSTEM tablespace, views, V$ views and external tables. It could not be also performed if a DDL request has be performed after the targeted point in time.
note: Flashback table generates redo and undo data.

It could be possible the flashback a table to a specific SCN or restore point.

SQL> FLASHBACK TABLE <table_name> TO SCN #;

SQL> FLASHBACK TABLE <table_name> TO RESTORE POINT <restore_point_name>;