Oracle Flashback: Multiple Recovery Options After an Accidental DELETE

Scenario: An accidental DELETE has been executed on a critical table in production. Before immediately going for backup restore, an Oracle DBA should first check whether Flashback can provide a faster recovery option.

Oracle Flashback provides several ways to look at or recover previous versions of data. Depending on the situation, you may be able to recover a few rows, an entire table, or even rewind the database to an earlier point in time.

Oracle Flashback Recovery Options

1. Flashback Query – View historical data
2. Flashback Table – Recover a table to an earlier point in time
3. Flashback Database – Rewind the entire database
4. Guaranteed Restore Point – Create a guaranteed recovery reference

---

1. Flashback Query – Check Historical Data

The first thing I would check after an accidental DELETE is whether the required data can still be viewed using Flashback Query.

Flashback Query uses Oracle’s undo information to retrieve data as it existed at an earlier point in time.

Example

SELECT *
FROM emp
AS OF TIMESTAMP SYSTIMESTAMP - INTERVAL '10' MINUTE;

This query attempts to show the EMP table as it existed approximately 10 minutes ago.

Check specific historical data

SELECT empno, ename, job, sal
FROM emp
AS OF TIMESTAMP SYSTIMESTAMP - INTERVAL '10' MINUTE;

This is extremely useful when you accidentally delete rows and want to find out exactly what was present before the DELETE.

Compare current data with historical data

SELECT *
FROM emp
MINUS
SELECT *
FROM emp
AS OF TIMESTAMP SYSTIMESTAMP - INTERVAL '10' MINUTE;

This can help identify rows that existed 10 minutes ago but are no longer present in the current table.

Important: Flashback Query depends on the availability of the required undo information. If the required undo has already been overwritten, the query may fail with an error such as ORA-01555: snapshot too old.

---

2. Flashback Table – Recover a Table

If an entire table has been accidentally modified or deleted from, FLASHBACK TABLE can sometimes be used to return the table to an earlier point in time.

Example

FLASHBACK TABLE emp
TO TIMESTAMP SYSTIMESTAMP - INTERVAL '10' MINUTE;

This attempts to restore the EMP table to its state approximately 10 minutes ago.

Enable Row Movement

Before using Flashback Table, row movement generally needs to be enabled:

ALTER TABLE emp ENABLE ROW MOVEMENT;

Then execute:

FLASHBACK TABLE emp
TO TIMESTAMP SYSTIMESTAMP - INTERVAL '10' MINUTE;

Using a specific timestamp

FLASHBACK TABLE emp
TO TIMESTAMP TO_TIMESTAMP(
    '2026-08-31 10:30:00',
    'YYYY-MM-DD HH24:MI:SS'
);

Important: The required undo information must still be available. Flashback Table is not a replacement for a proper backup and recovery strategy.

---

3. Flashback Database – Rewind the Entire Database

Flashback Database works at a much larger level. Instead of recovering only one table, it can rewind the database to an earlier point in time.

This can be useful for situations such as:

• Large accidental data changes
• Incorrect batch processing
• Application deployment problems
• Large-scale accidental DELETE or UPDATE operations

Check Flashback Database Status

SELECT FLASHBACK_ON
FROM V$DATABASE;

If the result is:

YES

Flashback Database is enabled.

If the result is:

NO

Flashback Database is not enabled.

Check current database status

SELECT NAME,
       OPEN_MODE,
       FLASHBACK_ON
FROM V$DATABASE;

Flashback Database to a specific timestamp

FLASHBACK DATABASE
TO TIMESTAMP TO_TIMESTAMP(
    '2026-08-31 10:00:00',
    'YYYY-MM-DD HH24:MI:SS'
);

Important: Flashback Database is a database-level recovery operation and has operational prerequisites. In a production environment, carefully validate the recovery plan, database state, Data Guard configuration if applicable, and business impact before executing it.

---

4. Guaranteed Restore Point – Create a Recovery Reference

A Guaranteed Restore Point provides a guaranteed point to which the database can be flashed back, provided the required flashback infrastructure and storage are available.

This is particularly useful before risky activities such as:

• Major application deployments
• Database upgrades
• Large batch processing
• Application migrations
• Major schema changes

Create a Guaranteed Restore Point

CREATE RESTORE POINT before_batch_process
GUARANTEE FLASHBACK DATABASE;

Check Restore Points

SELECT NAME,
       TIME,
       GUARANTEE_FLASHBACK_DATABASE
FROM V$RESTORE_POINT;

Flashback Database to the Restore Point

FLASHBACK DATABASE
TO RESTORE POINT before_batch_process;

Drop the Restore Point After Successful Processing

DROP RESTORE POINT before_batch_process;

Important: A guaranteed restore point can cause significant Flash Recovery Area (FRA) storage consumption because Oracle must retain the required flashback logs. Always monitor FRA usage.

---

5. Important Flashback Prerequisites

Before depending on Flashback for production recovery, a DBA should verify the following.

