Proactive FRA Monitoring Using OEM Metrics

1 min read

Aug 27, 2013 12:00:00 AM

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: [code]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 from MGMT$TARGET_PROPERTIES 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[/code] 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!

On this page

Ready to unlock value from your data?

With Pythian, you can accomplish your data transformation goals and more.

Speak with Pythian consultants now →

What We Do

Proactive FRA Monitoring Using OEM Metrics

Share this

Share this

More resources

Process mining as a link between business process modelling and data mining

SQL 2012: Let's Make a Date with DATEFROMPARTS and TRY_PARSE!

Mongostat - A nifty tool for Mongo DBAs

Ready to unlock value from your data?