The Geek Diary

Database Crashed With ORA-19815, ORA-19809, ORA-16038

by

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

  1. Database Hangs
  2. Users not able to connect to database
  3. Not able to open the database
  4. 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;

You May Also Like