Oracle Flashback Technology

Oracle Flashback Database provides a more efficient alternative to database point-in-time recovery. When you use flashback database, your current datafiles revert to their contents at a past time. The result is much like the result of a point-in-time recovery using datafile backups and redo logs, but you do not have to restore datafiles from backup and you do not have to re-apply as many individual changes in the redo logs as you would have to do in conventional media recovery.


Flashback database relay on Flashback logs.


set linesize 132 pages 80
col name format a30
col time format a32
SELECT name,to_char(scn),time,guarantee_flashback_database FROM v$restore_point order by name;

edit database 'Stadnby' set state = 'APPLY-OFF';
edit database 'Primary' set state = 'TRANSPORT-OFF';
standby -> CREATE RESTORE POINT Test GUARANTEE FLASHBACK DATABASE;
Primary -> CREATE RESTORE POINT Test GUARANTEE FLASHBACK DATABASE;
edit database 'Primary' set state = 'TRANSPORT-ON';
edit database 'Stadnby' set state = 'APPLY-ON' with apply instance = '';

edit database 'Stadnby' set state = 'APPLY-OFF';
edit database 'Primary' set state = 'TRANSPORT-OFF';
Stop Databases on Primary and standby
start one of the instances
Primary -> flashback database to RESTORE POINT Test;
Standby -> flashback database to RESTORE POINT Test;
Start Databases on Primary and standby
edit database 'Primary' set state = 'TRANSPORT-ON';
edit database 'Stadnby' set state = 'APPLY-ON' with apply instance = '';

Oracle Flashback Query feature lets you specify a target time and then run queries against your database, viewing results as they would have appeared at that time. To recover from an unwanted change like an erroneous update to a table, a user could choose a target time before the error and run a query to retrieve the contents of the lost rows

SELECT * FROM EMPLOYEE AS OF TIMESTAMP TO_TIMESTAMP('2009-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS') WHERE name = 'Adwin';


Oracle Flashback Version Query lets you view all the versions of all the rows that ever existed in one or more tables in a specified time interval. You can also retrieve metadata about the different versions of the rows, including start time, end time, operation, and transaction ID of the transaction that created the version. This feature can be used both to recover lost data values and to audit changes to the tables queried.

SELECT versions_startscn, versions_starttime,
versions_endscn, versions_endtime,
versions_xid, versions_operation,
last_name, salary
FROM employees
VERSIONS BETWEEN TIMESTAMP
TO_TIMESTAMP('2013-02-18 14:00:00', 'YYYY-MM-DD HH24:MI:SS')
AND TO_TIMESTAMP('2014-12-18 18:00:00', 'YYYY-MM-DD HH24:MI:SS')
WHERE first_name = 'Adwin';


Oracle Flashback Transaction Query to retrieve metadata and historical data for a given transaction or for all transactions in a given time interval.The column UNDO_SQL shows the SQL code that is the logical opposite of the DML operation performed by the transaction. You can usually use this code to reverse the logical steps taken during the transaction 

SELECT xid, operation, start_scn, commit_scn, logon_user, undo_sql
FROM flashback_transaction_query
WHERE xid = HEXTORAW('000200030000002D');


Oracle Flashback Table returns a table to its state at a previous point in time. You can restore table data while the database is online, undoing changes only to the specified table. 

FLASHBACK TABLE employee TO TIMESTAMP TO_TIMESTAMP('2003-04-04 09:30:00', `YYYY-MM-DD HH24:MI:SS');


Oracle Flashback Drop reverses the effects of a DROP TABLE statement.

When you drop a table, the database does not immediately remove the space associated with the table. Instead, the table is renamed and, along with any associated objects, it is placed in the Recycle Bin of the database. The Flashback Drop operation recovers the table from the recycle bin.

FLASHBACK TABLE "BIN$KSD8DB9L345KLA==$0" TO BEFORE DROP;
FLASHBACK TABLE HR.INT_ADMIN_EMP TO BEFORE DROP;
FLASHBACK TABLE "BIN$KSD8DB9L345KLA==$0" TO BEFORE DROP RENAME TO HR.int2_admin_emp;


Flashback Table, Flashback Query, Flashback Transaction Query and Flashback Version Query all rely on undo data, records of the effects of each update to an Oracle database and values overwritten in the update. Used primarily for such purposes as providing read consistency for SQL queries and rolling back transactions, these undo records contain the information required to reconstruct data as it stood at a past time and examine the record of changes since that past time