[checkmk-commits] Check_MK Git: check_mk: oracle_tablespace: Fixed tablespace size/ free space calculations

Module: check_mk Branch: master Commit: b35f8796d4cf9c956a7cff790823a645fa738a6b URL: http://git.mathias-kettner.de/git/?p=check_mk.git;a=commit;h=b35f8796d4cf9c… Author: Lars Michelsen &lt;lm(a)mathias-kettner.de&gt; Date: Tue Jan 31 13:40:59 2012 +0100 oracle_tablespace: Fixed tablespace size/free space calculations --- ChangeLog | 1 + agents/plugins/mk_oracle | 23 +++++++++++++--- agents/windows/plugins/mk_oracle | 51 ++++++++++++++++++++++++++++++++++++++ checks/oracle_tablespaces | 7 +++-- 4 files changed, 74 insertions(+), 8 deletions(-) diff --git a/ChangeLog b/ChangeLog index 064a472..ce1bd0d 100644 --- a/ChangeLog +++ b/ChangeLog @@ -44,6 +44,7 @@ "Temperature %s", in order to be consistent with the other checks. * mounts: exclude changes of the commit option (might change on laptops), make only switch to ro critical, other changes warning. + * oracle_tablespace: Fixed tablespace size/free space calculations Multisite: * Improve transaction handling and reload detection: user can have diff --git a/agents/plugins/mk_oracle b/agents/plugins/mk_oracle index 0069f22..eaeb49c 100755 --- a/agents/plugins/mk_oracle +++ b/agents/plugins/mk_oracle @@ -114,12 +114,25 @@ for SID in $SIDS; do 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, 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; + 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, decode(sum(fs.blocks), NULL, 0, + sum(fs.blocks)) free_blocks + from dba_data_files f, dba_tablespaces t, dba_free_space fs + where f.tablespace_name = t.tablespace_name + and f.file_id = fs.file_id(+) + group by 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 + UNION + 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, 0 + 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* + fi + } > $CACHE_FILE.new && mv $CACHE_FILE.new $CACHE_FILE || rm -f $CACHE_FILE* " fi done diff --git a/agents/windows/plugins/mk_oracle b/agents/windows/plugins/mk_oracle new file mode 100644 index 0000000..82b772d --- /dev/null +++ b/agents/windows/plugins/mk_oracle @@ -0,0 +1,51 @@ +@echo off +echo ^<^<^<oracle_sessions^>^>^> +(echo.|set /p x=%ORACLE_SID%) +( +echo set cmdsep on +echo set cmdsep '"'; --" +echo "set pages 0" +echo "set feedback off" +echo "set head off" +echo "select count(*) from v$session where status = 'ACTIVE';" +) | sqlplus -S / as sysdba + + +echo ^<^<^<oracle_logswitches^>^>^> +(echo.|set /p x=%ORACLE_SID%) +( +echo set cmdsep on +echo set cmdsep '"'; --" +echo "set pages 0" +echo "set feedback off" +echo "set head off" +echo "select count(*) from v$loghist where first_time > sysdate - 1/24;" +) | sqlplus -S / as sysdba + + + +echo ^<^<^<oracle_tablespaces^>^>^> +( +echo set cmdsep on +echo set cmdsep '"'; --" +echo "set pages 0" +echo "set linesize 900" +echo "set tab off" +echo "set feedback off" +echo "set head off" +echo "column instance format a10" +echo "column file_name format a100" +echo "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, decode(sum(fs.blocks), NULL, 0, sum(fs.blocks)) free_blocks from dba_data_files f, dba_tablespaces t, dba_free_space fs where f.tablespace_name = t.tablespace_name and f.file_id = fs.file_id(+) group by 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 UNION 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, 0 from dba_temp_files f, dba_tablespaces t where f.tablespace_name = t.tablespace_name;" +) | sqlplus -S / as sysdba + +echo ^<^<^<oracle_version^>^>^> +REM (echo %ORACLE_SID%) +(echo.|set /p x=%ORACLE_SID% ) +( +echo set cmdsep on +echo set cmdsep '"'; --" +echo "set pages 0" +echo "set feedback off" +echo "set head off" +echo "select * from v$version;" +) | sqlplus -S / as sysdba diff --git a/checks/oracle_tablespaces b/checks/oracle_tablespaces index 89f0928..a98f95a 100644 --- a/checks/oracle_tablespaces +++ b/checks/oracle_tablespaces @@ -45,6 +45,7 @@ # 9. wheter the file is in use (online) # 10. block size in bytes # 11. status of the table space +# 12. free space in the datafile # default levels for *free* space. float: percent, # integer: MB. @@ -99,7 +100,7 @@ def check_oracle_tablespaces(item, params, info): if line[3] in [ "AVAILABLE", "ONLINE", "READONLY" ]: num_avail += 1 current_size += blocksize * int(line[5]) - used += blocksize * int(line[7]) + used += blocksize * (int(line[5]) - int(line[12])) # Autoextensible? Honor max size if line[4] == "YES": num_extensible += 1 @@ -112,12 +113,12 @@ def check_oracle_tablespaces(item, params, info): incs = free_bl / incsize num_increments += incs increment_size += blocksize * incsize * incs - free_space += blocksize * (incsize * incs + (int(line[5]) - int(line[7]))) + free_space += blocksize * (incsize * incs + (int(line[12]))) # not autoextensible: take current size as maximum else: my_max_size = blocksize * int(line[5]) max_size += my_max_size - free_space += blocksize * (int(line[5]) - int(line[7])) + free_space += blocksize * int(line[12]) if ts_status == None: