4 User-Defined Metrics

User-defined metrics allow you to extend the reach of Enterprise Manager's monitoring to conditions specific to particular environments via custom scripts or SQL queries and function calls. Once defined, user-defined metrics will be monitored, aggregated in the repository and trigger alerts like regular metrics.

This chapter covers the following topics:

Extending Monitoring Capability

There are two types of user-defined metrics:

  • OS-Based User-Defined Metrics: Accessed from Host target home pages, these user-defined metrics allow you to define new metrics using custom Operating System (OS) scripts.

    To monitor for a particular condition (for example, check successful completion of monthly system maintenance routines), you can write a custom script that will monitor that condition, then create an OS-based user-defined metric that will use your custom script. Each time the metric is evaluated by Enterprise Manager, it will use the specified script, relying on that script to return the value of the condition.

  • SQL-Based User-Defined Metrics: Accessed from Database target home pages, these user-defined metrics allow you to implement custom database monitoring using SQL queries or function calls.

    SQL-based user-defined metrics do not use external scripts. You enter SQL directly into the Enterprise Manager user interface at the time of metric creation

Once a user-defined metric is created, all other monitoring features, such as alerts, notifications, historical collections, and corrective actions are automatically available to it.

Administrators who already have their own library of custom monitoring scripts can leverage these monitoring features by integrating their scripts with Enterprise Manager via user-defined metrics. Likewise, existing SQL queries or function calls currently used to monitor database conditions can be easily integrated into Enterprise Manager's monitoring framework using the SQL-based user-defined metric.

Creating OS-Based User-Defined Metrics

Creating an OS-based user-defined metric involves two steps:

Create Your OS Monitoring Script

Using a scripting language of your choice, create a script that contains logic to check for the condition being monitored. For example, scripts that check for disk space or memory usage. All scripts to be run with user-defined metrics should be placed in a directory to which the Management Agent has full access privileges. Scripts themselves must have the requisite permissions set so that they can be executed by the Management Agent. The script runtime environment must also be configured: If your script requires an interpreter, such as a Perl interpreter, this must be installed on that host as well.

All monitoring scripts should contain code to perform the following basic functions:

Code to check the status of monitored objects

Define logic in the code that checks the condition being monitored such as determining the amount of free space on a particular file system or level of memory usage.

After checking the monitored condition, the script should return the value associated with the monitored object.

When you choose to have the script return a specific value from the monitored object (for example, current disk space usage), you can also have Enterprise Manager evaluate the object's current value against specific warning and critical thresholds. You specify these warning and critical thresholds from the Grid Control console at the time you create the user-defined metric. Based on the evaluation of the metric's value against the thresholds, an alert may be triggered at one of the following severity levels:

Table 4-1 Metric Severity Levels

Severity Level Status

Script Failure

The script failed to run properly.

Clear

No problems with the object monitored; status is clear. If thresholds were specified for the metric, then it means the thresholds were not reached.

Warning

The value of the monitored object reached the warning threshold.

Critical

The value of the monitored object reached the critical threshold.


Code to return script results to Enterprise Manager

After checking the monitored condition, the script should return the value associated with the monitored object. The script returns values back to Enterprise Manager by sending formatted information to standard output (stdout) using the syntax that is consistent with the scripting language (the "print" statement in Perl, for example). Enterprise Manager then checks the standard output of a script for this formatted information; specifically it checks for the tags: em_result and em_message and the values assigned to these tags.

The script must assign the value of the monitored object to the tag em_result. The output must be written as a string delimited by new line characters. For example, if the value of the monitored object is 200, your script can return this to Enterprise Manager as shown in this Perl statement:

print "em_result=200\n"

You can also have Enterprise Manager evaluate the returned value against specified warning and critical thresholds. You specify these warning and critical thresholds when you register your script as a user-defined metric in the console.

If the comparison between the warning or critical threshold holds true, a warning or critical alert will be generated. The default message for this alert will be:

"The value is $em_result".

You can choose to override this default message with a custom message by assigning the string to be used to the tag em_message.

For example, if you want your alert message to say 'Disk usage is high", your script can return this custom message as follows:

print "em_message=Disk usage is high\n"

Important:

Script output tags must be lower-case in order for Enterprise Manager to recognize the script output as valid user-defined metric feedback. Messages or values associated with each tag can be mixed case.
  • Valid tag output: em_result=My Value\n

  • Invalid tag output: Em_Result=My Value\n

For a successful script execution, the script output must start with the "em_result=" string in a new line. The message must start with the "em_message=" string in a new line.

The following table summarizes the script output tags.

Table 4-2 Script Output Information Tags

Tag Definition

em_result

Use this tag to return script result values. Exactly one em_result tag must be found in STDOUT. If more than one em_result tag is found, the first tag encountered will be used; subsequent em_result tags will be ignored.

Example:

print "em_result=200\n" 

Returns 200 as the value of the monitored object.

em_message

Use this tag to specify a message with the script result value in STDOUT. For OS-based user-defined metrics, only one em_message tag is permitted. If you submit more than one em_message tag, only the first tag is used. Subsequent tags are ignored.

Example:

print "em_result=200\nem_message=Disk usage is high\n" 

Returns 200 as the value of the monitored object in addition to the message "Disk usage is high".

If you want to include the value of em_result in the message, you can use the placeholder $em_result.

Example:

print "em_message=Disk usage is at $em_result.\n"

