25 Jul
2011
25 Jul
'11
3:26 p.m.
Module: check_mk
Branch: master
Commit: 1b8f307a7e231eb0d04e8f5348b3ea2815876a6e
URL:
http://git.mathias-kettner.de/git/?p=check_mk.git;a=commit;h=1b8f307a7e231e…
Author: Lars Michelsen <lm(a)mathias-kettner.de>
Date: Mon Jul 25 11:47:51 2011 +0200
Improved mk_oracle agent plugin
* Added caching of results to prevent problems with long
running SQL queries. Cache is controlled by CACHE_MAXAGE var which
is preset to
120 seconds
* EXCLUDE_<sid>=ALL or EXCLUDE_<sid>=oracle_sessions can be
used to exclude specific checks now
* Added optional configuration file to configure the new options
---
ChangeLog | 6 ++
agents/plugins/mk_oracle | 122 +++++++++++++++++++++++++++++++--------------
2 files changed, 90 insertions(+), 38 deletions(-)
diff --git a/ChangeLog b/ChangeLog
index 93f605f..7e1403e 100644
--- a/ChangeLog
+++ b/ChangeLog
@@ -20,6 +20,12 @@
* vbox_guest: new check for checking guest additions of Linux virtual box hosts
* if/if64: Fixed bug in operstate detection when using old tuple based params
* if/if64: Fixed bug in operstate detection when using tuple of valid operstates
+ * mk_oracle: Added caching of results to prevent problems with long
+ running SQL queries. Cache is controlled by CACHE_MAXAGE var which is preset to
+ 120 seconds
+ * mk_oracle: EXCLUDE_<sid>=ALL or EXCLUDE_<sid>=oracle_sessions can be
+ used to exclude specific checks now
+ * mk_oracle: Added optional configuration file to configure the new options
Multisite:
* FIX: make non-Ascii characters in services names work again
diff --git a/agents/plugins/mk_oracle b/agents/plugins/mk_oracle
old mode 100644
new mode 100755
index 18e7bcf..ad0f114
--- a/agents/plugins/mk_oracle
+++ b/agents/plugins/mk_oracle
@@ -26,52 +26,98 @@
# Check_MK agent plugin for monitoring ORACLE databases
# Get list of all running databases
-if [ $(uname -s) = Linux ]
+SIDS=$(UNIX95=true ps ax -o args | sed -n '/^ora_pmon_\([^ ]*\)/s//\1/p')
+if [ -z "$SIDS" ] ; then exit 0 ; fi
+
+CACHE_MAXAGE=120
+
+# Source the optional configuration file for this agent plugin
+if [ -e "$MK_CONFDIR/mk_oracle.cfg" ]
then
- PS="ps ax -o args"
-else
- PS="ps -ef -o args"
+ . $MK_CONFDIR/mk_oracle.cfg
fi
-SIDS=$(UNIX95=true $PS | sed -n '/^ora_pmon_\([^ ]*\)/s//\1/p')
-if [ -z "$SIDS" ] ; then exit 0 ; fi
+# It is possible to filter SIDS totally. Just add the following to
+# the mk_oracle.cfg file:
+#
+# EXCLUDE_<sid>="ALL"
+#
+# Another option is to filter single checks for SIDS. Just add
+# lines as follows to the mk_oracle.cfg file. One service per
+# line:
+#
+# EXCLUDE_<sid>="<service>"
+#
+# For example skip oracle_sessions and oracle_logswitches checks
+# for the instance "mysid".
+#
+# EXCLUDE_mysid="oracle_sessions oracle_logswitches"
+#
+#
+# This check uses a cache file to prevent problems with long running
+# SQL queries. It starts building a cache when
+# a) no cache is present or the cache is too old and
+# b) the cache is not currently being built
+# The cache is used for $CACHE_MAXAGE seconds. The CACHE_MAXAGE
+# option is pre-set to 120 seconds but can be changed in mk_oracle.cfg.
-# Make an sqlplus query, clean up the result and add an artificial
-# column with the SID at the front of each line. If the query fails,
-# no output at all is made.
-function sqlplus
-{
- OUTPUT=$({ echo 'set pages 0' ; echo 'set lines 8000' ; echo 'set
feedback off'; cat ; } | $MK_CONFDIR/sqlplus.sh $1) || return 1
- echo "${OUTPUT}" | sed -e 's/[[:space:]][[:space:]]*/ /g' -e
'/^[[:space:]]*$/d' -e "s/^/$1 /"
-}
+for SID in $SIDS; do
+ EXCLUDE=EXCLUDE_$SID
+ EXCLUDE=${!EXCLUDE}
+ # SID filtered totally?
+ if [ "$EXCLUDE" = "ALL" ]; then
+ continue
+ fi
-# Sessions
-echo '<<<oracle_sessions>>>'
-for SID in $SIDS
-do
- echo "select count(*) from v"'$'"session where status =
'ACTIVE';" | sqlplus "$SID"
-done
+ CACHE_FILE=$MK_CONFDIR/oracle_$SID.cache
-echo '<<<oracle_logswitches>>>'
-for SID in $SIDS
-do
- sqlplus "$SID" <<EOF
-select count(*) from v\$loghist where first_time > sysdate - 1/24;
-EOF
-done
+ # Check if file exists and recent enough
+ if [ -s $CACHE_FILE ]; then
+ NOW=$(date +%s)
+ MTIME=$(stat -c %Y $CACHE_FILE)
+ if [ $(($NOW - $MTIME)) -le $CACHE_MAXAGE ]; then
+ USE_CACHE_FILE=1
+ fi
+ fi
+
+ # Outputs the contents of the cache file when it exists and is recent enough.
+ # When not it checks if there is another task to update the cache is running
+ # at the moment (*.new) file present.
+ # When the cache is old and there is no *new file present, then start a query
+ # to update the information for this instance.
+ if [ -z "$USE_CACHE_FILE" -a ! -e "$CACHE_FILE.new" ]
+ then
+ setsid bash -c "
+ set -o noclobber
+ function sqlplus ()
+ {
+ OUTPUT=\$({ echo 'set pages 0' ; echo 'set lines 8000' ;
echo 'set feedback off'; cat ; } | $MK_CONFDIR/sqlplus.sh \$1) || return 1
+ echo \"\${OUTPUT}\" | sed -e 's/[[:space:]]\+/ /g' -e
'/^[[:space:]]*$/d' -e \"s/^/\$1 /\"
+ }
+
+ {
+ # Only execute checks when not filtered
+ if [ "$EXCLUDE" = "${EXCLUDE/oracle_sessions/}" ]; then
+ echo '<<<oracle_sessions>>>'
+ echo \"select count(*) from v\\\$session where status =
'ACTIVE';\" | sqlplus \"$SID\"
+ fi
+ if [ "$EXCLUDE" = "${EXCLUDE/oracle_logswitches/}" ];
then
+ echo '<<<oracle_logswitches>>>'
+ echo \"select count(*) from v\\\$loghist where first_time >
sysdate - 1/24;\" | sqlplus \"$SID\"
+ fi
-# Tablespaces
-echo '<<<oracle_tablespaces>>>'
-for SID in $SIDS
-do
- sqlplus "$SID" <<EOF | sed 's/READ ONLY/READONLY/g'
-select f.file_name, f.tablespace_name, f.status, f.AUTOEXTENSIBLE, f.blocks, f.maxblocks,
f.blocks - b.free_blocks as used_blocks, f.INCREMENT_BY, f.ONLINE_STATUS, t.BLOCK_SIZE,
t.status
-from dba_data_files f, dba_tablespaces t ,(SELECT file_id, SUM(blocks) free_blocks FROM
dba_free_space b GROUP BY file_id) b
-where f.tablespace_name = t.tablespace_name and f.file_id=b.file_id
+ if [ "$EXCLUDE" = "${EXCLUDE/oracle_tablespaces/}" ];
then
+ echo '<<<oracle_tablespaces>>>'
+ sqlplus "$SID" <<EOF | sed 's/READ
ONLY/READONLY/g'
+select f.file_name, f.tablespace_name, f.status, f.AUTOEXTENSIBLE, f.blocks, f.maxblocks,
f.USER_BLOCKS, f.INCREMENT_BY, f.ONLINE_STATUS, t.BLOCK_SIZE, t.status from
dba_data_files f, dba_tablespaces t where f.tablespace_name = t.tablespace_name
UNION
-select f.file_name, f.tablespace_name, f.status, f.AUTOEXTENSIBLE, f.blocks, f.maxblocks,
b.blocks_used as used_blocks, f.INCREMENT_BY, 'TEMP', t.BLOCK_SIZE, t.status
-from dba_temp_files f, dba_tablespaces t ,V\$TEMP_SPACE_HEADER b
-where f.tablespace_name = t.tablespace_name and f.file_id=b.file_id ;
+select f.file_name, f.tablespace_name, f.status, f.AUTOEXTENSIBLE, f.blocks, f.maxblocks,
f.USER_BLOCKS, f.INCREMENT_BY, 'TEMP', t.BLOCK_SIZE, t.status from
dba_temp_files f, dba_tablespaces t where f.tablespace_name = t.tablespace_name;
EOF
+ fi
+ } > $CACHE_FILE.new && mv $CACHE_FILE.new $CACHE_FILE || rm -f
$CACHE_FILE*
+ "
+ else
+ cat "$CACHE_FILE"
+ fi
done