Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 









Oracle UNIX Administration Web Server Alert Report

Oracle UNIX/Linux Tips by Burleson Consulting

Web Server Alert Report

In a production Web environment, it is often useful to alert the staff whenever an Oracle-related program aborts. This script is generally executed via a cron every five minutes, and this code can be extended to locate core or trace file in any UNIX file location. IN this example, the webserver_alert.ksh script can be customized to search the dump file location for any Pro*C, C++, or perl programs. This script requires the following modifications:

* Change the e-mail addresses to match the people who want to be notified of program dumps.

* Change /usr/src/asp/core to the name and location of core files on your Web server.

This is a simple but quite important script. It searches for a core file and instantly e-mails it to alert the staff about a production abort.



MYDATE=`date +"%Y%m%d"`

SERVER=`uname -a|awk '{print $2}'`

if [ -f /usr/src/asp/core ]

   # Move the file to a dated location . . .
   mv /usr/src/asp/core /tmp/core_$MYDATE

   # send an e-mail to the administrator
   head /tmp/core_$MYDATE|\
   mail -s "EMERGENCY - WebServer $SERVER abort in /tmp/core_$MYDATE"\\\

As we see, this type of script instantly alerts Oracle developers of the presence of trace file, and e-mails them the information that they need to solve the production problem. Next, let?s look at a real-time alert script that can warn the DBA about impending problems in the Oracle environment.

A Real-Time Check for Oracle Problems

The script is usually scheduled to run hourly in a production environment to report on any exception condition that may jeopardize the Oracle database. This script is quite sophisticated and contains four parameter files that control the level of reporting. The parameter files for this script include:

* parm_mount_point_kb_free.ora?This file contains the threshold for any Oracle mount point. If you are using tablespaces with AUTOEXTEND ON, you must constantly monitor the UNIX mount points to prevent Oracle from hanging on a failure to extend problem.

* parm_ts_free.ora?This file contains the threshold for reporting on full tablespaces.

* parm_num_extents.ora?This file contains the number by which a table or index?s extents cannot exceed. For example, placing 600 in this file will cause the DBA to be e-mailed when any object exceeds 600 extents.

* parm_alert_log.ora?This file contain alert log messages that should be reported to the DBA. Below is a common list for this file:

>cat parm_alert_log.ora

* is a Korn shell script that reports on anything that might cause the database to hang up or crash. The idea behind this script is to allow the DBA to repair impending problems before that database crashes. Here are the checks that are performed by this script:

     * Alert log messages?This script e-mails any alert log messages that are found in the alert log. The parameter file parm_alert_log.ora contains a list of alert log messages to be reported.

     * Low free space in archived redo log directory?If the archived redo log directory become full, our Oracle database will hang up. This alert allows the Oracle DBA to add space before the database hangs.

     * UNIX mount point space alert?The script checks all datafile mount points in Oracle, including the UNIX Oracle home directory. Since most databases now use AUTOEXTEND ON, the DBA must be constantly alert for file systems that may not be able to extend. If the free space in any mount point is less than specified in parm_mount_point_kb_free.ora, an e-mail alert will be sent to the DBA.

     * Object cannot extend?This report will alert the Oracle DBA whenever an Oracle table or index does not have room to take another extent. This alert is obsolete if you are using tablespaces with AUTOEXTEND ON, but many DBAs still keep this alert because they want to monitor the growth of the database tables and indexes.

     * Tablespace > nn% free?This report sends an e-mail alert whenever any tablespaces contain less space than specified by parm_ts_free.ora. Again, this alert is obsolete when using AUTOEXTEND ON, but many DBAs still want to see the available space within each tablespace.

     * Object > nnn extents?This report is very useful for reporting tables and indexes that experience unexpected growth. Whenever a table or index exceeds the number defined in parm_num_extents.ora, an e-mail alert will be sent to the DBA.

Here is an actual sample of the e-mail output from this script:

NON-EMERGENCY ORACLE ALERT. Mount point /home has less than 250000 K-Bytes free.

The full text for the script and the get_dict_parm.sql script is located at the Oracle Press web site at

For the purpose of illustration, we have broken this script into sub components. Here is the first section of the script.

Preliminary checking and set-up

This section of the script ensures that a proper $ORACLE_SID has been passed to the script.  We do this by comparing the passed value to the $ORACLE_SID in the oratab file.  Note that for Solaris, you need to change the /etc directory to /var/opt/oracle directory.


# Ensure that the parms have been passed to the script
if [ -z "$1" ]
   echo "Usage: <ORACLE_SID>"
   exit 99

var=`cat /etc/oratab|grep -v "#"|cut -d: -f1|grep ${1}|wc -l`

oracle_num=`expr $var`
if [ $oracle_num -ne 1 ]
 echo "The variable ${1} is not a valid ORACLE_SID.  Please retry."
 exit 0


ORACLE_HOME=`cat /etc/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'`

Get the UNIX home directory

This section of the script gets the name of the oracle UNIX user home directory.  We do this my using the grep utility to find the line in /etc/passwd for the oracle user.

# Get the Oracle users home directory from /etc/passwd
ora_unix_home_dir=`cat /etc/passwd|grep ^oracle|cut -f6 -d':'`
#echo home dir = $ora_unix_home_dir

Get the free space limits

This section of the script gathers the minimum allowed free space from our parm_ts_free.ora file. This value will be used to trigger an e-mail alert if the UNIX directory has too little free space.

