Flashback Database Technology Article

FLASHBACK RECOVERY

Introduction:

Flashback recovery feature is used to rollback the database to the prior point in time or system change number. Flashback Database is a new approach to point-in-time (PIT) database recovery. This incomplete recovery strategy can be used to recover a database that has been logically corrupted due to human error. Introduced in 10g, it is designed to maximize availability by reducing recovery time.

Incomplete Recovery:

Incomplete recovery is recovery of the database to a prior state. There are two steps to this process: restoring data and forward-recovering transaction activity to a desired time. The major difference between traditional recovery and Flashback Database is that traditional recovery begins by restoring all data files prior to the desired recovery time while Flashback Database works backwards by restoring blocks changed after the corruption. To put this in perspective, let's consider a 10 TB database with 1 MB of corruption. Traditional recovery begins by restoring 10 TB of application data while Flashback Database backs out 1 MB of application data to arrive at a point before the corruption. Restoring all data files from a backup and rolling forward redo logs to the desired point-in-time accomplish recovery. Recovery time is proportional to the size of the database, not the amount of changes that need to be backed out. The mean time to recover (MTTR) actually increases as the database grows in size. But in this feature the time to restore a database is proportional to the number of changes that need to be backed out, not the size of the database.

Pre-requisites:
You must have either the FLASHBACK ANY TABLE or the more specific FLASHBACK object privilege on the table you want to recover.
Database must be in Archivelog mode to enable Flashback.
The feature is enabled in database MOUNT EXCLUSIVE mode.
The following parameters must be set before enabling the feature

• DB_FLASHBACK_RETENTION_TARGET
• DB_RECOVERY_FILE_DEST
• DB_RECOVERY_FILE_DEST_SIZE.

Configuring Flashback Database:

Flash Recovery Area:
FRA is a disk location for holding recovery-related files. For Flashback Database, a new background process called Recovery Writer (RVWR) periodically writes before images of data blocks from the SGA flashback buffer to disk as Flashback Logs in the FRA. Flashback Logs are automatically managed within the FRA by Oracle. The cost of Flashback Logs is measured in space and performance. Space will be a factor of the write intensity of the database.

Alter system set db_recovery_file_dest=
'C:\oracle\product\10.2.0\flash_recovery_area' scope=both;

Alter system set db_recovery_file_dest_size = 10G scope=both;

Flashback Retention Target:
This initialization parameter, measured in minutes, represents how many days we need to keep the logs. After that period, the files are automatically deleted if more space is required. Its value translates into the amount and duration of Flashback Logs in the FRA. It is important to understand that this retention is not guaranteed. If the FRA is in need of space, Flashback Logs may be automatically deleted before their target retention. With the retention set, Flashback Database can be enabled.

SQL> startup mount;
Database mounted.
SQL> alter system set db_flashback_retention_target = 1440 scope=both;
System altered.
SQL> alter database flashback on;
Database altered.
SQL> alter database open;
Database altered.

Flashback Logs:
Database retains a copy of the modified database blocks in the logs in
Flashback recovery area. Flashback logs have a file extension of .FLB. The logs are automatically deleted when space needed.

Flashback Technology Levels:

• Flashback database enables you to take the entire database to a past point in time (using flashback logs).
• Flashback drop lets you retrieve accidentally dropped tables and indexes (using the recycle bin).
• Flashback table lets you recover a table to a time in the past (using undo data).
• Flashback query lets you query and restore data rows to a point in time (using undo data). (Select * from emp as of timestamp to_timestamp (.))
• Flashback versions query The Flashback Versions Query feature enables you to use the VERSIONS clause to retrieve all the versions of rows that exist between two points in time or between two SCNs.
• Flashback Transaction query The flashback transaction query table shows the DML statements that used against the tables.

Advantages:

• Restoring data files from backup sets and image copies are not needed. Therefore the recovery is faster.
• The database automatically deletes obsolete logs and backup sets when space is required.
• Recovery related files become obsolete when they exceed the RMAN retention policy or have been backed up to tape.
• Can easily re-flash more than once if needed.
• If you determine that you flashed back too far into the past, you can use redo logs to roll forward.
• We can exclude the particular tablespace to be part of these Flashback operations.

Disadvantages:

• Transaction rate on the database will have an affect on performance of creating the logs.
• If a control file has been restored or re-created during the time span you want to flashback over, you can’t use the Flashback Database feature.
• You can’t flashback a database to before a RESETLOGS operation.
• You can’t flashback a data file that was dropped or shrunk during the time span covered by the flashback table operation.

Conclusion:
This feature is the best strategy to reduce recovery time. We've seen this technology is easy to use, faster than traditional recovery and best of all, it's free! I hope that Flashback Database is an essential component of availability architecture.

References:
http://www.skillbuilders.com/download/download-sample.cfm?course=ora10g-nfo&file=Ora10g_New_Features_Sample_V2.1.pdf
http://www.globusz.com/ebooks/Oracle/00000019.htm
http://download-uk.oracle.com/docs/cd/B19306_01/backup.102/b14194/rcmsynta032.htm
http://www.oracle.com/technology/pub/articles/10gdba/week9_10gdba.html http://www.databasejournal.com/features/oracle/article.php/3446681
http://www.dbazine.com/olc/olc-articles/liu6
http://sysdba.wordpress.com/2006/01/23/flashback-any-error-with-oracle-10g-release-2/