Fix Mistakes Without a Restore
Flashback is a family of features that lets you look at, or return to, the past. It is the fastest way to fix human mistakes.
| Feature | What it does | Uses |
| Flashback Query | Shows data as it was at a past time | Undo |
| Flashback Version Query | Shows every version of a row | Undo |
| Flashback Table | Rewinds one table | Undo |
| Flashback Drop | Brings back a dropped table | Recycle bin |
| Flashback Database | Rewinds the whole database | Flashback logs |
Flashback Query
sql
SELECT * FROM shop.customers
AS OF TIMESTAMP SYSTIMESTAMP - INTERVAL '10' MINUTE
WHERE city = 'Mumbai';
Rows that were deleted 5 minutes ago are visible here. Put them back with an insert:
sql
INSERT INTO shop.customers (full_name, city, email)
SELECT full_name, city, email
FROM shop.customers
AS OF TIMESTAMP SYSTIMESTAMP - INTERVAL '10' MINUTE
WHERE email NOT IN (SELECT email FROM shop.customers);
Limit: Flashback Query only works as far back as the undo is kept (undo_retention).
Version Query
sql
SELECT versions_starttime, versions_operation, price
FROM shop.products
VERSIONS BETWEEN TIMESTAMP SYSTIMESTAMP - INTERVAL '1' HOUR AND SYSTIMESTAMP
WHERE product_name = 'Laptop';
Flashback Table
sql
ALTER TABLE shop.products ENABLE ROW MOVEMENT;
FLASHBACK TABLE shop.products
TO TIMESTAMP SYSTIMESTAMP - INTERVAL '15' MINUTE;
Flashback Drop and the Recycle Bin
When you DROP TABLE, Oracle does not delete it. It renames it and puts it in the recycle bin.
sql
DROP TABLE shop.products;
SELECT object_name, original_name, droptime
FROM dba_recyclebin
WHERE owner = 'SHOP';
FLASHBACK TABLE shop.products TO BEFORE DROP;
sql
-- really delete
DROP TABLE shop.products PURGE;
PURGE RECYCLEBIN;
Tip: Use DROP ... PURGE only when you are sure. A purged table cannot be flashed back.
Flashback Database
Rewinds the whole database by applying flashback logs backwards. It is much faster than a restore for large databases.
sql
-- one time: turn it on (needs the Fast Recovery Area)
ALTER SYSTEM SET db_flashback_retention_target = 1440 SCOPE = BOTH; -- minutes
ALTER DATABASE FLASHBACK ON;
SELECT flashback_on FROM v$database;
sql
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
FLASHBACK DATABASE TO TIMESTAMP SYSTIMESTAMP - INTERVAL '30' MINUTE;
ALTER DATABASE OPEN RESETLOGS;
Guaranteed Restore Points
Before risky work such as an upgrade or a big data change, mark a point you can always return to:
sql
CREATE RESTORE POINT before_upgrade GUARANTEE FLASHBACK DATABASE;
-- if everything goes wrong
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
FLASHBACK DATABASE TO RESTORE POINT before_upgrade;
ALTER DATABASE OPEN RESETLOGS;
-- if everything goes fine, free the space
DROP RESTORE POINT before_upgrade;
Space warning: A guaranteed restore point keeps all flashback logs from that moment. Drop it when you are finished, or the recovery area will fill.
Flashback is not a backup: It cannot help if a datafile is physically lost. You still need RMAN backups.
Conclusion
Use flashback query and table for everyday mistakes, the recycle bin for dropped tables and Flashback Database or a restore point for big risky changes.
Quick Summary: AS OF to look, FLASHBACK TABLE to rewind, TO BEFORE DROP to undrop, restore points before risky work.