Loading....

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;
Please follow and like us:

Last Update: August 4, 2009  

August 4, 2009 112 Arjun Raja
Total 0 Votes:
0

Tell us how can we improve this post?

+ = Verify Human or Spambot ?

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?

Leave a Comment

Your email address will not be published. Required fields are marked *

*
*

Social media & sharing icons powered by UltimatelySocial
Back To Top

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?