An Oracle blog about Oracle Enterprise Manager and Oracle Management Cloud

Fast Recovery Area for Archive Destination

Courtney Llamas

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

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

To create a metric extension select Enterprise / Monitoring / Metric Extensions, and then click on Create.

On the General Properties screen select either Cluster
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

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

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
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

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.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha

Integrated Cloud Applications & Platform Services