If script execution is successful AND it does not contain a em_message string, a default em_message string is automatically generated. The following message format is used:

em_message=The value is $em_result 

Example:

print "em_result=200\n"

Returns 200 as the value of the monitored object and the generated message "The value is 200"


The output of the user-defined monitoring script must be either em_result or em_message. In the event of system error, such as Perl aborting and writing information to STDERR pertaining to invalid commands, the script returns:

  • Non-zero value

  • STDOUT and STDERR messages are concatenated and sent to STDERR

This error situation results in a metric error for this user-defined metric. You can view metric errors in the Errors page of the Alerts tab in the Enterprise Manager console.

OS Script Location

Oracle recommends that user-defined metric OS scripts reside in a location outside the Agent Oracle Home. Doing so isolates scripts from any changes that may occur as a result of an Agent upgrade and ensures your scripts remain operational. When registering your script in the Grid Control console, you must specify the full path to the script. Do not use Available Properties (for example, %scriptsDir% or %emdRoot%) as part of the path specification.

Script Runtime Environment

When the user-defined metric is evaluated, it executes the script using the credentials (user name and password) specified at the time the user-defined metric was registered in the Enterprise Manager console. See "Register the Script as a User-Defined Metric". Ensure that the user name and password you specify for the user-defined metric is an active account (on that machine) possessing the requisite permissions to run the script.

Register the Script as a User-Defined Metric

Once you have created the monitoring script, you are ready to add this monitoring functionality to Enterprise Manager as a user-defined metric.

Important:

: For OS-based user-defined metrics, make sure the Management Agent is up and running on the machine where the monitoring script resides before creating the user-defined metric. Operator privilege or higher is required on the host target.

Creating an OS-Based User-Defined Metric

  1. From the home page of the Host that has your OS monitoring script (Related Links), choose User-Defined Metrics. The User-Defined Metrics summary page appears containing a list of previously defined User-Defined Metrics. From this page, you perform edit, view, delete, or create like functions on existing User-Defined Metrics.

  2. Click Create. The Create User-Defined Metric page appears.

  3. Enter the requisite metric definition, threshold, and scheduling information. For the Command Line field, enter the full path to your script, including any requisite shell or interpreters. For example, /bin/sh myscript. See the following section for more details.

  4. Click OK. The User-Defined Metric summary page appears with the new User-Defined Metric appended to the list.

If the user-defined metric has been created and the severity has not been updated recently, it is possible that there are metric errors associated with the user-defined metric execution. In this situation, access the Errors subtab under Alerts tab to check.

Create User-Defined Metric Page (OS-based User-Defined Metric)

The Create User-Defined Metric page allows you to specify the metric information required to successfully register the metric with Enterprise Manager. The page is divided into functional categories:

  • Definition: You define the operational and environmental parameters required to run your script, in addition to the name of the script itself.

  • Operating System Credentials: You enter the credentials used to run the monitoring script. See Enterprise Manager online help for more details on Response Actions. This functional area appears when creating OS-based user-defined metrics.

  • Thresholds: To have the value returned by your script compared with set threshold values, enter the requisite threshold information. The value of the monitored metric returned by your script (as specified by em_result) will be compared against the thresholds you specify. If the comparison holds true, a warning or critical alert may be generated.

  • Schedule: Specify the start time and frequency at which the user-defined script should be run. The time zone used is that of the Agent running on the monitored host.

The following figures show the Create User-Defined Metric pages for an OS-based user-defined metric. When accessing this page from any Host home page, the Create User-Defined Metric page appears as shown in Figure 4-1.

Figure 4-1 Create User-Defined Metric Page (OS-Based)

Description of Figure 4-1 follows
Description of "Figure 4-1 Create User-Defined Metric Page (OS-Based)"

Key elements of this page are described in the following tables.

Table 4-3 Create User-Defined Metric Page: Definition

User-Interface Element Description

Metric Name

Metric name identifying the user-defined metric in the Enterprise Manager user interface. This name must be unique for all User-Defined Metrics created on that host.

Metric Type

Type of the value returned by the user-defined script. Choose "NUMBER" if your script returns a number. Choose "STRING" if your script returns an alphanumeric text string.

Command Line

Enter the complete command line entry required to execute the user-defined script. You must enter the full command path as well as full path to the script location. For example, to run a Perl script, you might enter something like the following in the Command Line entry field:

/u1/bin/perl /u1/scripts/myScript.pl

The content of the Command Line is passed as a literal string, so you may use any syntax, special characters, or parameters allowed by your operating system.

Environment

Optional. Enter any environmental variable(s) required to run the user-defined script. A list of predefined properties that can be passed to your script as variables is listed in the Available Properties box. You may also specify your own environment variables. Multiple variables can be defined as a space-separated list.

Example: If your script uses three variables (var1, var2, var3) where var1 is the location of the Perl directory (predefined), var2 is the directory where your Perl scripts are stored (predefined), and var3 is an Oracle home, your entry in the Environment text entry field would appear as follows:

var1=%perlBin% var2=%scriptsDir% var3=/u1/orahome10


Table 4-4 Create User-Defined Metric Page: Operating System

User-Interface Element Description

User Name

Enter the user name for a valid operating system account on the machine where the script is to be run. Make sure the specified account has the requisite privileges to access the script directory and execute the script.

Password

Enter the password associated with the User Name.


