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.
FeatureDepends OnScope
Flashback QueryUndo dataRow-level, as of a past time/SCN
Flashback TableUndo dataRewind an entire table to a past time/SCN
Flashback DropRecycle BinRestore a dropped table from the recycle bin
Flashback DatabaseFlashback 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)