Database Flashback Commands
Share 
A collection of useful Oracle database Flashback commands for managing and restoring your database to previous points in time. This guide covers tasks such as enabling and disabling Flashback, creating and dropping restore points, and performing Flashback queries to recover specific data. Whether you’re troubleshooting or need to revert changes, these commands will help you leverage the Flashback feature for quick and efficient data recovery. Learn how to use Flashback to undo changes, recover from mistakes, and maintain data integrity.
| Task | Command |
|---|---|
| 1. Check if Flashback is Enabled | SELECT flashback_on FROM v$database; |
| 2. Enable Flashback in Database | Ensure database is in ARCHIVELOG mode:ALTER SYSTEM SET db_recovery_file_dest_size=10G SCOPE=BOTH;alter system set db_recovery_file_dest='/u01/app/oracle/flash_recovery_area' scope=both;ALTER DATABASE FLASHBACK ON; |
| 3. Disable Flashback in Database | ALTER DATABASE FLASHBACK OFF; |
| 4. Create Flashback Restore Point | CREATE RESTORE POINT FLASHBACK_PREP GUARANTEE FLASHBACK DATABASE; |
| 5. Find List of Restore Points | From SQL Prompt:SELECT * FROM v$restore_points;From RMAN Prompt: LIST RESTORE POINT ALL; |
| 6. Drop Restore Point | DROP RESTORE POINT FLASHBACK_PREP; |
| 7. Flashback Database to Restore Point | Get restore point name:SELECT NAME, time FROM v$restore_point;Shutdown and mount the database: SHUTDOWN IMMEDIATE;STARTUP MOUNT;Flashback to restore point: FLASHBACK DATABASE TO RESTORE POINT FLASHBACK_PREP;Open with resetlogs: ALTER DATABASE OPEN RESETLOGS; |
| 8. Flashback Query as of Timestamp | SELECT * FROM W3BUDDY.EMP AS OF TIMESTAMP TO_TIMESTAMP('2025-01-05 10:00:00', 'YYYY-MM-DD HH:MI:SS');SELECT * FROM W3BUDDY.EMP AS OF TIMESTAMP SYSDATE - 1/24; |
| 9. Flashback Database to SCN/Time | Shutdown and mount the database:SHUTDOWN IMMEDIATE;STARTUP MOUNT;Flashback to SCN: FLASHBACK DATABASE TO SCN 202381;Flashback to timestamp: FLASHBACK DATABASE TO TIMESTAMP (SYSDATE-1/24);Specific timestamp: FLASHBACK DATABASE TO TIMESTAMP TO_TIMESTAMP('2025-01-05 16:00:00', 'YYYY-MM-DD HH24:MI:SS');Open with resetlogs: ALTER DATABASE OPEN RESETLOGS; |
| 10. Flashback a Table from Recycle Bin | Check if table exists in recycle bin:SELECT object_name, original_name, createtime FROM recyclebin WHERE original_name='EMP';Restore table with the same name: FLASHBACK TABLE int_admin_emp TO BEFORE DROP;Restore table with a new name: FLASHBACK TABLE int_admin_emp TO BEFORE DROP RENAME TO int2_admin_emp; |
| 11. Get Flashback Area Usage Info | SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE; |
| 12. How Far Can We Flashback | By Time:SELECT TO_CHAR(oldest_flashback_time, 'DD-MON-YYYY HH24:MI:SS') AS "Oldest Flashback Time" FROM v$flashback_database_log;By SCN: SELECT oldest_flashback_scn FROM v$flashback_database_log; |
