EM12c: Using Metric Extensions to Generate Composite Alerts

There was a question on OTN forums about how to generate alerts based on two different metrics. The user wants to get alert only if the warning threshold is over 80% “AND” there is less then 20Gb of free space of a tablespace. So he doesn’t want to get alert if the tablespace is over %80 full but still has 100GB free space. Of course, he can set different thresholds for each tablespace: He can set percentage threshold for small tablespaces and set free space threshold for bigger ones. I do not know how many databases he monitors but if he’s managing lots of DBs, this could be a time consuming task.

So how can we solve it? EM12c doesn’t let you generate an alert based on two different metrics. For these situations, you can create metric extensions. All you need is to query mgmt$alert_current and see if two alerts occurred for same target. Examine the following SQL:

This query checks if both “pctUsed” (Tablespace Space Used) and “bytesFree” (Tablespace Free Space) alerts are generated for same tablespace. The query doesn’t check the level of alert (warning or critical), but if you need so, you can use the alert_state column of mgmt$alert_current view.

Now let’s see how we can create it as a metric extension. First I have set the thresholds on one of my DBs (DEVDB) to produce alert for both percentage and disk space. So I will be able to test my metric extension.

metric1

Then I opened “metric extension” page and clicked “repository-side metric extension” under “create” menu. I will create a “repository-side metric extension”, so it will let me query management views of Enterprise Manager.

metric2

Because the tablespace metrics are related with databases, I pick “database instance” as the target type. When creating your own user metric, you can pick the target type according to your metrics.

metric3

I enter the above query, and click “next”. The query should always return target_guid and an additional column which Enterprise Manager will use as metric value.

metric4

At the columns page, we see one column returned from the query (target_guid is not visible here). I clicked on it to edit, and modified the display name, comparison operator and values for warning and critical. I entered a dummy value, because if my query returns any value (the name of the tablespace), I want this alert triggered! I also modified the messages to show which tablespace has low disk space.

metric5

We can check our metric before we actually deploy it. That’s a great feature! As you can see I added several instances, and run tests on them. Only one of them produced the alert.

metric6

I reviewed my metric extension and clicked “finish” to create it.

metric7

After it’s ready, all we need is to “save it as deployable draft”, and then deploy the targets.

metric8

This is an email generated by our metric extension. I hope it helps.

Please share
  • 5
  •  
  •  
  •  
  •  
  •  

AWS Big Data Specialist. Oracle Certified Professional (OCP) for EBS R12, Oracle 10g and 11g. Co-author of "Expert Oracle Enterprise Manager 12c" book published by Apress. Awarded as Oracle ACE (in 2011) and Oracle ACE Director (in 2016) for the continuous contributions to the Oracle users community. Founding member, and vice president of Turkish Oracle User Group (TROUG). Presented at various international conferences including Oracle Open World.

4 Comments

  1. Thierry G

    Hello;

    I am not able to have an alert.

    I try many value with the comparaison operator NULL, DUMMY, ‘ ‘..

    but enable to have an alert. Could you help me please ?

    Regards

    Thierry

  2. Marat

    In order this setup to work, you have to set thresholds for built-in metrics “Tablespace Free Space(MB)” and “Tablespace Space Used (%)” right?
    If so then how can you suppress alerts from this metrics?

    • Gokhan Atil

      Marat, you don’t need to set thresholds for built-in metrics. You just need to enable collecting data for these metrics.

  3. John

    you don’t need to set thresholds for built-in metrics. You just need to enable collecting data for these metrics.

    If thresholds are not set for Tablespace Free Space (MB) and Tablespace Space Used (%) metrics then mgmt$alert_current will not be populated.

Leave Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.