New archivelog Files are Deleted before Applied During the Restore of Standby Database

Following one of blogs last year about restoring/recovering archivelog files to a standby database manually, I run into another interesting issue. At that time, I didn’t have time to write a blog about the issue. Right now I finally can write down what I found last year.

After a few days’ restore of archivelog files, our recover process began running into issue. Our process was to restore all archivelog files first for a certain date, then perform the recovery for the same date. So the recovery should run through until the last recover point identified by my previous blog . Interesting, the recovery stop in the middle of process and complaint about archivelog files not found. At first, I was thinking maybe I run the restore for the wrong sequence range. Then performed the restore of archive logfile again. The recovery process passed that sequence, but quickly stop at another archivelog. At this moment, I know something was not right. Of course, it is not related to wine shown below.

file_missing

Could be archivelog file deleted from RECO due to the FRA space constraint? But it did not make sense for the deletion of archive log even before the archivelogs were applied. I checked the alert logfile and found the something interesting. The alert log shows log seqence 615578 thread 5 was deleted at 09:34.

Sun Sep 07 09:34:50 2014
Deleted Oracle managed file +RECO/userdb/archivelog/2014_09_07/thread_5_seq_615578.22013.857628717
Sun Sep 07 09:34:50 2014
Deleted Oracle managed file +RECO/userdb/archivelog/2014_09_07/thread_5_seq_615577.22019.857628719
Deleted Oracle managed file +RECO/userdb/archivelog/2014_09_07/thread_5_seq_615568.22021.857628661

The recover log shows we just recover to 614809 thread 5 at 20:19. So for the log sequence 615578, 615577, and 615568 for thread 5 were deleted long before they can be applied.

ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT …
ALTER DATABASE RECOVER CONTINUE DEFAULT
Media Recovery Log +RECO/userdb/archivelog/2014_09_07/thread_6_seq_491327.21626.857651973
ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT …
ALTER DATABASE RECOVER CONTINUE DEFAULT
Media Recovery Log +RECO/userdb/archivelog/2014_09_07/thread_5_seq_614809.9496.857655203
Sun Sep 07 20:19:51 2014
ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT …
ALTER DATABASE RECOVER CONTINUE DEFAULT

After did some research, we found an Oracle note (Doc ID: Bug 17370174 : NEW ARCHIVELOG FILES ARE DELETED FIRST FROM THE FRA). Here is part of the description from the note:

DETAILED PROBLEM DESCRIPTION
============================
The algorithm used by the FRA to delete the files is deleting the more recent archivelog files created when there are old archivelogs in the FRA eligible to delete first.

TECHNICAL IMPACT
================
The backup of the archivelog files is failing because the archivelog file was deleted.

WORKAROUND INFORMATION
======================
The workaround recommended is add another condition to the archivelog policy in order to prevent that the archivelog files are deleted if there is not backup taken.

The Solutions
Instead of messing around our standard archivelog policy in RMAN, we identified the following approaches to get around the issue.

Approach 1:
Increase db_recovery_file_dest_size parameter from 10000G to 12800G. This approach can resolve the issue immediately for the current date. But will run into the same issue a few days later when FRA is reaching to full again.

Approach 2:
Manually remove completed applied archive logfiles using the following command:
delete archivelog all completed before ‘sysdate – n’;

Approach 3:
Add DELETE ARCHIVELOG MAXSIZE in the recover command.

RMAN > recover database until sequence 343848 thread 2 delete archivelog maxsize 1000g;

So the above command will recover to a latest consistent state and while recovery as when 1TB of archives are applied, the applied archives will bedeleted thus making space in Flash recovery area.

DELETE ARCHIVELOG causes RMAN to delete restored log files after they have been applied to the datafiles, to save disk space.

MAXSIZE 1000g limits space occupied by restored logs at any given moment to 1000GB

The other feasible options to deletion policy for archivelogs can be the followings:
Configure deletion policy to none. Archivelogs don’t get deleted unless we manually delete them.
RMAN > configure archivelog deletion policy to none;

Configure deletion policy to backed up 1 times to disk: Archivelogs will not get deleted unless they are backed up.
RMAN > configure archivelog deletion policy to backed up 1 times to disk;

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s