ORA-19815: WARNING: DB_RECOVERY_FILE_DEST_SIZE 100.00% USED

Symptoms

ALERT LOG
-----------
ORA-19815: WARNING: db_recovery_file_dest_size of 3221225472 bytes is 100.00% used, and has 0 remaining bytes available.
************************************************************************
ARC0: Error 19809 Creating archive log file to '+DBFLASH'

Errors in file D:\APP\INAM\diag\rdbms\testrac\testrac1\trace\testrac1_arc2_4752.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 4558159872 bytes is 100.00% used, and has 0 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.
************************************************************************

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 Oracle 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.

Solution

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
------------------------------ ---------- ---------- ----------- ----------
+DBFLASH                    4294967296 2647365120           0        116

# 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
------------------------------ ---------- ---------- ----------- ----------
+DBFLASH                       4558159872 4421844992  1302331392        116


You can increase the FRA size as below

ALTER SYSTEM SET db_recovery_file_dest_size='6144M' SCOPE=BOTH;

 If you get the below error during backup that means the log files were deleted externally to RMAN using some utility eg; asmcmd,ftp utility etc.

RMAN-6059: expected archived log not found, lost of archived log compromises
recoverability

Cause: The archived log was not found. The repository thinks it does exist. If the archived log has in fact been lost and there is no backup, then the database is no longer recoverable across the point in time covered by the archived log. This may occur because the archived log was removed by an outside utility without updating the repository. 

Action: If the archived log has been removed with an outside utility and the archivelog has already been backed up, then you can synchronize the repository by running CROSSCHECK ARCHIVELOG ALL. If the archivelog has not been previously backed up, then you should take a full backup of the database and archivelogs to preserve recoverability. Previous backups are not fully recoverable.

Related Post:
RMAN backup fails with Ora-00245 And Rman-08132
Ref: 293418.1,291415.1