- 17 Aug, 2023
- 0 Comments
- 2 Mins Read
Important Flashback Commands
1. Enable flashback in database:
P.S. Database should be in archive log mode.
1 2 3 4 5 | alter system set db_recovery_file_dest_size=10G scope=both;alter system set db_recovery_file_dest='/dumparea/FRA/D2PMS3' scope=both;alter database flashback on; |
2. How to check whether flashback is enabled or not:
1 | select flashback_on from v$database; |
3. Disable flashback in database:
1 | alter database flashback off; |
4. How far can we flashback:
1 2 3 4 5 6 7 | Flashback To (Time) :select to_char(oldest_flashback_time,’dd-mon-yyyy hh24:mi:ss’) “Oldest Flashback Time”from v$flashback_database_log;Flashback To (SCN) :col oldest_flashback_scn format 99999999999999999select oldest_flashback_scn from v$flashback_database_log; |
5. Flashback a table from recyclebin:
1 2 3 4 5 6 7 8 9 10 11 | ## First we need to check whether the table name exists in recyclebin or not:SELECT object_name, original_name, createtime FROM recyclebin where original_name='EMP';## Now restore the table with same name:FLASHBACK TABLE emp TO BEFORE DROP;## Restore that table with a new name:FLASHBACK TABLE emp TO BEFORE DROPRENAME TO emp1; |
6. Fetch flashback usage info:
1 | SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE; |
7. Flashback database to particular SCN or timestamp:
1 2 3 4 5 6 7 8 9 10 | shutdown immediate;startup mount;FLASHBACK DATABASE TO SCN 202381; ## this is for particular scnFLASHBACK DATABASE TO TIMESTAMP (SYSDATE-1/24); ## This is for flashback to last one hourFLASHBACK DATABASE TO TIMESTAMP to_timestamp('2023-08-17 16:00:00', 'YYYY-MM-DD HH24:MI:SS'); ## This is to specific timestamp.alter database open resetlogs; |
8. Flashback query as of timestamp:
1 2 3 4 5 | SELECT * FROM PDBUSER.EMP AS OF TIMESTAMPTO_TIMESTAMP('2023-08-17 10:00:00', 'YYYY-MM-DD HH:MI:SS');SELECT * FROM PDBUSER.EMP AS OF TIMESTAMP SYSDATE -1/24; |
9. Flashback database to restore point:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | Follow the steps for flashback database to a guaranteed restore point;1. Fetch the restore point name:SQL> select NAME,time from v$restore_point;NAME TIME-------------------------------- -----------------------------------------------FLASHBACK_PRIM 17-AUG-23 03.41.33.000000000 PM2. Shutdown database and start db in Mount stage:shutdown immediate;startup mount;3. flashback db to restore point:flashback database to restore point FLASHBACK_PRIM;4. Open database with resetlog:alter database open resetlogs; |
10. Drop restore point:
1 | drop restore point FLASHBACK_PRIM; |
11. Create flashback restore point :
1 | create restore point FLASHBACK_PRIM guarantee flashback database; |
12. Find the list of restore points:
1 2 3 4 5 | ## From SQL prompt:SQL>Select * from v$restore_points:## From RMAN prompt:RMAN>LIST RESTORE POINT ALL; |