Imagine this: a user accidentally deletes crucial data from a table or updates rows with the wrong values. Panic might set in, but Oracle’s Flashback technology swoops in to save the day. This powerful feature lets you travel back in time to view or/and recover data as it was before the error occurred. In this post, we’ll explore how you can use Oracle Flashback in various scenarios, complete with practical examples.
Flashback Query
This can be used if you want to view the data how it looked some time ago:
Syntax
SELECT * FROM table_name AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '5' MINUTE);Example
SELECT * FROM employees AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '10' MINUTE);This is particularly used if you need to troubleshoot certain scenario and comes handy while debugging.
Flashback Table
This is like the undo functionality which can be used to move table back to stable state and remove unintended operations such as Update/Delete/Insert etc.
Syntax
FLASHBACK TABLE table_name TO TIMESTAMP timestamp_expression;Example
FLASHBACK TABLE employees TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' HOUR);Flashback Drop
Dropped a table? Well you can bring it back (However you will need to enable recycle bin)
Syntax
FLASHBACK TABLE table_name TO BEFORE DROP;Example
FLASHBACK TABLE sales TO BEFORE DROP;Flashback Database
It rolls back the entire database to previous point in time and can be very helpful for quick recovery. It like a rewind button for the entire DB
Syntax
FLASHBACK DATABASE TO TIMESTAMP timestamp_expression;
FLASHBACK DATABASE TO SCN scn_value;
Example
--To restore the database to its state from 2 hours ago:
FLASHBACK DATABASE TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '2' HOUR);
Flashback Transaction Query
This can be utilized you are looking for little details at transaction level
Syntax
SELECT * FROM FLASHBACK_TRANSACTION_QUERY WHERE xid = 'transaction_id';Example
--Investigating Transaction ID 0002000300000007
SELECT * FROM FLASHBACK_TRANSACTION_QUERY WHERE xid = '0002000300000007';It can be used to audit or troubleshoot changes made by particular transaction.
Flashback Versions Query
It helps to track changes for row level changes over period of time.
Syntax
SELECT versions_starttime, versions_endtime, versions_xid, versions_operation, column_list
FROM table_name
VERSIONS BETWEEN TIMESTAMP start_time AND end_time;
Example
SELECT versions_starttime, versions_endtime, versions_xid,
versions_operation, employee_id, salary
FROM emp VERSIONS BETWEEN TIMESTAMP (SYSTIMESTAMP - INTERVAL '30'MINUTE) AND SYSTIMESTAMP;
Flashback Data Archive
This needs to be carefully used if you want to track (and revert to if needed) historical changes made to the table
Syntax
ALTER TABLE table_name FLASHBACK ARCHIVE archive_name;Example
--Enable long-term history tracking on the employees table:
ALTER TABLE employees FLASHBACK ARCHIVE my_archive;
This can be used for compliance requirements and to perform testing of data changes which requires analysis or retention of historical changes to the table.
Flashback Restore Points
These can be created and utilized before making significant changes to the Database.
Syntax
CREATE RESTORE POINT restore_point_name;
FLASHBACK DATABASE TO RESTORE POINT restore_point_name;
--To Drop the restore point
DROP RESTORE POINT restore_point_name;
Example
CREATE RESTORE POINT before_update;
--Later, if needed, roll back to this point:
FLASHBACK DATABASE TO RESTORE POINT before_update;
--to drop this restore point
DROP RESTORE POINT before_update;
Things to keep in mind
Set proper configuration and allocate resources: UNDO tablespace should be configured with sufficient space, and it can be used if flashback is required.
Performance Costs: Flashback might slightly impact performance based on transaction size and number of transactions as it need to retain all the changes made as per configurations.
Precision: If you need precise recovery, then try to capture and use SCN instead of timestamps.
Recycle Bin: Flashback from drop will work only when recycle bin is enabled and dropped object is present there.
