ORA-19809: limit exceeded for recovery files, ORA-19804: cannot reclaim, ORA-16038: log sequence# cannot be archived

DBA should use this for Oracle 10G, 11G database and flash recovery (recovery destination) areas only. Database has stopped working and showing when login"ORA-00257: archiver error. Connect internal only, until freed."

Following is the error message from alert log file.
    Errors in file /test01/diag/rdbms/test 01/test 01/trace/ test 01_ora_12966.trc:
    ORA-19809: limit exceeded for recovery files
    ORA-19804: cannot reclaim 840186368 bytes disk space from 96636764160 limit
    ARCH: Error 19809 Creating archive log file to '/test 01/archive/test 01/archivelog/2011_06_10  /o1_mf_1_26619_%u_.arc'
    Errors in file / test01/diag/rdbms/ test01/ test01/trace/ test01_ora_12966.trc:
    ORA-16038: log 6 sequence# 26619 cannot be archived
    ORA-19809: limit exceeded for recovery files
    ORA-00312: online log 6 thread 1: '/ test01/oradata/ test01/redo06.log'
    USER (ospid: 12966): terminating the instance due to error 16038
    Instance terminated by USER, pid = 12966

Cause:

When use db_recovery_file_dest for archive log destination and the size given to it is full. Check both parameters using following command.

SQL> show parameter db_recovery_file_dest
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /test01/archive
db_recovery_file_dest_size           big integer 91G

Here 91G is allocated to db_recovery_file_dest which is at "/test01/archive" location. If this location is full up to 91G, it will start giving above errors.

Solution:

There are two ways for Database Administrator to solve this Problem.

Resize DB_Recovery_File_Dest_Size:

Increase the parameter db_recovery_file_dest_size, which is dynamic.

SQL> alter system set db_recovery_file_dest_size=XG; (larger amount)

This is only possible if you have enough free space on disk to increase the size of db_recovery_file_dest_size. Database Administrator has to check space on file system and then resize db_recovery_file_dest_size parameter accordingly. If Resize is not the option DBA can also Change Location of db_recovery_file_dest.

If Disk space doesn't have enough free space and resize is not an option then DBA has to Delete expired archive log files.

Delete Expired Archive log Files:

Delete unwanted archive log files and update rman repository. Delete old archive log files from db_recovery_file_dest using (rm/del) command on UNIX prompt.

Update RMAN Repository. Use following steps:
#[dba01] /home/oracle> export ORACLE_SID=test01
#[dba01] /home/oracle> rman target sys/sys

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Jun 10 06:23:22 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: TEST01 (DBID=2546571239)

RMAN> crosscheck archivelog all;

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=698 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=1030 device type=DISK
validation succeeded for archived log
archived log file name=/test01/archive/TEST01/archivelog/2011_06_10/o1_mf_1_26652_6z3s0r5o_.arc RECID=26789 STAMP=753427802
validation succeeded for archived log
archived log file name=/test01/archive/TEST01/archivelog/2011_06_10/o1_mf_1_26653_6z3t6b5x_.arc RECID=26790 STAMP=753429002
validation succeeded for archived log
RMAN> delete expired archivelog all;
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=699 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=689 device type=DISK
List of Archived Log Copies for database with db_unique_name TEST01
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
26780 1 26650 X 10-JUN-11
Name: /test01/archive/TEST01/archivelog/2011_06_10/o1_mf_1_26650_6z3q5vmm_.arc
26781 1 26650 X 10-JUN-11
Name: /test01/archive/TEST01/archivelog/2011_06_10/o1_mf_1_26650_6z3q5vmm_.arc
26782 1 26650 X 10-JUN-11
Name: /test01/archive/TEST01/archivelog/2011_06_10/o1_mf_1_26650_6z3q5vmm_.arc
26786 1 26650 X 10-JUN-11
Name: /test01/archive/TEST01/archivelog/2011_06_10/o1_mf_1_26650_6z3q5vmm_.arc
26784 1 26650 X 10-JUN-11
Name: /test01/archive/TEST01/archivelog/2011_06_10/o1_mf_1_26650_6z3q5vmm_.arc
26785 1 26650 X 10-JUN-11
Name: /test01/archive/TEST01/archivelog/2011_06_10/o1_mf_1_26650_6z3q5vmm_.arc
26783 1 26650 X 10-JUN-11
Name: /test01/archive/TEST01/archivelog/2011_06_10/o1_mf_1_26650_6z3q5vmm_.arc

Do you really want to delete the above objects (enter YES or NO)? yes

/test01/archive/TEST01/archivelog/2011_06_10/o1_mf_1_26650_6z3q5vmm_.arc deleted.
-------------
------------
-------------
/test01/archive/TEST01/archivelog/2011_06_10/o1_mf_1_26650_6z3q5vmm_.arc deleted.

Now use below command in database to check for solution:

SQL> alter system switch logfile;
System altered.

If DBA is successfully able to switch log file, this means enough space is generated at db_recovery_file_dest_size location. 

Please share your views about this article.

1 comment: