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

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