ORA-19815: WARNING: DB_RECOVERY_FILE_DEST_SIZE 100.00% USED

Subject:	ORA-19815: WARNING: DB_RECOVERY_FILE_DEST_SIZE 100.00% USED
	Doc ID:	Note:293418.1	Type:	PROBLEM
	Last Revision Date:	10-OCT-2005	Status:	PUBLISHED

The information in this document applies to:

Oracle Server - Enterprise Edition - Version: 10.1.0.0 to 10.2.0.0
This problem can occur on any platform.
This affects RMAN backups to the 10g Flash Recovery Area

Errors

ORA-19815 WARNING: %s of %s bytes is %s used, and has %s remaining bytes

Symptoms

ALERT LOG
-----------
ORA-19815: WARNING: db_recovery_file_dest_size of 3221225472 bytes is 100.00% used, and has 0 remaining bytes available.

Cause

You are making backups to the flash recovery area. You may be using the Oracle disk strategy or just backing up the database to the FRA. All objects written to the FRA will consume space there and to reclaim this space you must backup the FRA files. Once you backup the recovery files they are placed on an internal "file can be deleted" list. Once this is done the reclaimable space increases and Oracle will manage the FRA automatically.
You can monitor the space in the flash_recovery_area using the provided query. The main column is the reclaimable column. If it is 0 then Oralce will not cleanup any files in the db_recovery_file_dest. Where if you backup the recovery area to tape as shown above the reclaimable space is > 0 and the files that were backed up are placed on a file can be deleted list because there is a tape backup of the files. If Oracle needs space after that it will remove the oldest can be delete file available in the FRA. It will remove old archivelogs that have been backed up then backupsets.
The only way to increase the reclaimable column is to have RMAN backup the flash recovery area. Once backed up the files in the recovery area are placed on a files that can be deleted list and Oracle will manage the flash recovery area space.

Fix

SELECT substr(name, 1, 30) name, space_limit AS quota,
       space_used        AS used,
        space_reclaimable AS reclaimable,
        number_of_files   AS files
  FROM  v$recovery_file_dest ;
NAME                                QUOTA       USED RECLAIMABLE      FILES
------------------------------ ---------- ---------- ----------- ----------
/n01/10G/FRAB                  4294967296 2647365120           0        105

# backup the flash recovery area to tape
RMAN> backup recovery area;

SELECT substr(name, 1, 30) name, space_limit AS quota,
       space_used        AS used,
        space_reclaimable AS reclaimable,
        number_of_files   AS files
  FROM  v$recovery_file_dest ;
NAME                                QUOTA       USED RECLAIMABLE      FILES
------------------------------ ---------- ---------- ----------- ----------
/n01/10G/FRAB                  4294967296 2647365120  2254337536        105

References

Note 20978.1 OERR: ORA-9815 File name buffer overflow