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

Database Crashed With ORA-19815 ORA-19809 ORA-16038 [ID 829254.1]

Applies to:
Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 11.2.0.2.0 - Release: 10.2 to 11.2
Information in this document applies to any platform.

Symptoms
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

Cause
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 )

 

Solution
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           176If 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

a ) Increase the FRA size
b) Take backup backup of the archivelogs to different location
c) If tape backup is a option then take backup of FRA to tape
d) Change archivelogs destination out of FRA
d) 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;

+ If this is an 11.2 database, due to an unpublished bug# 6964464, stuck archiver causes the instance to crash which is not the normal bahavior. Normally, the instance would hang until the stuck archiver is freed from errors.

References
NOTE:829755.1 - Space issue in Flash Recovery Area( FRA )