[Nagiosplug-devel] (no subject)

[jmarquart at planalytics.com]

'lo all,
     I spent a few more moments working on the check_oracle script:

I  dropped my previous changes - so the following differes from stock
distribution as follows:


addition of --tablespace command

--tablespace takes 6  arguments:
     USER PASSWD    INSTANCE  TABLESPACE      Crit %    Warn%

it then returns results in the form of:

     INSTANCE : TABLESPACE STATUS - %used [ free space / total MB ]

please note!!!   the user used MUST have access to the dba_free_space and
dba_data_files views (this can be granted by SYS user - but usually not by
SYSTEM)


I am planning on extending this functionality further - such that it checks
available extent sizes vs potential next_extent sizes -  so that it gives a
correct answer at a more granular level (i.e. if a table expects to allocate
50MB as next_extent - but the largest available extent is 40MB - even though
tablespace has 200MB free - the allocation will fail)

My patch is included below (inline) -
     unless This is usefull to anyone else - I will stop polluting the list
after this post.


cheers
-john


---snip---
*** check_oracle.orig    Fri Jul 19 16:11:01 2002
--- check_oracle.jjm     Fri Jul 19 16:11:08 2002
***************
*** 36,41 ****
--- 36,42 ----
    echo "Usage:"
    echo "  $PROGNAME --tns <Oracle Sid or Hostname/IP address>"
    echo "  $PROGNAME --db <ORACLE_SID>"
+   echo "  $PROGNAME --tablespace <USER> <PASS> <INST> <TABLESPACE> <CRITICAL>
<WARNING>"
    echo "  $PROGNAME --oranames <Hostname>"
    echo "  $PROGNAME --help"
    echo "  $PROGNAME --version"
***************
*** 53,58 ****
--- 54,62 ----
    echo "--db=SID"
    echo "   Check local database (search /bin/ps for PMON process and check"
     echo "   filesystem for sgadefORACLE_SID.dbf"
+   echo "--tablespace"
+   echo "   Check local database for tablespace capacity in ORACLE_SID"
+   echo "       --->  Requires Oracle user/password specified."
    echo "--oranames=Hostname"
    echo "   Check remote Oracle Names server"
    echo "--help"
***************
*** 156,161 ****
--- 160,195 ----
     exit $STATE_CRITICAL
      fi
      ;;
+ --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
+     ;;
  --help)
          print_help
      exit $STATE_OK
---snip---