FRA Usage and Administration

 In Oracle database, the Flash Recovery Area or FRA is a location on disk where the database can create and manage several kinds of backup and recovery-related files.

Main file types are archivelog, flashback log, backups, as well as mirrors for your control files and redo log files.

All files in the FRA are Oracle-managed files. Using a Flash Recovery Area simplifies the administration of your database by automatically retaining them for as long as they are needed for restore and recovery activities, and deleting them when they are no longer needed, because the space is needed for another backup and recovery-related purpose.

Checking the current usage 

You can check the configuration by looking at two parameters.The parameters are db_receovery_file_dest and second one db_recovery_file_dest_size . 

db_receovery_file_dest point to location where the files will be located in Server . FOr normal single Databases this will be any directory for RAC this will be set to a Diskgroup which will be spcifically created for FRA pupose . 

db_receovery_file_dest_size  Point to amount of Stoage that is assingned to Current Database . Database cannot create files in FRA beyind this Size . In RAC environments some times all Databases may use same diskgroup as FRA location and this Size parameter will act as barrier from one DB consuming all the Space and will limit the SPace Utilzation . 

 

  Example :

 

 

FRA utilization can be tracked  in Database using multiple views .Some of the Views Used are 

1)  v$flash_recovery_area_usage or v$recovery_area_usage  ==> Displays the FRA usage in % basis 

2) v$recovery_file_dest ==> Displays FRA utilization in units (Byes,KB,MB,GB)

The v$recovery_area_usage view gives information about reclaimable files. Files that are reclaimable will be removed by the database when the space is needed for other purposes. This is done when the usage of the FRA is about 80% of the defined size (db_recovery_file_dest_size).

 

   ##################### Below Queries can be used to check FRA utilzation #########

set lines 120
break on report
 
compute sum of percent_space_used on report
compute sum of percent_space_reclaimable on report
 
select file_type,percent_space_used,
percent_space_reclaimable,number_of_files,con_id
from v$recovery_area_usage
order by 1
/

 

col name format a7
clear breaks
clear computes
 
select name,round(space_limit / 1024 / 1024) size_mb,
round(space_used  / 1024 / 1024) used_mb,
decode(nvl(space_used,0),0,0,round((space_used/space_limit) * 100)) pct_used
from v$recovery_file_dest
order by name
/

 

For monitoring your FRA, you need to check your un-reclaimable space. Just checking the percentage of the FRA in use is not very helpful, because it will often be around 80%. 

This query combines the two views to calculate the PERCENT_SPACE_NOT_RECLAIMABLE. If it is around (or above) 80% you will need to act, because that is a situation where your actual FRA usage will also rise above 80%. It is an indication that Oracle cannot remove files, because all files need to be kept for recovery purposes. The most common problem with an undersized FRA is that the database will hang when it cannot create an archivelog file at time of a logswitch.

 

 col name format a7
clear breaks
clear computes
 
select name,round(space_limit / 1024 / 1024) space_limit_mb,
round(space_used  / 1024 / 1024) space_used_mb,percent_space_used,
percent_space_reclaimable,percent_space_not_reclaimable
from v$recovery_file_dest,
( select sum(percent_space_reclaimable) percent_space_reclaimable,
sum(percent_space_used) percent_space_used,
sum(percent_space_used - percent_space_reclaimable) percent_space_not_reclaimable
from  v$recovery_area_usage)
order by name
/

Configuring archivelog deletion

 You can define your archivelog deletion policy in RMAN. If there is no archived redo log deletion policy in RMAN, the files can be deleted when backed up at least once to disk or SBT. Or the logs are obsolete according to the backup retention policy.

 

If you do create an archivelog deletion policy, they can be deleted after you meet the requirements in the policy. 

Examples are:

• CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 2 TIMES TO SBT;
• CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO ALL STANDBY;
• CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;

The deletion policy itself does not delete archivelog files. It is a protection; files are not deleted if it conflicts with the policy.