RE: Monitoring script

From: Afanassiev, Alex <Alex.Afanassiev_at_team.telstra.com>
Date: Tue, 22 Aug 2000 15:37:37 +1000
Message-Id: <10596.115187@fatcity.com>

This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible.

------_=_NextPart_000_01C00BFD.DFA72FE2
Content-Type: text/plain

Hi Alex

Is it really a minimum? :)

Can' cover everything but here are couple examples (please note they are the examples only)

<<chkalert.sh>> <<chklgdir.sh>> <<chklsnr.sh>> <<chksystem.sh>> <<chkuntil.sh>> <<dbreport.sh>> <<dbstatus.sh>>

With regards
Alex Afanassiev
Oracle DBA, TOC OPS/Internet.Operations

Tel:	(03) 8 661 20 61
Fax:	(03) 9 650 36 74


> -----Original Message-----
> From:	Alex Hillman [SMTP:alex_hillman_at_physia.com]
> Sent:	Tuesday, August 22, 2000 9:56 AM
> To:	Multiple recipients of list ORACLE-L
> Subject:	RE: Monitoring script
> 
> Database up, listener up, space available in archive destination, bdump,
> udump, $ORACLE_BASE, errors in alert.log, 
> available space in all tablespaces - this is a minimum. 
> 
> Alex 
> 
> -----Original Message----- 
> From: Winnie_Liu_at_infonet.com [ <mailto:Winnie_Liu_at_infonet.com>] 
> Sent: Monday, August 21, 2000 5:54 PM 
> To: Multiple recipients of list ORACLE-L 
> Subject: Re: Monitoring script 
> 
> 
> 
> What kind of information you are monitoring for? 
> 
> Winnie 
> 
> 
> 
> 
> 
> Alex Hillman <alex_hillman_at_physia.com> on 08/21/2000 02:06:09 PM 
> 
> Please respond to ORACLE-L_at_fatcity.com 
> 
> To:   Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> 
> cc:    (bcc: Winnie Liu/HQ/ISC) 
> 
> 
> 
> 
> 
> Could anybody provide samples of  DB monitoring script please 
>

> Alex Hillman
>

------_=_NextPart_000_01C00BFD.DFA72FE2
Content-Type: application/octet-stream;

name="chkalert.sh"
Content-Disposition: attachment;

filename="chkalert.sh"

#!/bin/ksh
#
# Program Name: @(#) chkalert.sh
#
# This script checks alertXXXX.log log file
# for error, warning or ORA messages
# and sends status via email
#
#*****************************************************************************
#* MAINTENANCE LOG *
#* --------------- *
#* DATE WHO DESCRIPTION *
#* ------- -------------- ----------------------------------- *
#* 29/01/98 AAfanassiev Created *
#*****************************************************************************
#
#ignore interrupt

trap " " 2
# return codes

SUCCESS=0
WARNING=1
ERROR=2
# booleans

TRUE=0
FALSE=1
# job name and error message

JOBNAME="$0"
JOBNAME_SHORT=`basename $JOBNAME`
# Make sure that the job is not running

if [ `ps -ef | grep -c ${JOBNAME_SHORT} ` -gt 3 ]; then

echo "$JOBNAME_SHORT: the program is already running" exit 1
fi
# set parameter

if [ "$1" ]; then

MTIME=$1
else

MTIME=1
fi
if [ "$2" ]; then

MAIL=$2
else

MAIL=N
fi
#
# Directories

TOOLS=/ora/admin/maint
MONITOR=/ora/admin/monitor
#
# Check backup completion

echo "$JOBNAME_SHORT: Starting..."
# Get all available ORACLE_SID

SIDLIST=`cat /var/opt/oracle/oratab| awk -F: '/^[^#]/' | cut -d ":" -f1` for SID in `echo $SIDLIST`; do

    # set environment vars
    . ${TOOLS}/dbenv.sh $SID
    if [ $? != 0 ]; then

       echo "$JOBNAME_SHORT: Failed to set oracle env vars. Aborting.."
       exit ${ERROR}

fi
echo "$JOBNAME_SHORT: Checking logs in ${ALERT} directory..." ${MONITOR}/chklgdir.sh ${ALERT} $MTIME $MAIL ${MONITOR}/chkuntil.sh ${ALERT}/alert_${SID}.log $MAIL done
echo "$JOBNAME_SHORT: Completed successfully" exit

------_=_NextPart_000_01C00BFD.DFA72FE2
Content-Type: application/octet-stream;

name="chklgdir.sh"
Content-Transfer-Encoding: quoted-printable Content-Disposition: attachment;

filename="chklgdir.sh"

#!/bin/ksh
#
# Program Name: @(#) chklgdir.sh
#
# This script check logs status in the speciofied
# directory
#
#***********************************************************************=

#* MAINTENANCE LOG =

     *


#*                    ---------------                                   =

     *


