BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 
 

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.


Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.

 

   

 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.



Hit Counter