Mistakes happen in database management, whether due to human error, application issues, or misconfigurations. Oracle's Flashback Database feature allows you to quickly restore your database to a previous state without needing a full restore and recovery process.
Flashback Database can be done using:
SCN (System Change Number) – A unique identifier for each database change.
Timestamp – A specific point in time for recovery.
This guide will walk you through the process of using Flashback Database with SCN and Timestamp.
Prerequisites for Flashback Database
Before using Flashback Database, ensure that:
The database is in ARCHIVELOG mode.
Flashback Database is enabled.
Sufficient Fast Recovery Area (FRA) is configured.
Steps to Perform Flashback Database
1. Connect as SYSDBA and Check Flashback Status
Connect to the database and verify if Flashback is enabled.
CONNECT / AS SYSDBA;
SELECT flashback_on FROM v$database;
If the result is NO, proceed to enable it.
2. Enable Flashback Database
Shut down and restart the database in MOUNT mode, then enable Flashback.
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE FLASHBACK ON;
ALTER DATABASE OPEN;
Flashback is now active.
3. Check Current SCN and Timestamp
Before making changes, note the SCN and timestamp.
SELECT CURRENT_SCN FROM V$DATABASE;
SELECT SYSTIMESTAMP FROM DUAL;
4. Connect as Application User and Verify Data
Switch to the application user and check existing data.
CONNECT FLASHBACK_USER/oracle123;
SELECT * FROM CARTOONS;
5. Perform Accidental Changes
Delete a row and commit the changes.
DELETE FROM CARTOONS WHERE S_NO = 1;
COMMIT;
SELECT * FROM CARTOONS;
The row is now deleted.
6. Shutdown and Start Database in MOUNT Mode
To perform Flashback, restart the database in MOUNT mode.
CONNECT / AS SYSDBA;
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
7. Perform Flashback Database Using SCN
Restore the database to a previous SCN.
FLASHBACK DATABASE TO SCN 2730038;
8. Open Database with RESETLOGS
After Flashback, open the database using RESETLOGS.
ALTER DATABASE OPEN RESETLOGS;
9. Verify Data After Flashback
Reconnect as the application user and check if the deleted data is restored.
CONNECT FLASHBACK_USER/oracle123;
SELECT * FROM CARTOONS;
(Optional) Perform Flashback Database Using Timestamp
A. Drop the Table
DROP TABLE CARTOONS;
SELECT * FROM CARTOONS;
The table is now dropped.
B. Restart Database in MOUNT Mode
CONNECT / AS SYSDBA;
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
C. Perform Flashback Using Timestamp
FLASHBACK DATABASE TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '10' MINUTE);
The database is restored to its state 10 minutes earlier.
D. Open Database with RESETLOGS
ALTER DATABASE OPEN RESETLOGS;
E. Verify Restored Data
CONNECT FLASHBACK_USER/oracle123;
SELECT * FROM CARTOONS;
The dropped table is now restored.
(Optional) Disable Flashback
If you no longer need Flashback Database, you can disable it.
CONNECT / AS SYSDBA;
ALTER DATABASE FLASHBACK OFF;
Conclusion
Oracle Flashback Database is a useful feature for quickly recovering from accidental deletions, incorrect updates, or schema changes without needing a full restore.
Key Takeaways:
Use SCN for precise recovery.
Use Timestamp if SCN is unavailable.
Flashback Database requires ARCHIVELOG mode.
RESETLOGS is needed after performing Flashback Database.
This feature is highly valuable for minimizing downtime and ensuring data integrity.
Commentaires