Fast Recovery Area for Archive Destination
By Courtney Llamas on Feb 27, 2014
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. As this metric is controlled by the server side database thresholds
it cannot be modified by Enterprise Manager (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.
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.
|FRADestination||Fast Recovery Area Destination||Key||String
|FRALimit||Fast Recovery Area Limit||Data||Number||Capacity||MB|
|FRAUsed||Fast Recovery Area Used||Data||Number
|FRAPercentUsed||Fast Recovery Area % Used||Data||Number
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. Using the default credentials has the added benefit of ensuring that if the passwords are changed for the target, and this is done using default credentials, the Metric Extension will continue to operate.
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
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.
A nice additional benefit of creating a Metric Extension to both calculate this value and alert on it being violated is that the metric history is stored in the Enterprise Manager repository allowing you to report on this through Information Publisher or BI Publisher as you would any other metric for a target.