The Problem
Instance terminated due to error 16038 as its not able to archive the log in FRA:
ORA-19815: WARNING: db_recovery_file_dest_size of 99614720000 bytes is 100.00% used, and has 0 remaining bytes available. Sat Mar 8 00:57:07 2008 ************************************************************************ You have following choices to free up space from flash 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. ************************************************************************ Sat Mar 8 00:57:07 2008 Errors in file /usr/oracle/admin/ORAPTCMK/bdump/oraptcmk1_arc0_623454.trc: ORA-19809: limit exceeded for recovery files ORA-19804: cannot reclaim 308281344 bytes disk space from 99614720000 limit Sat Mar 8 00:57:07 2008 ARC0: Error 19809 Creating archive log file to '+DATA' Sat Mar 8 00:57:07 2008 Errors in file /usr/oracle/admin/ORAPTCMK/udump/oraptcmk1_ora_680508.trc: ORA-16038: log 17 sequence# 34003 cannot be archived ORA-19809: limit exceeded for recovery files ORA-00312: online log 17 thread 1: '+DATA/oraptcmk/onlinelog/redolog171.log' ORA-00312: online log 17 thread 1: '+FLRC/oraptcmk/onlinelog/redolog172.log' Sat Mar 8 00:57:07 2008 USER: terminating instance due to error 16038
The Solution
Default archive log destination was set to Flash Recovery Area and FRA is 100% used. There is no space to create additional archive log. Similar situation also occur if the database is up and running and archive log’s destination for FRA is full then the database will hang.
Other similar issue because of archiving is stuck because of FRA space pressure are
- Database Hangs
- Users not able to connect to database
- Not able to open the database
- FRA space related error in the alert.log file ( ORA-19809 )
Make more space in Flash Recovery Area or change the archivelog destination to outside Flash Recovery Area. By default Archive log are created in FRA if no specific log_archive_dest_n parameter was set and Flash Recovery Area is enabled.
SQL> show parameter db_recovery_file_dest NAME VALUE ---------------------- -------------------------- db_recovery_file_dest E:\oracle\product\10.2.0\flash_recovery_area db_recovery_file_dest_size 2G
SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 174 Next log sequence to archive 176 Current log sequence 176
If you are using RMAN for the database backup then check the space distribution in FRA. For example:
SQL>select file_type, percent_space_used as used,percent_space_reclaimable as reclaimable,
number_of_files as "number" from v$flash_recovery_area_usage;
FILE_TYPE USED RECLAIMABLE number
------------ ---------- ----------- ----------
CONTROLFILE 0 0 0
ONLINELOG 0 0 0
ARCHIVELOG 89.94 0 53
BACKUPPIECE 9.51 0 11
IMAGECOPY 0 0 0
FLASHBACKLOG 0 0 0
In the Above example almost all the space are used by Archivelogs and backup pieces and there is no space to reclaim. In this type of case you can
- Increase the FRA size
- Take backup backup of the archivelogs to different location
- If tape backup is a option then take backup of FRA to tape
- Change archivelogs destination out of FRA
- Delete archivelogs to make more space. ( should be the last option) and in case of standby database make sure those logs are already applied to standby
Usually archiving is configured to FRA for automatic management of archivelog files. This works well if you are using a standby configuration or using RMAN for backups so that there is a basis for archives to get obsolete and be cleaned up automatically from FRA. If you do not want to take advantage of automatic space management in FRA, you can set any non-FRA location for the archivelogs. For example
Set an archivelog destination:
SQL> alter system set log_archive_dest_1='LOCATION=E:\oracle\product' scope=both ;
Unset the default setting for FRA:
SQL> alter system set log_archive_dest_10='' scope=both;