From 53cbc4f81194f8766cad96755a9820d9c3a9eafd Mon Sep 17 00:00:00 2001 From: Ton Voon Date: Wed, 29 Jan 2003 20:47:56 +0000 Subject: Added cache and tablespace check (621567 - John Marquart) git-svn-id: https://nagiosplug.svn.sourceforge.net/svnroot/nagiosplug/nagiosplug/trunk@270 f882894a-f735-0410-b71e-b25c423dba1c diff --git a/plugins-scripts/check_oracle.sh b/plugins-scripts/check_oracle.sh index 7e2f6f9..998899b 100755 --- a/plugins-scripts/check_oracle.sh +++ b/plugins-scripts/check_oracle.sh @@ -37,6 +37,8 @@ print_usage() { echo " $PROGNAME --tns " echo " $PROGNAME --db " echo " $PROGNAME --login " + echo " $PROGNAME --cache " + echo " $PROGNAME --tablespace " echo " $PROGNAME --oranames " echo " $PROGNAME --help" echo " $PROGNAME --version" @@ -56,6 +58,14 @@ print_help() { echo " filesystem for sgadefORACLE_SID.dbf" echo "--login=SID" echo " Attempt a dummy login and alert if not ORA-01017: invalid username/password" + echo "--cache" + echo " Check local database for library and buffer cache hit ratios" + echo " ---> Requires Oracle user/password and SID specified." + echo " ---> Requires select on v_$sysstat and v_$librarycache" + echo "--tablespace" + echo " Check local database for tablespace capacity in ORACLE_SID" + echo " ---> Requires Oracle user/password specified." + echo " ---> Requires select on dba_data_files and dba_free_space" echo "--oranames=Hostname" echo " Check remote Oracle Names server" echo "--help" @@ -191,6 +201,71 @@ case "$cmd" in exit $STATE_CRITICAL fi ;; +--cache) + if [ ${5} -gt ${6} ] ; then + echo "UNKNOWN - Warning level is less then Crit" + exit $STATE_UNKNOWN + fi + result=`sqlplus -s ${2}/${3}@${4} << EOF + set pagesize 0 + +select (1-(pr.value/(dbg.value+cg.value)))*100 \ +from v\\$sysstat pr, v\\$sysstat dbg, v\\$sysstat cg \ +where pr.name = 'physical reads' \ + and dbg.name='db block gets' \ + and cg.name='consistent gets'; ` + + buf_hr=`echo $result | awk '{print int($1)}'` + result=`sqlplus -s ${2}/${3}@${4} << EOF + set pagesize 0 + +select sum(lc.pins)/(sum(lc.pins)+sum(lc.reloads))*100 \ +from v\\$librarycache lc ; ` + + lib_hr=`echo $result | awk '{print int($1)}'` + + if [ $buf_hr -le ${5} -o $lib_hr -le ${5} ] ; then + echo "${3} : ${4} CRITICAL - Cache Hit Rates: $lib_hr % Lib -- $buf_hr % Buff" + exit $STATE_CRITICAL + fi + if [ $buf_hr -le ${6} -o $lib_hr -le ${6} ] ; then + echo "${3} : ${4} WARNING - Cache Hit Rates: $lib_hr % Lib -- $buf_hr % Buff" + exit $STATE_WARNING + fi + echo "${3} : ${4} OK - Cache Hit Rates: $lib_hr % Lib -- $buf_hr % Buff" + + exit $STATE_OK + ;; +--tablespace) + if [ ${6} -lt ${7} ] ; then + echo "UNKNOWN - Warning level is more then Crit" + exit $STATE_UNKNOWN + fi + result=`sqlplus -s ${2}/${3}@${4} << EOF + set pagesize 0 + +select b.free,a.total,100 - trunc(b.free/a.total * 1000) / 10 prc \ +from ( \ +select tablespace_name,sum(bytes)/1024/1024 total \ +from dba_data_files group by tablespace_name) A, \ +( select tablespace_name,sum(bytes)/1024/1024 free \ +from dba_Free_space group by tablespace_name) B \ +where a.tablespace_name=b.tablespace_name and a.tablespace_name='${5}'; ` + + ts_free=`echo $result | awk '{print int($1)}'` + ts_total=`echo $result | awk '{print int($2)}'` + ts_pct=`echo $result | awk '{print int($3)}'` + if [ $ts_pct -ge ${6} ] ; then + echo "${4} : ${5} CRITICAL - $ts_pct% used [ $ts_free / $ts_total MB available ]" + exit $STATE_CRITICAL + fi + if [ $ts_pct -ge ${7} ] ; then + echo "${4} : ${5} WARNING - $ts_pct% used [ $ts_free / $ts_total MB available ]" + exit $STATE_WARNING + fi + echo "${4} : ${5} OK - $ts_pct% used [ $ts_free / $ts_total MB available ]" + exit $STATE_OK + ;; *) print_usage exit $STATE_UNKNOWN -- cgit v0.10-9-g596f