Failure Of Backup Due To Limit Exceeded For Recovery Files Since Cannot Reclaim Disk Space [RMAN-03009,ORA-19809,ORA-19804]

Question

While I try to take incremental backup level 0 by using the command

RMAN> backup incremental level 0 database plus archivelog;

, encountered this error.

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================

RMAN-03009: failure of backup command on ORA_DISK_1 channel at 08/27/2013 15:58:
05
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 52428800 bytes disk space from 4327473152 limit

When I went through some forums, I found a lot of solutions, but when I checked with their situations, it doesn't match.

• One solution was to increase the db_recovery space. So I checked the space and got this result.

db_recovery size

 SQL> show parameter db_recovery;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/oracle/fast_recovery_
                                                 area
db_recovery_file_dest_size           big integer 4127M

Space Usage

SQL> select SPACE_USED,SPACE_LIMIT from  v$recovery_file_dest;

SPACE_USED SPACE_LIMIT
---------- -----------
 800155136  4327473152

Available Space

 SQL>SELECT
NAME,
TO_CHAR(SPACE_LIMIT, '999,999,999,999') AS SPACE_LIMIT,
TO_CHAR(SPACE_LIMIT - SPACE_USED + SPACE_RECLAIMABLE,
'999,999,999,999') AS SPACE_AVAILABLE,
ROUND((SPACE_USED - SPACE_RECLAIMABLE)/SPACE_LIMIT * 100, 1)
AS PERCENT_FULL
FROM V$RECOVERY_FILE_DEST;

NAME
--------------------------------------------------------------------------------
SPACE_LIMIT      SPACE_AVAILABLE  PERCENT_FULL
---------------- ---------------- ------------
/u01/app/oracle/fast_recovery_area
   4,327,473,152    3,488,460,800         19.4

From RMAN

RMAN> show all;

RMAN configuration parameters for database with db_unique_name AGGE are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_agge.f'; # default

What should I do to resolve this problem? Do I have to increase the recovery space even after I am having this much space?

Using ORACLE 11G R2 in RHEL5

Answer 1

The db_recovery_file_dest includes a variety of files, including online AND archived redo log files; take a look at the Oracle Backup and Recovery User's Guide chapter 5 to find out the best solution; disabling db_recovery_file_dest is not a good idea. You can try

RMAN> report obsolete;

RMAN command to see what can be deleted. Then do a delete obsolete to delete them. Do you have any guaranteed restore points defined?

RMAN> list restore point all;

If you see any results with type GUARANTEED, if they are no longer needed, remove them:

SQL> DROP RESTORE POINT before_app_upgrade;

Is flashback database enabled?

Answer 2

I was facing the exact same issue. I tried the options mentioned above as well, to no avail. Finally, as suggested by a friend, disabling the db_recovery_file_dest solved the issue for me.

SQL> alter system set db_recovery_file_dest='' scope=both;