How to troubleshoot High FRA usage in Oracle database

Do you know how to troubleshoot high FRA usage in Oracle, if you are looking for the same, then you are at the right place.

FRA stands for fast recovery area/flash recovery area. It is a storage that contains mainly the backup and recovery-related files in Oracle database. In our operational task, we may often encounter this problem that our FRA is getting full. Today we will learn how to resolve high FRA usage in Oracle database in this article. Here are the steps that we can follow to resolve high FRA usage in Oracle database :

Check the FRA utilization :

select name
,      round(space_limit / 1024 / 1024/1024) size_gb
,      round(space_used  / 1024 / 1024/1024) used_gb
,      decode(nvl(space_used,0),0,0,round((space_used/space_limit) * 100)) pct_used
from v$recovery_file_dest
order by name
/

2. Find the reason which Files are mostly responsible for this :

select file_type
,      percent_space_used
,      percent_space_reclaimable
,      number_of_files
,      con_id
from   v$recovery_area_usage
order by 1
/

Files responsible for high FRA usage in Oracle database

3. Take action based on the scenarios :

Here in the above image, we can see that, archive logs are taking the most. Further, we can check if archives are getting deleted properly. If so, then we can check the retention period of the archive log deletion. If that is also fine, then we could check if the archive generations are getting high suddenly, etc.

2. High FRA usage in Oracle can also happen sometimes, if we see the flashback logs are taking up most of the spaces, then we could check the flashback retention period if it can be changed and whether the flashback logs are getting deleted properly. Sometimes, flashback logs are also generated rapidly if there is any restore point created due to some maintenance activity. So, ensure to drop that restore point once activity gets completed post-validation or sanity from the application team.

3. If we see the redo logs or control files are taking up most of the space, we could check if redo logs/control files be recreated later in other locations(It is critical). Be careful with production databases.

Also as a DBA, you must check the alert log if any error is coming. Still, if you are unable to figure out or to make a decision, you can increase the FRA space by issuing the below command provided some breathing space should be there in that File system or ASM disk, which is a temporary workaround to resolve high FRA usage in Oracle database but not always recommended.

Syntax :
alter system set db_recovery_file_dest_size=15G scope=both;

Hope this helps to resolve the high FRA scenario. Also, you may follow the Oracle Documentation for the same.

Share on Facebook

Post on X

Save