Table 4-5 Create User-Defined Metric Page: Threshold

User-Interface Element Description

Comparison Operator

Select the comparison method Enterprise Manager should use to compare the value returned by the user-defined script to the threshold values.

Available Comparison Operators

Operator Value Metric Type Description

= Number equal to

> Number greater than

< Number less than

>= Number greater than or equal to

<= Number less than or equal to

!= Number not equal to

CONTAINS String contains at least

MATCH String exact match

Warning

The value returned by the script is compared to the Warning threshold value using the specified comparison operator. If this comparison holds true, an alert triggers at the warning severity level.

Specifically, an alert triggers at the warning severity level if the following comparison is true:

<script_value> <comparison_operator> <warning_threshold>

and if the consecutive occurrences preceding notification has been reached.

Critical

The value returned by the script is compared to the Critical threshold value using the specified comparison operator. If this comparison holds true, an alert triggers at the critical severity level.

Specifically, an alert triggers at the critical severity level if the following comparison is true:

<script_value> <comparison_operator> <critical_threshold>

and if the consecutive occurrences preceding notification has been reached.

Consecutive Occurrences Preceding Notification

Consecutive number of times a returned value reaches either the warning or critical thresholds before an alert triggers at a warning or critical severity. This feature is useful when monitoring for sustained conditions. For example, if your script monitors the CPU load for a particular machine, you do not want to be notified at every CPU load spike. Instead, you are only concerned if the CPU load remains at a particular threshold (warning or critical) level for a consecutive number of monitoring cycles.

Response Action

Optional. Specify a script or command that will be executed if the user-defined metric generates a warning or critical alert. The script or command will be executed using the credentials of the Agent owner. Important: Only an Enterprise Manager Super Administrator can create/edit response actions for metrics.

For example, the Management Agent executes the response action if:

The Alert severity is Warning or Critical

AND

There is a change in severity (for example, warning -> critical, critical --> warning, clear --> warning or critical)

For more information, see Enterprise Manager online help.


The User-Defined Metric Schedule interface lets you specify when the Management Agent should start monitoring and the frequency at which it should monitor the condition using your OS script.

OS-Based User-Defined Metric Example

The sample Perl script used in this example monitors the 5-minute load average on the system. The script performs this function by using the 'uptime' command to obtain the average number of jobs in the run queue over the last 5 minutes.

The script is written in Perl and assumes you have Perl interpreter located in /usr/local/bin on the monitored target.

This script, called udmload.pl, is installed in a common administrative script directory defined by the user. For example, /u1/scripts.

Important:

Do not store user-defined metric monitoring scripts in the same location as Enterprise Manager system scripts.

Full text of the script:

#!/usr/local/bin/perl


# Description: 5-min load average.

# Sample User Defined Event monitoring script.


$ENV{PATH} = "/bin:/usr/bin:/usr/sbin"; 


$DATA = `uptime`; 

$DATA =~ /average:\s+([\.\d]+),\s+([\.\d]+),\s+([\.\d]+)\s*$/; 

  


if (defined $2) { 

   print "em_result=$2\n"; 

} else { 

   die "Error collecting data\n"; 

} 

  1. Copy the script (udmload.pl) to the monitored target. For example: /u1/scripts. Make sure you have an Enterprise Manager 10g Management Agent running on this machine.

  2. Edit the script, if necessary, to point to the location of the Perl interpreter on the monitored target. By default, the script assumes the Perl interpreter is in /usr/local/bin.

  3. As a test, run the script: udmload.pl You may need to set its file permissions so that it runs successfully. You should see output of this form:

    em_result=2.1 
    
  4. In Create User-Defined Metric page, create a new user-defined metric as follows:

    1. Definition Settings

      • Metric Name: Test User-Defined Metric

      • Metric Type: Number

      • Command Line: %perlBin%/perl /u1/scripts/udmload.pl

      • Environment: leave blank

      • Operating System User Name: <OS user able to execute the script>

      • Password: ******

    2. Threshold Settings

      • Comparison Operator: >=

      • Critical Threshold: 0.005

      • Warning Threshold: 0.001

      • Consecutive Occurrences Preceding Notification: 1

      In this example, we want the metric to trigger an alert at a Warning level if the 5-minute load average on the machine reaches 0.001, and trigger an alert at a Critical level if the 5-minute load average reaches 0.005. Feel free to change these thresholds depending on your system.

    3. Schedule Settings:

      • Start: Immediately after creation

      • Frequency: Repeat every 5 minutes. You must specify at least a 5 minute interval.

Setting Up the Sample Script as a User-Defined Metric

When the 5-minute load reaches at least 0.001, you should see the metric trigger an alert.

Creating a SQL-Based User-Defined Metric

You can also define new metrics using custom SQL queries or function calls supported against single instance databases and instances on Real Application Clusters (RAC). To create this type of user-defined metric, you must have Enterprise Manager Operator privileges on the database:

  1. From the Related Links area of any Database home page, choose User-Defined Metrics. The User-Defined Metrics summary page appears containing a list of previously defined user-defined metrics. From this page, you perform edit, view, delete, or create like functions on existing user-defined metrics.

  2. Click Create. The Create User-Defined Metric page appears.

  3. Enter the requisite metric definition, threshold, and scheduling information. For the SQL Query field, enter the query or function call. See the following section for more information.

    Click Test to verify that the SQL query or function call can be executed successfully using the credentials you have specified

  4. Click OK. The User-Defined Metric summary page appears with the new user-defined metric appended to the list.

