Proactive FRA Monitoring Using OEM Metrics

by Andrey Goryunov
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
Productionizing Machine Learning Models
Diverse team of data scientists and engineers working on deploying machine learning models in a modern office environment.
Analytics

Productionizing Machine Learning Models

Dec 12, 2025 10:50:11 AM 5 min read
Oracle BI EE 10.1.3.4.1 – Modeling Degenerate dimensions – Fact attributes
Data professionals collaborating on a holographic database schema model to visualize degenerate dimensions and business intelligence structures.
Analytics

Oracle BI EE 10.1.3.4.1 – Modeling Degenerate dimensions – Fact attributes

Dec 11, 2025 3:40:21 PM 5 min read
Enabling achivelog mode in 12c RAC database
Linux

Enabling achivelog mode in 12c RAC database

Oct 17, 2016 12:00:00 AM 1 min read

Ready to unlock value from your data?

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

Speak with Pythian consultants now →

Follow Us     Pythian-LinkedIn   Pythian-Twitter   Pythian-FB

 

Pythian-logo-White

© 2025 All rights reserved.