ORA-19809: limit exceeded for recovery files ORA-19804: cannot reclaim 35536384 bytes disk space from 4781506560 limit

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.