• Required UNDO information is still available for Flashback Query or Flashback Table.
• UNDO_RETENTION is configured appropriately for the workload.
• The database has sufficient Flash Recovery Area (FRA) space when Flashback Database is being used.
• Flashback Database must be enabled for database-level flashback operations.
• The required flashback logs must still be available.
• For Flashback Table, required table settings and privileges must be available.
• Always understand the RPO and RTO requirements before selecting a recovery method.

---

6. Check UNDO Configuration

A DBA can check the current undo configuration using:

SHOW PARAMETER undo;

Or:

SELECT NAME, VALUE
FROM V$PARAMETER
WHERE NAME IN ('undo_management', 'undo_retention');

Check the available undo tablespaces:

SELECT TABLESPACE_NAME,
       STATUS,
       RETENTION
FROM DBA_TABLESPACES
WHERE CONTENTS = 'UNDO';

---

7. Check Flash Recovery Area

For Flashback Database, the Flash Recovery Area is an important consideration.

SHOW PARAMETER db_recovery_file_dest;

Check FRA usage:

SELECT NAME,
       SPACE_LIMIT,
       SPACE_USED,
       SPACE_RECLAIMABLE
FROM V$RECOVERY_FILE_DEST;

Monitoring FRA is especially important when using guaranteed restore points.

---

8. A Practical DBA Recovery Approach

Suppose a developer accidentally executes:

DELETE FROM emp
WHERE deptno = 10;

COMMIT;

The DBA should not immediately assume that a full database restore is required.

A practical approach would be:

1. Stop or restrict further changes if necessary.
2. Identify the exact time of the accidental DELETE.
3. Check whether the required data is available through Flashback Query.
4. Use Flashback Query to identify the deleted rows.
5. If appropriate, recover the affected rows into a temporary table.
6. Validate the data before putting it back into production.
7. Use Flashback Table if the entire table needs to be reverted and the situation allows it.
8. Use Flashback Database only when database-level recovery is appropriate.
9. If Flashback cannot recover the required data, move to backup/restore and other recovery options.

---

9. Recover Deleted Rows Using Flashback Query

One of the safest approaches for an accidental DELETE is often to first retrieve the old rows rather than immediately flashing back the whole table.

For example:

CREATE TABLE emp_deleted_backup AS
SELECT *
FROM emp
AS OF TIMESTAMP TO_TIMESTAMP(
    '2026-08-31 10:00:00',
    'YYYY-MM-DD HH24:MI:SS'
);

You can then inspect the recovered data:

SELECT *
FROM emp_deleted_backup;

After validation, the required rows can potentially be inserted back into the original table.

INSERT INTO emp
SELECT *
FROM emp_deleted_backup
WHERE deptno = 10;

COMMIT;

Always validate carefully before inserting data back into a production table. Check primary keys, foreign keys, unique constraints, triggers, dependent tables and application consistency.

---

10. Flashback Query vs Flashback Table vs Flashback Database

Feature	Recovery Level	Typical Use
Flashback Query	Query/Data	View historical data and recover specific rows
Flashback Table	Table	Revert a table to an earlier point in time
Flashback Database	Entire Database	Rewind database to an earlier point in time
Guaranteed Restore Point	Database Reference Point	Create a guaranteed point before risky operations

---

11. Important DBA Takeaway

Flashback is not a replacement for backups.

Flashback can be the right tool at the right time and can save hours or even days of recovery effort. However, a production database should always have a properly tested backup and recovery strategy.

The recovery decision should be based on:

• What was changed?
• When did the change happen?
• Is the required undo/flashback information still available?
• How much data was affected?
• Can the affected data be safely recovered?
• What are the RPO and RTO requirements?

---

Quick Oracle Flashback Command Cheat Sheet

Check Flashback Database

SELECT FLASHBACK_ON
FROM V$DATABASE;

View historical data

SELECT *
FROM emp
AS OF TIMESTAMP SYSTIMESTAMP - INTERVAL '10' MINUTE;

Enable row movement

ALTER TABLE emp ENABLE ROW MOVEMENT;

Flashback Table

FLASHBACK TABLE emp
TO TIMESTAMP SYSTIMESTAMP - INTERVAL '10' MINUTE;

Flashback Database

FLASHBACK DATABASE
TO TIMESTAMP TO_TIMESTAMP(
    '2026-08-31 10:00:00',
    'YYYY-MM-DD HH24:MI:SS'
);

Create Guaranteed Restore Point

CREATE RESTORE POINT before_batch_process
GUARANTEE FLASHBACK DATABASE;

Check Restore Points

SELECT NAME,
       TIME,
       GUARANTEE_FLASHBACK_DATABASE
FROM V$RESTORE_POINT;

Drop Restore Point

DROP RESTORE POINT before_batch_process;

---

Conclusion

When an accidental DELETE happens in production, the first reaction should not always be “restore the database from backup.”

As an Oracle DBA, first determine what happened, when it happened, and what Flashback information is still available.

For a small data-level problem, Flashback Query may be enough. For a table-level problem, Flashback Table may be appropriate. For a database-level problem, Flashback Database can be considered. And before a high-risk operation, a Guaranteed Restore Point can provide a valuable recovery safety net.

Plan Ahead. Configure Right. Recover Smart.

---

Oracle DBA Tip: Always test Flashback and backup recovery procedures in a non-production environment before relying on them during a production incident.