If you are using Fast Recovery Area (FRA) for the archive destination and the destination is set to USE_DB_RECOVERY_FILE_DEST, you may notice that the Archive Area % Used metric does not trigger anymore. Instead you will see the Recovery Area % Used metric trigger when it hits a Warning threshold of 85% full, and Critical of 97% full. Unfortunately, this metric is controlled by the database and the thresholds cannot be modified (see MOS Note 428473.1 for more information). Thresholds of 85/97 are not sufficient for some of the larger, busier databases. This may not give you enough time to kickoff a backup and clear enough logs before the archiver hangs. If you need different thresholds, you can easily accomplish this by creating a Metric Extension (ME) and setting thresholds to your desired values. This blog will walk through an example of creating an ME to monitor archive on FRA destinations, for more information on ME's and how they can be used, refer to the Oracle Enterprise Manager Cloud Control Administrator's Guide.To determine if you are using FRA for your archive destination, issue an archive log list command from SQL or you can view in EM 12c by selecting the Availability menu and clicking on Recovery Settings.
In this example, we will create a Metric Extension that uses the following query to monitor the Fast Recovery Area destination:
select name, round(space_limit / 1048576) space_limit_in_mb, round(space_used / 1048576) space_used_in_mb, round((space_used / 1048576) / (space_limit / 1048576),2)*100 percent_usage from v$recovery_file_dest;
Create a Metric Extension
To create a metric extension select Enterprise / Monitoring / Metric Extensions, and then click on Create.
On the General Properties screen select either Cluster Database or Database Instance, depending on which target you need to monitor. If you have both RAC and Single instance you may need to create one for each. In this example we will create a Cluster Database metric. Enter a Name for the ME and a Display Name. Then select SQL for the Adapter. Adjust the Collection Schedule as desired, for this example we will leave it at 15 minutes.
Enter the query that you wish to execute, in this example we will use the query above that checks for space used on the recovery destination.
The next step is to create the columns to store the data returned from the query. Click Add and add a column for each of the fields in the same order that data is returned. The table below shows how each field is defined as either a Key or Data column.
|Name||Display Name||Column Type||Value Type||Metric Category||Unit|
|FRADestination||Fast Recovery Area Destination||Key||String||Capacity|
|FRALimit||Fast Recovery Area Limit||Data||Number||Capacity||MB|
|FRAUsed||Fast Recovery Area Used||Data||Number||Capacity||MB|
|FRAPercentUsed||Fast Recovery Area % Used||Data||Number||Capacity||%|
For the Fast Recovery Area % Used column, you can set a default threshold by selecting a Comparison Operator and setting Warning and Critical thresholds. In this example we set Warning to > 60% and Critical to > 75%
When all columns have been added, review your columns and click Next.
On the Credentials screen, you can choose to use the default monitoring credentials or specify new credentials. We will use the default monitoring credentials
established for our target, in this case that is DBSNMP.
The next step is to test your metric extension. Click on Add to select a target for testing, then click Select.
Now click the button Run Test to execute the test against the selected target(s).
We can see in the example below that the metric extension has executed and returned a value of 77 for Fast Recovery Area % Used. Click Next to proceed.
Review the metric extension in the final screen and click Finish.
The metric will be created in Editable status, before you can deploy to targets you must select the metric, click Actions and select Save as Deployable Draft. This step allows you to test your metric on a set of targets. Once you've confirmed the metric is as you expect, click Actions and select Publish Metric Extension.
Finally, we want to apply this metric to a target. You can also add a metric to a template which can be mass deployed to all targets, however in this example we will deploy to a target directly. Select the metric, click Actions and then Deploy to Targets. Click Add and select the target you wish to deploy to, then click Submit.
The deployment job will be shown in the final window.
Once deployment is complete, we can go to the target and select Cluster Database / Monitoring / Metric & Collection Settings to see the new metric and its thresholds.
After 15 minutes, we should be able to see the most recent upload. In our example, you can see we have already triggered a Critical event as our destination is 77% full.
If you have and Incident Rule which creates an incident or notification for all events of Warning or Critical, you will receive an incident similar to the one below. If you have selective metrics creating incidents or notifications, be sure to add your new ME to the Incident Rules.
By creating a Metric Extension, we are now able to customize the monitoring thresholds at which our Fast Recovery Area notifies us when it’s full. Taking this a step further, we could create a Corrective Action on the Metric Extension we just created to kickoff an archive log backup. By creating a Corrective Action to trigger the archive log backup, you can reduce time spent by DBAs logging in, looking at the issue and kicking off a backup.