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…..

4 comments:

  1. Thanks for sharing! What's the best way to script or perform your "few log switches" before setting the restore point?

    ReplyDelete
  2. Hi Jason,
    Sorry for the delay been on a long holiday to Norway and Iceland and your comment also appeared as spam for some reason - no idea why.

    We have just been manually running 'alter system switch logfile' a few times after the flashback. This shouldn't be necessary at all and i suspect there is some sort of bug involved here but that seemed to fix it for us.

    You could automate it i guess by having a database level event trigger that did some logfile switches but thats probably over engineering (for us at least manual running is ok).

    Regards,
    Rich

    ReplyDelete
  3. Hi Rich,
    Thanks for your sharing.
    I have a complex question.
    1. I turn on the flashback database . create 2 guarantee restore points. Do I need to keep archive logs to let me flashback either 2 points freedom.
    2.I turn off the flashback database. How about the upon question?
    Thanks!

    ReplyDelete
  4. Hi Illidan,
    Answers below:

    1. If flashback database is on then you need the archive logs
    2. If flashback database is off then the archive logs are not needed

    Point 2 was quite a surprise to me!

    Regards,
    Rich

    ReplyDelete