DB version:11.2
Platform : Solaris 10
We have a DB on 2-node RAC for a finance related application which is fairly busy during peak time (8AM to 11pm). After 11pm it will still have transactions critical to business but less.
Level 0 backups on Monday early morning 1am (Takes about 5 hours to complete with parallelism)
For remaining days we have Level 1 Cumulative incremental backup (Takes about 2 hours to complete)
We have a monitoring system which will raise tickets and send SMS within 5 to 10 minutes once the DB goes down. But the application guys are not happy with a time based recovery. I can't blame them.
Lets say we get get SMS alerts/tickets at 4AM in the morning that DB went down because someone deleted all datafiles by mistake(but I archive redo logs and online redo logs). This means that the DB was fine at least until 3:50 . So, I can do a time based restore and recovery like
run
{
set until time "to_date('05-08-2012 03:50:00','DD-MM-YYY HH24:MI:SS')";
restore database;
recover database;
}
But our business need is to recover DB to the last succesfully commited transaction. Can this be achieved? If not, what is the closest I can get ? Can I somehow get the 'closest' SCN so that I can recover using
run
{
set until SCN <SCN number> ;
restore database;
recover database;
}
Just reiterating my scenario : All datafiles gone, but i have all online redo logs, archive redo logs, Level1 and Level 0 backup.