Oracle Flashback Technology and features

Oracle Flashback Technology is a group of Database features that that will help you to view past states of database objects or to return database objects to a previous state without using point-in-time media recovery.

With flashback features, you can do the following:
· Queries that return past data
· Recover rows or tables to a previous point in time
· Track and archive transactional data changes
· Roll back a transaction and its dependent transactions while the db online

Oracle flashback uses automatic undo management (AUM) for all flashback transactions.

Here are few Oracle flashback features.
· Flashback Database
· Flashback Table
· Flashback drop
· Flashback query
· Flashback Version/Transaction Query
· Flashback Data Archive (From Oracle 11g)
· Flashback Recovery Area:-

Flashback Database:- The FLASHBACK DATABASE is a fast alternative to performing an incomplete recovery. The database must be running in ARCHIVELOG mode, because archived logs are used in the Flashback Database operation. You must have a flash recovery area enabled, because flashback logs can only be stored in the flash recovery area.

To enable logging for Flashback Database, set the DB_FLASHBACK_RETENTION_TARGET initialization parameter and issue the ALTER DATABASE FLASHBACK ON statement.

DB_FLASHBACK_RETENTION_TARGET- length of the desired flashback window in minutes (1440 min)

SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320; # 3 days

For ex-
FLASHBACK DATABASE TO TIMESTAMP my_date;
FLASHBACK DATABASE TO SCN my_scn;

Limitations:
· It cannot be used to repair media failures, or to recover from accidental deletion of data files.
· You cannot use Flashback Database to undo a shrink data file operation.
· If the database control file is restored from backup or re-created, all accumulated flashback log information is discarded
· Flashback database cannot be used against block corruptions.

Flashback Table The FLASHBACK TABLE used to restore an earlier state of a table in the event of human or application error. The time in the past to which the table can be flashed back is dependent on the amount of undo data in the system and cannot restore a table to an earlier state across any DDL operation.

SQL> FLASHBACK TABLE test TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' minute);

Flashback Drop:

The Oracle 10g provides the ability to reinstating an accidentally dropped table from recyclebin.
SQL> FLASHBACK TABLE test TO BEFORE DROP;

Flashback Query The feature allows the DBA to see the value of a column as of a specific time, as long as the before-image copy of the block is available in the undo segment.

For ex:-
SQL> SELECT comments FROM employee AS OF TIMESTAMP TO_TIMESTAMP ('2004-03-29 13:34:12', 'YYYY-MM-DD HH24:MI:SS');

SQL> SELECT comments FROM employee AS OF SCN 722452;

Flashback Version/Transaction Query Flashback Query only provides a fixed snapshot of the data as of a time. Use Flashback Version Query feature to see the changed data between two time points.

Ex: - The following query shows the changes made to the table:

SQL> select versions_starttime, versions_endtime, versions_xid, versions_operation, rate from rates versions between timestamp minvalue and maxvalue order by VERSIONS_STARTTIME

Flashback transaction query can be used to get extra information about the transactions listed by flashback version queries using FLASHBACK_TRANSACTION_QUERY view. The UNDO_SQL column in the table shows the actual SQL Statement.

For Ex:-
SQL> SELECT xid, operation, start_scn, commit_scn, logon_user, undo_sql FROM flashback_transaction_query

Oracle 11g has more enhancements in Flashback Transaction and LogMiner. The LogMiner Viewer has been incorporated into Enterprise Manager and integrated with the new Flashback Transaction feature, making it simple to recover transactions. Flashback Transaction allows the changes made by a transaction to be undone.

Flashback Data Archive (From Oracle 11g) : Flashback data archive allows long-term retention (for ex years) of changed data to a table or set of tables for a user-defined period of time. Flashback Data Archive (which can logically span one or more table spaces) specifies a space quota and retention period for the archive, and then creates the required tables in the archive.

User needs the FLASHBACK ARCHIVE ADMINISTER, FLASHBACK ARCHIVE privileges

SQL> CREATE FLASHBACK ARCHIVE flash_back_archive

TABLESPACE flash_back_archive QUOTA 10G RETENTION 5 YEARS;

Flashback Recovery Area:-
Flash recovery area is a disk location in which the database can store and manage files related to Backup and Recovery. To setup a flash recovery area, you must choose a directory location or Automatic Storage Management disk group to hold the files.

Flash recovery area simplifies the administration of your database by automatically naming recovery-related files, retaining the files as long as they are needed for restore and recovery activities, and deleting the files when they are no longer needed to restore your database and space is needed for some other backup and recovery-related purpose.

To Setup Flash Recovery Area (FRA), you just need to specify below two parameters.
1. DB_RECOVERY_FILE_DEST_SIZE (Specifies max space to use for FRA)

2. DB_RECOVERY_FILE_DEST (Location of FRA)

Performance Guidelines for Oracle Flashback Technology
· For Oracle Flashback Version Query, use index structures.

· Not to scan entire tables and use indexes to query small set of past data. If you need to scan a full table then use parallel hint to the query

· Keep the statistics current as cost-based optimized relies on statistics and use the DBMS_STATS package to generate statistics for tables involved in a Oracle Flashback Query.

· In a Oracle Flashback Transaction Query, the xid column is of the type RAW(8). Use the HEXTORAW conversion function: HEXTORAW(xid)to take advantage of inbuilt function

· The I/O performance cost is mainly paging in the data and undo blocks I the buffer cache. The CPU performance cost is to apply undo.

· A Oracle Flashback Query against a materialized view does not take advantage of query rewrite optimization.

Regards

Satishbabu Gunukula, Oracle ACE

http://www.oracleracexpert.com