Remote DBA Plans
Remote DBA Service

Web Server Alert Report

Oracle Tips by Burleson Consulting

In a production web environment, it is often useful to alert the staff whenever a program aborts. The script that follows is generally executed every five minutes. The webserver_alert.ksh script can be customized to search the dump file location for any Pro*C, C++, or Perl programs. The script requires these modifications:

Change the e-mail addresses to match those belonging to 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 script is simple, but quite important. 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@remote-Remote DBA.net\
      omar@oracle.com\
      carlos@oracle.com

Next, let’s examine a daemon script that can poll every five minutes for buffer busy waits.

Buffer Busy Waits Alerts

One of the shortcomings of STATSPACK is that it measures waits only at the system-wide level. If you want to track buffer busy waits and sequential read waits with the block_id, you need to run a real-time script every five minutes to search for such waits. Once you get the block ID, you can dump the block, get the name of the object, and add additional freelists to the object.

The get_busy.ksh script displays any buffer busy waits that happen to be occurring when the script is executed and e-mails the information to the Oracle Remote DBA. The Remote DBA can then use the details provided in Chapter 10 to locate the specific block where the buffer busy wait occurred.

This short script performs these steps:

1. First, the script sets the sample time between checks.

2. It then queries the v$session_wait view, looking for buffer busy waits and db sequential file read waits.

3. If either type of wait is found, the script e-mails the waits to the Remote DBA for further investigation.

get_busy.ksh

#!/bin/ksh

# First, we must set the environment . . . .
ORACLE_SID=PRODMN1
export ORACLE_SID
ORACLE_HOME=`cat /etc/oratab|grep \^$ORACLE_SID:|cut -f2 -d':'`
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH
MON=`echo ~oracle/mon`
export MON
ORA_ENVFILE=${ORACLE_HOME}/${ORACLE_SID}.env
. $ORA_ENVFILE

SERVER_NAME=`uname -a|awk '{print $2}'`
typeset -u SERVER_NAME
export SERVER_NAME

# sample every 10 seconds
SAMPLE_TIME=10

while true
do

rm -f /home/oracle/statspack/busy.lst

$ORACLE_HOME/bin/sqlplus -s / <<!>/home/oracle/statspack/busy.lst

set feedback off;
select
sysdate,
substr(tablespace_name,1,14),
p2
from v\$session_wait a, Remote DBA_data_files b
where
a.p1 = b.file_id
and
event = 'db file sequential read'
;

select
sysdate,
substr(tablespace_name,1,14),
p2
from v\$session_wait a, Remote DBA_data_files b
where
a.p1 = b.file_id
and
event = 'buffer busy waits'
;

!

var=`cat /home/oracle/statspack/busy.lst|wc -l`

echo $var
if [[ $var -gt 1 ]];
then
echo
**********************************************************************"
echo "There are waits"
cat /home/oracle/statspack/busy.lst|mailx -s "Monona block wait found"\
dburleson@doglog.com \
dhurley@oracle.com \
echo
**********************************************************************"
exit
fi

sleep $SAMPLE_TIME
done

Once started, this script is scheduled to check every 10 seconds so that problems can be quickly identified and resolved. Here is an actual report from the e-mail:

TO_CHAR(SYSDATE,'Y
------------------
EVENT SUBSTR(TABLESP
----------------------------------------------------------- --------------
P2
----------
000-12-30 00:15:52
buffer busy waits APPLSYSD
26716

This is an excerpt from "Oracle9i High Performance tuning with STATSPACK" by Oracle Press.

If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts.

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