If the user-defined metric has been created and the severity has not been updated recently, it is possible that there are metric errors associated with the user-defined metric execution. In this situation, access the Errors subtab under Alerts tab to check.

Create User-Defined Metric Page (SQL-Based User-Defined Metric)

The Create User-Defined Metric page allows you to specify the metric information required to successfully register the metric with Enterprise Manager. The page is divided into functional categories:

  • Definition: You define the operational and environmental parameters required to run your script, in addition to the name of the script itself.

  • Database Credentials: You enter the user name and password for a valid user account on the database where the SQL is to be run. Make sure the specified user account has the requisite administrative and access privileges to execute the SQL query or function call.

  • Thresholds: To have the value returned by your SQL query or function call compared with set threshold values, enter the requisite threshold information. The value of the monitored metric returned by your query or function call will be compared against the thresholds you specify. If the comparison holds true, a warning or critical alert may be generated.

  • Schedule: Specify the start time and frequency at which the user-defined SQL query or function call should be executed. The time zone used is that of the Agent running on the monitored machine.

The following figures show the Create User-Defined Metric pages for a SQL-based user-defined metric. When accessing this page from any Database home page, the Create User-Defined Metric page appears as shown in Figure 4-2.

Figure 4-2 Create User-Defined Metric Page (SQL-Based)

Description of Figure 4-2 follows
Description of "Figure 4-2 Create User-Defined Metric Page (SQL-Based)"

Key elements of this page are described in the following tables.

Table 4-6 Create User-Defined Metric Page: Definition

User-Interface Element Description

Metric Name

Metric name identifying the user-defined metric in the Enterprise Manager user interface.

Metric Type

Type of the value returned by the user-defined script. Choose "NUMBER" if your script returns a number. Choose "STRING" if your script returns an alphanumeric text string.

SQL Query Output

Specify whether the SQL script is to return a single value (one column) or a multiple rows (two columns).

  • Single Value: Query is one of the following types.

    A SELECT statement returning a single value. Example: SELECT sal FROM emp WHERE empno=7369

    A function call returning a single value. Example: myfunc(123, 'abc')

  • Two Columns: Query is a SELECT statement that returns two columns and possibly multiple rows. Example: SELECT ename, sal FROM emp. Each entry in the first column (the key column) must be a unique string. The second column (the value column) must be of the selected Metric Type.

SQL Query

Enter a SQL query or function call that returns values of the appropriate type (STRING or NUMBER). The SQL statement must return one or two column. If your SQL statement only returns one column, only one row can be returned. If you want multiple rows returned, your SQL statement must return two columns.


Table 4-7 Create User-Defined Metric Page: Database Credentials

User-Interface Element Description

User Name

Enter the user name for a valid database account on the database where the SQL query is to be run. Make sure that the specified account has the requisite privileges to run the SQL query.

Password

Enter the password associated with the User Name.


Table 4-8 Create User-Defined Metric Page: Threshold

User-Interface Element Description

Comparison Operator

Select the comparison method Enterprise Manager should use to compare the value returned by the SQL query or function call to the threshold values. When the query returns two columns, the second column (value column) will be used for comparison against threshold values.

Available Comparison Operators

Operator Value Metric Type Description

= Number equal to

> Number greater than

< Number less than

>= Number greater than or equal to

<= Number less than or equal to

!= Number not equal to

CONTAINS String contains at least

MATCH String exact match

Warning

The value returned by the SQL query or function call is compared to the Warning threshold value using the specified comparison operator. If this comparison holds true, an alert triggers at the warning severity level.

Specifically, an alert triggers at the warning severity level if the following comparison is true:

<query_value> <comparison_operator> <warning_threshold>

and if the consecutive occurrences preceding notification has been reached.

Critical

The value returned by the SQL query or function call is compared to the Critical threshold value using the specified comparison operator. If this comparison holds true, an alert triggers at the critical severity level.

Specifically, an alert triggers at the critical severity level if the following comparison is true:

<query_value> <comparison_operator> <critical_threshold>

and if the consecutive occurrences preceding notification has been reached.

Warning Thresholds by Key

and

Critical Thresholds by Key

For queries returning two columns (the first column is the key and the second column is the value), you can specify thresholds on a per key basis. The following example uses the following query:

SELECT ename FROM emp

Threshold settings for this example are shown.

Use the format key:value . Keys are case-sensitive.

  • Warning:500

  • Critical:300

  • Comparison Operator: <

  • Warning threshold by key: SMITH:250;JONES:400;CLARK:900

    The warning threshold is set to 250 for SMITH, 400 for JONES, and 900 for CLARK.

  • Critical threshold by key: SMITH:100;JONES:200;CLARK:500

    The critical threshold is set to 100 for SMITH, 200 for JONES, and 500 for CLARK.

    All other keys will use the threshold values specified in the Warning and Critical fields.

Consecutive Occurrences Preceding Notification

Consecutive number of times a returned value reaches either the warning or critical thresholds before an alert triggers at a warning or critical severity. This feature is useful when monitoring for sustained conditions. For example, if your script monitors the CPU load for a particular machine, you do not want to be notified at every CPU load spike. Instead, you are only concerned if the CPU load remains at a particular threshold (warning or critical) level for a consecutive number of monitoring cycles.

Response Action

