Creating alerts in OEM DB Control 11G

In the Oracle Enterprise Management you can create alerts for users. This alerts can be  OS_BASED (using scripts) or SQL_BASED (using PL/SQL queries). In this post  I will show an example about this alerts.

• OS_BASED: For to do an alert using scripts , before you must do a script  to put it in the OEM metrics. Then, I will use a script from Oracle’s Documentation:

#!/usr/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=$2n”;

} else {

die “Error collecting datan”;

}

What does this script do? It shows the load average in Operating System.

Ok, we have a script and this script return a value. Now, we have to define the metrics in OEM.

Then…

• Click “Database”
• Home -> Click “Host”
• Click “User-Defined Metrics”
• Click “Create”

In “User-Defined Metrics” Page , you must complete the informations (Metric Name, Metric Type, Command Line ,Operating System Credentials,  Threshold , Schedule and Frequency ).

• After complete the informations, click “OK” and we have the OS-BASED created.

Is it Ok? Let’s wait 15 minutes (the frequency that I choose ) …

It works!

Now we will have the alerts using SQL or PL/SQL (SQL_BASED).

• Click “Database
• Click “User-Defined Metrics”
• Click “Create”

In the next page , as OS-BASED alerts, you must complete some informations. But now the difference it is in the SQL Query (where you must pass a SQL ). The SQL That I will use it is from Oracle’s documentation:

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');


This script will return the tablespace name and percent used.

•  Click “OK” and now your alert was created.

I hope this post can help you!

References:
Oracle Enterprise Manager Documentation, 11g Release 1 (11.1) Enterprise Manager Administration 4 User-Defined Metrics

brunors