Rewinding Data with Ease – Oracle Flashback Features You Need to Know

Oracle Flashback Technology is a powerful suite of features that helps administrators and
developers recover from human errors and logical data corruptions with minimal
downtime. Traditional recovery methods usually involve restoring data from backups and
applying archived redo logs, which can be time-consuming and may result in data loss.
Flashback solves this by providing fast, flexible, and granular recovery mechanisms
using undo data, flashback logs, and system change numbers (SCN) to restore data to
a consistent point in time.

1 Theoretical Background

1.1 What is a Flashback?

1. A Flashback is the ability to look at or restore data as it existed at a prior point
   in time, without physically restoring from backups.
2. Oracle internally maintains Flashback Logs in the Fast Recovery Area (FRA).
   These logs store before-images of changed blocks and can be applied to rewind
   the database or objects.
3. Oracle also leverages UNDO tablespace to reconstruct older versions of rows for
   query-level flashbacks.

1.2 Key Components

1. System Change Number (SCN):
   • Oracle maintains a monotonically increasing number (SCN) for every
     committed transaction.
   • SCNs act as “timestamps” and help identify consistent snapshots of the
     database.
2. Undo Data:
   • Stored in undo tablespaces.
   • Used for query-based flashback features (e.g., Flashback Query, Flashback
     Version Query).
3. Flashback Logs:
   • Stored in FRA.
   • Used by Flashback Database and Guaranteed Restore Points.
   • These logs are not redo logs but specifically track before-images of
     database blocks.
4. Fast Recovery Area (FRA):
   • Central storage for redo, archived logs, flashback logs, backups, and
     restore points.
   • Sizing FRA correctly is critical for effective Flashback usage.

1.3 Benefits of Flashback

1. Fast Recovery: No need to restore physical files from backup.
2. Minimal Downtime: Applications can continue with quick fixes (e.g., rolling
   back an update).
3. Granularity: Recovery can be at row level, table level, or database level.
4. Cost-Effective: Reduces dependency on frequent restores from tape or backup
   appliances.
5. Developer-Friendly: Helps developers and testers quickly recover test data
   without DBA overhead.

1.4 Limitations

1. Flashback does not protect against media failures (disk crashes, storage
   corruption).
2. Requires sufficient UNDO space and FRA space; otherwise, flashback
   operations may fail.
3. Retention is limited by space and retention settings
   (DB_FLASHBACK_RETENTION_TARGET).
4. Cannot be used for physical corruption recovery — backups/ZDLRA are
   required.

2 Prerequisites for Flashback

1. Database must be in ARCHIVELOG mode.
2. UNDO tablespace must be adequately sized.
3. Flash Recovery Area (FRA) must be configured.
4. Flashback Database must be enabled for database-level rewind.
5. For Flashback Table, row movement must be enabled to allow Oracle to change
   row locations when rewinding.

3 Types of Flashback Features

3.1 Flashback Query

1. Allows you to run queries against a past version of data.
2. It is read-only, and no actual data change occurs.
3. Internally, Oracle reconstructs the row image using undo data.

SELECT * FROM employees AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '15'
MINUTE)
WHERE employee_id = 101;

3.2 Flashback Version Query

1. Shows all versions of a row, including start SCN, end SCN, and transaction IDs.
2. Useful for auditing who changed what and when.
3. Internally, Oracle uses undo + redo information.

SELECT versions_startscn, versions_endscn, versions_xid,
versions_operation, salary
FROM employees VERSIONS BETWEEN TIMESTAMP (SYSTIMESTAMP - INTERVAL '1'
HOUR) AND SYSTIMESTAMP
WHERE employee_id = 101;

3.3 Flashback Transaction Query

1. Identifies the transactions that modified data.
2. Can even generate undo SQL statements to rollback specific transactions.

SELECT xid, logon_user, operation, commit_timestamp, undo_sql
FROM flashback_transaction_query
WHERE table_name='EMPLOYEES';

Rollback:

EXECUTE DBMS_FLASHBACK.TRANSACTION_BACKOUT('050012001F030000');

3.4 Flashback Table

1. Rewinds an entire table to a past state (SCN or timestamp).
2. Requires row movement enabled because row IDs may change when rewinding

ALTER TABLE employees ENABLE ROW MOVEMENT;

FLASHBACK TABLE employees TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '30'
MINUTE);

3.5 Flashback Drop (Recycle Bin)

1. When a table is dropped, it goes into the recycle bin (if enabled).
2. Flashback Drop restores the table from recycle bin with original or new name.

DROP TABLE employees;

FLASHBACK TABLE employees TO BEFORE DROP;

3.6 Flashback Database

1. Rewinds the entire database to a past SCN or timestamp.
2. Internally, it applies flashback logs (before-images of blocks).
3. Equivalent to PITR, but much faster because no backup restore is needed.

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

FLASHBACK DATABASE TO TIMESTAMP TO_TIMESTAMP('2025-09-18
10:00:00','YYYY-MM-DD HH24:MI:SS');

ALTER DATABASE OPEN RESETLOGS;

3.7 Guaranteed Restore Points

1. Special restore points ensure that flashback logs are always retained until
   dropped.
2. Very useful before upgrades or risky deployments.
3. Provides a guaranteed rollback point.

CREATE RESTORE POINT before_upgrade GUARANTEE FLASHBACK DATABASE;

FLASHBACK DATABASE TO RESTORE POINT before_upgrade;

DROP RESTORE POINT before_upgrade;

4 Flashback Use Case with example

Scenario:
At 11:30 AM, a user accidentally deletes rows from EMPLOYEES. Requirement is to
restore the table to its state at 11:15 AM.

Conceptual Flow:

1. Identify timestamp or SCN (from logs or by querying system time).
2. Enable row movement on EMPLOYEES.
3. Issue Flashback Table command.
4. Verify data consistency.

ALTER TABLE employees ENABLE ROW MOVEMENT;

FLASHBACK TABLE employees TO TIMESTAMP TO_TIMESTAMP('2025-09-18
11:15:00','YYYY-MM-DD HH24:MI:SS');

This restores the table instantly, without needing full restore from backup.

5 Conclusion

1. Purpose: Quick recovery from logical corruption (e.g., DROP, DELETE,
   UPDATE).
2. Mechanism: Uses undo data + flashback logs.
3. Granularity: Supports row, table, schema, or entire DB rewind.
4. Retention: Limited by undo/FRA size.
5. Limitations: Not for physical corruption, media failures, or long-term
   compliance.