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

From: Herring Dave - dherri <Dave.Herring_at_acxiom.com>
Date: Tue, 14 Aug 2012 14:50:56 +0000
Message-ID: <BD475CE0B3EE894DA0CAB36CE2F7DEB4E3CFBA61_at_LITIGMBCRP02.Corp.Acxiom.net>

Fergal,

This isn't a direct answer on determining exactly which archives are needed, but I can share our experience under 10.2.0.2. We frequently ran into problems with archives getting removed until we full automated the process, making sure that the guaranteed restore point was created while the database was in mount mode, immediately after enabling flashback on the database.

From flashback to new restore point, we do:

• startup mount, flashback, open resetlogs
• thru RMAN delete all archivelogs
• startup the db, turn flashback off, then shutdown -> this clears all flashback logs
• startup mount, enable flashback, create guaranteed restore point, then open the db.

HTH. DAVID HERRING
DBA
Acxiom Corporation

EML   dave.herring_at_acxiom.com
TEL    630.944.4762
MBL   630.430.5988 

1501 Opus Pl, Downers Grove, IL 60515, USA WWW.ACXIOM.COM -----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Fergal Taheny Sent: Friday, August 10, 2012 5:47 PM
To: oracle-l-freelists
Subject: 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


***************************************************************************
The information contained in this communication is confidential, is
intended only for the use of the recipient named above, and may be legally
privileged.

If the reader of this message is not the intended recipient, you are
hereby notified that any dissemination, distribution or copying of this
communication is strictly prohibited.

If you have received this communication in error, please resend this
communication to the sender and delete the original message or any copy
of it from your computer system.

Thank You.
****************************************************************************

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

Received on Tue Aug 14 2012 - 09:50:56 CDT