Optional. Specify a script or command that will be executed if the user-defined metric generates a warning or critical alert. The script or command will be executed using the credentials of the Agent owner. Important: Only an Enterprise Manager Super Administrator can create/edit response actions for metrics.

For example, the Management Agent executes the response action if:

The Alert severity is Warning or Critical

AND

There is a change in severity (for example, warning -> critical, critical --> warning, clear --> warning or critical)

For more information, see Enterprise Manager online help.

Alert Message

Enter a custom message (up to 400 characters) to be used when an alert is sent. The default message uses %Key% and %value% variables to display the metric key and its returned value. The %Key% and %value% variables are case-sensitive.

For example, a payroll system alert for underpayment of salary might be defined as:

Underpaid Employee: %Key% has salary of %value%

If the SQL query returns 2 columns, you can use the %Key% variable to represent the key value and the %value% variable to represent the return value.

If the SQL query returns 1 column, only the %value% variable is applicable in the alert message.


The User-Defined Metric Schedule interface lets you specify the frequency at which the SQL query or function should be run.

SQL-Based User-Defined Metric Examples

For a database version 9i and higher, you can run the example queries as dbsnmp, which is the default monitoring user account for the Management Agent. On a 8.1.7 database (which does not have SELECT ANY DICTIONARY system privilege), you must grant dbsnmp the following privileges in order for the queries to run successfully:

For example #1:

grant select on sys.dba_tablespaces to dbsnmp;

grant select on sys.dba_data_files to dbsnmp;

grant select on sys.dba_free_space to dbsnmp;

For example #2:

grant select on sys.dba_extents to dbsnmp;

The above grant statements can be run as SYSDBA after logging in via "connect internal". The queries can also be run by any user who has been granted the DBA role.

Example 1: Query Returning Tablespace Name and Percent Used

This sample user-defined metric monitors the percentage of space used for dictionary managed permanent tablespaces. A DBA can use this as a reference on when to add datafiles for the tablespace.Oracle recommends setting a polling frequency of 30 minutes, warning threshold at 75, and critical threshold at 85.

Example 1 SQL

SELECT d.tablespace_name,

     round(((a.bytes - NVL(f.bytes,0))*100/a.maxbytes),2) used_pct

FROM   sys.dba_tablespaces d,

      (select tablespace_name, sum(bytes) bytes, sum(greatest(maxbytes,bytes)) maxbytes

         from sys.dba_data_files group by tablespace_name) a,

      (select tablespace_name, sum(bytes) bytes

         from sys.dba_free_space group by tablespace_name) f

WHERE d.tablespace_name = a.tablespace_name(+)

 AND d.tablespace_name = f.tablespace_name(+)

 AND NOT (d.extent_management = 'LOCAL' AND d.contents = 'TEMPORARY') 

Example 2: Query Returning Segment Name/Type and Extent Count

This sample user-defined metric checks for non-system table and index segments that are reaching a high number of extents. A high number of extents could indicate a segment with fragmentation and/or performance problems. A DBA can use this as a reference on when to call Segment Shrink or the Reorganization Wizard in Enterprise Manager.Oracle recommends setting a polling frequency of 24 hours, warning threshold at 1000, and critical threshold at 2000.

Example 2 SQL

SELECT decode(nvl(partition_name, ' '),

             ' ',   owner || '.' || segment_name || ' ' || segment_type,

             owner || '.' || segment_name || '.' || partition_name || ' ' || segment_type) as segment,

      count(extent_id) as extent_count

FROM dba_extents

WHERE (segment_type like 'TABLE%' OR segment_type like 'INDEX%') AND

     (owner != 'SYSTEM' AND owner != 'SYS')

GROUP BY owner, segment_name, partition_name, segment_type

ORDER BY EXTENT_COUNT DESC 

Example 3: Embed a Long SQL statement in a PL/SQL Routine

In situations where the SQL statement forming the SQL user-defined metric exceeds 1024 characters, you must embed the SQL statement in a PL/SQL routine. This must be carried out in three step. In this example, a long SQL statement is used to track tablespaces & free space in them and raise alerts if the free space falls below a user specified threshold. A 2-column SQLUDM is crated using this query.

Example 4-1 of a long (more than1024 characters) SQL statement that returns two values: tablespace_name (key) and free_mb(value)

Example 4-1 Long SQL Statement

select Tablespace, case when (MxAvail <= 15) and (MxFreeMB < 20000) then 'CRITICAL, '||MxUsed||'%' when (MxAvail <= 20) and (MxFreeMB < 20000) then 'WARNING, '||MxUsed||'%' else 'OK' end Error_Level, MxAvail,MxUsed,MxFreeMB,MxExdMB from (select nvl(b.tablespace_name, nvl(a.tablespace_name,'UNKNOWN')) as Tablespace, data_files as NumDBFs, mbytes_alloc as AllocMB, Round( mbytes_alloc-nvl(mbytes_free,0),0) as UsedMB, Round(nvl(mbytes_free,0),0) "AllocFreeMB", Round((( mbytes_alloc-nvl(mbytes_free,0))/ mbytes_alloc)*100,0) as AllocUsed, MaxSize_Mbytes as MxExdMB, Round(MaxSize_Mbytes - (mbytes_alloc-nvl(mbytes_free,0)),0) as MxFreeMB, Round((mbytes_alloc/MaxSize_Mbytes*100),0) as MxUsed, Round((MaxSize_Mbytes - (mbytes_alloc-nvl(mbytes_free,0)))/MaxSize_Mbytes *100,0) as MxAvail from ( select sum(bytes)/1024/1024 mbytes_free, max(bytes)/1024/1024 largest,tablespace_name from sys.dba_free_space group by tablespace_name ) a, ( select sum(bytes)/1024/1024 mbytes_alloc, sum(decode(maxbytes,0,bytes,maxbytes))/1024/1024 MaxSize_Mbytes, tablespace_name,count(file_id) data_files from sys.dba_data_files group by tablespace_name )b where a.tablespace_name (+) = b.tablespace_name order by 1)

