FLASHBACK DATABASE TO SCN

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 –

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

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 –

Shutdown immediate;

Startup mount ;

Alter database open resetlogs;