#*	DATE		WHO		DESCRIPTION			     *

#*	------- 	--------------	-----------------------------------  *

#*	29/01/98	AAfanassiev	Created                              *

#***********************************************************************=

******=20
#
#ignore interrupt

trap " " 2
# return codes

SUCCESS=3D0
WARNING=3D1
ERROR=3D2
# booleans

TRUE=3D0
FALSE=3D1
# job name and error message

JOBNAME=3D"$0"
JOBNAME_SHORT=3D`basename $JOBNAME`
GREP=3D/usr/xpg4/bin/grep
# Make sure that the job is not running

if [ `ps -ef | ${GREP} -c ${JOBNAME_SHORT}` -gt 3 ]; then

   echo "$JOBNAME_SHORT: program is already running"    exit 1
fi
# set parameter

ERRMSG=3D"
   $JOBNAME_SHORT: invalid parameter:
   $JOBNAME_SHORT <check directory> <last modified> <mail flag> < cc = flag>"
#if [ ${#@} =3D 1 ]; then

if [ $1 ]; then

DIR=3D$1
else

echo $ERRMSG
exit ${ERROR}
fi
if [ "$2" ]; then

MTIME=3D$2
else

MTIME=3D1=20
fi
if [ "$3" ]; then

MAIL=3D$3
else

MAIL=3DN=20
fi
if [ "$4" ]; then

CC=3D$4
else

CC=3DN=20
fi
#

if [ ! -s ${DIR} ]; then

echo "$JOBNAME_SHORT: $DIR not found. Aborting..." exit $ERROR
fi
# Directories

TOOLS=3D"/ora/admin/maint"
if [ "$ORACLE_SID" =3D "" ]; then

# default it to the fisrt sid in the oratab file ORATAB=3D/var/opt/oracle/oratab
ORACLE_SID=3D`cat $ORATAB | awk -F: '/^[^#]/' | cut -d ":" -f1 |head = -n 1`
fi
# set environment vars

. ${TOOLS}/dbenv.sh $ORACLE_SID y
if [ $? !=3D 0 ]; then

echo "$JOBNAME_SHORT: Failed to set oracle env vars. Aborting.." exit ${ERROR}
fi
#

MESSAGE_FILE=3D/tmp/fifo$$

TMP=3D${MESSAGE_FILE}tmp
echo "$JOBNAME_SHORT: Checking the logs in the $DIR directory,"
echo "$JOBNAME_SHORT:   last modified not earlier than $MTIME day(s), "
echo "$JOBNAME_SHORT:   mail flag is set to $MAIL, cc flag is set to =

$CC"
for LOGFILE in `find ${DIR} ! -type d -mtime -${MTIME}`; do

# check for error .New lines are IMPORTANT for ${GREP}

if [ `cat $LOGFILE | $GREP -E 'error warning
ORA-
failed
Failed
Aborting' | ${GREP} -v "without warning" | ${GREP} -v Stoponerror | = ${GREP} -v "The following error/warning/ORA- messages found" | ${GREP} = -v "signalled during:" | ${GREP} -v ORA-00278 | ${GREP} -v ORA-00279 | = ${GREP} -v ORA-00280 | ${GREP} -v ORA-00289 | ${GREP} -v ORA-07360 | wc = -l` -gt 0 ]; then

echo "The following error/warning/ORA- messages found" >> = ${MESSAGE_FILE}

           echo "in" >> ${MESSAGE_FILE}
           echo "${LOGFILE} file:" >> ${MESSAGE_FILE}
           echo  >> ${MESSAGE_FILE}
        cat $LOGFILE | $GREP -E 'error

warning
ORA-
failed
Failed
Aborting' | ${GREP} -v "without warning" | ${GREP} -v Stoponerror | =

${GREP} -v "The following error/warning/ORA- messages found" | ${GREP} =
-v "signalled during:" | ${GREP} -v ORA-00278 | ${GREP} -v ORA-00279 | =
${GREP} -v ORA-00280 | ${GREP} -v  ORA-00289 | ${GREP} -v ORA-07360 >> =
${MESSAGE_FILE}=20
           echo >> ${MESSAGE_FILE}
       #else
       #    echo "   No error/warning/ORA- messages found." >> =
${MESSAGE_FILE}
       fi

