#!/bin/ksh #============================================================================ # File: sp_vmstat.sh # Type: UNIX korn-shell script # Author: Tim Gorman (Evergreen Database Technologies, Inc) # Date: 31-May 2003 # # Description: # # UNIX shell script to run the "vmstat" command and save the results # in a table in an Oracle database. # # This script is intended to be run from the UNIX "cron" utility. # Upon failure, it will send email to the email addresses specified in # the "~/.dbamail" file. If the type of error message indicates a # "failure", then it will send email to the email addresss specified # in the "~/.dbapage" file as well... # # It expects to use the following standard Oracle scripts to set up # the UNIX environment variables: dbhome # # It expects to login to the Oracle database according to the values # in the variable "_UnPwd", retrieved from the hidden file "$HOME/.unpwd". # # Modifications: # TGorman 31may03 created for Sun Solaris8 # TGorman 03jun03 added support for Linux # TGorman 18aug03 added support for HP-UX #============================================================================ export PATH=/usr/local/bin:/usr/bin:${PATH} _Prog=sp_vmstat _SqlFile=/tmp/.${_Prog}_$$.sql _LstFile=/tmp/.${_Prog}_$$.lst _HostName=`hostname` # #---------------------------------------------------------------------------- # Define a shell function to handle error conditions... #---------------------------------------------------------------------------- _Echo() ### ...define shell-function "_Echo()"... { if [[ "$1" = "failure" ]] then if [ -r ${HOME}/.dbapage ] then sed '/^#/d' ${HOME}/.dbapage | \ while read _PageRcpt do date | mailx -s "${_Prog}: $1" ${_PageRcpt} done fi fi if [ -r ${HOME}/.dbamail ] then sed '/^#/d' ${HOME}/.dbamail | \ while read _EmailRcpt do echo $2 | mailx -s "${_Prog}: $1" ${_EmailRcpt} done else echo "\n${_Prog} $1\n\n$2\n" fi } ### ...end of definition of shell-function "_Echo()"... # #---------------------------------------------------------------------------- # Check Unix variant... #---------------------------------------------------------------------------- _Platform="`uname`" case "${_Platform}" in SunOS) ;; Linux) ;; HP-UX) ;; *) echo "Platform \"${_Platform}\" not supported; aborting..." exit 1 ;; esac # #---------------------------------------------------------------------------- # ...validate command-line parameters... #---------------------------------------------------------------------------- case $# in 3) ;; *) _Echo warning "Usage: \"${_Prog}.sh OraSid CallFrequency CallDuration\"; aborting..." exit 1 ;; esac _OraSid=$1 _CallFrequency=$2 _CallDuration=$3 # #---------------------------------------------------------------------------- # Validate the CallFrequency and CallDuration parameter values... #---------------------------------------------------------------------------- if (( ${_CallFrequency} < 1 || ${_CallFrequency} > 600 )) then _Echo warning "Parameter \"CallFrequency\" must be between 1 and 600; aborting..." exit 1 fi if (( ${_CallDuration} < 1 || ${_CallDuration} > 3600 )) then _Echo warning "Parameter \"CallDuration\" must be between 1 and 3600; aborting..." exit 1 fi # #---------------------------------------------------------------------------- # Retrieve Oracle PERFSTAT account password from protected hidden file... #---------------------------------------------------------------------------- if [ ! -r ${HOME}/.unpwd ] then _Echo warning "Hidden file \"${HOME}/.unpwd\" not found; aborting..." exit 1 fi _UnPwd=perfstat/"`grep -i "^perfstat/" ${HOME}/.unpwd | awk -F/ '{print $2}'`" # #---------------------------------------------------------------------------- # ...set the instance identifier for the Oracle database instance... #---------------------------------------------------------------------------- export ORACLE_SID=${_OraSid} # #---------------------------------------------------------------------------- # ...verify that the specified ORACLE_SID exists in the "oratab" file using # the "dbhome" script... #---------------------------------------------------------------------------- dbhome ${ORACLE_SID} > /dev/null 2>&1 if (( $? != 0 )) then _Echo warning "OraSid \"${ORACLE_SID}\" not found in \"oratab\" file; aborting..." exit 1 fi # #---------------------------------------------------------------------------- # Check to ensure that the Oracle database instance is available... #---------------------------------------------------------------------------- if [[ "`ps -eaf | grep ora_pmon_${ORACLE_SID} | grep -v grep`" = "" ]] then exit 0 fi # #---------------------------------------------------------------------------- # Set up the Oracle environment variables for UNIX based on ORACLE_SID using # the "oraenv" script... #---------------------------------------------------------------------------- export ORAENV_ASK=NO . oraenv > /dev/null 2>&1 if (( $? != 0 )) then _Echo warning "\"oraenv ${ORACLE_SID}\" failed; aborting..." exit 1 fi unset ORAENV_ASK # #---------------------------------------------------------------------------- # ...verify that the "SQL*Plus" executables are available... #---------------------------------------------------------------------------- if [ ! -x ${ORACLE_HOME}/bin/sqlplus ] then _Echo warning "\"sqlplus\" not found in \"ORACLE_HOME/bin\"; aborting..." exit 1 fi # #---------------------------------------------------------------------------- # Check on the presence of the "vmstat" executable... #---------------------------------------------------------------------------- whence vmstat > /dev/null 2>&1 if (( $? != 0 )) then _Echo warning "\"vmstat\" executable not found; aborting..." exit 1 fi # #---------------------------------------------------------------------------- # Create a SQL*Plus script to use to insert "vmstat" data into the database... #---------------------------------------------------------------------------- echo "whenever oserror exit failure rollback" > ${_SqlFile} if (( $? != 0 )) then _Echo warning "Cannot create SQL*Plus script \"${_SqlFile}\"; aborting..." exit 1 fi chmod 600 ${_SqlFile} echo "whenever sqlerror exit failure rollback" >> ${_SqlFile} echo "set echo on feedback on timing on" >> ${_SqlFile} echo "spool ${_LstFile}" >> ${_SqlFile} echo "connect ${_UnPwd}" >> ${_SqlFile} echo "host chmod 600 ${_LstFile}" >> ${_SqlFile} echo "declare i integer;" >> ${_SqlFile} echo "begin" >> ${_SqlFile} echo " select 1 into i" >> ${_SqlFile} echo " from user_tables" >> ${_SqlFile} echo " where table_name = 'CSTATS\$VMSTAT';" >> ${_SqlFile} echo " if i = 0 then" >> ${_SqlFile} echo " raise no_data_found;" >> ${_SqlFile} echo " end if;" >> ${_SqlFile} echo "exception" >> ${_SqlFile} echo " when no_data_found then" >> ${_SqlFile} case "${_Platform}" in SunOS) echo " execute immediate 'create table cstats\$vmstat (hostname varchar2(30),timestamp date,instno number,r number,b number,w number,swp number,fre number,re number,mf number,pi number,po number,fr number,de number,sr number,fin number,fsy number,fcs number,us number,sy number,id number)';" >> ${_SqlFile} ;; Linux) echo " execute immediate 'create table cstats\$vmstat (hostname varchar2(30),timestamp date,instno number,r number,b number,w number,swp number,fre number,buff number,cache number,si number,so number,bi number,bo number,sin number,scs number,us number,sy number,id number)';" >> ${_SqlFile} ;; HP-UX) echo " execute immediate 'create table cstats\$vmstat (hostname varchar2(30),timestamp date,instno number,r number,b number,w number,avm number,fre number,re number,at number,pi number,po number,fr number,de number,sr number,fin number,fsy number,fcs number,us number,sy number,id number)';" >> ${_SqlFile} ;; esac echo "end;" >> ${_SqlFile} echo "/" >> ${_SqlFile} # integer _Cnt=0 while (( ${_Cnt} < ${_CallFrequency} )) do # case "${_Platform}" in SunOS) echo "insert into cstats\$vmstat (hostname,timestamp,instno,r,b,w,swp,fre,re,mf,pi,po,fr,de,sr,fin,fsy,fcs,us,sy,id)" echo "values('"${_HostName}"',to_date('`date +20%y%m%d%H%M%S`','YYYYMMDDHH24MISS'),to_number(userenv('INSTANCE'))," _Tmp1="`vmstat ${_CallDuration} 2 2> /dev/null | tail -1`" echo "\t`echo ${_Tmp1} | \ awk '{printf("%d,%d,%d,%d,%d,%d,%d,%d,%d,%d,%d,%d,%d,%d,%d,%d,%d,%d\n",$1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$17,$18,$19,$20,$21,$22)}'`);" ;; Linux) echo "insert into cstats\$vmstat (hostname,timestamp,instno,r,b,w,swp,buff,cache,si,so,bi,bo,sin,scs,us,sy,id)" echo "values('"${_HostName}"',to_date('`date +20%y%m%d%H%M%S`','YYYYMMDDHH24MISS'),to_number(userenv('INSTANCE'))," _Tmp1="`vmstat ${_CallDuration} 2 2> /dev/null | tail -1`" echo "\t`echo ${_Tmp1} | \ awk '{printf("%d,%d,%d,%d,%d,%d,%d,%d,%d,%d,%d,%d,%d,%d,%d\n",$1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15)}'`);" ;; HP-UX) echo "insert into cstats\$vmstat (hostname,timestamp,instno,r,b,w,avm,fre,re,at,pi,po,fr,de,sr,fin,fsy,fcs,us,sy,id)" echo "values('"${_HostName}"',to_date('`date +20%y%m%d%H%M%S`','YYYYMMDDHH24MISS'),to_number(userenv('INSTANCE'))," _Tmp1="`vmstat ${_CallDuration} 2 2> /dev/null | tail -1`" echo "\t`echo ${_Tmp1} | \ awk '{printf("%d,%d,%d,%d,%d,%d,%d,%d,%d,%d,%d,%d,%d,%d,%d,%d,%d,%d\n",$1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$17,$18,$19,$20,$21,$22)}'`);" ;; esac # integer _Cnt=${_Cnt}+1 # done >> ${_SqlFile} echo "exit success commit" >> ${_SqlFile} # #---------------------------------------------------------------------------- # Run the SQL*Plus script... #---------------------------------------------------------------------------- ${ORACLE_HOME}/bin/sqlplus /nolog @${_SqlFile} > /dev/null 2>&1 if (( $? != 0 )) then _Echo warning "\"SQL*Plus\" script \"${_SqlFile}\" failed -- spooled output in \"${_LstFile}\"; aborting..." exit 1 fi # #---------------------------------------------------------------------------- # Clean up and exit successfully... # # ...also, clean up any files left around older than 7 days... #---------------------------------------------------------------------------- find /tmp -name "${_Prog}*" -mtime +7 -exec rm -f {} \; > /dev/null 2>&1 rm -f ${_SqlFile} ${_LstFile} exit 0