Below are the collection of useful flashback related commands.
1. How to check whether flashback is enabled or not:
select flashback_on from v$database;
2. Enable flashback in database:
--- make sure database is in archivelog mode:
alter system set db_recovery_file_dest_size=10G scope=both;
alter system set db_recovery_file_dest='/dumparea/FRA/B2PMT3' 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 the list of restore points:
-- From SQL prompt:
SQL>Select * from v$restore_points:
-- From RMAN prompt:
RMAN>LIST RESTORE POINT ALL;
6. Drop restore point:
drop restore point FLASHBACK_PREP;
7. Flashback database to restore point:
--- Below are the steps for flashback database to a guaranteed restore point;
1. Get the restore point name:
SQL> select NAME,time from v$restore_point;
NAME TIME
-------------------------------- -----------------------------------------------
FLASHBACK_PREP 21-MAR-17 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_PREP;
4. Open with resetlog:
alter database open resetlogs;
8. Flashback query as of timestamp:
SELECT * FROM EXATREE.EMP AS OF TIMESTAMP
TO_TIMESTAMP('2017-01-07 10:00:00', 'YYYY-MM-DD HH:MI:SS');
SELECT * FROM EXATREE.EMP AS OF TIMESTAMP SYSDATE -1/24;
9. Flashback database to particular SCN or timestamp:
shutdown immediate;
startup mount;
--FLASHBACK DATABASE TO SCN 202381; -- Use this for particular scn
--FLASHBACK DATABASE TO TIMESTAMP (SYSDATE-1/24); - Use for flashback to last one hour
--FLASHBACK DATABASE TO TIMESTAMP to_timestamp('2018-03-11 16:00:00', 'YYYY-MM-DD HH24:MI:SS');- to specific timestamp:
alter database open resetlogs;
10. Flashback a table from recyclebin:
-- First get whether the table name exists in recyclebin or not:
SELECT object_name, original_name, createtime FROM recyclebin where original_name='EMP';
-- restore the table as same name:
FLASHBACK TABLE int_admin_emp TO BEFORE DROP;
-- Restore that table to a new name:
FLASHBACK TABLE int_admin_emp TO BEFORE DROP
RENAME TO int2_admin_emp;
11. Get flashback are usage info:
SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;
12. How far can we flashback:
--How Far Back Can We Flashback To (Time)
select to_char(oldest_flashback_time,-dd-mon-yyyy hh24:mi:ss-) -Oldest Flashback Time"
from v$flashback_database_log;
--How Far Back Can We Flashback To (SCN)
col oldest_flashback_scn format 99999999999999999999999999
select oldest_flashback_scn from v$flashback_database_log;
flashback BACKUP & RECOVERY