Oracle DBA – A lifelong learning experience

How to create a User Defined metric(UDM) in Grid

Posted by John Hallas on December 8, 2010

Using Grid it is possible to create user defined metrics that capture information about the state of a database, a host or an application. Once you have a script or sql statement that returns a value then a UDM can be created. Within EM a report can be created to report the metrics or an alert can be raised.

Firstly I will create a UDM that reports when an account is locked and automatically raise an alert. It may be a simple example but I show one or two issues that can occur.

EM/Select the database/User defined Metrics at the bottom of the main page. Create a new UDM and I suggest starting the name off with a business area such as Retail or EBS as you can end up with quite a few metrics once you get the hang of them.

The key choice to make is whether the output will be a number or a string and whether or not you want one or two values output. In my case I want to know the status of an account so that will be string and I want to output two columns, the username and the status. Enter your query and an account to use for the connection. Ensure that the query works and the account has the privilege to run that query. In this case when  I tested the UDM it fails as I had not  created the accounts beforehand. Select a comparison operator (equal, less than MATCH etc) to the sql query you are running and then format the output you want in the alert.

Note the %Key% and the %value% keywords have to be in exactly that case.

Set the test time to be 5 minutes in the schedule team for the purposes of testing it out and then run the test command (or save if fully confident). Remember to reschedule later. Note 5 minutes is the lowest frequency you can use.

If you have followed my example you will have had a failure which is not immediately obvious why. The first options in the drop down select box cover those applicable to the number value and the MATCH and CONTAINS are applicable for when choosing a string.

The test results show the result of the query and the UDM can then be saved.

Now it is time to schedule the job but first lock an account to ensure the UDM has something to pick up on.

One trick to see when the metric is next scheduled to run on the agent is to set the environment to the agent home and execute the command below and search the output of what the scheduler has been doing. Normally the metric page will  show the last time it ran but this is the first run so the field is empty in the screenshot below.

emctl status agent scheduler|grep -i account
2010-12-07 14:58:58 : oracle_database:MOMSIT2A:sga_start+incident_meter+BUS_AREA: Key account locked

If you want to know details of what collections are enabled then look in the $AGENT_HOME/sysman/emd/collections directory and search for a file called oracle_database_SID.xml or rac_database_SID.xml if RAC system. PS Note that if you are creating a UDM on a RAC cluster do it on the cluster not the instances.

A few minutes later we have an metric raised that one of our accounts is locked.

To set a notification up go to the home page and the preferences / notification rules. If you have a dashboard enabled then the alert will be raised in that automatically. Create a new rule and stick to the same naming convention of business area that we chose earlier. Decide if you want e-mail notification (Preferences / General page to set up if you have not done so already). Then it is a matter of selecting the instance or group of instances that you want the notification to act on. The Metric name in the Notifcation rules is always ‘User Defined Numeric/String metric’ which sometimes makes it hard to distinguish if you have multiple metrics.

If you are geting several notifications for a metric then check that script is selected rather than all object.

A couple of helpful pieces of SQL are

In an example where you have two columns and a number output but want  to show multiple columns then you can concatenate them

'Wave:'||WAVE_NBR||' Time: '||to_char(WAVE_END_TS,'DD-MON-YY:HH24:MI:SS'),
from wmsprd.distribution_metrics
where wave_end_ts = (
select max(WAVE_END_TS)
from wmsprd.distribution_metrics
where wave_nbr <> 999
and selected_lines > 999

To query the OEM repository for UDM details use following code

 select target_name "Database", key_value2 "Vdate", value "Result", collection_timestamp "Collected"
where metric_label='User-Defined SQL Metrics' and column_label = 'Retail MOM Vdate Check'
and collection_timestamp =
(select max(collection_timestamp) from SYSMAN.MGMT$METRIC_CURRENT where metric_label='User-Defined SQL Metrics' and column_label = 'Bus_area: Vdate Check')

If you have a situation where you are looking for a string that can be one of  ABORTED, ENABLED, DISABLED or null then you cannot match that using the comparison operators available. Therefore code it with a case statement and use the ‘critical Thresholds by Key’ column in EM to map ABORTED to ERROR , DISABLED to ERROR, ENABLED to NOERROR and MATCH ON ERROR


I hope that has been helpful. In my next blog I will demonstrate how to graph application server memory utilisation over time and also raise an alert if it exceeds a threshold.

6 Responses to “How to create a User Defined metric(UDM) in Grid”

  1. Rajanish Joshi said

    How to run this across the databases (i.e. More than one database) .
    Also need your pointers for following
    How to create User defined Metrics Reports with multiple metrics for e.g.
    I want to create a Report which will give me
    Space on Server for e.g. /tmp or /var/tmp
    Space on Database for e.g. SYSAUX Tablespace >90% Full.
    Lock on Database.
    Thanks in advance.

    • rajanish said

      Got the answer to the first question ,
      UDMs can only be created at the host, database and middleware level as per OEM Documentation. But we can deploy it across many targets using Monitoring Templates.
      For second question
      There is two types of UDM
      OS based.
      SQL based.
      So I need to create OS based scripts to monitor the OS related parameters.
      And for SQL based need to have scripts in sql. Question remains is it possible to view them as a report , I think answer is no as this is an alert type action, I need something like a report,is there any way to do the same?

      Thanks in advance.


  2. Gold Wave Table…

    […]How to create a User Defined metric(UDM) in Grid « Oracle DBA – A lifelong learning experience[…]…

  3. Mandrake Ooi said

    are we able to check who created the specific UDM, is the any table we can refer to?

    • John Hallas said

      I am not sure that you can find out who created/owns a UDM. The view SYSMAN.MGMT$METRIC_CURRENT

      has the best information but a UDM is against a database rather than owned by anyone specific. Happy for someone to correct me. One of the best views about a UDM is the one shown below

      select target_name, column_label, max(collection_timestamp) collection_timestamp
      where metric_label in ('User-Defined SQL Metrics','User Defined Metrics')
      group by target_name, column_label;
  4. vinay said

    How to create UDM for getting the full description of ALERT log ORA- error message in the email.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: