Proactive FRA Monitoring Using OEM Metrics

Posted in: Technical Track

As you know, thresholds for flash/fast recovery area (FRA) usage are internally set in a database to 85 and 97 per cent, and there are no ways to change the thresholds — at least none that are supported by Oracle. These settings may work fine in most cases, but being aware of changes in FRA usage can sometimes be helpful. You can then contact your DBA and suggest verifying your database’s settings.

This process is very simple and doable if your database is monitored by OEM; the agent that gathers all the information and saves it in the OEM repository. The only thing you would need to do is either schedule a report or create a user-defined metric with a specific threshold that pages you before it reaches the threshold pre-set by Oracle.

The metrics are from the Flash Recovery group and are available in mgmt$metric_current and in hourly and daily metrics history tables. For user-defined metrics, the following SQL can be used:

with s1 as (
  select target_type, target_name, metric_label, column_label, value, key_value
  from mgmt$metric_current
  where metric_label = 'Flash Recovery'
  and column_label = 'Usable Flash Recovery Area (%)'
  and value is not null
s2 as (
select target_type, target_name, max(db_ver) db_ver, max(host_name) host_name, max(dg_stat) dg_stat from (
  select target_type, target_name,
  (case when property_name = 'DBVersion' then property_value end) db_ver,
  (case when property_name = 'MachineName' then property_value end) host_name,
  (case when property_name = 'DataGuardStatus' then property_value end) dg_stat
  where target_name in (select target_name from s1)
  or property_name in ('DBVersion', 'MachineName', 'DataGuardStatus')
  group by target_type, target_name
select s1.target_name, s2.host_name, s2.db_ver, s2.dg_stat, s1.column_label, s1.value from s1, s2
where s1.target_name = s2.target_name
and value < 30 --threshold per cent to page on
order by cast(value as number) desc

The query uses 30% of the Usable Flash Recovery Area (%) metric and brings additional information on the database’s location, its version, and its Data Guard status. For the metrics that bring a list of databases with breached metrics, I personally use count(*) around the query to page on and then execute the SQL saved as a report in SQL Developer to get all the information on targets.

Happy OEM’ing!

Want to talk with an expert? Schedule a call with our team to get the conversation started.

2 Comments. Leave new

Thanks Andrey for the info. I am trying to write a procedure which can check FRA value and send mail if its above 75%. Can you help me with a script writing ?


Hey Ajay,

I’m going to modify the script to alert with a metric extension, assuming Oracle replies to my SR with no method other than that.

Let me know if you’d like me to send you my method later.


Leave a Reply

Your email address will not be published. Required fields are marked *