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.
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.
Features
Application
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:
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. The SlideShow below explains how this script functions.
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./li>
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.