Saturday, September 5, 2020

ORA-19809: limit exceeded for recover files

 I faced ORA-19809 when backup of one of my databases failed during archived log backup phase. In our backup script we had a statement “ALTER SYSTEM ARCHIVE LOG CURRENT” just before the backup command for archived redo logs. When ALTER SYSTEM ARCHIVE LOG CURRENT statement was executed, following error stack was returned.

sql statement: alter system archive log current released channel: dsk1 released channel: dsk2
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ==============
RMAN-00571: ===========================================================
RMAN-03009: failure of sql command on default channel at 02/09/2018 02:36:10
RMAN-11003: failure during parse/execution of SQL statement: alter system archive log current
ORA-16038: log 9 sequence# 52279 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 9 thread 1: '/oradata/mydb/redolog07.log'
 

Recovery Manager complete.

 

Meanwhile we also saw following errors/warning appearing in the alert log file

ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance mcrspos - Archival Error
ORA-16038: log 9 sequence# 52279 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 9 thread 1: '/oradata/mydb/redolog07.log'
Fri Feb 09 07:56:27 2018
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance mydb - Archival Error
ORA-16014: log 7 sequence# 52279 not archived, no available destinations
ORA-00312: online log 9 thread 1: '/oradata/mydb/redolog07.log'
Fri Feb 09 07:56:57 2018
Errors in file /u01/app/oracle/diag/rdbms/mydb/mydb/trace/mydb_m005_5103.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 59055800320 bytes is 99.97% used, and has 20135936 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.

After seeing above, it can easily be judged that problem was with fast recovery area. If fast recover area is full, no more archived logs could be created and ORA-19809 is evident.

To solve this problem the suggestions appearing in alert log file can be follows. Either free up the fast recovery area by deleting the files in the FRA, or space in the FRA can be increased. A soft increase in the FRA space is to rest DB_RECOVERY_FILE_DEST_SIZE to a value closer to the actual size of the FRA file system, thus allowing more files to be created under FRA.

If archive generation has increased over time, or flashback logs are there in the FRA for a guaranteed restore point that you cannot drop, you might consider adding more space to the FRA file system.

No comments:

Post a Comment

Popular Posts - All Times