Flashback database ORA-38754

In one of our test environments where we (over)use flashback database we had an issue when trying to flashback using sqlplus:

SYS@EDERBLD>flashback database to restore point V_2_1_0_done;

flashback database to restore point V_2_1_0_done

*

ERROR at line 1:

ORA-38754: FLASHBACK DATABASE not started; required redo log is not available

ORA-38762: redo logs needed for SCN 8822163425690 to SCN 8822163429839

ORA-38761: redo log sequence 1 in thread 1, incarnation 265 could not be

Accessed

We’ve seen this before and it seems that sometimes sqlplus gets confused, usually when this happens rman seems to be ‘cleverer’ and will restore any missing logs (that the FRA has helpfully autodeleted – and this is a feature not a bug – the docs will be updated for 12 to reflect that). However this time rman did not succeed either and hot a similar type error

RMAN> flashback database to restore point V_2_1_0_done;

Starting flashback at 27-MAY-13

using channel ORA_SBT_TAPE_1

using channel ORA_DISK_1

flashback database to restore point V_2_1_0_done;

starting media recovery

archived log for thread 1 with sequence 1 is already on disk as file /oracle/EDER/recovery_area/EDERBLD/archivelog/2013_05_27/o1_mf_1_1_8t6b5wyz_.arc

archived log for thread 1 with sequence 3 is already on disk as file /oracle/EDER/recovery_area/EDERBLD/archivelog/2013_05_27/o1_mf_1_3_8t6dxy0f_.arc

archived log for thread 1 with sequence 4 is already on disk as file /oracle/EDER/recovery_area/EDERBLD/archivelog/2013_05_27/o1_mf_1_4_8t6kfnj3_.arc

archived log for thread 1 with sequence 5 is already on disk as file /oracle/EDER/recovery_area/EDERBLD/archivelog/2013_05_27/o1_mf_1_5_8t6kfnh8_.arc

unable to find archived log

archived log thread=1 sequence=1

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of flashback command at 05/27/2013 14:20:48

RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 1 and starting SCN of 8822163425538

RMAN>

This was a little confusing but it seemed even rman was confused by the number of incarnations all with similar sequence numbers. To fix this issue I then ran

RMAN> reset database to incarnation 265;

database reset to incarnation 265

RMAN> restore archivelog from logseq=1 until logseq=1;

Starting restore at 27-MAY-13

allocated channel: ORA_SBT_TAPE_1

channel ORA_SBT_TAPE_1: SID=573 device type=SBT_TAPE

channel ORA_SBT_TAPE_1: Data Protection for Oracle: version 5.4.1.0

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=1 device type=DISK

channel ORA_SBT_TAPE_1: starting archived log restore to default destination

channel ORA_SBT_TAPE_1: restoring archived log

archived log thread=1 sequence=1

channel ORA_SBT_TAPE_1: reading from backup piece redolog_EDERBLD_816523018_20130527_2380_1.rman

channel ORA_SBT_TAPE_1: piece handle=redolog_EDERBLD_816523018_20130527_2380_1.rman tag=TAG20130527T115614

channel ORA_SBT_TAPE_1: restored backup piece 1

channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:01:55

Finished restore at 27-MAY-13

RMAN>

This restored the missing file and then the rman version of flashback then worked in a fresh session (sqlplus did not). We’ve since made usre that we do a few log switches before creating a restore point as before we were doing an open resetlogs followed almost immediately by a guaranteed restore point. Since we did that everything seems much happier and the problem seems to have gone away.

The main tip though is to use rman to flashback it’s much more robust than sqlplus…..