Oracle Flashback Technology provides rapid data recovery solutions, reducing recovery time from hours to minutes. It enables selective undoing of errors at various levels including row, transaction, table, and entire database operations.

Core Concepts

Undo Segments

Undo segments store pre-change data for transaction rollback and flashback operations. Each transaction writes original data to an undo segment before modification.

Flashback Methods

Flashback Query

Retrieves historical data at a specific timestamp:

SELECT * FROM employees 
AS OF TIMESTAMP TO_TIMESTAMP('2023-01-01 09:00:00', 'YYYY-MM-DD HH24:MI:SS');

Flashback Table

Reverts a table to a previous state:

ALTER TABLE employees ENABLE ROW MOVEMENT;
FLASHBACK TABLE employees TO TIMESTAMP TO_TIMESTAMP('2023-01-01 09:00:00');

Flashback Drop

Recovers accidentally dropped tables:

FLASHBACK TABLE employees TO BEFORE DROP;
-- Rename during recovery
FLASHBACK TABLE employees TO BEFORE DROP RENAME TO employees_restored;

Flashback Data Archive

Maintains long-term historical data:

CREATE FLASHBACK ARCHIVE historical_data 
TABLESPACE archive_tbs RETENTION 5 YEAR;
ALTER TABLE employees FLASHBACK ARCHIVE historical_data;

Flashback Database

Restores entire database to a past point:

FLASHBACK DATABASE TO TIMESTAMP TO_TIMESTAMP('2023-01-01 09:00:00');
ALTER DATABASE OPEN RESETLOGS;

Implementation Requirements

Flashback Database Prerequisites

1. Enable ARCHIVELOG mode
2. Configure recovery area:

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/u01/app/oracle/flash_recovery_area';
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 20G;

3. Set retention target:

ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET = 2880; -- Minutes (48 hours)

4. Enable flashback:

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE FLASHBACK ON;
ALTER DATABASE OPEN;

Flashback Table Considerations

• Ensure adequate UNDO retention:

ALTER SYSTEM SET UNDO_RETENTION = 86400; -- Seconds (24 hours)

• Enable row movement:

ALTER TABLE employees ENABLE ROW MOVEMENT;

Operational Limitations

• Physical media damage requires traditional RMAN recovery
• System tablespace cannot be flashed back
• DDL operations block flashback operations
• Space constraints in recovery areas may purge flashback data

Use Case Examples

Recovering Accidantal Data Modification

UPDATE employees SET salary = salary * 1.1 WHERE department_id = 50;
-- Discover error
FLASHBACK TABLE employees TO TIMESTAMP TO_TIMESTAMP('2023-01-01 09:00:00');

Restoring Dropped Table

DROP TABLE project_data;
FLASHBACK TABLE project_data TO BEFORE DROP;

Cross-Transaction Recovery

BEGIN
  DBMS_FLASHBACK.TRANSACTION_BACKOUT(
    NUMTXNS => 1,
    XIDS => SYS.XID_ARRAY('0A000700A10F0000'),
    OPTIONS => DBMS_FLASHBACK.CASCADE
  );
END;
/
COMMIT;

Monitoring and Maintenance

Check Enabled Features

SELECT flashback_on FROM v$database;

View Flashbakc Window

SELECT oldest_flashback_scn, oldest_flashback_time 
FROM v$flashback_database_log;

Purge Historical Archive

ALTER FLASHBACK ARCHIVE historical_data 
PURGE BEFORE TIMESTAMP TO_TIMESTAMP('2022-01-01', 'YYYY-MM-DD');