Because a 2-column SQL UDM is being created (tablespace, free_space_in_MB), an array must created for the data being returned from this query, as shown in

Example 4-2 Creating an Array of Returned Values

CREATE OR REPLACE TYPE tablespace_obj as OBJECT

(

    tablespace_name VARCHAR2(256),

    free_mb NUMBER);

/


CREATE OR REPLACE TYPE tablespace_array AS TABLE OF tablespace_obj;

/

The next step is to embed the long SQL statement shown in Example 4-1 in a PL/SQL routine as shown in Example 4-3

Example 4-3 Embedded SQL in a PL/SQL Routine

CREATE OR REPLACE FUNCTION calc_tablespace_free_mb

RETURN tablespace_array

IS

    tablespace_data TABLESPACE_ARRAY := TABLESPACE_ARRAY();

BEGIN

    SELECT tablespace_obj(tablespace, mxfreemb)

        BULK COLLECT INTO tablespace_data

    FROM

(

select Tablespace, case when (MxAvail <= 15) and (MxFreeMB < 20000) then

 'CRITICAL, '||MxUsed||'%' when (MxAvail <= 20) and (MxFreeMB < 20000) then

 'WARNING, '||MxUsed||'%' else 'OK' end Error_Level,

 MxAvail,MxUsed,MxFreeMB,MxExdMB from (select nvl(b.tablespace_name,

 nvl(a.tablespace_name,'UNKNOWN')) as Tablespace, data_files as NumDBFs, mbytes

_alloc as AllocMB, Round( mbytes_alloc-nvl(mbytes_free,0),0) as UsedMB,

 Round(nvl(mbytes_free,0),0) "AllocFreeMB", Round((( mbytes_alloc-nvl(mbytes

_free,0))/ mbytes_alloc)*100,0) as AllocUsed, MaxSize_Mbytes as MxExdMB,

 Round(MaxSize_Mbytes - (mbytes_alloc-nvl(mbytes_free,0)),0) as MxFreeMB,

 Round((mbytes_alloc/MaxSize_Mbytes*100),0) as MxUsed, Round((MaxSize_Mbytes -

 (mbytes_alloc-nvl(mbytes_free,0)))/MaxSize_Mbytes *100,0) as MxAvail from (

 select sum(bytes)/1024/1024 mbytes_free, max(bytes)/1024/1024 largest,tablespace

_name from sys.dba_free_space group by tablespace_name ) a, ( select

 sum(bytes)/1024/1024 mbytes_alloc,

 sum(decode(maxbytes,0,bytes,maxbytes))/1024/1024 MaxSize_Mbytes, tablespace

_name,count(file_id) data_files from sys.dba_data_files group by tablespace_name

 )b where a.tablespace_name (+) = b.tablespace_name order by 1)

) CUSTOMER_QUERY;


  RETURN tablespace_data;

END calc_tablespace_free_mb;

/

The final step in the process is to create the 2-column UDM with the following:

  • Metric Type: NUMBER

  • SQL Query Output: Two Columns

  • SQL Query:

    SELECT tablespace_name, free_mb
    
    FROM TABLE(CAST(calc_tablespace_free_mb as TABLESPACE_ARRAY))
    
    

Notifications, Corrective Actions, and Monitoring Templates

User-Defined Metrics, because they are treated like other metrics, can take advantage of Enterprise Manager's notification system, corrective actions and monitoring templates.

Note:

Corrective actions and monitoring templates support both OS user-defined metrics and SQL-based user-defined metrics that return single scalar values.

Getting Notifications for User-Defined Metrics

As with regular metrics, you can receive e-mail notifications when user-defined metric critical or warning alert severities are reached. Assuming you have already defined your e-mail addresses and notification schedule, the remaining task is to set up a notification rule for the user-defined metric.

