Recovering Data with Flashback Table in Oracle

Have you ever accidentally deleted important records or updated the wrong values in your Oracle database? Restoring data from a backup can take a long time and may not always be the best solution. Oracle’s Flashback Table feature helps you quickly recover lost or modified data without affecting the entire database.

With Flashback Table, you can:

• Restore a table to a previous state using a System Change Number (SCN) or timestamp.

• Undo DELETE, UPDATE, and INSERT operations.

• Recover dropped tables from the Recycle Bin without restoring a full backup.

This guide will walk you through the steps to use Flashback Table effectively for data recovery.

Requirements for Using Flashback Table

Before using Flashback Table, make sure:

• The database is in ARCHIVELOG mode.

• Row movement is enabled for the table (this is required).

• There are no active foreign key constraints (unless disabled).

• The table has not been truncated (Flashback Table cannot undo truncation).

• If recovering a dropped table, it should still be in the Recycle Bin.

Steps to Use Flashback Table

1. Check Database Settings

Before proceeding, ensure your database meets the prerequisites.

Check if ARCHIVELOG mode is enabled

SELECT log_mode FROM v$database

Check UNDO tablespace availability

SELECT tablespace_name, status FROM dba_tablespaces WHERE contents = 'UNDO';

Create a new user for testing (optional):

CREATE USER FLASHBACK_USER IDENTIFIED BY oracle123;

GRANT CONNECT, RESOURCE TO FLASHBACK_USER;

ALTER USER FLASHBACK_USER QUOTA UNLIMITED ON USERS;

CONNECT FLASHBACK_USER/oracle123;

2. Create and Populate a Sample Table

Create a table and insert sample data for testing.

CREATE TABLE cartoons ( s_no NUMBER, cartoon_name VARCHAR(50) ) TABLESPACE USERS;

INSERT INTO cartoons VALUES (1, 'Tom and Jerry'); INSERT INTO cartoons VALUES (2, 'Ninja Hattori'); INSERT INTO cartoons VALUES (3, 'Pakdam Pakdai');

SELECT * FROM cartoons; COMMIT;

3. Enable Row Movement

Enable row movement to allow Flashback Table to work.

ALTER TABLE cartoons ENABLE ROW MOVEMENT;

4. Get the Current SCN or Timestamp

Before making any changes, note the current SCN or timestamp.

Get the current SCN:

SELECT current_scn FROM v$database;

Get the current timestamp:

SELECT SYSTIMESTAMP FROM dual;

5. Modify or Delete Data

Now, let's delete a record to simulate data loss.

DELETE FROM cartoons WHERE s_no = 3;

COMMIT;

6. Restore the Table Using Flashback

Restore using SCN:

FLASHBACK TABLE cartoons TO SCN 2717257;

Restore using Timestamp (alternative method):

FLASHBACK TABLE cartoons TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '5' MINUTE);

The table is now restored to its previous state before the deletion.

7. Verify the Restoration

Check if the data is recovered successfully.

SELECT * FROM cartoons;

8. Disable Row Movement (Optional)

After restoration, you may disable row movement.

ALTER TABLE cartoons DISABLE ROW MOVEMENT;

Conclusion

Oracle’s Flashback Table is a powerful tool for recovering lost or accidentally modified data without needing a full database restore.

Key Takeaways:

• Use SCN for precise recovery.

• Use a timestamp if the SCN is unknown.

• Enable Row Movement before using Flashback Table.

• Recover dropped tables from the Recycle Bin if needed.

By following these steps, you can efficiently restore lost data and minimize downtime in your Oracle database.