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.

webserver_alert.ksh

#!/bin/ksh

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

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

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

   # 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"\
      don@mydomain.com\
      omar@otherdomain.com\
      carlos@otherdomain.com

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 oracheck.run 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
ORA-00600
ORA-1631
ORA-1650
ORA-1652
ORA-1653
ORA-00447
ORA-00603
ORA-01092
ORA-02050
ORA-1535

* oracheck.run?This 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 oracheck.run script and the get_dict_parm.sql script is located at the Oracle Press web site at www.oraclepressbooks.com.

For the purpose of illustration, we have broken this script into sub components. Here is the first section of the oracheck.run 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.

#!/bin/ksh

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

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

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

ORACLE_SID=${1}
export ORACLE_SID

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

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 ]
then
   TS_FREE=`cat ${ora_unix_home_dir}/mon/parm_ts_free_$ORACLE_SID.ora`
else
   TS_FREE=`cat ${ora_unix_home_dir}/mon/parm_ts_free.ora`
fi
if [ -f ${ora_unix_home_dir}/mon/parm_num_extents_$ORACLE_SID.ora ]
then
   NUM_EXTENTS=\
`cat ora_unix_home_dir}/mon/parm_num_extents_$ORACLE_SID.ora`
else
   NUM_EXTENTS=`cat ${ora_unix_home_dir}/mon/parm_num_extents.ora`
fi

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

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='steven@yourdomain.com, joanne@yourdomain.com,
ngene@yourdomain.com' ;;
     "PUMP1" )
               dbalist='shri@yourdomain.com, \
               sash@yourdomain.com, steven@yourdomain.com, \
               richard@yourdomain.com, engene@yourdomain.com' ;;
esac

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 ]
 then
 exit 0
fi

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

#*************************************************************
# 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 ]
 then
 exit 0
fi

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

exit
!

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.

get_dict_parm.sql
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
select
   'cd '||value||'; df -k .|grep ora|awk ''{print $3}'''
from
   v$parameter
where
   name like '%dump%'
and
   value like '%/%'
;
spool off;

set linesize 500

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

select
   value
from
   v$parameter
where
   name = 'background_dump_dest'
;

spool off;

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

select
   value
from
   v$parameter
where
   name = 'log_archive_start'
;

spool off;

--*****************************************************
spool /tmp/log_archive_dest_&&2..ora;
select
   substr(value,1,instr(value,'/',-1))
from v$parameter
where
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 > \
   tmp/dump1_$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 ]
then
  new=`echo $ALERT_DIR|cut -d? -f2`
  ALERT_DIR=${ORACLE_HOME}$new
fi

 

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.