To set up notification rules:

  1. Click Preferences.

  2. From the vertical navigation bar, click Rules if you are a Super Administrator or My Rules if you are a regular Enterprise Manager administrator.

  3. Click Create to define a new notification rule. The Create Notification Rule pages appear.

  4. From the General page, enter the required rule definition information and choose Target Type Host for OS-based user-defined metrics or choose Database Instance or Cluster Database for SQL-based user-defined metrics.

  5. On the Metrics page, click Add. A list of available metrics appears. To view all metrics simultaneously, choose Show All from the drop-down menu.

  6. Select User-Defined Numeric Metric or User-Defined String Metric based on the type of value returned by your user-defined metric.

  7. In the Objects column, choose whether you want to receive notification for all user-defined metrics (All Objects) or specific user-defined metrics (Select).

    image shows the Add Metric page for a UDM

    When choosing the Select option, enter the name of the user-defined metric, or specify multiple user-defined metrics separated by commas. You can use the wildcard character (%) to match patterns for specific user-defined metrics.

    You can search for available user-defined metrics using the search function (flashlight icon). However, search results will only show user-defined metrics that have at least one collected data point. For metrics that have not yet collected at least one data point, as may be the case for a newly created user-defined metric, you must specify them in the Select text entry field.

    The format used to specify individual user-defined metrics in the Select text entry field depends on the type of value(s) returned. The following three examples illustrate how to specify user-defined metrics for three possible return value situations.

    Formats shown in the following table summarize how to specify multiple user-defined metrics returning single and double values. Specific examples follow.

    Return Values Select Text Entry Field Format
    Single MY_UDM1,MY_UDM3,MY_UDM5
    Double (2-column), All Key Values MY_UDM1;%,MY_UDM2;%,MY_UDM4;%

    User-defined metrics returning two columns are specified as <UDM Name>;% where "%" is a wildcard signifying ALL key values associated with that user-defined metric.

    Note: 2-column values apply to SQL user-defined metrics only.

    Double (2-column), Specific Key Values MY_UDM1;K1,MY_UDM1_K2

    Note: 2-column values apply to SQL user-defined metrics only.


    Example 1: Host User-Defined Metrics Returning a Single Value

    In this example, you will receive notifications for two host user-defined metrics: MY_UDM9 and MY_UDM11. Both metrics return numeric values. To receive notifications for all host user-defined metrics, select the All Objects (Script) option where Script refers to the user-defined metric name.

    Graphic shows a host UDM retruning a single value.

    Example 2: SQL User-Defined Metric Returning a Single Value

    In this example, you will receive notifications for four SQL user-defined metrics: MY_UDM1 and MY_UDM3 (numeric value returned) and MY_UDM5 and MY_UDM7 (string value returned). To receive notifications for all SQL user-defined metrics, select the All Objects (Script) option where Script refers to the user-defined metric name.

    Graphic shows a SQL UDM returning a single value.

    Example 3: SQL User-Defined Metrics Returning Two Values

    To receive notifications for SQL user-defined metrics returning two values, the syntax is slightly different. In this situation, you specify the user-defined metric using the following format: <Metric ID;Key> where Metric ID is the name of the user-defined metric and Key is the specific key value you want returned. To receive notifications for all SQL user-defined metrics, select the All Objects (Metric ID;Key) option.

    graphic shows 2-column udm for all keys and specific keys

    For SQL user-defined metrics returning two values, you have the option of receiving notifications for all key values or just specific values for a given metric. Use a wildcard character (%) to specify all key values. Example: MY_UDM1;%, MY_UDM2;%.

    To receive notifications for specific key values, specify the exact key value to be returned. Example: MY_UDM5;K1,MY_UDM5;K2 (only key values K1 and K2 are returned from MY_UDM5).

  8. Select the severity or corrective action state for which you would like to receive the notification and then click Continue.

  9. If you want to receive e-mail for the specified user-defined metric, go to the Notification Rule and check the "Send me E-mail" option.

  10. Click OK to create the new notification rule. If you made the notification rule public, other administrators can subscribe to the same rule.

Setting Corrective Actions for User-Defined Metrics

Corrective actions allow you to specify automated responses to alerts ensuring that routine responses to alerts are automatically executed. Corrective actions can be defined for both SQL and OS-based user-defined metrics.

To set up corrective actions:

  1. From a target home page, click Metric and Policy Settings from Related Links.

  2. Locate and edit the user-defined metric.

  3. From the Edit Advanced Settings page, click Add under Corrective Actions for the Critical or Warning alert severity and define the corrective action. Corrective actions can be defined for one or both alert severities.

Deploying User-Defined Metrics Across Many Targets Using Monitoring Templates

Monitoring Templates simplify the task of standardizing monitoring settings across your enterprise by allowing you to specify the monitoring settings once and applying them to your monitored targets. You can thus use Monitoring Templates as a way to propagate user-defined metrics across a large number of targets.

Assuming you have created the user-defined metric on the host or database target, you can use Monitoring Templates to propagate the user-defined metric to other hosts or database targets.

To create a Monitoring Template for the user-defined metric:

  1. Click Setup.

  2. From the vertical navigation bar, click Monitoring Templates

  3. Click Create. The Copy Target Settings page appears.

  4. Specify the host or database on which you defined the user-defined metric and click Continue.

  5. Fill in the requisite information on the General page.

  6. On the Metric Thresholds page, you can choose to keep or remove the other metrics that have been copied over from the target.

  7. You can also edit the user-defined metric's thresholds, collection schedule, and corrective actions.

  8. On the Policies page, you can choose to keep or remove any policy rules that have been copied over from the target.

  9. Click OK to save the template settings to the Management Repository.

Once the template containing the user-defined metric has been created, you can propagate the user-defined metric by applying the template to other hosts or databases.

Important:

For OS-based user-defined metrics, you will first need to separately deploy the OS Script used by the user-defined metric to all destination hosts. The OS Script should reside in the same location across all host targets.

For SQL-based user-defined metrics, if the SQL query specified is a function call, then you need to create this function across all databases on which the SQL-based user-defined metric will be created.

