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

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


--
http://www.freelists.org/webpage/oracle-l

Other related posts: