Monitoring and Maintaining the Fast Recovery Area
Following error occurred when we have flash back recovery is enabled which caused 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 parameter if you have space on disk.
1. Check the size of 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 archive to other location)
1. Delete from operating system if backup is not needed.
2. connect with rman, run the crosscheck command to intimate database the space is released for reuse.
RMAN>crosscheck archivelog all;
RMAN> crosscheck;
3. If expired backup exists then delete expired backup to gain space
RMAN> Delete Expired
Check the space area allocated to 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
