Orasyscheck

This script is designed to report several important factors which may affect the smooth running of an Oracle database. These include disk space available, running Oracle processes, and the status of tablespaces in the database. To customise this for a different site: change the MAILLIST variable to an appropriate recipient; change the paths to where the various scripts reside; and supply the SYSTEM user's password.

The script is designed to run from a crontab file and takes two parameters: ORAUSER, the user the Oracle database runs as, and ORASID, the system ID of the database the script is checking.

This script requires the tsmon.sql and dfmon.sql scripts.

#!/bin/ksh
#
# Oracle systems check
#
blankline=" "
endline="####################################################################"
MAIL_LIST=dba.account@example.co.uk
DBSERVER=`hostname`
ORAUSER=$1
ORASID=$2

#
# Gather basic information
#
echo $endline
echo "System check on $DBSERVER begins at `date`"
echo $blankline
uptime
echo $blankline
echo $endline
echo $blankline
echo "Checking disk space on $DBSERVER"
echo $blankline
df -kF ufs
echo $blankline
echo "Free space on $DBSERVER ... (Gb)"
df -kF ufs | tail +2 | awk '{ m += $4 } END { print m/1024/1024 }'
echo $blankline
echo "Checking $ORASID DB on $DBSERVER"
echo $blankline
ps -ef | grep $ORAUSER | grep $ORASID | grep -v grep | grep -v tnslsnr | grep -v orasyscheck | grep -v Apache | sort +7
echo $blankline
echo "Checking $ORASID Listener on $DBSERVER"
echo $blankline
ps -ef | grep $ORAUSER | grep tnslsnr | grep -v grep | sort +7
echo $blankline

#
# Gather Freespace Information
#
$ORACLE_HOME/bin/sqlplus system/ @/home/path/to/tsmon.sql

#
# Gather Tablespace and Datafile Information (not needed yet)
#
$ORACLE_HOME/bin/sqlplus system/ @/home/path/to/dfmon.sql

#
# Mail the output
#
mailx -s "$ORASID Daily Log" $MAIL_LIST < /home/$ORAUSER/logs/orasyscheck.log