The Issue

One of my colleagues tried to flashback a test Oracle database after purging data over the weekend.  Even though there were flashback logs and archive logs covering the period between the current time and the restore point, RMAN gave the following errors:

RMAN> flashback database to restore point TEST_DELETE_20110726;


RMAN-03002: failure of flashback command at 08/01/2011 10:54:21
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of log thread 1 seq 2798 lowscn 42915579894 found to restore

The restore point was created at 17:57 (SCN 42915583024), and there were 14 archive logs retained on disk before the restore point was created.  (The other archive logs were deleted to make sure that the data purge would not fill the FRA filesystem over the weekend).

 THREAD#            SEQUENCE#        FIRST_CHANGE# FIRST_TIME                   NEXT_CHANGE#
 ------- -------------------- -------------------- -------------------- --------------------
       1                 2796          42915575682 26-JUL-2011 16:38:45          42915577409
       1                 2797          42915577409 26-JUL-2011 16:45:50          42915579894
       1                 2798          42915579894 26-JUL-2011 17:15:49          42915580580        
       1                 2799          42915580580 26-JUL-2011 17:45:48          42915581157 (logs retained from here)         
       1                 2800          42915581157 26-JUL-2011 17:47:54          42915581467         
       1                 2801          42915581467 26-JUL-2011 17:50:09          42915581745         
       1                 2802          42915581745 26-JUL-2011 17:50:16          42915581884         
       1                 2803          42915581884 26-JUL-2011 17:50:23          42915581986         
       1                 2804          42915581986 26-JUL-2011 17:50:30          42915582095         
       1                 2805          42915582095 26-JUL-2011 17:50:37          42915582199         
       1                 2806          42915582199 26-JUL-2011 17:50:45          42915582308         
       1                 2807          42915582308 26-JUL-2011 17:50:51          42915582418         
       1                 2808          42915582418 26-JUL-2011 17:50:58          42915582517         
       1                 2809          42915582517 26-JUL-2011 17:51:04          42915582626         
       1                 2810          42915582626 26-JUL-2011 17:51:10          42915582731         
       1                 2811          42915582731 26-JUL-2011 17:51:22          42915582834         
       1                 2812          42915582834 26-JUL-2011 17:51:30          42915591023 (restore point created)        
       1                 2813          42915591023 26-JUL-2011 17:59:14          42915608529

Why was the database flashback asking for such an old archivelog?

The Explanation

When flashback database is enabled, old versions of blocks are written to the flashback logs if they are changed.  During a flashback of the database, the old versions of blocks are copied back into the datafiles.  The most recent version before the restore point is used and then made consistent using the archived logs.

However, for performance reasons, a block isn’t copied to the flashback logs every time it is changed.  The parameter _flashback_barrier_interval controls the minimum time between copies of the same block being written to the flashback logs.  The parameter _flashback_barrier_interval defaults to 1800 seconds.

This explains why archivelogs are required up to 30min before a restore point when attempting to flashback the database.  This requirement is useful to know for managing test databases, but it isn’t documented precisely.

This restriction doesn’t apply when guaranteed restore points are used.  Immediately after a guaranteed restore point has been created, all changed blocks are written to the flashback logs.

The behaviour described above can be easily proved by monitoring the flashback log writes statistic of V$SYSSTAT during a simple test of updating a single block multiple times, with and without a guaranteed restore point.


Today I saw a case where the first archive log needed to flashback to a guaranteed restore point was removed from the FRA by Oracle when it needed space.

RMAN knew it should not remove the required archive log after backing it up:

channel c2: deleting archived log(s)
RMAN-08139: WARNING: archived redo log not deleted, needed for guaranteed restore point
archived log file name=/fra01/PLAYDB/archivelog/2012_07_26/o1_mf_1_542_81213d84_.arc thread=1 sequence=542

Subsequent backups left the archive log alone:

Backup_DB_PLAYDB_ARCH_20120726104717.log:skipping archived log file /fra01/PLAYDB/archivelog/2012_07_26/o1_mf_1_542_81213d84_.arc; already backed up 1 time(s)
Backup_DB_PLAYDB_ARCH_20120726104717.log:archived log file name=/fra01/PLAYDB/archivelog/2012_07_26/o1_mf_1_542_81213d84_.arc thread=1 sequence=542
Backup_DB_PLAYDB_ARCH_20120726225311.log:skipping archived log file /fra01/PLAYDB/archivelog/2012_07_26/o1_mf_1_542_81213d84_.arc; already backed up 1 time(s)
Backup_DB_PLAYDB_ARCH_20120726225311.log:archived log file name=/fra01/PLAYDB/archivelog/2012_07_26/o1_mf_1_542_81213d84_.arc thread=1 sequence=542

Later, when the FRA was under space pressure, the first archive log since the restore point was removed!

Fri Jul 27 00:09:59 2012
Deleted Oracle managed file /fra01/PLAYDB/archivelog/2012_07_26/o1_mf_1_542_81213d84_.arc

When we tried to flashback to the guaranteed restore point, it failed because that archive log was missing.

Tue Jul 31 13:21:25 2012
flashback database to restore point GRP1
ORA-38754 signalled during: flashback database to restore point GRP1...

Executing the flashback database command in RMAN instead of SQL*Plus would have triggered an automatic restore of the deleted archive log.

This situation is covered in bug 14312292: “ORACLE DELETING ARCHIVELOG NEEDED FOR GUARANTEED RESTORE POINT, ORA-38754”
For us, it only affects the archive of the redo log that was current at the time of the guaranteed restore point creation.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s