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'
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
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
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 126.96.36.199.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
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 A
RCHIVE 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.