[Nagiosplug-checkins] CVS: nagiosplug/plugins-scripts check_oracle.sh,1.5,1.6

[Ton Voon]

Update of /cvsroot/nagiosplug/nagiosplug/plugins-scripts
In directory sc8-pr-cvs1:/tmp/cvs-serv29761

Modified Files:
	check_oracle.sh 
Log Message:
Fixed sqlplus calls and rearranged parameters for --tablespace & --cache


Index: check_oracle.sh
===================================================================
RCS file: /cvsroot/nagiosplug/nagiosplug/plugins-scripts/check_oracle.sh,v
retrieving revision 1.5
retrieving revision 1.6
diff -C2 -r1.5 -r1.6
*** check_oracle.sh	17 Mar 2003 22:22:26 -0000	1.5
--- check_oracle.sh	1 Apr 2003 22:24:18 -0000	1.6
***************
*** 32,37 ****
    echo "  $PROGNAME --db <ORACLE_SID>"
    echo "  $PROGNAME --login <ORACLE_SID>"
!   echo "  $PROGNAME --cache <USER> <PASS> <INST> <CRITICAL> <WARNING>"
!   echo "  $PROGNAME --tablespace <USER> <PASS> <INST> <TABLESPACE> <CRITICAL> <WARNING>"
    echo "  $PROGNAME --oranames <Hostname>"
    echo "  $PROGNAME --help"
--- 32,37 ----
    echo "  $PROGNAME --db <ORACLE_SID>"
    echo "  $PROGNAME --login <ORACLE_SID>"
!   echo "  $PROGNAME --cache <ORACLE_SID> <USER> <PASS> <CRITICAL> <WARNING>"
!   echo "  $PROGNAME --tablespace <ORACLE_SID> <USER> <PASS> <TABLESPACE> <CRITICAL> <WARNING>"
    echo "  $PROGNAME --oranames <Hostname>"
    echo "  $PROGNAME --help"
***************
*** 49,53 ****
    echo "   Check remote TNS server"
    echo "--db=SID"
!   echo "   Check local database (search /bin/ps for PMON process and check"
    echo "   filesystem for sgadefORACLE_SID.dbf"
    echo "--login=SID"
--- 49,53 ----
    echo "   Check remote TNS server"
    echo "--db=SID"
!   echo "   Check local database (search /bin/ps for PMON process) and check"
    echo "   filesystem for sgadefORACLE_SID.dbf"
    echo "--login=SID"
***************
*** 55,59 ****
    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"
--- 55,59 ----
    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"
***************
*** 204,234 ****
  	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
--- 204,234 ----
  	exit $STATE_UNKNOWN
      fi
!     result=`sqlplus -s ${3}/${4}@${2} << 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';
! EOF`
  
      buf_hr=`echo $result | awk '{print int($1)}'` 
!     result=`sqlplus -s ${3}/${4}@${2} << EOF
! set pagesize 0
! select sum(lc.pins)/(sum(lc.pins)+sum(lc.reloads))*100
! from v\\$librarycache lc;
! EOF`
  	
      lib_hr=`echo $result | awk '{print int($1)}'`
  
      if [ $buf_hr -le ${5} -o $lib_hr -le ${5} ] ; then
!   	echo "${4} : ${2} 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 "${4} : ${2} WARNING  - Cache Hit Rates: $lib_hr % Lib -- $buf_hr % Buff"
  	exit $STATE_WARNING
      fi
!     echo "${4} : ${2} OK - Cache Hit Rates: $lib_hr % Lib -- $buf_hr % Buff"
  
      exit $STATE_OK
***************
*** 239,265 ****
  	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
      ;;
--- 239,269 ----
  	exit $STATE_UNKNOWN
      fi
!     result=`sqlplus -s ${3}/${4}@${2} << 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}';
! EOF`
  
      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_free -eq 0 -a $ts_total -eq 0 -a $ts_pct -eq 0 ] ; then
+         echo "No data returned by Oracle - tablespace $5 not found?"
+         exit $STATE_UNKNOWN
+     fi
      if [ $ts_pct -ge ${6} ] ; then
!   	echo "${2} : ${5} CRITICAL - $ts_pct% used [ $ts_free / $ts_total MB available ]"
  	exit $STATE_CRITICAL
      fi
      if [ $ts_pct -ge ${7} ] ; then
!   	echo "${2} : ${5} WARNING  - $ts_pct% used [ $ts_free / $ts_total MB available ]"
  	exit $STATE_WARNING
      fi
!     echo "${2} : ${5} OK - $ts_pct% used [ $ts_free / $ts_total MB available ]"
      exit $STATE_OK
      ;;