ADVERTISEMENT

Oracle Flashback Commands

A concise guide to essential Oracle Flashback commands for database recovery, restore points, and undoing changes. Useful for managing Flashback settings, querying historical data, and performing recovery to a specific point in time or SCN. Ideal for troubleshooting, data correction, and maintaining data integrity.

Check Flashback Status

-- Check if Flashback is enabled
SELECT flashback_on FROM v$database;

Enable Flashback

-- Set recovery file destination and size
ALTER SYSTEM SET db_recovery_file_dest = '/u01/app/oracle/flash_recovery_area' SCOPE=BOTH;
ALTER SYSTEM SET db_recovery_file_dest_size = 10G SCOPE=BOTH;

-- Enable Flashback (database must be in ARCHIVELOG mode)
ALTER DATABASE FLASHBACK ON;

Disable Flashback

-- Disable Flashback
ALTER DATABASE FLASHBACK OFF;

Create & Drop Restore Points

-- Create guaranteed restore point
CREATE RESTORE POINT FLASHBACK_PREP GUARANTEE FLASHBACK DATABASE;

-- Drop restore point
DROP RESTORE POINT FLASHBACK_PREP;

List Restore Points

-- From SQL
SELECT * FROM v$restore_point;

-- From RMAN
LIST RESTORE POINT ALL;

Flashback to Restore Point

-- Check available restore points
SELECT name, time FROM v$restore_point;

-- Flashback database to restore point
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
FLASHBACK DATABASE TO RESTORE POINT FLASHBACK_PREP;
ALTER DATABASE OPEN RESETLOGS;

Flashback to SCN or Timestamp

-- Flashback to specific SCN
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
FLASHBACK DATABASE TO SCN 202381;
ALTER DATABASE OPEN RESETLOGS;

-- Flashback to specific timestamp
FLASHBACK DATABASE TO TIMESTAMP TO_TIMESTAMP('2025-01-05 16:00:00', 'YYYY-MM-DD HH24:MI:SS');
ALTER DATABASE OPEN RESETLOGS;

Flashback Query (View Past Data)

-- Flashback query using exact timestamp
SELECT * FROM W3BUDDY.EMP AS OF TIMESTAMP TO_TIMESTAMP('2025-01-05 10:00:00', 'YYYY-MM-DD HH24:MI:SS');

-- Flashback query 1 hour ago
SELECT * FROM W3BUDDY.EMP AS OF TIMESTAMP SYSDATE - 1/24;

Flashback Dropped Table (Recycle Bin)

-- Check table in recycle bin
SELECT object_name, original_name, createtime FROM recyclebin WHERE original_name = 'EMP';

-- Restore with original name
FLASHBACK TABLE int_admin_emp TO BEFORE DROP;

-- Restore with new name
FLASHBACK TABLE int_admin_emp TO BEFORE DROP RENAME TO int2_admin_emp;

Flash Recovery Area Usage

-- View Flashback area usage
SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;

Determine Flashback Window

-- Check how far you can flashback (by time)
SELECT TO_CHAR(oldest_flashback_time, 'DD-MON-YYYY HH24:MI:SS') AS "Oldest Flashback Time" FROM v$flashback_database_log;

-- Check oldest SCN available
SELECT oldest_flashback_scn FROM v$flashback_database_log;

ADVERTISEMENT