To apply the monitoring template:

  1. On the Monitoring Templates page, select the monitoring template and click Apply.

  2. On the Apply Monitoring Template page, add the targets on which the user-defined metric should be created.

  3. If a two-column SQL-based user-defined metric is part of the template, the Metric with Multiple Thresholds option is applied according one of the following situations:

    • Situation One: The target to which the template will be applied does not contain the two-column SQL user-defined metric defined in the template. In this situation, regardless of which Metric with Multiple Thresholds option is chosen, the user-defined metric is copied to the target when you apply the template.

    • Situation Two: The target to which the template will be applied does contain the two-column SQL user-defined metric. The name (case insensitive) and return value (numeric, scalar, or two column) of both the target and template user-defined metrics must match. In this situation, you must select one of the Metric with Multiple Thresholds options:

      • Apply threshold settings for monitored objects common to both template and target: For only those keys which the target has in common with the template, the target threshold values will be set to the values defined in the template. This option is chosen by default and is recommended for most situations.

      • Duplicate threshold settings on target: For keys which are common between target and template, the thresholds will be set to the values defined in the template. Any extra keys (and their thresholds) that exist in the template but not on the target will be copied to the target in anticipation that these keys will be created in the target at some point in the future. Any extra keys (and their thresholds) that exist on the target but not in the template will be deleted from the target.

    Important: If there are other metrics in the monitoring template, refer first to the Enterprise Manager online help for implications of what this option means for these other metrics.

  4. Click Continue.

  5. On the subsequent page, specify the credentials that should be used when running the user-defined metric on the destination targets.

  6. Click Finish.

  7. When you return back to the Monitoring Templates page, check that "Pending Apply Operations" count for your template is zero. This indicates the number of template apply operations that could be pending. Once they are all complete, the count should be zero.

Deploying User-Defined Metrics Using Scripts

An alternate method of deploying user-defined metrics to large numbers of targets is to use the Enterprise Manager Command Line Interface (EMCLI). Using the EMCLI "apply_template" verb, you can deploy user-defined metrics via custom scripts. For more information about the "apply_template" verb, see the Oracle Enterprise Manager Command Line Interface manual.

Deleting User-Defined Metrics Across Many Targets Using Monitoring Templates

Just as templates can be used to deploy user-defined metrics across targets, templates can also be used to delete these metrics across targets should these metrics no longer be in use.

To create a Monitoring Template for the user-defined metric:

  1. Click Setup.

  2. From the vertical navigation bar, click Monitoring Templates

  3. Click Create. The Copy Target Settings page appears.

  4. Specify the host or database on which there is a user-defined metric that needs to be deleted and click Continue.

  5. Fill in the requisite information on the General page.

  6. On the Metric Thresholds page, remove all metrics from the template except the user-defined metric to be deleted.

  7. Click the pencil icon to access the Edit Advanced Settings page.

  8. Check the Mark for Delete option and click Continue. The Mark for Deletion icon now appears next to the user-defined metric on the Metric Thresholds page.

  9. On the Policies page, remove all policies.

  10. Click OK to save the template settings to the Management Repository.

To apply the monitoring template:

  1. On the Monitoring Templates page, select the monitoring template and click Apply.

  2. On the Apply Monitoring Template page, add the targets on which the user-defined metric should be deleted.

  3. Click Continue.

  4. On the subsequent page, specify the credentials that should be used when running the user-defined metric on the destination targets.

  5. Click Finish.

  6. On the Monitoring Templates page, check that "Pending Apply Operations" count for your template is zero. This indicates the number of template apply operations that could be pending. Once they are all complete, the count should be zero.

Enterprise manager will delete all user-defined metrics found on the selected target that match the following criteria:

  • Name of the user-defined metrics (case insensitive)

  • Return value of the user-defined metric (numeric or scalar)

  • For SQL-based user-defined metrics, the output of the query (single value or two columns). The match does not take into consideration the actual script used by the user-defined metric. For this reason, even though the script on the target user-defined metric may be different from that of the template user-defined metric, the target user-defined metric will still be deleted.

  • Host User-Define Metrics using scripts: You must delete the script from the host on which you want the UDM deleted.

  • SQL-based user-defined metrics using function calls: You must delete the function from the database on which you want the user-defined metric deleted.

Changing User-Defined Metric Credentials

As discussed earlier, user-defined metrics require valid credentials (username and password) in order to execute monitoring scripts/SQL queries. For this reason, both the monitored target's password and the password defined in the user-defined metric must match. This can be problematic if target passwords are changed frequently. For environments with a large number of targets, you can use the Enterprise Manager Command Line Interface (EMCLI) to change the target password and user-defined metric password simultaneously using scripts. Use the "update_password" verb to change the target password. This password change is then propagated to all features of Enterprise Manager that use the specified username, which includes preferred credentials, corrective actions, jobs, and both host and SQL-based user-defined metrics.

The following example changes the password associated with the OS user sysUser from sysUserOldPassword to sysUserNewPassword.

Example 4-4 Host Password Change

update_password   -target_type=host  -target_name=MyHost  -credential_type=HostCreds  -key_column=HostUserName:sysUser-non_key_column=HostPassword:sysUserOldPassword:sysUserNewPassword

The next example changes the password associated with the database user sys from sysPassword to sysNewPassword.

Example 4-5 Database Password Change

update_password -target_type=oracle_database -target_name=ORCL -credential_type=DBCreds -key_column=DBUserName:sys-non_key_column=DBPassword:sysPassword:sysNewPassword:DBAROLE

For more information about EMCLI, see the Oracle Enterprise Manager Command Line Interface guide.