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_UNKNOWN"
  91     ;;
  92 -h)
  93     print_help
  94     exit "$STATE_UNKNOWN"
  95     ;;
  96 --version)
  97     print_revision "$PROGNAME" "$REVISION"
  98     exit "$STATE_UNKNOWN"
  99     ;;
 100 -V)
 101     print_revision "$PROGNAME" "$REVISION"
 102     exit "$STATE_UNKNOWN"
 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 -r SID ORACLE_HOME _;
 114         do
 115             if [ "$SID" = "$2" ] || [ "$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" ] && [ -d "$PROGPATH"/oracle ] && ORACLE_HOME=$PROGPATH/oracle
 125
 126 if [ "$cmd" != "--db" ]; then
 127     if [ -z "$ORACLE_HOME" ] || [ ! -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=$(pgrep -f "(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
 223     if echo "$result" | grep -q 'ORA-' ; then
 224         error=$(echo "$result" | grep "ORA-" | head -1)
 225         echo "CRITICAL - $error"
 226         exit "$STATE_CRITICAL"
 227     fi
 228
 229     buf_hr=$(echo "$result" | awk '/^[0-9\. \t]+$/ {print int($1)}')
 230     buf_hrx=$(echo "$result" | awk '/^[0-9\. \t]+$/ {print $1}')
 231     result=$(sqlplus -s "${3}"/"${4}"@"${2}" << EOF
 232 set pagesize 0
 233 set numf '9999999.99'
 234 select sum(lc.pins)/(sum(lc.pins)+sum(lc.reloads))*100
 235 from v\\$librarycache lc;
 236 EOF
 237 )
 238
 239     if echo "$result" | grep -q 'ORA-' ; then
 240         error=$(echo "$result" | grep "ORA-" | head -1)
 241         echo "CRITICAL - $error"
 242         exit "$STATE_CRITICAL"
 243     fi
 244
 245     lib_hr=$(echo "$result" | awk '/^[0-9\. \t]+$/ {print int($1)}')
 246     lib_hrx=$(echo "$result" | awk '/^[0-9\. \t]+$/ {print $1}')
 247
 248     if [ "$buf_hr" -le "${5}" ] || [ "$lib_hr" -le "${5}" ] ; then
 249         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"
 250         exit "$STATE_CRITICAL"
 251     fi
 252     if [ "$buf_hr" -le "${6}" ] || [ "$lib_hr" -le "${6}" ] ; then
 253         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"
 254         exit "$STATE_WARNING"
 255     fi
 256     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"
 257
 258     exit "$STATE_OK"
 259     ;;
 260 --tablespace)
 261     if [ "${6}" -lt "${7}" ] ; then
 262         echo "UNKNOWN - Warning level is more then Crit"
 263         exit "$STATE_UNKNOWN"
 264     fi
 265     result=$(sqlplus -s "${3}"/"${4}"@"${2}" << EOF
 266 set pagesize 0
 267 set numf '9999999.99'
 268 select NVL(b.free,0.0),a.total,100 - trunc(NVL(b.free,0.0)/a.total * 1000) / 10 prc
 269 from (
 270 select tablespace_name,sum(bytes)/1024/1024 total
 271 from dba_data_files group by tablespace_name) A
 272 LEFT OUTER JOIN
 273 ( select tablespace_name,sum(bytes)/1024/1024 free
 274 from dba_free_space group by tablespace_name) B
 275 ON a.tablespace_name=b.tablespace_name WHERE a.tablespace_name='${5}';
 276 EOF
 277 )
 278
 279     if echo "$result" | grep -q 'ORA-' ; then
 280         error=$(echo "$result" | grep "ORA-" | head -1)
 281         echo "CRITICAL - $error"
 282         exit "$STATE_CRITICAL"
 283     fi
 284
 285     ts_free=$(echo "$result" | awk '/^[ 0-9\.\t ]+$/ {print int($1)}')
 286     ts_total=$(echo "$result" | awk '/^[ 0-9\.\t ]+$/ {print int($2)}')
 287     ts_pct=$(echo "$result" | awk '/^[ 0-9\.\t ]+$/ {print int($3)}')
 288     ts_pctx=$(echo "$result" | awk '/^[ 0-9\.\t ]+$/ {print $3}')
 289     if [ "$ts_free" -eq 0 ] && [ "$ts_total" -eq 0 ] && [ "$ts_pct" -eq 0 ] ; then
 290         echo "No data returned by Oracle - tablespace $5 not found?"
 291         exit "$STATE_UNKNOWN"
 292     fi
 293     if [ "$ts_pct" -ge "${6}" ] ; then
 294         echo "${2} : ${5} CRITICAL - $ts_pctx% used [ $ts_free / $ts_total MB available ]|${5}=$ts_pctx%;${7};${6};0;100"
 295         exit "$STATE_CRITICAL"
 296     fi
 297     if [ "$ts_pct" -ge "${7}" ] ; then
 298         echo "${2} : ${5} WARNING  - $ts_pctx% used [ $ts_free / $ts_total MB available ]|${5}=$ts_pctx%;${7};${6};0;100"
 299         exit "$STATE_WARNING"
 300     fi
 301     echo "${2} : ${5} OK - $ts_pctx% used [ $ts_free / $ts_total MB available ]|${5}=$ts_pctx%;${7};${6};0;100"
 302     exit "$STATE_OK"
 303     ;;
 304 *)
 305     print_usage
 306                 exit "$STATE_UNKNOWN"
 307 esac