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.
This script has several useful features.
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
You can execute this task hourly within a crontab and spool the output to a flat file.
The script checks the file for lines of content.
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
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.
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)
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.
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.
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.
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.
Sending alert output to the DBA
This feature allows the DBA to drill down into important Oracle performance areas including
SQL response time,
SGA pool wastage, and
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.
In this example, we have spooled the output from our script to /tmp/oracheck.ora.
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.
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.