done
if [ -s ${MESSAGE_FILE} ]; then

   # reformat message file=20
   BFIRST=3D$TRUE
   cat $MESSAGE_FILE 2>/dev/null | while read LINE    do

      if [ $BFIRST -eq $TRUE ]; then
         echo $LINE > $MESSAGE_FILE
         BFIRST=3D$FALSE
      else
         echo $LINE >> $MESSAGE_FILE
      fi
      ERR=3D`echo $LINE | ${GREP} ORA- | ${GREP} -v 'The following =
error/warning/ORA' | awk '{print $1}'| cut -d "-" -f2 | cut -d ":" -f1`
      if [ "$ERR" !=3D "" ]; then
         echo Oracle error description: >> $MESSAGE_FILE=20
         ${ORACLE_HOME}/bin/oerr ora $ERR >> $MESSAGE_FILE=20
         echo >> $MESSAGE_FILE=20
      fi

   done
   # remove repeating lines
   #cat $MESSAGE_FILE | uniq > $MESSAGE_FILE    uniq $MESSAGE_FILE > $TMP=20
   cp $TMP $MESSAGE_FILE=20
   # send email
   if [ ${MAIL} =3D "Y" ]; then

      # Send warning email=20
      ${TOOLS}/sendmail.sh ${MESSAGE_FILE} "$JOBNAME_SHORT: Check logs =

in ${DIR} directory" N

else

cat $MESSAGE_FILE 2>/dev/null
fi
else

echo "$JOBNAME_SHORT: No message file generated" fi
if [ -f ${MESSAGE_FILE} ]; then

rm ${MESSAGE_FILE}
fi
if [ -f ${TMP} ]; then

rm ${TMP}
fi
echo "$JOBNAME_SHORT: Completed successfully" exit

------_=_NextPart_000_01C00BFD.DFA72FE2
Content-Type: application/octet-stream;

name="chklsnr.sh"
Content-Disposition: attachment;

filename="chklsnr.sh"

#!/bin/ksh
#
# Program Name: @(#) chklsnr.sh
#
# This script checks if the listener is up
# and attempts to start it up if it is down
#
#
#*****************************************************************************
#* MAINTENANCE LOG *
#* --------------- *
#* DATE WHO DESCRIPTION *
#* ------- -------------- ----------------------------------- *
#* 29/10/99 AAfanassiev Created *
#*****************************************************************************
#

JOB=`basename $0`
# Make sure that the job is not running

if [ `ps -ef | grep -c ${JOB}` -gt 3 ]; then

