Data Guard Standby Archive Destination Full

Problem:

You notice the following error in the alert.log of the Data Guard Standby database. Your Standby is configured to write its archive logs to the Fast Recovery Area.

Errors in file /u01/app/oracle/diag/rdbms/standby/standby/trace/standby_arc0_8253.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 11646 bytes is 100.00% used, and has 0 remaining bytes available.
************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
   then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
   BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
   reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
   system command was used to delete files, then use RMAN CROSSCHECK and
   DELETE EXPIRED commands.
************************************************************************
Errors in file /u01/app/oracle/diag/rdbms/standby/standby/trace/standby_arc0_8253.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 1292288 bytes disk space from 11646 limit
ARC0: Error 19809 Creating archive log file to '/u01/app/oracle/oradata/standby/arc/STANDBY/archivelog/2010_06_01/o1_mf_1_134_%u_.arc'

Solution:

You have several of options in resolving this issue as suggested in the error message. Below we will detail the options available along with providing recommendations.

Changing the RMAN RETENTION POLICY or Backup using BACKUP RECOVERY AREA

The RMAN RETENTION POLICY is used to determine how long to keep backups on the file system and records of the backup. If this value is changed to lower number backups can be marked as available for space reclamation. I would recommend that you do not make this change lightly. Changing the retention policy should be done with input of the owner of the data as it could adversely impact the recovery time.

A better option would be to backup the files in Fast Recovery Area using the BACKUP RECOVERY AREA command. When files are backed up using this method they are marked as available for space reclamation.

Recommendation- Changing the RETENTION POLICY should be a discussion with the owner of the data and not used as a short term fix for a full Fast Recovery Area. Backing up the Fast Recovery Area is the better of these two options but may not resolve the issue at hand if the Fast Recovery Area is subject to regular backups.

Adding space to DB_RECOVERY_FILE_DEST that can be reflected in DB_RECOVERY_FILE_DEST_SIZE

If possible you should consider adding more space to the location pointed to by DB_RECOVERY_FILE_DEST and reflect the new space in DB_RECOVERY_FILE_DEST_SIZE. Adding space to the Fast Recovery Area may not be possible for either a short term solution such as this or as long term solution. The size of the Fast Recovery Area is the topic of another good discussion to have the owner of the data. It is very probable that size originally planed for the Fast Recovery Area is now inadequate and a new size needs to be agreed upon

Recommendation- If you have the space available this could be a quick short term fix to get archive logs flowing again. However it is not the optimal solution.

Delete archive logs that are no longer needed and set ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY

Removing archive logs that are no longer needed is a quick and easy way to free up space. Using RMAN is the recommended method and removes a lot of the guess work out of which logs to remove.

In RMAN you can use the delete archivelog all completed after|before|between ‘date’; command to remove archive logs that are no longer needed. Below is an example.

[oracle@dreco ~]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Jun 2 09:56:29 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

RMAN> connect target /

connected to target database: PRODDB (DBID=459961910, not open)

RMAN> delete archivelog all completed before 'sysdate';

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=43 device type=DISK

List of Archived Log Copies for database with db_unique_name STANDBY
=====================================================================

Key     Thrd Seq     S Low Time 
------- ---- ------- - ---------
140     1    139     A 01-JUN-10
        Name: /u01/app/oracle/oradata/standby/arch/1_139_719914169.dbf

144     1    140     A 01-JUN-10
        Name: /u01/app/oracle/oradata/standby/arch/1_140_719914169.dbf

142     1    141     A 02-JUN-10
        Name: /u01/app/oracle/oradata/standby/arch/1_141_719914169.dbf

146     1    142     A 02-JUN-10
        Name: /u01/app/oracle/oradata/standby/arch/1_142_719914169.dbf

<<DELETED for clarity>>

Do you really want to delete the above objects (enter YES or NO)?  

If you want to avoid being prompted to enter YES or NO in regards to deleting the archive logs you can add noprompt to end of the command.

You can automate the deletion of archive logs that have been applied to the standby in the case of space of pressure on the Fast Recovery Area by setting the RMAN parameter ARCHIVE DELETION POLICY TO APPLIED ON STANDBY. After setting the parameter you will noticed in the alert log when logs are deleted.

Wed Jun 02 08:48:06 2010
Media Recovery Log /u01/app/flash_recovery_area/STANDBY/archivelog/2010_06_02/o1_mf_1_182_60dqhzvy_.arc
Wed Jun 02 08:48:13 2010
Deleted Oracle managed file /u01/app/flash_recovery_area/STANDBY/archivelog/2010_06_02/o1_mf_1_177_60dqh5h1_.arc
Deleted Oracle managed file /u01/app/flash_recovery_area/STANDBY/archivelog/2010_06_02/o1_mf_1_172_60dqp6s3_.arc
Wed Jun 02 08:48:14 2010

Recommendation- Setting the ARCHIVE DELETION POLICTY TO APPLIED ON STANDBY is the best solution for ongoing archive log maintenance on the Data Guard Standby. If you are currently experiencing space pressure now the removal of archive logs already applied using the RMAN command delete archivelog all completed after|before|between ‘date’; should get the logs flowing once again.

Note: Even after setting the archive deletion policy you still might encounter space pressure in Fast Recovery Area due to archive logs. This could occur if there is a sharp increase in log traffic and low available space. Sound Fast Recovery Area management practices should limit the occurrence of such issues.