diff options
| -rwxr-xr-x | plugins-scripts/check_oracle.sh | 75 |
1 files changed, 75 insertions, 0 deletions
diff --git a/plugins-scripts/check_oracle.sh b/plugins-scripts/check_oracle.sh index 7e2f6f99..998899b9 100755 --- a/plugins-scripts/check_oracle.sh +++ b/plugins-scripts/check_oracle.sh @@ -37,6 +37,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 --oranames <Hostname>" echo " $PROGNAME --help" echo " $PROGNAME --version" @@ -56,6 +58,14 @@ print_help() { 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 "--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" @@ -191,6 +201,71 @@ case "$cmd" in exit $STATE_CRITICAL fi ;; +--cache) + if [ ${5} -gt ${6} ] ; then + 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'; ` + + 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 ; ` + + 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" + 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" + exit $STATE_WARNING + fi + echo "${3} : ${4} OK - Cache Hit Rates: $lib_hr % Lib -- $buf_hr % Buff" + + exit $STATE_OK + ;; +--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 + ;; *) print_usage exit $STATE_UNKNOWN |