Recently, I discovered a cool tweak that I really should have known about before now, given the number of times I’ve performed FLASHBACK DATABASE in the past.
You are probably aware that in order to FLASHBACK DATABASE, not only do you need the Flashback logs, but the corresponding archive logs must also be accessible.
Let’s say it’s 5pm – because that’s when these requests always come in (especially on Fridays just before you start a vacation of some type) – and you need to revert the database to what state it was in at 2pm.
Let’s also say that you do hourly backups of the archive logs to tape before purging them from local disk as you don’t have an infinite amount of local storage and that these backups did complete at 3pm, 4pm and 5pm.
If your archive logs are NOT available to the database because they’ve been moved off to tape and you issue the FLASHBACK DATABASE command from inside SQL*Plus, you will see this error”
SQL> STARTUP MOUNT;
SQL> FLASHBACK DATABASE TO TIMESTAMP TO_TIMESTAMP(‘2014-JAN-27 14:00:00′,’YYYY-MON-DD HH24:MI:SS’);
ERROR at line 1:
ORA-38754: FLASHBACK DATABASE not started; required redo log is not available
ORA-38761: redo log sequence 5 in thread 1, incarnation 2 could not be accessed.
One option is to restore your archive logs via RMAN using either TIME, SCN or SEQUENCE to get back the ones you need and then re-issue the FLASHBACK DATABASE command in SQL*Plus.
For instance, using SEQUENCE:
RESTORE ARCHIVELOG FROM SEQUENCE 5 UNTIL SEQUENCE x;
Your second option – which I didn’t know about until recently – is to use RMAN to run the FLASHBACK DATABASE command.
If you have allocated your channels appropriately (i.e. to where the backups are), it will handle the recovery of the archive logs for you!
RMAN> FLASHBACK DATABASE TO TIMESTAMP TO_TIMESTAMP(‘2014-JAN-27 14:00:00′,’YYYY-MON-DD HH24:MI:SS’);
starting media recovery channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
media recovery complete, elapsed time: 00:00:12
Finished flashback at 27.JAN.2014 17:02:05
Voila. No need to restore tons of archive logs locally