1 files changed, 590 insertions, 0 deletions

diff --git a/web/attachments/56241-check_oracle.jjm b/web/attachments/56241-check_oracle.jjm
new file mode 100644
index 0000000..3142511
--- /dev/null
+++ b/web/attachments/56241-check_oracle.jjm
@@ -0,0 +1,590 @@
+#!/bin/sh
+#
+# latigid010@yahoo.com
+# 01/06/2000
+#
+#  This Nagios plugin was created to check Oracle status
+#
+
+PROGNAME=`basename $0`
+PROGPATH=`echo $0 | sed -e 's,[\\/][^\\/][^\\/]*$,,'`
+REVISION=`echo '$Revision: 1.6 $' | sed -e 's/[^0-9.]//g'`
+
+. $PROGPATH/utils.sh
+
+
+function init_var() {
+RETURN_MSG=""
+CHECK_CACHE=no
+CHECK_DB=no
+CHECK_EXTENTS=no
+CHECK_LOGIN=no
+CHECK_NAMES=no
+CHECK_TS=no
+CHECK_TNS=no
+CRIT_LVL=-1
+WARN_LVL=-1
+ORACLE_SID=${ORACLE_SID}
+ORA_USER=""
+ORA_PASS=""
+ORA_TS=""
+ORA_HOST=""
+ORA_NAMES=""
+
+
+# Hunt down a reasonable ORACLE_HOME
+if [ -z "$ORACLE_HOME" ] ; then
+        # Adjust to taste
+        for oratab in /var/opt/oracle/oratab /etc/oratab
+        do
+        [ ! -f $oratab ] && continue
+        ORACLE_HOME=`IFS=:
+                while read SID ORACLE_HOME junk;
+                do
+                        if [ "$SID" = "$2" -o "$SID" = "*" ] ; then
+                                echo $ORACLE_HOME;
+                                exit;
+                        fi;
+                done < $oratab`
+        [ -n "$ORACLE_HOME" ] && break
+        done
+fi
+# Last resort
+[ -z "$ORACLE_HOME" -a -d $PROGPATH/oracle ] && ORACLE_HOME=$PROGPATH/oracle
+
+if [ -z "$ORACLE_HOME" -o ! -d "$ORACLE_HOME" ] ; then
+        echo "Cannot determine ORACLE_HOME for sid ${ORACLE_SID}"
+        exit ${STATE_UNKNOWN}
+fi
+PATH=$PATH:$ORACLE_HOME/bin
+LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
+export ORACLE_HOME PATH LD_LIBRARY_PATH
+}
+
+
+print_usage() {
+  echo "This check_oracle provides a new argument handling scheme."
+  echo "        To use the old scheme - add a "--" before any arguments"
+  echo "Old Usage:"
+  echo "  $PROGNAME --tns <Oracle Sid or Hostname/IP address>"
+  echo "  $PROGNAME --db <ORACLE_SID>"
+  echo "  $PROGNAME --login <ORACLE_SID>"
+  echo "  $PROGNAME --cache <ORACLE_SID> <USER> <PASS> <CRITICAL> <WARNING>"
+  echo "  $PROGNAME --extents <ORACLE_SID> <USER> <PASS> <TABLESPACE> <CRITICAL> <WARNING>"
+  echo "  $PROGNAME --tablespace <ORACLE_SID> <USER> <PASS> <TABLESPACE> <CRITICAL> <WARNING>"
+  echo "  $PROGNAME --oranames <Hostname>"
+  echo "  $PROGNAME --help"
+  echo "  $PROGNAME --version"
+  echo "The new argument handling takes shorter args + allows chaining of checks"
+  echo "New Usage:"
+  echo "  $PROGNAME [args...]"
+  echo "        -c      Cache Check             -C <ARG>        Critical level"
+  echo "        -d      DB Check                -W <ARG>        Warning level"
+  echo "        -e      Extent Check"   
+  echo "        -l      Listener Check          -u <ARG>        User"
+  echo "        -n      Oranames check          -p <ARG>        Password"
+  echo "        -t      tns names check         -s <ARG>        oracle SID"
+  echo "        -T      Tablespace check        -H <ARG>        Host check"     
+  echo "                        -h      Help"
+}
+
+print_help() {
+  print_revision $PROGNAME $REVISION
+  echo ""
+  print_usage
+  echo ""
+  echo "Check Oracle status"
+  echo ""
+  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 "   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 "--extents"
+  echo "   Check to see if there are extents unable to be extended."
+  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"
+  echo "   Print this help screen"
+  echo "--version"
+  echo "   Print version and license information"
+  echo ""
+  echo "If the plugin doesn't work, check that the ORACLE_HOME environment"
+  echo "variable is set, that ORACLE_HOME/bin is in your PATH, and the"
+  echo "tnsnames.ora file is locatable and is properly configured."
+  echo ""
+  echo "When checking local database status your ORACLE_SID is case sensitive."
+  echo ""
+  echo "If you want to use a default Oracle home, add in your oratab file:"
+  echo "*:/opt/app/oracle/product/7.3.4:N"
+  echo ""
+  support
+}
+
+
+check_cache() {
+    if [ ${CRIT_LVL} -gt ${WARN_LVL} ] ; then
+        RETURN_MSG="UNKNOWN - Warning level is less then Crit"
+        return ${STATE_UNKNOWN}
+    fi
+    result=`sqlplus -s ${ORA_USER}/${ORA_PASS}@${ORACLE_SID} << 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`
+
+    if [ -n "`echo $result | grep ORA-`" ] ; then
+      error=` echo "$result" | grep "ORA-" | head -1`
+      echo "CRITICAL - $error"
+      exit $STATE_CRITICAL
+    fi
+
+
+    buf_hr=`echo $result | awk '{print int($1)}'`
+    result=`sqlplus -s ${ORA_USER}/${ORA_PASS}@${ORACLE_SID} << EOF
+set pagesize 0
+select sum(lc.pins)/(sum(lc.pins)+sum(lc.reloads))*100
+from v\\$librarycache lc;
+EOF`
+
+    if [ -n "`echo $result | grep ORA-`" ] ; then
+      error=` echo "$result" | grep "ORA-" | head -1`
+      echo "CRITICAL - $error"
+      exit $STATE_CRITICAL
+    fi
+
+    lib_hr=`echo $result | awk '{print int($1)}'`
+
+    if [ $buf_hr -le ${CRIT_LVL} -o $lib_hr -le ${CRIT_LVL} ] ; then
+        export RETURN_MSG="${ORACLE_SID} CRITICAL - Cache Hit Rates: $lib_hr% Lib -- $buf_hr% Buff"
+        exit $STATE_CRITICAL
+    fi
+    if [ $buf_hr -le ${WARN_LVL} -o $lib_hr -le ${WARN_LVL} ] ; then
+        export RETURN_MSG="${ORACLE_SID} WARNING  - Cache Hit Rates: $lib_hr% Lib -- $buf_hr% Buff"
+        exit $STATE_WARNING
+    fi
+    export RETURN_MSG="${ORACLE_SID} OK - Cache Hit Rates: $lib_hr% Lib -- $buf_hr% Buff"
+
+    return ${STATE_OK}
+
+
+}
+
+check_extents() {
+    if [ ${CRIT_LVL} -lt ${WARN_LVL} ] ; then
+        RETURN_MSG="UNKNOWN - Warning level is higher then Crit"
+        return ${STATE_UNKNOWN}
+    fi
+    result=`sqlplus -s ${ORA_USER}/${ORA_PASS}@${ORACLE_SID} << EOF
+set pagesize 200
+column Sname form a40    heading 'Object Name'
+column Size  form 99,999  heading 'Size'
+column Next  form 99,999 heading 'Next'
+column Tname form a15    heading 'TsName'
+
+select a.owner||'.'||a.segment_name "Sname",
+       a.bytes/1024/1024            "Size",
+       a.next_extent/1024/1024 "Next",
+       a.tablespace_name "TName"
+from  sys.dba_segments a
+where a.tablespace_name not like 'T%MP'   -- Exclude TEMP tablespaces
+and   a.tablespace_name like '%${ORA_TS}%'
+and   next_extent * 1 > (                  -- Cannot extend 1x, can change to 2x...
+                           select max(b.bytes)
+                             from dba_free_space b
+                            where a.tablespace_name = b.tablespace_name)
+order by 3 desc;
+EOF`
+ 
+    if [ -n "`echo $result | grep ORA-`" ] ; then
+       error=` echo "$result" | grep "ORA-" | head -1`
+       echo "CRITICAL - $error"
+       exit $STATE_CRITICAL
+    fi
+
+    if [ `echo $result | grep -c "no rows selected"` -eq 1 ]
+    then
+        export RETURN_MSG="${ORACLE_SID} : ${ORA_TS} OK - Extents.  Extensible"
+        return ${STATE_OK}
+    else
+        nn=`expr \`echo "$result" | wc -l\` - 3`
+        export RETURN_MSG="CRIT: Result: $nn objects can't extend"
+        return $STATE_CRITICAL
+    fi
+}
+
+check_tablespace() {
+    if [ ${CRIT_LVL} -lt ${WARN_LVL} ] ; then
+        RETURN_MSG="UNKNOWN - Warning level is more then Crit"
+        return ${STATE_UNKNOWN}
+    fi
+    result=`sqlplus -s ${ORA_USER}/${ORA_PASS}@${ORACLE_SID} << 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='${ORA_TS}';
+EOF`
+ 
+    if [ -n "`echo $result | grep ORA-`" ] ; then
+       error=` echo "$result" | grep "ORA-" | head -1`
+       echo "CRITICAL - $error"
+       exit $STATE_CRITICAL
+    fi
+
+
+    ts_free=`echo $result | awk '{print int($1)}'` 
+    ts_total=`echo $result | awk '{print int($2)}'` 
+    ts_pct=`echo $result | awk '{print int($3)}'` 
+        
+    ce_ret=${STATE_OK}
+    ce_ret_msg=""
+    if [ ${CHECK_EXTENTS} == "yes" ]
+    then
+        check_extents
+        ce_ret=${?}
+        ce_ret_msg=${RETURN_MSG}
+    fi
+    if [ ${ts_pct} -ge ${CRIT_LVL} ] || [ ${ce_ret} -eq ${STATE_CRITICAL} ] 
+    then
+        RETURN_MSG="${ORACLE_SID} : ${ORA_TS} CRITICAL - $ts_pct% used [ $ts_free / $ts_total MB available ] || ${ce_ret_msg}"
+        return ${STATE_CRITICAL}
+    fi
+    if [ ${ts_pct} -ge ${WARN_LVL} ] || [ ${ce_ret} -eq ${STATE_WARNING} ] 
+    then
+        RETURN_MSG="${ORACLE_SID} : ${ORA_TS} WARNING  - $ts_pct% used [ $ts_free / $ts_total MB available ] || ${ce_ret_msg}"
+        return ${STATE_WARNING}
+    fi
+    if [ $ts_free -eq 0 -a $ts_total -eq 0 -a $ts_pct -eq 0 ] || \
+                [ ${ce_ret} -eq ${STATE_UNKNOWN} ]
+    then
+        RETURN_MSG="No data returned by Oracle - tablespace ${ORA_TS} not found? || ${ce_ret_msg}"
+        return ${STATE_UNKNOWN}
+    fi
+    RETURN_MSG="${ORACLE_SID} : ${ORA_TS} OK - $ts_pct% used [ $ts_free / $ts_total MB available ] || ${ce_ret_msg}"
+    return ${STATE_OK}
+}
+
+check_tns() {
+    tnstarg=""
+    if [ -n "${ORA_HOST}" -a "${ORA_HOST}" != "" ]
+    then
+        tnstarg=${ORA_HOST}
+    elif [ -n "${ORACLE_SID}" ] #-a "${ORACLE_SID}" != "" ]
+    then
+        tnstarg=${ORACLE_SID}
+        RETURN_MSG="set tnstarg = $tnstarg (ORACLE_SID = ${ORACLE_SID})"
+    else
+        RETURN_MSG="Could not determine destination"
+        return $STATE_WARNING
+    fi
+        
+    tnschk=` tnsping ${tnstarg}`
+    tnschk2=` echo  $tnschk | grep -c OK`
+    if [ ${tnschk2} -eq 1 ] ; then 
+        tnschk3=` echo $tnschk | sed -e 's/.*(//' -e 's/).*//'`
+        export RETURN_MSG="OK - reply time ${tnschk3} from ${tnstarg}"
+        return ${STATE_OK}
+    else
+        export RETURN_MSG="No TNS Listener on ${tnstarg}"
+        return $STATE_CRITICAL
+    fi
+}
+
+
+check_oranames() {
+    namesctl status ${ORA_NAMES} | awk '
+    /Server has been running for:/ {
+        msg = "OK: Up"
+        for (i = 6; i <= NF; i++) {
+            msg = msg " " $i
+        }
+        status = '${STATE_OK}'
+    }
+    /error/ {
+        msg = "CRITICAL: " $0
+        status = '$STATE_CRITICAL'
+    }
+    END {
+        print msg
+        RETURN status
+    }'
+}
+
+check_db() {
+    pmonchk=`ps -ef | grep -v grep | grep ${ORACLE_SID} | grep -c pmon`
+    if [ ${pmonchk} -ge 1 ] ; then
+        export RETURN_MSG="${ORACLE_SID} OK - ${pmonchk} PMON process(es) running"
+        return ${STATE_OK}
+    else
+        export RETURN_MSG="${2} Database is DOWN"
+        return $STATE_CRITICAL
+    fi
+}
+
+check_login() {
+    loginchk=`sqlplus dummy/user@${ORACLE_SID} < /dev/null`
+    loginchk2=` echo  ${loginchk} | grep -c ORA-01017`
+    if [ ${loginchk2} -eq 1 ] ; then
+        export RETURN_MSG="OK - dummy login connected"
+        return ${STATE_OK}
+    else
+        loginchk3=` echo "${loginchk}" | grep "ORA-" | head -1`
+        export RETURN_MSG="CRITICAL - ${loginchk3}"
+        return $STATE_CRITICAL
+    fi
+}
+#begin our "main"
+#
+
+init_var
+
+
+while getopts ":cC:dehH:ln:p:s:tT:u:W:" arg ; do
+   case ${arg} in
+
+   c )                          #echo "Cache Received" 
+        CHECK_CACHE=yes
+        ;;
+   C )                          #echo "Critical lvl = ${OPTARG}" 
+        CRIT_LVL=${OPTARG}
+        ;;
+   d )                          #echo "DB Received" 
+        CHECK_DB=yes
+        ;;
+   e )                          #echo "Extents Received" 
+        CHECK_EXTENTS=yes
+        ;;
+   H )                          #echo "Host Received = ${OPTARG}" 
+        ORA_HOST=${OPTARG}
+        ;;
+   h )                          #echo "Help Received" 
+        print_help 
+        exit ${STATE_UNKNOWN}
+        ;;
+   l )                          #echo "Login Received" 
+        CHECK_LOGIN=yes
+        ;;
+   n )                          #echo "oranames Received = $OPTARG}" 
+        CHECK_NAMES=yes
+        ORA_NAMES=${OPTARG}
+        ;;
+   p )                          #echo "Passwd Received = ${OPTARG}" 
+        ORA_PASS=${OPTARG}
+        ;;
+   s )                          #echo "SID Received = ${OPTARG}" 
+        ORACLE_SID=${OPTARG}
+        ;;
+   t )                          #echo "tnsnames Received"
+        CHECK_TNS=yes
+        ;;
+   T )                          #echo "Tablespace Received = ${OPTARG}" 
+        ORA_TS=${OPTARG}
+        CHECK_TS=yes
+        ;;
+   u )                          #echo "User Received = ${OPTARG}"
+        ORA_USER=${OPTARG}
+        ;;
+   W )                          #echo "Warning lvl = ${OPTARG}"
+        WARN_LVL=${OPTARG} 
+        ;;
+   \?)  print_usage 
+        exit ${STATE_UNKNOWN}
+        ;;
+   esac
+done
+
+
+shift $(($OPTIND - 1))
+
+if [ "$1" == "--help" ]
+then
+        print_help
+fi
+
+# "This is where new options can be used - working on compat first
+#  "step through following:"
+if [ -z $1 ] 
+then
+  
+if [ -n ${ORACLE_SID} ] && [ "${ORACLE_SID}" != "" ]
+then
+   if [ -n ${ORA_PASS} -a "${ORA_PASS}" != "" ] || \
+                [ -n ${ORA_USER} -a "${ORA_USER}" != "" ] || \
+                [ -n ${CRIT_LVL} >= 0 ] || [ ${WARN_LVL} >= 0 ]
+   then
+        if [ ${CHECK_CACHE} == "yes" ] && [ ${CHECK_TS} == "no" ]
+        then
+                check_cache
+                ret=${?}
+                echo ${RETURN_MSG}
+                exit ${ret}
+        fi
+        if [ ${CHECK_EXTENTS} == "yes" ] && [ ${CHECK_TS} == "no" ]
+        then
+                echo "call check_Extents"
+                check_extents
+                ret=${?}
+                echo ${RETURN_MSG}
+                exit ${RET}
+        fi
+   else
+        echo "error - not proper args"
+   fi
+   if [ -n ${ORA_TS} ] && [ "${ORA_TS}" != "" ]
+   then
+        if [ ${CHECK_TS} == "yes" ] && [ ${CHECK_CACHE} == "no" ]
+        then
+                check_tablespace
+                ret=${?}
+                echo ${RETURN_MSG}
+                exit ${ret}
+        fi
+        if [ ${CHECK_TS} == "yes" ] && [ ${CHECK_CACHE} == "yes" ]
+        then
+                RETURN_MSG="INVALID: Currently Cache and tablespace checks don't mesh"
+                echo ${RETURN_MSG}
+                exit ${STATE_UNKNOWN}
+        fi
+   fi
+   if [ ${CHECK_LOGIN} == "yes" ]
+   then
+        check_login
+        ret=${?}
+        echo ${RETURN_MSG}
+        exit ${ret}
+   fi
+   if [ ${CHECK_DB} == "yes" ]
+   then
+        check_db
+        ret=${?}
+        echo ${RETURN_MSG}
+        exit ${ret}
+   fi   
+fi 
+else
+   echo "Using old Invocation Methods: \"$*\""
+fi
+
+case "$1" in
+1)
+    cmd='--tns'
+    ;;
+2)
+    cmd='--db'
+    ;;
+*)
+    cmd="$1"
+    ;;
+esac
+
+# Information options
+case "$cmd" in
+--help)
+                print_help
+    exit ${STATE_OK}
+    ;;
+-h)
+                print_help
+    exit ${STATE_OK}
+    ;;
+--db)
+                CHECK_DB=yes
+                ORACLE_SID=$2
+                check_db 
+                ret=${?}
+    echo ${RETURN_MSG}
+    exit $ret
+    ;;
+--cache)
+                CHECK_CACHE=yes
+                ORACLE_SID=$2
+                ORA_USER=$3
+                ORA_PASS=$4
+                CRIT_LVL=$5
+                WARN_LVL=$6
+                check_cache
+                ret=${?}
+    echo ${RETURN_MSG}
+    exit $ret
+    ;;
+--extents)
+                CHECK_EXTENTS=yes
+                ORACLE_SID=$2
+                ORA_USER=$3
+                ORA_PASS=$4
+                CRIT_LVL=$5
+                WARN_LVL=$6
+                check_extents
+                ret=${?}
+    echo ${RETURN_MSG}
+    exit $ret
+    ;;
+--login)
+                CHECK_LOGIN=yes
+                ORACLE_SID=$2
+                check_login
+                ret=${?}
+    echo ${RETURN_MSG}
+    exit $ret
+    ;;
+--oranames)
+                CHECK_NAMES=yes
+                ORA_NAMES=$2
+                check_oranames
+    exit ${?}
+    ;;
+--tablespace)
+                CHECK_TS=yes
+                ORACLE_SID=$2
+                ORA_USER=$3
+                ORA_PASS=$4
+                ORA_TS=$5
+                CRIT_LVL=$6
+                WARN_LVL=$7
+                check_tablespace
+                ret=${?} 
+    echo ${RETURN_MSG}
+    exit $ret
+    ;;
+--tns)
+                CHECK_TNS=yes
+                export ORACLE_SID=$2
+                echo "--tns ${ORACLE_SID} "
+                check_tns
+                ret=${?}
+    echo ${RETURN_MSG}
+    exit $ret
+    ;;
+--version)
+                print_revision $PLUGIN $REVISION
+    exit ${STATE_OK}
+    ;;
+-V)
+                print_revision $PLUGIN $REVISION
+    exit ${STATE_OK}
+    ;;
+*)
+    print_usage
+    exit ${STATE_UNKNOWN}
+    ;;
+esac
+
+
+exit ${STATE_UNKNOWN}