FLASHBACK DATABASE TO SCN
Time to FLASHBACK DATABASE TO – 06:30 a.m on 31 July.
Get oldest flashback time –
SELECT OLDEST_FLASHBACK_SCN,to_char(OLDEST_FLASHBACK_TIME,'DD-MON-YYYY HH24:MI:SS') FROM V$FLASHBACK_DATABASE_LOG;
If there are logs up until 06:30 on 31 Jul, you can flashback.
Get the SCN number to flashback to –
col first_change# format 99999999999 select first_change# ,to_char(first_time,'DD-MON-YYYY HH24:MI:SS'),sequence# from V$LOG_HISTORY ORDER BY 1 ;
23856899540 31-JUL-2009 04:30:09 119695
23856965501 31-JUL-2009 06:30:10 119696
23857031498 31-JUL-2009 08:30:09 119697
23859233947 31-JUL-2009 10:00:29 119704
23859242647 31-JUL-2009 10:31:34 119705
Note down the SCN at the time you want to flashback to which is the FIRST_CHANGE# in the V$LOG_HISTORY view –
Example here the SCN is 23856965501 at 06:30 – the time you want to flashback to.
Restore a few archivelogs prior to and after the flashback time – the log sequence is in the last column above….
Ex – to restore archivelogs from 119695 to 119702
rman target / catalog rman/rman@rman
RMAN > restore archivelog from sequence 119695 until sequence 119705;
Once the logs are restored make sure no automatic RMAN backup job of archivelogs run which delete old archivelogs from disk –
Next flashback the database –
FLASHBACK DATABASE TO SCN 23856965501;
Once FLASHBACK is complete-
Verify the data before opening with a resetlogs –
ALTER DATABASE OPEN READ ONLY;
If satisfied with the data –
Startup mount ;
Alter database open resetlogs;