Flashback Technologies

Key parameters: UNDO_RETENTION, DB_FLASHBACK_RETENTION_TARGET, DB_RECOVERY_FILE_DEST(_SIZE).

Flashback Query (AS OF) Flashback Version Query See all changes in an interval Flashback Transaction Query Find and undo a transaction Flashback Table Rewind a table Flashback Drop Flashback Database Rewind the whole DB

Flashback Data Archive (Flashback Time Travel) Long-term history/audit

Key parameters: UNDO_RETENTION, DB_FLASHBACK_RETENTION_TARGET, DB_RECOVERY_FILE_DEST(_SIZE).

-- Flashback Query
SELECT * FROM employees AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '15' MINUTE) WHERE employee_id = 100;
SELECT * FROM employees AS OF SCN 2345678;

-- Version Query
SELECT versions_starttime, versions_endtime, versions_xid, versions_operation, salary
FROM employees VERSIONS BETWEEN TIMESTAMP SYSTIMESTAMP - INTERVAL '1' HOUR AND SYSTIMESTAMP
WHERE employee_id = 100;

-- Flashback Table
ALTER TABLE employees ENABLE ROW MOVEMENT;
FLASHBACK TABLE employees TO TIMESTAMP SYSTIMESTAMP - INTERVAL '10' MINUTE;

-- Flashback Drop
FLASHBACK TABLE employees_bkp TO BEFORE DROP RENAME TO employees_restored;

-- Flashback Database (needs a Fast Recovery Area + FLASHBACK ON)
ALTER DATABASE FLASHBACK ON;
CREATE RESTORE POINT before_upgrade GUARANTEE FLASHBACK DATABASE;
-- SHUTDOWN IMMEDIATE; STARTUP MOUNT;
FLASHBACK DATABASE TO RESTORE POINT before_upgrade;
ALTER DATABASE OPEN RESETLOGS;

-- Flashback Data Archive
CREATE FLASHBACK ARCHIVE fda_5yr TABLESPACE fda_ts RETENTION 5 YEAR;
ALTER TABLE employees FLASHBACK ARCHIVE fda_5yr;