Fixed sqlplus calls and rearranged parameters for --tablespace & --cache

git-svn-id: https://nagiosplug.svn.sourceforge.net/svnroot/nagiosplug/nagiosplug/trunk@468 f882894a-f735-0410-b71e-b25c423dba1c

1 files changed, 37 insertions, 33 deletions

diff --git a/plugins-scripts/check_oracle.sh b/plugins-scripts/check_oracle.sh
index 3064db90..1de8fa07 100755
--- a/plugins-scripts/check_oracle.sh
+++ b/plugins-scripts/check_oracle.sh
@@ -31,8 +31,8 @@ print_usage() {
   echo "  $PROGNAME --tns <Oracle Sid or Hostname/IP address>"
   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 --cache <ORACLE_SID> <USER> <PASS> <CRITICAL> <WARNING>"
+  echo "  $PROGNAME --tablespace <ORACLE_SID> <USER> <PASS> <TABLESPACE> <CRITICAL> <WARNING>"
   echo "  $PROGNAME --oranames <Hostname>"
   echo "  $PROGNAME --help"
   echo "  $PROGNAME --version"
@@ -48,13 +48,13 @@ print_help() {
   echo "--tns=SID/IP Address"
   echo "   Check remote TNS server"
   echo "--db=SID"
-  echo "   Check local database (search /bin/ps for PMON process and check"
+  echo "   Check local database (search /bin/ps for PMON process) and check"
   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 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"
@@ -203,33 +203,33 @@ case "$cmd" in
 	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'; `
+    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 ${2}/${3}@${4} << EOF
-      set pagesize 0
-
-select sum(lc.pins)/(sum(lc.pins)+sum(lc.reloads))*100 \
-from v\\$librarycache lc ; `
+    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 "${3} : ${4} CRITICAL - Cache Hit Rates: $lib_hr % Lib -- $buf_hr % Buff"
+  	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 "${3} : ${4} WARNING  - Cache Hit Rates: $lib_hr % Lib -- $buf_hr % Buff"
+  	echo "${4} : ${2} 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"
+    echo "${4} : ${2} OK - Cache Hit Rates: $lib_hr % Lib -- $buf_hr % Buff"
 
     exit $STATE_OK
     ;;
@@ -238,29 +238,33 @@ from v\\$librarycache lc ; `
 	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}';  ` 
+    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 "${4} : ${5} CRITICAL - $ts_pct% used [ $ts_free / $ts_total MB available ]"
+  	echo "${2} : ${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 ]"
+  	echo "${2} : ${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 ]"
+    echo "${2} : ${5} OK - $ts_pct% used [ $ts_free / $ts_total MB available ]"
     exit $STATE_OK
     ;;
 *)