Proactive FRA Monitoring Using OEM Metrics
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!
