ORA-19809: limit exceeded for recovery files

The following error occurred when we had flashback recovery enabled which caused the database to hang state due to insufficient quota of space allocated during long insert/update operation.

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

Solution for Error ORA-19809
Option 1: Increase the size of the parameter if you have space on disk.
1. Check the size of the parameter
Show parameter DB_RECOVERY_FILE_DEST_SIZE

OR

select name, (space_limit/1024/1024) ||’MB’ as Space_Limit,(space_used/1024/1024)||’MB’ as Space_Used from v$recovery_file_dest;


2. Increase size from 30G to 40G

Alter system set db_recovery_file_dest_size=40G;

Option 2: Move or Remove the backup and empty the folder of flashback recovery(suppose move the archive to other location)

1. Delete from the operating system if the backup is not needed.

2. connect with RMAN, and run the crosscheck command to intimate database the space is released for reuse.

RMAN>crosscheck archivelog all;
RMAN> crosscheck;

3. If an expired backup exists then delete the expired backup to gain space

RMAN> Delete Expired

Check the space area allocated to the Fast Recovery Area:

SELECT * FROM V$RECOVERY_FILE_DEST;

NAME            SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
--------------  ----------- ---------- ----------------- ---------------
C:\flashback    60456567545  109240320             256000              30

SELECT * FROM V$RECOVERY_AREA_USAGE;

FILE_TYPE    PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE                   0                         0               0
ONLINELOG                     2                         0              20
ARCHIVELOG                 4.05                      2.01              30
BACKUPPIECE                3.94                      3.86               1
FLASHBACKLOG                .08                         0               1