Monitoring and Maintaining the Fast Recovery Area

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


 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.