Data Dictionary   «Prev  Next»
Lesson 4Building a quick Oracle alert monitor
Objective See how to build an alert monitor.

Advantage of using Oracle Alert monitor

Most DBAs today are responsible for many dozens of databases, and do not have the luxury of constantly monitoring each one.
We have provided a script that can be made to alert the DBA about potential problems.

Writing modifiable alert scripts

View the Code below to see the entire script.
Alert View 
drop table oracheck_fs-temp;
CREATE TABLE oracheck_fs_temp
(tablespace_name, total_bytes, free_bytes, max_chunk)
AS
 SELECT tablespace_name, NVL(SUM(bytes), l ) l , l
 FROM dba_data_files
 GROUP BY tablespace_name;

This script has several useful features.
FeaturesApplication
The parameters can be changed.You can choose to generate a tablespace-full alert when the tablespace becomes a 90% full, or when it is 95% full.
If there are no alerts, the SQL prodiuces no output
  1. You can execute this task hourly within a crontab and spool the output to a flat file.
  2. The script checks the file for lines of content.
  3. If the file is not empty, the Unix mail facility sends the alert.

Using an alert script, the DBA can monitor thousands of Oracle databases, and every alert will be sent to the DBA via email.
Let us examine how we build an alert script in the simulation below:


Building an alert Script

  1. When building an alert script, we must first create a temporary table.
    Type CREATE TABLE oracheck_fs_temp to create a temporary table and hit Enter. The rest of the code containing the tablespace name and the total bytes will be completed for you.
  2. The next step in building our alert log is to update the free_bytes column of our table by computing the number of free_bytes in the tablespace. You will see that we are updating our temporary table. <p>Type SET a.free_bytes, and hit Enter. The rest of the code will be completed for you. alt text (for Non-Java sidebar)
  3. Next we update the max_chunk column by computing the largest piece of free space in the tablespace. Type (SELECT NVL(MAX(b.bytes), 1) and hit Enter. We now have everything that we need to compute our alerts.
  4. Next, we display a list of all tablespaces that are more than 95% full. Enter the following text WHERE 95 < 100-(free_bytes*100/total_bytes) and hit Enter.
  5. Above, we can easily display tables and indexes that do not have room to take another extent. This alert gives the DBA time to add to the tablespace or shrink the next extent size before the object fails to extend.
  6. Next we display any object that is more than 600 extents. Of course, many DBAs use the MAXEXTENTS UNLIMITED clause to ensure that the table never locks-up with a CANNOT EXTEND failure. However, our alert is still useful, because a good DBA always sets the INITIAL extent for a table to hold the normal usage of the table, and we want to be alerted if any table or index is growing at an unexpected rate. This completes the Simulation. Click the Exit button.

Sending alert output to DBA

Now that we see how the script functions, let us examine a mechanism to send the output, if any, to the DBA. As we have noted, if there are no alerts, the SQL produces no output. sThe following series of images explains how this script functions.
1) We have spooled the output from our script to /tmp/oracheck.ora.
1) In this example, we have spooled the output from our script to /tmp/oracheck.ora. The first IF statement will be true if this file contains more than one line of output.

2) Smtp Alert: If errors exist, then we invoke a UNIX script called console_alert to send an on-screen message
2) SMTP Alert: If errors exist, then we invoke a UNIX script called console_alert to send an on-screen message to our operators so they know about the error. This is useful if the alert happens at night when the DBA many not see the email.
3) Email Alert: We send the DBA an alert email.
3) Email Alert: We send the DBA an alert email. Note that we use the UNIX 'cat' command to list the file and we pipe the file as input to the mail command. Our email has the subject line $ORACLE_SID alert detected and is sent to all DBAs who are defined in the $dba variable.

Sending alert output to the DBA

This feature allows the DBA to drill down into important Oracle performance areas including
  1. instance efficiency,
  2. SQL response time,
  3. SGA pool wastage, and
  4. wait bottlenecks.

There is more to the data collection than instance-wide metrics and OEM can be customized to send alerts for whatever combination of metric values desired.

  1. In this example, we have spooled the output from our script to /tmp/oracheck.ora.
  2. Smtp Alert: If errors exist, then we invoke a UNIX script called console_alert to send an on-screen message to our operators so they know about the error.
  3. Email Alert: We send the DBA an alert email. Note that we use the UNIX 'cat' command to list the file and we pipe the file as input to the mail command.


If you have any questions or comments regarding the alert script functions, then click on the Search button at the top of the screen. The next lesson wraps up this module.