echo "$JOB: the program is already running" exit 1
fi
MAIL=Y
if [ ${#@} -gt 0 ]; then

DBNAME=$1
else

    echo
    echo Insufficient parameters passed - ABORTING     echo
    echo "Parameter 1 : ORACLE_SID "
    echo
    exit 1
fi
ORACLE_SID=$DBNAME
export ORACLE_SID
ORACLE_HOME=`/usr/local/bin/dbhome`; export ORACLE_HOME PATH=$PATH:$ORACLE_HOME/bin; export PATH MESSAGE_FILE=/tmp/fifo$$
LSNRCTL=$ORACLE_HOME/bin/lsnrctl
# check the default listener

LISTENER=""
# Directories
# Working dir

TOOLS="/ora/admin/bin"
MONDIR="/ora/admin/bin"
#

if [ "$1" ]; then

CCFLAG=$1
else

CCFLAG=N
fi
print -n "$JOB: `date '+%H:%M:%S'` Checking listner status ..."
# ------------------------------------------------------------------------
$LSNRCTL status $LISTENER | grep "TNS-" > ${MESSAGE_FILE} 2>&1 if [ -s ${MESSAGE_FILE} ]; then

if [ `cat ${MESSAGE_FILE} | grep -ic "no listener"` -gt 0 ]; then

      #
      print
      print -n "$JOB: `date '+%H:%M:%S'` Warning: No listener, attempting to start ..."
      $LSNRCTL start $LISTENER > $MESSAGE_FILE 2>&1
   else
      print 
      print -n  "$JOB: `date '+%H:%M:%S'` Warning: TNS- errors found, attempting to reload..."
      $LSNRCTL reload $LISTENER > $MESSAGE_FILE 2>&1

fi
if [ `cat ${MESSAGE_FILE} | grep -c "The command completed successfully"` -gt 0 ]; then

      print " Restarted OK"
      rm $MESSAGE_FILE
      #The command completed successfully
   else
      print " Failed to restart"

   fi
   # send email
   if [ ${MAIL} = "Y" -a -s ${MESSAGE_FILE} ]; then

      # Send warning email 
      ${TOOLS}/sendmail.sh ${MESSAGE_FILE} "$JOB: Check Oracle listner status!" ${CCFLAG}

   fi
else
  echo " OK"
fi
if [ -f ${MESSAGE_FILE} ]; then
  rm ${MESSAGE_FILE}
fi
exit

------_=_NextPart_000_01C00BFD.DFA72FE2
Content-Type: application/octet-stream;

name="chksystem.sh"
Content-Transfer-Encoding: quoted-printable Content-Disposition: attachment;

filename="chksystem.sh"

#!/bin/ksh
#
# Program Name: @(#) chksystem.sh
#
# This script monitors the system: =20
# - the instance that are down
# - the file systems that are full
#=20
#
#***********************************************************************=

#* MAINTENANCE LOG =

     *


#*                    ---------------                                   =

     *


#*	DATE		WHO		DESCRIPTION			     *

#*	------- 	--------------	-----------------------------------  *

#*	29/01/98	AAfanassiev	Created                              *

#***********************************************************************=

******=20
#

JOBNAME_SHORT=3D`basename $0`
# Make sure that the job is not running

if [ `ps -ef | grep -c ${JOBNAME_SHORT}` -gt 3 ]; then

echo "$JOBNAME_SHORT: the program is already running" exit 1
fi
MAIL=3DY
HOST=3D`hostname`
MESSAGE_FILE=3D/tmp/fifo$$
# Space limit in percents

LIMIT=3D99
PING=3D/usr/sbin/ping
# Servers =20

M1=3D"m1.cdn.telstra.com.au"
M2=3D"m2.cdn.telstra.com.au"
M3=3D"m3.cdn.telstra.com.au"
M4=3D"m4.cdn.telstra.com.au"
UAT1=3D"uat1.cdn.telstra.com.au"

UAT2=3D"uat2.cdn.telstra.com.au"
NUS104=3D"nus104.telecom.com.au"
#
# Directories
# Working dir

TOOLS=3D"/ora/admin/maint"
MONDIR=3D"/ora/admin/monitor"
#

if [ "$1" ]; then

CCFLAG=3D$1
else

CCFLAG=3DN
fi
echo "$JOBNAME_SHORT: Checking system resources..."
# =

------------------------------------------------------------------------=

if [ `hostname` !=3D "wss13.webhosting.bigpond.com" ];then

   # Check if any server is down
   #
   HOSTS=3D`echo ${M1} ${M2} ${M3} ${UAT1} ${UAT2} ${NUS104}`    for NODE in `echo $HOSTS`; do

      if [ `$PING ${NODE} 2>&1 | grep -c alive` -ne 1 ]; then
         echo "${NODE} server is down!" >> ${MESSAGE_FILE}
      fi

done
fi
#=20
# =

------------------------------------------------------------------------=

-
# Check if any instances that are down

if [ `${MONDIR}/dbstatus.sh | grep -c down` -gt 0 ]; then=20

echo "ORACLE instance(s):" >> ${MESSAGE_FILE}=20 ${MONDIR}/dbstatus.sh | grep down >> ${MESSAGE_FILE}=20 echo >> ${MESSAGE_FILE}=20
fi
# =

------------------------------------------------------------------------=

-
# Check if any of file system is more than LIMIT full
TITLE=3D""
df -k | while read LINE
do

   #Filesystem=3D`echo $LINE | awk '{print $1}'`=20
   #kbytes=3D`echo $LINE | awk '{print $2}'`=20
   #used=3D`echo $LINE | awk '{print $3}'`=20
   #avail=3D`echo $LINE | awk '{print $4}'`=20

CAPACITY=3D`echo $LINE | awk '{print $5}'| cut -d "%" -f1`=20 MOUNTED=3D`echo $LINE | awk '{print $6}'`=20 if [ `echo ${MOUNTED} | grep -c /cdrom` -eq 0 -a ${CAPACITY} !=3D = "capacity" ]; then=20

      if [ ${CAPACITY} -ge ${LIMIT} ]; then
         if [ "${TITLE}" =3D "" ]; then=20
            TITLE=3D"File System:"=20
            echo ${TITLE} >> ${MESSAGE_FILE}   =20
         fi
         echo "${CAPACITY}% of ${MOUNTED} is already used" >> =
${MESSAGE_FILE}
      fi

fi
done
if [ -s ${MESSAGE_FILE} ]; then

echo >> ${MESSAGE_FILE}=20
fi
# =

------------------------------------------------------------------------=

WIO_LIMIT=3D10
TITLE=3D""
# Check CPU usage
# Applications should spend more time on user time than system time.=20
# Target percentages should be:=20
# total CPU (usr + sys): 80 percent
# wio: 10 percent
# idle: 10 percent
# tty sd0 sd6 sd7 st4 =

cpu
# tin tout kps tps serv kps tps serv kps tps serv kps tps serv us =
sy wt id
# 1 29 25 1 31 0 0 30 55 1 28 27 3 5 1 =
1 2 96
iostat -xtc | while read CPU
do

DEVICE=3D`echo $CPU | awk '{print $1}'`=20

    if [ "$DEVICE" !=3D "device" -a "$DEVICE" !=3D "extended" ]; then
       US=3D`echo $CPU | awk '{print $13}'`=20
       SY=3D`echo $CPU | awk '{print $14}'`=20
       WT=3D`echo $CPU | awk '{print $15}'`=20
       ID=3D`echo $CPU | awk '{print $16}'`=20
       if [ "${WT}" !=3D "" ]; then
          if [ ${WT} -ge ${WIO_LIMIT} ]; then
             if [ "${TITLE}" =3D "" ]; then=20
                TITLE=3D"CPU Usage on device $DEVICE:"=20
                echo ${TITLE} >> ${MESSAGE_FILE}   =20
             fi=20
             echo "${WT}% of CPU time is used for I/O waiting." >> =
${MESSAGE_FILE}=20
             echo "It is recommended to keep this value less than =
$WIO_LIMIT" >> ${MESSAGE_FILE}=20
          fi
       fi

fi
done
# =

------------------------------------------------------------------------=

# send email

if [ ${MAIL} =3D "Y" -a -s ${MESSAGE_FILE} ]; then

# Send warning email=20
${TOOLS}/sendmail.sh ${MESSAGE_FILE} "$JOBNAME_SHORT: Check server = resources!" ${CCFLAG}
fi
if [ -f ${MESSAGE_FILE} ]; then

rm ${MESSAGE_FILE}
else
echo "$JOBNAME_SHORT: No message file generated" fi
echo "$JOBNAME_SHORT: Completed successfully" exit

------_=_NextPart_000_01C00BFD.DFA72FE2
Content-Type: application/octet-stream;

name="chkuntil.sh"
Content-Disposition: attachment;

filename="chkuntil.sh"

#!/bin/ksh
#
# Program Name: @(#) chkuntil.sh
#
# This script Incompete recovery status in
# alert log file
#
#*****************************************************************************
#* MAINTENANCE LOG *
#* --------------- *
#* DATE WHO DESCRIPTION *
#* ------- -------------- ----------------------------------- *
#* 29/01/98 AAfanassiev Created *
#*****************************************************************************
#
#ignore interrupt

trap " " 2
# return codes

SUCCESS=0
WARNING=1
ERROR=2
# booleans

TRUE=0
FALSE=1
# job name and error message

JOBNAME="$0"
JOBNAME_SHORT=`basename $JOBNAME`
GREP=/usr/xpg4/bin/grep
# Make sure that the job is not running

if [ `ps -ef | ${GREP} -c ${JOBNAME_SHORT}` -gt 3 ]; then

echo "$JOBNAME_SHORT: program is already running" exit 1
fi
# Directories

TOOLS="/ora/admin/maint"
#
# set parameter

if [ "$1" ]; then

DBNAME=$1
else

DBNAME=$ORACLE_SID
fi
if [ "$2" ]; then

MAIL=$2
else

MAIL=N
fi
if [ "$3" ]; then

CC=$3
else

CC=N
fi
#
# set environment vars

. ${TOOLS}/dbenv.sh $ORACLE_SID y
if [ $? != 0 ]; then

echo "$JOBNAME_SHORT: Failed to set oracle env vars. Aborting.." exit ${ERROR}
fi
ALERTLOG=${ALERT}/alert_${ORACLE_SID}.log MESSAGE_FILE=/tmp/fifo$$
echo "$ORACLE_SID database recovery status" > ${MESSAGE_FILE} ${GREP} -h 'Incomplete recovery done' $ALERTLOG | uniq | tail -1 >> ${MESSAGE_FILE} if [ `cat ${MESSAGE_FILE}| wc -l` -gt 1 ]; then

# send email
if [ ${MAIL} = "Y" ]; then

      # Send warning email 
      ${TOOLS}/sendmail.sh ${MESSAGE_FILE} "$JOBNAME_SHORT: Incomplete recovery status" N
   else
      cat $MESSAGE_FILE

fi
else

echo "$JOBNAME_SHORT: No message file generated" fi
if [ -f ${MESSAGE_FILE} ]; then

rm ${MESSAGE_FILE}
fi
echo "$JOBNAME_SHORT: Completed successfully" exit

------_=_NextPart_000_01C00BFD.DFA72FE2
Content-Type: application/octet-stream;

name="dbreport.sh"
Content-Transfer-Encoding: quoted-printable Content-Disposition: attachment;

filename="dbreport.sh"

#! /bin/ksh
#
# name dbreport.sh
#
# purpose Run monitoring repoprts =20
# usage dbreport.sh <dbname>=20
#
# parameters $1=3Ddbname ( database name optional, if not passed=20
# all oratab databases are checked)
#***********************************************************************=

#* MAINTENANCE LOG =

     *


#*                    ---------------                                   =

     *


#*      DATE            WHO             DESCRIPTION                     =

     *


#*      -------         --------------  =

-----------------------------------  *


#*      09/03/99        AAfanassiev     Created                         =

     *


#***********************************************************************=

# boolean

TRUE=3D0
FALSE=3D1
# initialization

COMPILE=3D$TRUE=20
MAIL=3D$TRUE=20
PERC_USED=3D90
# user
#USER=3D`whoami`

USER=3Doracle=20
#
# return code

SUCCESS=3D0
WARNING=3D1
ERROR=3D2
ORA_PROCESS_CNT=3D5
ORATAB=3D/var/opt/oracle/oratab
SIDLIST=3D`cat $ORATAB | awk -F: '/^[^#]/' | cut -d ":" -f1`
#
# job name

TOOLS=3D/ora/admin/maint =20
MONITOR=3D/ora/admin/monitor
LOGS=3D/ora/admin/monitor/LOGS
JOBNAME=3D"$0"
JOBNAME_SHORT=3D`basename $JOBNAME`
JOB=3D`echo $JOBNAME_SHORT | cut -d "." -f1`

OUT=3D${LOGS}/${JOB}.trc =20
echo "$JOBNAME_SHORT: sqlplus output is redirected to "
echo "$JOBNAME_SHORT:  to $OUT"

date '+Started: %m/%d/%y %H:%M:%S' > $OUT=20
# check parameters

if [ $1 ]; then

   DBNAME=3D$1
   # validate DBNAME
   if [ `echo $SIDLIST |grep -c $DBNAME` -eq 0 ]; then

      echo "$JOBNAME_SHORT: Invalid <dbname> : $DBNAME does not exists"
      echo "$JOBNAME_SHORT: Must be one of: `echo $SIDLIST`"
      exit ${ERROR}
   else
      # reset list to DBNAME
      SIDLIST=3D${DBNAME}

   fi
fi
# Message file

MESSAGE_FILE=3D/tmp/fifo$$
echo "$JOBNAME_SHORT: Starting monitoring reports..." TITLE=3D$TRUE=20
for DBNAME in `echo $SIDLIST`; do
  # set environment vars=20
  . ${TOOLS}/dbenv.sh $DBNAME y=20
  if [ $? -ne 0 ]; then

     echo "$JOBNAME_SHORT: Failed to set oracle env vars. Skipping = $DBNAME ..."
  else
    # check if the instance is running
    if [ `ps -fu oracle |grep ora_ | \
grep ${ORACLE_SID} | wc -l` -ge $ORA_PROCESS_CNT ]; then

       #SHUTDOWN_AFTER=3D${TRUE}=20
       #echo "$JOBNAME_SHORT: Starting up ${ORACLE_SID} database..."
       #$ORACLE_HOME/bin/svrmgrl >> $OUT << EOF
       #connect internal
       #startup open pfile=3D${PFILE}


#EOF

       # make sure that the database is open
       STATUS=3D`${MONITOR}/dbstatus.sh | grep -v "Available ORACLE" | =
grep $ORACLE_SID |grep -c OPEN`
       if [ $STATUS -gt 0 ]; then
          OPEN=3D${TRUE}
       else=20
          echo "$JOBNAME_SHORT: ${ORACLE_SID} database is up but not =
open. Skipping ..."
          OPEN=3D${FALSE}
       fi
    else
       OPEN=3D${FALSE}
       echo "$JOBNAME_SHORT: ${ORACLE_SID} database is down. Skipping =
..."
       #SHUTDOWN_AFTER=3D${FALSE}=20

    fi
   if [ $OPEN -eq $TRUE ]; then
    # compile monitoring package if required     if [ $COMPILE -eq $TRUE ]; then

        $ORACLE_HOME/bin/svrmgrl >> $OUT << EOF
        connect internal
	REM SELECT on the views below is required
	REM to access them from PL/SQL code (db_monit.sql)
        REM disable out to exclude ORA- error when
        REM trying to drop DBA_LOCKS view
        REM as it does not exist and nevr created
        set termout off
        @$ORACLE_HOME/rdbms/admin/catblock.sql
        set termout on =20
	grant select on dba_data_files to OPS\$${USER};
	grant select on dba_free_space to OPS\$${USER};
	grant select on dba_segments to OPS\$${USER};
	grant select on dba_tablespaces to OPS\$${USER};
	grant select on dba_tables to OPS\$${USER};
	grant select on dba_objects to OPS\$${USER};
	grant select on dba_locks to OPS\$${USER};
	grant select on dba_rollback_segs to OPS\$${USER};
	grant select on v_\$session to OPS\$${USER};
	grant select on v_\$sysstat to OPS\$${USER};
	grant select on v_\$rollstat to OPS\$${USER};
	grant select on v_\$sesstat to OPS\$${USER};
	grant select on v_\$rollname to OPS\$${USER};
	grant select on v_\$timer to OPS\$${USER};
	grant select on v_\$database to OPS\$${USER};
        connect /           =20
	@${MONITOR}/db_monit.sql

EOF
fi=20
$ORACLE_HOME/bin/sqlplus -s / >> $OUT << EOF

        set echo off feedback off verify off
        set serveroutput on lines 132 pages 999
        spool ${LOGS}/${JOB}_${ORACLE_SID}.rpt
        execute db_monit.ts_space;
	PROMPT --------------------------------------------------------
        spool off

EOF
if [ -s ${LOGS}/${JOB}_${ORACLE_SID}.rpt ]; then

      # analyze output=20
      DBTITLE=3D$TRUE=20
      grep -i "\.\.\." ${LOGS}/${JOB}_${ORACLE_SID}.rpt | while read =
LINE
      do
        Tablespace=3D`echo $LINE | awk '{print $1}'`
        FreeExtents=3D`echo $LINE | awk '{print $4}'`
        UsedPerc=3D`echo $LINE | awk '{print $9}'`
	if [ ${UsedPerc} -gt ${PERC_USED} -a "${Tablespace}" !=3D =
"Total......."  ]; then
           if [ ${TITLE} -eq ${TRUE} ]; then
              echo "`hostname` server report">> $MESSAGE_FILE
              echo >> $MESSAGE_FILE=20
              TITLE=3D$FALSE
	   fi
           if [ ${DBTITLE} -eq ${TRUE} ]; then
              echo >> $MESSAGE_FILE=20
              echo "ATTENTION: More than ${PERC_USED}% of the space is =
already used in the following $ORACLE_SID tablespaces" >> $MESSAGE_FILE
              echo "             (more details in =
${LOGS}/${JOB}_${ORACLE_SID}.rpt)" >> $MESSAGE_FILE=20
              echo >> $MESSAGE_FILE=20
              echo =
"-----------------------------------------------------------------------=
-">> $MESSAGE_FILE
              echo 'Tablespace\tUsed Space %\tAvail. extents (estim.)' =
>> $MESSAGE_FILE
              echo =
"-----------------------------------------------------------------------=
-">> $MESSAGE_FILE
              DBTITLE=3D$FALSE
           fi=20
           echo $Tablespace"\t"$UsedPerc"\t\t\t\t"$FreeExtents >> =
$MESSAGE_FILE
        fi
       done
    else
       echo "$JOBNAME_SHORT: Failed to create =

${LOGS}/${JOB}_${ORACLE_SID}.rpt file..."

    fi
    #
    # recompile and check invalid objects and triggers     for I in "1 2"; do=20

        $ORACLE_HOME/bin/sqlplus -s / >> $OUT << EOF
        set pause off feedback off echo off verify off
	set head off pages 999 lines 255
        -- Check triger status
        SELECT  owner||'.'||trigger_name||   =20
           ' trigger on '||trigger_type||' event '||
           triggering_event||' into/from '||
           table_owner||'.'||table_name||' table'||' is '||status=20
        FROM sys.dba_triggers
        WHERE status =3D 'DISABLED'=20
        order by owner, trigger_name;=20
        -- Recompile invalid objects
        spool ${LOGS}/${JOB}_${ORACLE_SID}.gql
        SELECT 'ALTER TRIGGER '|| owner||'.'||
             trigger_name||' compile;'
        FROM dba_triggers
        WHERE status =3D 'INVALID' ;
        SELECT 'ALTER '||object_type||' '||owner||'.'||
            object_name|| ' COMPILE;'
        FROM dba_objects=20
        WHERE status =3D 'INVALID'=20
        AND   object_type IN ('PACKAGE', 'PROCEDURE',
                             'TRIGGER', 'VIEW');
        spool off=20
        @${LOGS}/${JOB}_${ORACLE_SID}.gql=20

EOF
    done
    # analyze output=20
    if [ -s ${LOGS}/${JOB}_${ORACLE_SID}.gql ]; then

       if [ ${TITLE} -eq ${TRUE} ]; then
          echo "`hostname` server report" >> $MESSAGE_FILE
          echo >> $MESSAGE_FILE
          TITLE=3D$FALSE
       fi
       echo >> $MESSAGE_FILE
       echo "ATTENTION: Invalid objects are found in $ORACLE_SID =

database (sql saved in ${LOGS}/${JOB}_${ORACLE_SID}.gql file)" >> = $MESSAGE_FILE

       cat ${LOGS}/${JOB}_${ORACLE_SID}.gql >> ${MESSAGE_FILE}=20
       echo >> $MESSAGE_FILE

    ## shutdown database if it was started by the script
    ##
    #if [ ${SHUTDOWN_AFTER} -eq ${TRUE} ]; then
    #   if [ `ps -fu oracle |grep ora_ | \


#grep ${ORACLE_SID} | wc -l` -ge ${ORA_PROCESS_CNT} ]; then

    #      echo "$JOBNAME_SHORT: Shutting down ${ORACLE_SID} =

database..."

    #   $ORACLE_HOME/bin/svrmgrl >> $OUT << EOF
    #      connect internal
    #      shutdown immediate;=09

#EOF

    # fi
    #fi
  fi =20
fi
done
# send email

if [ ${MAIL} -eq $TRUE -a -s ${MESSAGE_FILE} ]; then

# Send warning email
${TOOLS}/sendmail.sh ${MESSAGE_FILE} "$JOBNAME_SHORT: Database(s) = monitoring report!" ${CCFLAG}
fi
if [ -f ${MESSAGE_FILE} ]; then

rm ${MESSAGE_FILE}
else
echo "$JOBNAME_SHORT: No message file generated" fi
echo "$JOBNAME_SHORT: Completed monitoring reports..." exit=20

------_=_NextPart_000_01C00BFD.DFA72FE2
Content-Type: application/octet-stream;

name="dbstatus.sh"
Content-Transfer-Encoding: quoted-printable Content-Disposition: attachment;

filename="dbstatus.sh"

#!/bin/ksh
#
# This script checks for the status =20
# Created: Alex Afanassiev 05/02/199
# Last updated: 31/o8/1999
#
# WARNING: This script has been tested only v7.3.4.3 and v8i1.5
# It relyes on x$ tables structure and output that could differ=20
# in Oracle releases =20
#
#JOBNAME=3D"$0"
#JOBNAME_SHORT=3D`basename $JOBNAME`

ORATAB=3D/var/opt/oracle/oratab
if [ ! -s $ORATAB ]; then

echo "$ORATAB file does not exist. Searching all file systems..." ORATAB=3D`find / -name oratab 2>/dev/null | head -1` if [ "$ORATAB" =3D "" ]; then

      echo "Cannot find oratab file. Aborting..."
      exit

fi
fi
OUT=3D/tmp/fifo$$ =20
CUR_SID=3D$ORACLE_SID
CUR_HOME=3D$ORACLE_HOME
SIDLIST=3D`cat ${ORATAB} | awk -F: '/^[^#]/' | cut -d ":" -f1` if [ "${SIDLIST}" !=3D "" -a `echo "${SIDLIST}" |grep -c '*'` -eq 0 ]; = then

   echo
   echo "Available ORACLE SIDS:" ${SIDLIST}    ORA_PROCESSES=3D5
   for SID in `echo $SIDLIST`; do
    PIDCNT=3D`ps -fu oracle |grep ora_ | grep $SID | wc -l`     if [ $PIDCNT -eq 0 ]; then

	echo
        echo  "$SID is down"
    else
        if [ $PIDCNT -ge $ORA_PROCESSES ]; then
           ORACLE_SID=3D$SID; export ORACLE_SID=20
           ORACLE_HOME=3D`cat ${ORATAB} | awk -F: '/^[^#]/' | grep =
$ORACLE_SID |cut -d ":" -f2`
           export ORACLE_HOME
           # get version=20
           $ORACLE_HOME/bin/svrmgrl << EOF > $OUT
           connect internal
           SELECT '***'||BANNER vers=20
           FROM x\$version =20
           WHERE indx =3D (SELECT min(indx) FROM x\$version);
EOF
           if [ `cat $OUT | grep -c ORA-01034` -gt 0  ]; then
             STATUS=3D"${ORACLE_SID} is started, not available;=20
   startup or shutdown process might be in progress"=20
              VERSION=3D""
           else
              VERSION=3D`cat $OUT | grep '\*\*\*' | cut -d "*" -f4`
              VERNO=3D`echo $VERSION | awk '{print $1}' |cut -c7`
              # init flags
              if [ $VERNO -lt 8 ]; then
                # DIFLG - Flag: 0 - archive log mode off;=20
                #               1 - archive log mode on
                #               5191 - mounted as standby=20
                # DIIRS - Incomplete recovery status
                # DINOT, DIOTH, DIOTT - ??? not sure which one is the =
open flag
                # guessing each of them  should be set to 1
                #
                $ORACLE_HOME/bin/svrmgrl << EOF > $OUT
                connect internal
                SELECT '***'||DIDBN||' database '||
                 DECODE(DIFLG,
		  0, 'is in NOARCHIVELOG mode'||
                  DECODE(DINOT+DIOTH+DIOTT, 3, ', OPEN', ', MOUNTED, =
NOT OPEN'),
                  1, 'is in ARCHIVELOG mode'||
                  DECODE(DINOT+DIOTH+DIOTT, 3, ', OPEN', ', MOUNTED, =
NOT OPEN'),
                  5191, 'mounted as a standby database',
                 'has unknown status '||DIFLG)
                 ||DECODE(DIIRS,0,NULL, CHR(10)||CHR(13)||'***'||
                    '     Incomplete Recovery Status: ' ||DIIRS) status =

                from x\$kccdi;
EOF
              else
                $ORACLE_HOME/bin/svrmgrl << EOF > $OUT
                connect / as sysdba
                select '***'||name||' is in '|| log_mode|| ' mode'||
                  DECODE(open_mode, 'MOUNTED', ', MOUNTED, NOT OPEN', =
', open as '||
                  open_mode) status
                  from v\$database;
EOF
              fi
              if [ `cat $OUT | grep -c 'ORA-01507: database not =

Received on Tue Aug 22 2000 - 00:37:37 CDT