As we know “Archive Area % Used” metric does not work for using FRA as archive destination when the destination is set to USE_DB_RECOVERY_FILE_DEST. We only receive after “Recovery Area % Used” metric triggers when it hits a Warning threshold of 85% full, and Critical of 97% full.
But the fixed thresholds of 85( Warning )/97( Critical) is not good enough for handling FRA full. So here we develop a customized more flexible way to monitor and alert when FRA is used as archive destination.
1) Make sure to use FRA for your archive destination
SQL> show parameter recovery
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string +FRA db_recovery_file_dest_size big integer 100G ... .. log_archive_dest_1 string location=USE_DB_RECOVERY_FILE_DEST
2) Prepare the Sql script to monitor the Flash Recovery Area destination. In our situation, space_reclaimable is regarded as available space.
select name, round(space_limit / 1024/1024/1024) space_limit_in_gb, round(space_used / 1024/1024/1024) space_used_in_gb, round(space_reclaimable / 1024/1024/1024)space_reclaimable_in_gb, round( (space_used - space_reclaimable)/ space_limit , 2 )*100 percent_usage from v$recovery_file_dest;
3) Create a Metric Extension ( ME )
a: Go to Enterprise / Monitoring / Metric Extensions, and then click on Create
b: For General Properties, Give the rerequired values, the click NEXT
c) Enter the query of monitoring FRA usage, the click NEXT
d) For Create New:Columns:
Click Add->New Metric Column, add below columns with supplied properties.
f) for TEST, choose a cluster database, then click “Run Test”
g)For Review page, Check all and click Finish if everything is OK.
i) Select Actions->Save as Deployable Draft , then the ME changes in to “Deployable Draft” status.
k) Check whether the ME is working as expected, go to target home page->Cluster Database->Monitoring->All Metrics:
So, the Metric Extension ( ME) is created successfully, in another post, we will create a Incident Rule which will create an incident and notification for all the over threshold Warning and Critical alerts.
Further more, we will create a Corrective Action against this Metric Extension ( ME ) to to a archive log backup automatically.