plugins-scripts/check_oracle.sh

   1 #!/bin/sh
   2 #
   3 # latigid010@yahoo.com
   4 # 01/06/2000
   5 #
   6 #  This Monitoring plugin was created to check Oracle status
   7 #
   8
   9 PATH="@TRUSTED_PATH@"
  10 export PATH
  11 PROGNAME=`basename $0`
  12 PROGPATH=`echo $0 | sed -e 's,[\\/][^\\/][^\\/]*$,,'`
  13 REVISION="@NP_VERSION@"
  14
  15 . $PROGPATH/utils.sh
  16
  17
  18 print_usage() {
  19   echo "Usage:"
  20   echo "  $PROGNAME --tns <Oracle Sid or Hostname/IP address>"
  21   echo "  $PROGNAME --db <ORACLE_SID>"
  22   echo "  $PROGNAME --login <ORACLE_SID>"
  23   echo "  $PROGNAME --connect <ORACLE_SID>"
  24   echo "  $PROGNAME --cache <ORACLE_SID> <USER> <PASS> <CRITICAL> <WARNING>"
  25   echo "  $PROGNAME --tablespace <ORACLE_SID> <USER> <PASS> <TABLESPACE> <CRITICAL> <WARNING>"
  26   echo "  $PROGNAME --oranames <Hostname>"
  27   echo "  $PROGNAME --help"
  28   echo "  $PROGNAME --version"
  29 }
  30
  31 print_help() {
  32   print_revision $PROGNAME $REVISION
  33   echo ""
  34   print_usage
  35   echo ""
  36   echo "Check Oracle status"
  37   echo ""
  38   echo "--tns SID/IP Address"
  39   echo "   Check remote TNS server"
  40   echo "--db SID"
  41   echo "   Check local database (search /bin/ps for PMON process) and check"
  42   echo "   filesystem for sgadefORACLE_SID.dbf"
  43   echo "--login SID"
  44   echo "   Attempt a dummy login and alert if not ORA-01017: invalid username/password"
  45   echo "--connect SID"
  46   echo "   Attempt a login and alert if an ORA- error is returned"
  47   echo "--cache"
  48   echo "   Check local database for library and buffer cache hit ratios"
  49   echo "       --->  Requires Oracle user/password and SID specified."
  50   echo "                --->  Requires select on v_$sysstat and v_$librarycache"
  51   echo "--tablespace"
  52   echo "   Check local database for tablespace capacity in ORACLE_SID"
  53   echo "       --->  Requires Oracle user/password specified."
  54   echo "                --->  Requires select on dba_data_files and dba_free_space"
  55   echo "--oranames Hostname"
  56   echo "   Check remote Oracle Names server"
  57   echo "--help"
  58   echo "   Print this help screen"
  59   echo "--version"
  60   echo "   Print version and license information"
  61   echo ""
  62   echo "If the plugin doesn't work, check that the ORACLE_HOME environment"
  63   echo "variable is set, that ORACLE_HOME/bin is in your PATH, and the"
  64   echo "tnsnames.ora file is locatable and is properly configured."
  65   echo ""
  66   echo "When checking local database status your ORACLE_SID is case sensitive."
  67   echo ""
  68   echo "If you want to use a default Oracle home, add in your oratab file:"
  69   echo "*:/opt/app/oracle/product/7.3.4:N"
  70   echo ""
  71   support
  72 }
  73
  74 case "$1" in
  75 1)
  76     cmd='--tns'
  77     ;;
  78 2)
  79     cmd='--db'
  80     ;;
  81 *)
  82     cmd="$1"
  83     ;;
  84 esac
  85
  86 # Information options
  87 case "$cmd" in
  88 --help)
  89                 print_help
  90     exit $STATE_OK
  91     ;;
  92 -h)
  93                 print_help
  94     exit $STATE_OK
  95     ;;
  96 --version)
  97                 print_revision $PROGNAME $REVISION
  98     exit $STATE_OK
  99     ;;
 100 -V)
 101                 print_revision $PROGNAME $REVISION
 102     exit $STATE_OK
 103     ;;
 104 esac
 105
 106 # Hunt down a reasonable ORACLE_HOME
 107 if [ -z "$ORACLE_HOME" ] ; then
 108         # Adjust to taste
 109         for oratab in /var/opt/oracle/oratab /etc/oratab
 110         do
 111         [ ! -f $oratab ] && continue
 112         ORACLE_HOME=`IFS=:
 113                 while read SID ORACLE_HOME junk;
 114                 do
 115                         if [ "$SID" = "$2" -o "$SID" = "*" ] ; then
 116                                 echo $ORACLE_HOME;
 117                                 exit;
 118                         fi;
 119                 done < $oratab`
 120         [ -n "$ORACLE_HOME" ] && break
 121         done
 122 fi
 123 # Last resort
 124 [ -z "$ORACLE_HOME" -a -d $PROGPATH/oracle ] && ORACLE_HOME=$PROGPATH/oracle
 125
 126 if [ "$cmd" != "--db" ]; then
 127         if [ -z "$ORACLE_HOME" -o ! -d "$ORACLE_HOME" ] ; then
 128                 echo "Cannot determine ORACLE_HOME for sid $2"
 129                 exit $STATE_UNKNOWN
 130         fi
 131 fi
 132 PATH=$PATH:$ORACLE_HOME/bin
 133 LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
 134 export ORACLE_HOME PATH LD_LIBRARY_PATH
 135
 136 case "$cmd" in
 137 --tns)
 138     tnschk=` tnsping $2`
 139     tnschk2=` echo  $tnschk | grep -c OK`
 140     if [ ${tnschk2} -eq 1 ] ; then
 141         tnschk3=${tnschk##*(}; tnschk3=${tnschk3%)*}
 142         echo "OK - reply time ${tnschk3} from $2"
 143         exit $STATE_OK
 144     else
 145         echo "No TNS Listener on $2"
 146         exit $STATE_CRITICAL
 147     fi
 148     ;;
 149 --oranames)
 150     namesctl status $2 | awk '
 151     /Server has been running for:/ {
 152         msg = "OK: Up"
 153         for (i = 6; i <= NF; i++) {
 154             msg = msg " " $i
 155         }
 156         status = '$STATE_OK'
 157     }
 158     /error/ {
 159         msg = "CRITICAL: " $0
 160         status = '$STATE_CRITICAL'
 161     }
 162     END {
 163         print msg
 164         exit status
 165     }'
 166     ;;
 167 --db)
 168     pmonchk=`ps -ef | grep -v grep | grep -E -c "(asm|ora)_pmon_${2}$"`
 169     if [ ${pmonchk} -ge 1 ] ; then
 170         echo "${2} OK - ${pmonchk} PMON process(es) running"
 171         exit $STATE_OK
 172     #if [ -f $ORACLE_HOME/dbs/sga*${2}* ] ; then
 173         #if [ ${pmonchk} -eq 1 ] ; then
 174     #utime=`ls -la $ORACLE_HOME/dbs/sga*$2* | cut -c 43-55`
 175             #echo "${2} OK - running since ${utime}"
 176             #exit $STATE_OK
 177         #fi
 178     else
 179         echo "${2} Database is DOWN"
 180         exit $STATE_CRITICAL
 181     fi
 182     ;;
 183 --login)
 184     loginchk=`sqlplus dummy/user@$2 < /dev/null`
 185     loginchk2=` echo  $loginchk | grep -c ORA-01017`
 186     if [ ${loginchk2} -eq 1 ] ; then
 187         echo "OK - dummy login connected"
 188         exit $STATE_OK
 189     else
 190         loginchk3=` echo "$loginchk" | grep "ORA-" | head -1`
 191         echo "CRITICAL - $loginchk3"
 192         exit $STATE_CRITICAL
 193     fi
 194     ;;
 195 --connect)
 196     connectchk=`sqlplus $2 < /dev/null`
 197     connectchk2=` echo  $connectchk | grep -c ORA-`
 198     if [ ${connectchk2} -eq 0 ] ; then
 199         echo "OK - login successful"
 200         exit $STATE_OK
 201     else
 202         connectchk3=` echo "$connectchk" | grep "ORA-" | head -1`
 203         echo "CRITICAL - $connectchk3"
 204         exit $STATE_CRITICAL
 205     fi
 206     ;;
 207 --cache)
 208     if [ ${5} -gt ${6} ] ; then
 209         echo "UNKNOWN - Warning level is less then Crit"
 210         exit $STATE_UNKNOWN
 211     fi
 212     result=`sqlplus -s ${3}/${4}@${2} << EOF
 213 set pagesize 0
 214 set numf '9999999.99'
 215 select (1-(pr.value/(dbg.value+cg.value)))*100
 216 from v\\$sysstat pr, v\\$sysstat dbg, v\\$sysstat cg
 217 where pr.name='physical reads'
 218 and dbg.name='db block gets'
 219 and cg.name='consistent gets';
 220 EOF`
 221
 222     if [ -n "`echo $result | grep ORA-`" ] ; then
 223       error=` echo "$result" | grep "ORA-" | head -1`
 224       echo "CRITICAL - $error"
 225       exit $STATE_CRITICAL
 226     fi
 227
 228     buf_hr=`echo "$result" | awk '/^[0-9\. \t]+$/ {print int($1)}'`
 229     buf_hrx=`echo "$result" | awk '/^[0-9\. \t]+$/ {print $1}'`
 230     result=`sqlplus -s ${3}/${4}@${2} << EOF
 231 set pagesize 0
 232 set numf '9999999.99'
 233 select sum(lc.pins)/(sum(lc.pins)+sum(lc.reloads))*100
 234 from v\\$librarycache lc;
 235 EOF`
 236
 237     if [ -n "`echo $result | grep ORA-`" ] ; then
 238       error=` echo "$result" | grep "ORA-" | head -1`
 239       echo "CRITICAL - $error"
 240       exit $STATE_CRITICAL
 241     fi
 242
 243     lib_hr=`echo "$result" | awk '/^[0-9\. \t]+$/ {print int($1)}'`
 244     lib_hrx=`echo "$result" | awk '/^[0-9\. \t]+$/ {print $1}'`
 245
 246     if [ $buf_hr -le ${5} -o $lib_hr -le ${5} ] ; then
 247         echo "${2} CRITICAL - Cache Hit Rates: $lib_hrx% Lib -- $buf_hrx% Buff|lib=$lib_hrx%;${6};${5};0;100 buffer=$buf_hrx%;${6};${5};0;100"
 248         exit $STATE_CRITICAL
 249     fi
 250     if [ $buf_hr -le ${6} -o $lib_hr -le ${6} ] ; then
 251         echo "${2} WARNING  - Cache Hit Rates: $lib_hrx% Lib -- $buf_hrx% Buff|lib=$lib_hrx%;${6};${5};0;100 buffer=$buf_hrx%;${6};${5};0;100"
 252         exit $STATE_WARNING
 253     fi
 254     echo "${2} OK - Cache Hit Rates: $lib_hrx% Lib -- $buf_hrx% Buff|lib=$lib_hrx%;${6};${5};0;100 buffer=$buf_hrx%;${6};${5};0;100"
 255
 256     exit $STATE_OK
 257     ;;
 258 --tablespace)
 259     if [ ${6} -lt ${7} ] ; then
 260         echo "UNKNOWN - Warning level is more then Crit"
 261         exit $STATE_UNKNOWN
 262     fi
 263     result=`sqlplus -s ${3}/${4}@${2} << EOF
 264 set pagesize 0
 265 set numf '9999999.99'
 266 select NVL(b.free,0.0),a.total,100 - trunc(NVL(b.free,0.0)/a.total * 1000) / 10 prc
 267 from (
 268 select tablespace_name,sum(bytes)/1024/1024 total
 269 from dba_data_files group by tablespace_name) A
 270 LEFT OUTER JOIN
 271 ( select tablespace_name,sum(bytes)/1024/1024 free
 272 from dba_free_space group by tablespace_name) B
 273 ON a.tablespace_name=b.tablespace_name WHERE a.tablespace_name='${5}';
 274 EOF`
 275
 276     if [ -n "`echo $result | grep ORA-`" ] ; then
 277       error=` echo "$result" | grep "ORA-" | head -1`
 278       echo "CRITICAL - $error"
 279       exit $STATE_CRITICAL
 280     fi
 281
 282     ts_free=`echo "$result" | awk '/^[ 0-9\.\t ]+$/ {print int($1)}'`
 283     ts_total=`echo "$result" | awk '/^[ 0-9\.\t ]+$/ {print int($2)}'`
 284     ts_pct=`echo "$result" | awk '/^[ 0-9\.\t ]+$/ {print int($3)}'`
 285     ts_pctx=`echo "$result" | awk '/^[ 0-9\.\t ]+$/ {print $3}'`
 286     if [ "$ts_free" -eq 0 -a "$ts_total" -eq 0 -a "$ts_pct" -eq 0 ] ; then
 287         echo "No data returned by Oracle - tablespace $5 not found?"
 288         exit $STATE_UNKNOWN
 289     fi
 290     if [ "$ts_pct" -ge ${6} ] ; then
 291         echo "${2} : ${5} CRITICAL - $ts_pctx% used [ $ts_free / $ts_total MB available ]|${5}=$ts_pctx%;${7};${6};0;100"
 292         exit $STATE_CRITICAL
 293     fi
 294     if [ "$ts_pct" -ge ${7} ] ; then
 295         echo "${2} : ${5} WARNING  - $ts_pctx% used [ $ts_free / $ts_total MB available ]|${5}=$ts_pctx%;${7};${6};0;100"
 296         exit $STATE_WARNING
 297     fi
 298     echo "${2} : ${5} OK - $ts_pctx% used [ $ts_free / $ts_total MB available ]|${5}=$ts_pctx%;${7};${6};0;100"
 299     exit $STATE_OK
 300     ;;
 301 *)
 302     print_usage
 303                 exit $STATE_UNKNOWN
 304 esac