Module: check_mk Branch: master Commit: 762ba465b49f34df9d49e5332523fbfda868ce12 URL: http://git.mathias-kettner.de/git/?p=check_mk.git;a=commit;h=762ba465b49f34… Author: Simon Betz &lt;si(a)mathias-kettner.de&gt; Date: Fri Sep 30 15:34:07 2016 +0200 3907 FIX oracle_tablespaces: simplyfied free space calculation depending on version Oracle version greater than 11: The size of next extent in datafile is ignored when remaining free space is > then next extend. Oracle uses all space up to the maximum! Oracle version lower than 11: The free space in this table is the current free space plus the additional space that can be gathered by using all available remaining increment --- .werks/3907 | 18 ++++++++++++++++++ ChangeLog | 1 + agents/plugins/mk_oracle | 7 +++++-- checks/oracle_tablespaces | 31 ++++++++++++++++++++++--------- 4 files changed, 46 insertions(+), 11 deletions(-) diff --git a/.werks/3907 b/.werks/3907 new file mode 100644 index 0000000..2cb14a3 --- /dev/null +++ b/.werks/3907 @@ -0,0 +1,18 @@ +Title: oracle_tablespaces: simplyfied free space calculation depending on version +Level: 1 +Component: checks +Class: fix +Compatible: compat +State: unknown +Version: 1.4.0i2 +Date: 1475242264 + +Oracle version greater than 11: +The size of next extent in datafile is ignored when remaining +free space is > then next extend. Oracle uses all space up to the maximum! + +Oracle version lower than 11: +The free space in this table is the current free space plus +the additional space that can be gathered by using all available +remaining increment + diff --git a/ChangeLog b/ChangeLog index c73f3ed..7f25a5c 100644 --- a/ChangeLog +++ b/ChangeLog @@ -4,6 +4,7 @@ Checks & Agents: * 3894 FIX: mkeventd_status: Fixed bug in case Event Console is not running + * 3907 FIX: oracle_tablespaces: simplyfied free space calculation depending on version... WATO: * 3915 User access times: New icon when never logged in; New column "last seen" diff --git a/agents/plugins/mk_oracle b/agents/plugins/mk_oracle index 040e9e7..f873d02 100755 --- a/agents/plugins/mk_oracle +++ b/agents/plugins/mk_oracle @@ -183,10 +183,12 @@ sql_tablespaces() ||'|'|| ONLINE_STATUS ||'|'|| BLOCK_SIZE ||'|'|| decode(tstatus,'READ ONLY', 'READONLY', tstatus) || '|' || free_blocks ||'|'|| contents + ||'|'|| iversion from v\$database d , v\$instance i, ( select f.file_name, f.tablespace_name, f.status fstatus, f.AUTOEXTENSIBLE, f.blocks, f.maxblocks, f.USER_BLOCKS, f.INCREMENT_BY, - f.ONLINE_STATUS, t.BLOCK_SIZE, t.status tstatus, nvl(sum(fs.blocks),0) free_blocks, t.contents + f.ONLINE_STATUS, t.BLOCK_SIZE, t.status tstatus, nvl(sum(fs.blocks),0) free_blocks, t.contents, + (select version from v\$instance) iversion 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(+) @@ -196,7 +198,8 @@ sql_tablespaces() 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, sum(sh.blocks_free) free_blocks, 'TEMPORARY' + t.BLOCK_SIZE, t.status, sum(sh.blocks_free) free_blocks, 'TEMPORARY', + (select version from v\$instance) version from v\$thread th, dba_temp_files f, dba_tablespaces t, v\$temp_space_header sh WHERE f.tablespace_name = t.tablespace_name and f.file_id = sh.file_id GROUP BY th.instance, f.file_name, f.tablespace_name, f.status, diff --git a/checks/oracle_tablespaces b/checks/oracle_tablespaces index e202c79..7e799cc 100644 --- a/checks/oracle_tablespaces +++ b/checks/oracle_tablespaces @@ -100,20 +100,25 @@ def parse_oracle_tablespaces(info): sid = line[0] error_sids[sid] = err - if len(line) not in (13, 14): + if len(line) not in (13, 14, 15): continue sid, datafile_name, ts_name, datafile_status, autoextensible, \ filesize_blocks, max_filesize_blocks, used_blocks, increment_size, \ file_online_status, block_size, ts_status, free_space = line[:13] + db_version = 0 + if len(line) == 14: ts_type = line[13] else: # old behaivor is all Tablespaces are treated as PERMANENT ts_type = 'PERMANENT' - tablespaces.setdefault((node_name, sid, ts_name), []) + if len(line) == 15: + db_version = line[14].split('.')[0] + + tablespaces.setdefault((node_name, sid, ts_name, db_version), []) this_tablespace = { "name" : datafile_name, @@ -145,13 +150,13 @@ def parse_oracle_tablespaces(info): "increment_size" : None, }) - tablespaces[(node_name, sid, ts_name)].append(this_tablespace) + tablespaces[(node_name, sid, ts_name, db_version)].append(this_tablespace) # Now join this into one dictionary. If there are more than # one nodes per tablespace, then we select that node with the # most data files result = {} - for (node_name, sid, ts_name), datafiles in tablespaces.items(): + for (node_name, sid, ts_name, db_version), datafiles in tablespaces.items(): ts_key = (sid, ts_name) # Use data from this node, if it is the first/only, or if it # has more data files than a previous one @@ -159,6 +164,7 @@ def parse_oracle_tablespaces(info): len(result[ts_key]["datafiles"]) < len(datafiles): result[ts_key] = { + "db_version" : db_version, "datafiles" : datafiles, "type" : datafiles[0]["ts_type"], "status" : datafiles[0]["ts_status"], @@ -208,6 +214,7 @@ def check_oracle_tablespaces(item, params, parsed): ts_type = tablespace["type"] ts_status = tablespace["status"] + db_version = tablespace["db_version"] num_files = 0 num_avail = 0 num_extensible = 0 @@ -267,10 +274,15 @@ def check_oracle_tablespaces(item, params, parsed): num_increments += incs increment_size += incsize * incs - # The free space in this table is the current free space plus - # the additional space that can be gathered by using all available - # remaining increments - free_space += increment_size + df_free_space + if db_version >= 11: + # The size of next extent in datafile is ignored when remaining + # free space is > then next extend. Oracle uses all space up to the maximum! + free_space += df_max_size - df_size + else: + # The free space in this table is the current free space plus + # the additional space that can be gathered by using all available + # remaining increments + free_space += increment_size + df_free_space # not autoextensible: take current size as maximum else: @@ -283,7 +295,8 @@ def check_oracle_tablespaces(item, params, parsed): get_bytes_human_readable(used_size), get_bytes_human_readable(max_size))] - if num_extensible > 0: + if num_extensible > 0 and db_version <= 10: + # only display the number of remaining extents in Databases <= 10g infotexts.append("%d increments (%s)" % \ (num_increments, get_bytes_human_readable(increment_size)))