Oracle’s built-in thresholds for the Flash Recovery Area (FRA) are a bit like a strict landlord: they’re set at 85% and 97%, and there’s absolutely no supported way to move them. While these defaults work for most, waiting until your FRA is 85% full to get a notification can be a recipe for a stressful Monday morning.
Fortunately, if you’re using Oracle Enterprise Manager (OEM), you don’t have to play by the landlord's rules. You can leverage the OEM repository to create your own early-warning system.
The Limitations of Default FRA Thresholds
Why Built-in Thresholds Aren't Always Enough
The database internally monitors FRA usage and triggers warnings at pre-defined levels. However, if your database generates a massive volume of redo or handles large backup sets, the jump from 85% to 97% (and eventually to a suspended database) can happen faster than a DBA can react.
Customizing your monitoring allows you to "move the goalposts" and receive alerts when space reaches a level that actually makes sense for your specific workload—say, at 70% usage (or when usable space drops below 30%).
Leveraging the Oracle Enterprise Manager (OEM) Repository
The OEM agent acts as a diligent librarian, gathering metrics and saving them into the repository. By querying these tables directly, you can bypass the fixed internal thresholds and create User-Defined Metrics (UDM) or custom reports.
Identifying Key Metrics and Properties
The specific data we need belongs to the Flash Recovery group. You can find this information in:
mgmt$metric_current: For real-time status.- Hourly/Daily history tables: For trend analysis.
The specific column to watch is Usable Flash Recovery Area (%). To make the alerts actionable, it’s also helpful to pull in metadata like the host name, database version, and Data Guard status.
Implementing Custom SQL Metrics
SQL Query for User-Defined Metrics
The following query identifies databases where the usable FRA space has dropped below 30%. It combines metric data with target properties to give you a complete picture of the environment.
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: page when usable space < 30% order by cast(value as number) desc;
Strategy for Alerts and Reporting
Actionable Monitoring
When setting up a proactive alert, I recommend using a count(*) wrapper around the query above. This allows you to page your team only when the count is greater than zero.
For a more visual approach, you can save this SQL as a report in SQL Developer or within the OEM Information Publisher. This gives you a one-stop-shop to see which targets are nearing their limits across the entire fleet, regardless of what Oracle’s internal "85% rule" says.
Happy OEM'ing!
Oracle Database Consulting Services
Ready to optimize your Oracle Database for the future?
.svg){kind=small}
.svg){kind=small}