Re: Which archive logs are required for flashing back to guaranteed restore point?

  • From: Martin Berger <martin.a.berger@xxxxxxxxx>
  • To: ftaheny@xxxxxxxxx
  • Date: Mon, 20 Aug 2012 07:31:19 +0200

Hi Fergal,

 Jonathan Lewis described flashback in his Book "Oracle Core -
Essential Internals for DBAs and Developers" at the End of chapter 6.
[1]

If I read right, there he writes Flashback does not contain ALL images
of any block, but only some ot the 'before images'.
the flashback logs are then used to bring the datafiles into a state
'short before' your target SCN, and afterwards archivelogs are used to
recover the DB.

I do not know how to query _which_ archivelogs are needed for a
particular flashback, I just know you need the archivelogs _before_
the target SCN.

Sorry for the not-so-qualified answer, I'm not diving into flashback
at the moment,

 Martin

[1] 
http://books.google.at/books?id=G9AJA91PL54C&lpg=PP1&pg=PA155#v=onepage&q&f=false

On Sat, Aug 11, 2012 at 12:47 AM, Fergal Taheny <ftaheny@xxxxxxxxx> wrote:
> Hi,
> Database Version: 10.2.0.4.0
> OS: Solaris 10
>
> We are using guaranteed restore points without enabling full flashback
> database logging.
>
> I'm trying to flashback a database to a guaranteed restore point. It is
> failing because it needs an archivelog which has been deleted.
> I understand there is nothing I can do about this but this has happened a
> couple of times so I would like to bullet proof way to avoid it.
>
> The archive log oracle is looking for is actually older that the restore
> point. i.e. the scn of the restore point is 18116228292 and
> the next_change# of the archivelog that oracle is looking for is
> 18116218687. I have archivelog 2635 and all the later archivelogs but I
> don't have 2634. See below:
>
>
> select name,SCN,DATABASE_INCARNATION#,SCN,TIME from v$restore_point order
> by time;
>
> NAME                 DATABASE_INCARNATION#
> SCN                                     TIME
>
> -------------------- --------------------- ----------------------
> ----------------------------------------
>
> PRE_DMLOAD_1                             4
> 18116228292             30-JUN-12 12:24:20.000000000
>
>  select SEQUENCE#,FIRST_CHANGE#,FIRST_TIME,NEXT_CHANGE#,NEXT_TIME from
> v$archived_log where SEQUENCE# between 2634 and 2638;
>
>  SEQUENCE#  FIRST_CHANGE#       FIRST_TIME         NEXT_CHANGE#
> NEXT_TIME
>
> ---------- ------------------- ------------------ -------------------
> ------------------
>
> 2634               18115806724 27-JUN-12 13:01:05         18116218687
> 30-JUN-12 12:22:25
>
> 2635               18116218687 30-JUN-12 12:22:25         18116228029
> 30-JUN-12 12:24:07
>
> 2636               18116228029 30-JUN-12 12:24:07         18116240885
> 30-JUN-12 12:40:48
>
> 2637               18116240885 30-JUN-12 12:40:48         18116288998
> 01-JUL-12 01:34:31
>
> 2638               18116288998 01-JUL-12 01:34:31         18116309343
> 01-JUL-12 01:35:15
>
>
> shutdown immediate;
>
> startup mount;
>
> flashback database to restore point PRE_DMLOAD_1;
>
> ERROR at line 1:
>
> ORA-38754: FLASHBACK DATABASE not started; required redo log is not
> available
>
> ORA-38761: redo log sequence 2634 in thread 1, incarnation 4 could not be
> accessed
>
>
>
> We  create a restore point bfore running batch processing each night and
> beforehand delete old archivelogs and restore points:
>
> drop old restore points.
>
> delete noprompt archivelog until time 'sysdate-0.5';
>
> alter system switch logfile;
>
> and then create the new restore point.
>
>
>
> This has worked fine until now. In this case archivelog sequence 2634 spans
> 3 days and so was deleted with "delete noprompt archivelog until time
> 'sysdate-0.5'". Nonetheless I would have expected that we would be ok
> having the archivelog that was current at the time the restore point was
> created and the previous archivelog. But not on this occasion.
>
> So my question is how can I determine if an archivelog will be needed for
> flashing back to a restore point before I detete it?
>
> Regards,
>
> Fergal
--
//www.freelists.org/webpage/oracle-l


Other related posts: