Important Flashback Commands
1. Enable flashback in database:
P.S. Database should be in archive log mode.
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:
select flashback_on from v$database;
3. Disable flashback in database:
alter database flashback off;
4. How far can we flashback:
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 99999999999999999 select oldest_flashback_scn from v$flashback_database_log;
5. Flashback a table from recyclebin:
## 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 DROP RENAME TO emp1;
6. Fetch flashback usage info:
SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;
7. Flashback database to particular SCN or timestamp:
shutdown immediate;
startup mount;
FLASHBACK DATABASE TO SCN 202381; ## this is for particular scn
FLASHBACK DATABASE TO TIMESTAMP (SYSDATE-1/24); ## This is for flashback to last one hour
FLASHBACK 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:
SELECT * FROM PDBUSER.EMP AS OF TIMESTAMP
TO_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:
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 PM 2. 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:
drop restore point FLASHBACK_PRIM;
11. Create flashback restore point :
create restore point FLASHBACK_PRIM guarantee flashback database;
12. Find the list of restore points:
## From SQL prompt: SQL>Select * from v$restore_points: ## From RMAN prompt: RMAN>LIST RESTORE POINT ALL;
