FLASHBACK
QUERY | TABLE | DROP | DATABASE
Flashback — Overview
- Flashback Technology lets you view or rewind data to a past point in time, without doing a traditional restore/recover.
- Used mainly for logical corruption / human error — accidental DELETE, UPDATE, DROP — much faster than a full point-in-time recovery.
- Different Flashback features rely on different sources: Undo data, the Recycle Bin, or Flashback Logs.
| Feature | Depends On | Scope |
|---|---|---|
| Flashback Query | Undo data | Row-level, as of a past time/SCN |
| Flashback Table | Undo data | Rewind an entire table to a past time/SCN |
| Flashback Drop | Recycle Bin | Restore a dropped table from the recycle bin |
| Flashback Database | Flashback Logs (FRA) | Rewind the entire database to a past time/SCN |
1) Flashback Query
- Lets you view the data of a table as it existed at a past point in time or SCN.
- Uses the AS OF clause; internally relies on undo data, so it is bound by UNDO_RETENTION.
SQL
-- Query data as of a past timestamp SELECT * FROM hr.employees AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '30' MINUTE) WHERE employee_id = 100; -- Query data as of a past SCN SELECT * FROM hr.employees AS OF SCN 1583421 WHERE employee_id = 100; -- Recover an accidentally deleted row INSERT INTO hr.employees SELECT * FROM hr.employees AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '10' MINUTE) WHERE employee_id = 100;
2) Flashback Table
- Rewinds an entire table (data + indexes) back to a past time/SCN, without needing a restore.
- Table must have ROW MOVEMENT enabled.
- Relies on undo data — bound by UNDO_RETENTION and how much undo is still available.
SQL
ALTER TABLE hr.employees ENABLE ROW MOVEMENT; FLASHBACK TABLE hr.employees TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '30' MINUTE); FLASHBACK TABLE hr.employees TO SCN 1583421; -- Flashback a table without restoring dependent objects FLASHBACK TABLE hr.employees TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '30' MINUTE) DISABLE TRIGGERS;
3) Flashback Drop (Recycle Bin)
- When a table is dropped, Oracle renames and keeps it (and its dependent objects) in the Recycle Bin instead of deleting it immediately.
- FLASHBACK TABLE ... TO BEFORE DROP restores it, without needing any backup/recovery.
- Controlled by the RECYCLEBIN initialization parameter (ON by default).
- DROP TABLE ... PURGE bypasses the recycle bin permanently — cannot be flashed back.
SQL
-- View dropped objects in the recycle bin SHOW RECYCLEBIN; SELECT object_name, original_name, droptime FROM recyclebin; -- Restore the dropped table FLASHBACK TABLE hr.employees TO BEFORE DROP; -- Restore and rename it at the same time FLASHBACK TABLE hr.employees TO BEFORE DROP RENAME TO employees_old; -- Permanently purge (skip recycle bin / clear it) DROP TABLE hr.employees PURGE; PURGE TABLE hr.employees; PURGE RECYCLEBIN; PURGE DBA_RECYCLEBIN; -- as SYSDBA, clears for all users -- Enable / disable recycle bin ALTER SYSTEM SET recyclebin = ON; ALTER SESSION SET recyclebin = OFF;
4) Flashback Database
- Rewinds the entire database to a past time/SCN — much faster than a full restore + point-in-time recovery.
- Requires Flashback Logs, stored in the Fast Recovery Area (FRA), and the database must be in ARCHIVELOG mode.
- Performed at the RMAN / MOUNT level — affects the whole database, not a single table.
- Controlled by FLASHBACK_RETENTION_TARGET (minutes) — how far back you can flash the database.
SQL
-- Check current flashback status SELECT flashback_on FROM v$database; -- Set FRA (needed before enabling flashback) ALTER SYSTEM SET db_recovery_file_dest_size = 20G; ALTER SYSTEM SET db_recovery_file_dest = '/u01/app/oracle/fast_recovery_area'; -- Enable Flashback Database (must be in MOUNT stage) SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALTER DATABASE ARCHIVELOG; ALTER DATABASE FLASHBACK ON; ALTER DATABASE OPEN; -- Set how far back flashback logs are kept (minutes) ALTER SYSTEM SET db_flashback_retention_target = 1440; -- 24 hours -- Perform the flashback (via RMAN, database must be MOUNTed) SHUTDOWN IMMEDIATE; STARTUP MOUNT; -- rman target / FLASHBACK DATABASE TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' HOUR); ALTER DATABASE OPEN RESETLOGS; -- Disable Flashback Database SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALTER DATABASE FLASHBACK OFF; ALTER DATABASE OPEN;
Easy Memory
FLASHBACK
|
+-- Undo-based (short term, needs UNDO_RETENTION)
| +-- Query --> data AS OF a time/SCN
| +-- Table --> rewind whole table
|
+-- Recycle Bin based
| +-- Drop --> TO BEFORE DROP (unless PURGE used)
|
+-- Flashback Logs (FRA) based
+-- Database --> rewind entire DB (needs ARCHIVELOG + FLASHBACK ON)