# Here we gather the values from the parm files . . .
if [ -f ${ora_unix_home_dir}/mon/parm_ts_free_$ORACLE_SID.ora ]
   TS_FREE=`cat ${ora_unix_home_dir}/mon/parm_ts_free_$ORACLE_SID.ora`
   TS_FREE=`cat ${ora_unix_home_dir}/mon/parm_ts_free.ora`
if [ -f ${ora_unix_home_dir}/mon/parm_num_extents_$ORACLE_SID.ora ]
`cat ora_unix_home_dir}/mon/parm_num_extents_$ORACLE_SID.ora`
   NUM_EXTENTS=`cat ${ora_unix_home_dir}/mon/parm_num_extents.ora`

if [ -f ${ora_unix_home_dir}/mon/parm_mount_point_kb_free_$ORACLE_SID.ora ]
`cat {ora_unix_home_dir}/mon/parm_mount_point_kb_free_$ORACLE_SID.ora`
   KB_FREE=`cat ${ora_unix_home_dir}/mon/parm_mount_point_kb_free.ora`

Get the e-mail lists

This section of the script creates a separate e-mail list based on the $ORACLE_SID. By allowing different e-mail list for each database, the same script can be run in large environments where we have dozens of DBAs, each with many databases. This section ensures that the appropriate DBA gets the alert e-mail message.

# E-mailx setup
# Here we setup the $dbalist variable to send messages to the right DBA's

case $ORACLE_SID in
     "PRODV" )
               dbalist=',,' ;;
     "PUMP1" )
               dbalist=', \
     ,, \
     ,' ;;

Terminate the script if the database is down

This section of the script skips any database that is not running at the time the script executes.  By doing this, we avoid false errors messages in a large environment, where a database might be down for a cold backup or DBA maintenance.

We perform a double check, first checking to see if the PMON background process is running, and another that attempts a connection to the database.  If either fails, the script terminates.

# Let's exit immediately if the database is not running . . . 
check_stat=`ps -ef|grep ${ORACLE_SID}|grep pmon|wc -l`;
oracle_num=`expr $check_stat`
if [ $oracle_num -lt 1 ]
 exit 0

# Test to see if Oracle is accepting connections
$ORACLE_HOME/bin/sqlplus -s /<<! > /tmp/check_$ORACLE_SID.ora
select * from v\$database;

# If not, exit immediately . . . 
check_stat=`cat /tmp/check_$ORACLE_SID.ora|grep -i error|wc -l`;
oracle_num=`expr $check_stat`
if [ $oracle_num -gt 0 ]
 exit 0

Cleanup old holding files

This section of the script removes all of the UNIX files from the last execution of the script.  Note that it is a good idea in UNIX to use the /tmp directory.  This is because the UNIX systems administrator will periodically remove all files in this directory and you do not have to worry about leaving ?junk? files in UNIX directories.

# Remove the prior files from /tmp . . . 
rm -f /tmp/alert_log_dir_${ORACLE_SID}.ora
rm -f /tmp/log_archive_start_${ORACLE_SID}.ora
rm -f /tmp/log_archive_dest_${ORACLE_SID}.ora
rm -f /tmp/dump*${ORACLE_SID}.ora
rm -f /tmp/ora600_${ORACLE_SID}.ora
rm -f /tmp/arch_${ORACLE_SID}.ora

Get dictionary file locations

This section of the script executes get_dict_parm.sql to get the values for the archived redo log directory, the location of the alert log, and whether the database is running in ARCHIVELOG mode.

# Get details from Oracle dictionary
$ORACLE_HOME/bin/sqlplus -s /<<!

@${ora_unix_home_dir}/mon/get_dict_parm $ORACLE_HOME $ORACLE_SID


Here is a listing of the SQL script.  Note that it must be called as a sub-program because of the special characters in the script.  Note how it spools the values from the dictionary into the UNIX /tmp directory, where later sections of the script to read the file names.  The UNIX file names are segregated according to the &&2 parameter, which is set to the value of the $ORACLE_SID.  This allow simultaneous executions of this script on UNIX servers that have multiple Oracle databases.

set linesize 80
set pagesize 0
set echo off
set space 0;

set termout off
set feedback off;
set verify off;

spool /tmp/dump_&&2..ora
   'cd '||value||'; df -k .|grep ora|awk ''{print $3}'''
   name like '%dump%'
   value like '%/%'
spool off;

set linesize 500

spool /tmp/alert_log_dir_&&2..ora;

   name = 'background_dump_dest'

spool off;

spool /tmp/log_archive_start_&&2..ora;

   name = 'log_archive_start'

spool off;

spool /tmp/log_archive_dest_&&2..ora;
from v$parameter
name = 'log_archive_dest'

spool off;

Get the fully-qualified UNIX path names for directories

This section of the script gets the full path name for the UNIX directories. The tricky part of this is when Oracle uses a question mark, and not the fully-qualified UNIX path name to the directory.  In this case, we need to prefix the name with the value from the $ORACLE_HOME variable.

# If the first character of the dump directory is a question-mark (?)
# then replace it with $ORACLE_HOME
sed 's/?/$ORACLE_HOME/g' /tmp/dump_$ORACLE_SID.ora > \

ALERT_DIR=`cat /tmp/alert_log_dir_${ORACLE_SID}.ora|awk '{print $1}'`
export ALERT_DIR

# If the first character of the alert ora directory is a question-mark (?)
# then prefix with $ORACLE_HOME

first_char=`echo $ALERT_DIR|grep ^?|wc -l`
first_num=`expr $first_char`
#echo $first_char
if [ $first_num -eq 1 ]
  new=`echo $ALERT_DIR|cut -d? -f2`


If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.


Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational