checks/oracle_tablespaces

   1 #!/usr/bin/python
   2 # -*- encoding: utf-8; py-indent-offset: 4 -*-
   3 # +------------------------------------------------------------------+
   4 # |             ____ _               _        __  __ _  __           |
   5 # |            / ___| |__   ___  ___| | __   |  \/  | |/ /           |
   6 # |           | |   | '_ \ / _ \/ __| |/ /   | |\/| | ' /            |
   7 # |           | |___| | | |  __/ (__|   <    | |  | | . \            |
   8 # |            \____|_| |_|\___|\___|_|\_\___|_|  |_|_|\_\           |
   9 # |                                                                  |
  10 # | Copyright Mathias Kettner 2014             mk@mathias-kettner.de |
  11 # +------------------------------------------------------------------+
  12 #
  13 # This file is part of Check_MK.
  14 # The official homepage is at http://mathias-kettner.de/check_mk.
  15 #
  16 # check_mk is free software;  you can redistribute it and/or modify it
  17 # under the  terms of the  GNU General Public License  as published by
  18 # the Free Software Foundation in version 2.  check_mk is  distributed
  19 # in the hope that it will be useful, but WITHOUT ANY WARRANTY;  with-
  20 # out even the implied warranty of  MERCHANTABILITY  or  FITNESS FOR A
  21 # PARTICULAR PURPOSE. See the  GNU General Public License for more de-
  22 # tails. You should have  received  a copy of the  GNU  General Public
  23 # License along with GNU Make; see the file  COPYING.  If  not,  write
  24 # to the Free Software Foundation, Inc., 51 Franklin St,  Fifth Floor,
  25 # Boston, MA 02110-1301 USA.
  26
  27 # no used space check for Tablsspaces with CONTENTS in ('TEMPORARY','UNDO')
  28 # It is impossible to check the used space in UNDO and TEMPORARY Tablespaces
  29 # These Types of Tablespaces are ignored in this plugin.
  30 # This restriction is only working with newer agents, because we need an
  31 # additional parameter at end if each datafile
  32
  33 # This definition needs to be removed at a later stage
  34 # A previous version of this check didn't write the parameter
  35 # name into the autochecks file, but the parameter itself
  36 # default levels for *free* space. float: percent,
  37 # integer: MB.
  38 oracle_tablespaces_default_levels = (10.0, 5.0)
  39
  40 factory_settings["oracle_tablespaces_defaults"] = {
  41     "levels": (10.0, 5.0),
  42     "magic_normsize": 1000,
  43     "magic_maxlevels": (60.0, 50.0),
  44     "defaultincrement": True,
  45 }
  46
  47 # Whether to check auto extend settings. Note: this setting is not ment to
  48 # be changed anymore. It cannot be edited via WATO either. There now exists
  49 # a check parameter, where the behaviour can be configured on a per-service-base.
  50 oracle_tablespaces_check_autoext = True
  51
  52 # this parameter is deprecated and needed for old configurations with
  53 # parameter in main.mk. It is not used anymore!
  54 oracle_tablespaces_check_default_increment = True
  55
  56 # <<<oracle_tablespaces>>>
  57 # pengt /database/pengt/daten155/dbf/system_01.dbf SYSTEM AVAILABLE YES 38400 4194302 38392 1280 SYSTEM 8192 ONLINE
  58 # pengt /database/pengt/daten155/dbf/undotbs_01.dbf UNDOTBS1 AVAILABLE YES 128000 4194302 127992 640 ONLINE 8192 ONLINE
  59 # pengt /database/pengt/daten155/dbf/sysaux_01.dbf SYSAUX AVAILABLE YES 25600 4194302 25592 1280 ONLINE 8192 ONLINE
  60 # pengt /database/pengt/daten155/dbf/ts_user_01.dbf TS_USER AVAILABLE YES 8480 1280000 8472 160 ONLINE 8192 ONLINE
  61 # pengt /database/pengt/daten155/dbf/TS_PENG_ABR_01.dbf TS_PENG_ABR AVAILABLE YES 12800 1280000 12792 12800 ONLINE 8192 ONLINE
  62
  63 # invalid data
  64 # <<<oracle_tablespaces>>>
  65 # AIMCOND1|/u00/app/oracle/product/db12010/dbs/MISSING00064|CONRPG_DATA|AVAILABLE||||||OFFLINE|8192|ONLINE|0|PERMANENT
  66 # MAE|/opt/oracle/oracle_base/product/11.2.0.4/dbs/pslife_dwh.dbf|PSLIFE_DWH|AVAILABLE||||||RECOVER|8192|OFFLINE|0|PERMANENT
  67
  68 # Order of columns (it is a table of data files, so table spaces appear multiple times)
  69 # -1 Node info (added by Check_MK)
  70 # 0  database SID
  71 # 1  data file name
  72 # 2  table space name
  73 # 3  status of the data file
  74 # 4  whether the file is auto extensible
  75 # 5  current size of data file in blocks
  76 # 6  maximum size of data file in blocks (if auto extensible)
  77 # 7  currently number of blocks used by user data
  78 # 8  size of next increment in blocks (if auto extensible)
  79 # 9  wheter the file is in use (online)
  80 # 10 block size in bytes
  81 # 11 status of the table space
  82 # 12 free space in the datafile
  83 # 13 Tablespace-Type (PERMANENT, UNDO, TEMPORARY)
  84
  85
  86 def parse_oracle_tablespaces(info):
  87     tablespaces = {}
  88     error_sids = {}
  89
  90     for line in info:
  91         node_name = line[0]
  92         line = line[1:]
  93
  94         # Check for query errors
  95         err = oracle_handle_ora_errors(line)
  96         if err is False:
  97             continue  # ignore ancient agent outputs
  98         elif isinstance(err, tuple):
  99             sid = line[0]
 100             error_sids[sid] = err
 101
 102         if len(line) not in (13, 14, 15):
 103             continue
 104
 105         sid, datafile_name, ts_name, datafile_status, autoextensible, \
 106         filesize_blocks, max_filesize_blocks, used_blocks, increment_size, \
 107         file_online_status, block_size, ts_status, free_space = line[:13]
 108
 109         db_version = 0
 110
 111         if len(line) >= 14:
 112             ts_type = line[13]
 113         else:
 114             # old behaivor is all Tablespaces are treated as PERMANENT
 115             ts_type = 'PERMANENT'
 116
 117         if len(line) == 15:
 118             db_version = line[14].split('.')[0]
 119
 120         tablespaces.setdefault((node_name, sid, ts_name, db_version), [])
 121
 122         this_tablespace = {
 123             "name": datafile_name,
 124             "status": datafile_status,
 125             "autoextensible": autoextensible == "YES",
 126             "ts_type": ts_type,
 127             "ts_status": ts_status,
 128             "file_online_status": file_online_status,
 129         }
 130
 131         try:
 132             bs = int(block_size)
 133             this_tablespace.update({
 134                 "block_size": bs,
 135                 "size": int(filesize_blocks) * bs,
 136                 "max_size": int(max_filesize_blocks) * bs,
 137                 "used_size": int(used_blocks) * bs,
 138                 "free_space": int(free_space) * bs,
 139                 "increment_size": int(increment_size) * bs,
 140             })
 141
 142         except:
 143             this_tablespace.update({
 144                 "block_size": None,
 145                 "size": None,
 146                 "max_size": None,
 147                 "used_size": None,
 148                 "free_space": None,
 149                 "increment_size": None,
 150             })
 151
 152         tablespaces[(node_name, sid, ts_name, db_version)].append(this_tablespace)
 153
 154     # Now join this into one dictionary. If there are more than
 155     # one nodes per tablespace, then we select that node with the
 156     # most data files
 157     result = {}
 158     for (node_name, sid, ts_name, db_version), datafiles in tablespaces.items():
 159         ts_key = (sid, ts_name)
 160         # Use data from this node, if it is the first/only, or if it
 161         # has more data files than a previous one
 162         if ts_key not in result or \
 163            len(result[ts_key]["datafiles"]) < len(datafiles):
 164
 165             result[ts_key] = {
 166                 "db_version": db_version,
 167                 "datafiles": datafiles,
 168                 "type": datafiles[0]["ts_type"],
 169                 "status": datafiles[0]["ts_status"],
 170                 "autoextensible": False,
 171                 "amount_missing_filenames": len([f for f in datafiles if f['name'] == ''])
 172             }
 173
 174             for df in datafiles:
 175                 if df["autoextensible"]:
 176                     result[ts_key]["autoextensible"] = True
 177
 178     return result, error_sids
 179
 180
 181 def inventory_oracle_tablespaces(parsed):
 182     tablespaces, _error_sids = parsed
 183     for (sid, ts_name), tablespace in tablespaces.items():
 184         if tablespace["status"] in ("ONLINE", "READONLY", "OFFLINE"):
 185             if oracle_tablespaces_check_autoext:
 186                 ae = tablespace["autoextensible"]
 187             else:
 188                 ae = None  # means: ignore, only display setting
 189
 190             parameters = {"autoextend": ae}
 191             yield "%s.%s" % (sid, ts_name), parameters
 192
 193
 194 def check_oracle_tablespaces(item, params, parsed):
 195     try:
 196         if item.count('.') == 2:
 197             # Pluggable Database: item = <CDB>.<PDB>.<Tablespace>
 198             cdb, pdb, ts_name = item.split('.')
 199             sid = cdb + "." + pdb
 200         else:
 201             sid, ts_name = item.split('.', 1)
 202     except ValueError:
 203         yield 3, 'Invalid check item (must be <SID>.<tablespace>)'
 204         return
 205
 206     tablespaces, error_sids = parsed
 207     if sid in error_sids:
 208         yield error_sids[sid]
 209         return
 210
 211     # In case of missing information we assume that the login into
 212     # the database has failed and we simply skip this check. It won't
 213     # switch to UNKNOWN, but will get stale.
 214     # TODO Treatment as in db2 and mssql dbs
 215     # "ts_status is None" possible?
 216     tablespace = tablespaces.get((sid, ts_name))
 217     if not tablespace or tablespace["status"] is None:
 218         raise MKCounterWrapped("Login into database failed")
 219
 220     ts_type = tablespace["type"]
 221     ts_status = tablespace["status"]
 222     db_version = tablespace["db_version"]
 223     num_files = 0
 224     num_avail = 0
 225     num_extensible = 0
 226     current_size = 0
 227     max_size = 0
 228     used_size = 0
 229     num_increments = 0
 230     increment_size = 0
 231     free_space = 0
 232     file_online_states = {}
 233
 234     # Conversion of old autochecks params
 235     if isinstance(params, tuple):
 236         params = {"autoextend": params[0], "levels": params[1:]}
 237
 238     autoext = params.get("autoextend")
 239     uses_default_increment = False
 240
 241     # check for missing filenames in Tablespaces. This is possible after recreation
 242     # of controlfiles in temporary Tablespaces
 243     # => CRIT, because we are not able to calculate used/free space in Tablespace
 244     #          in most cases the temporary Tablespace is empty
 245     if tablespace['amount_missing_filenames'] > 0:
 246         yield 2, "%d files with missing filename in %s Tablespace (!!), space calculation not possible" % \
 247                    (tablespace['amount_missing_filenames'], ts_type)
 248         return
 249
 250     for datafile in tablespace["datafiles"]:
 251
 252         df_file_online_status = datafile["file_online_status"]
 253         if df_file_online_status in ["OFFLINE", "RECOVER"]:
 254
 255             file_online_states_params = dict(params.get("map_file_online_states", []))
 256             if datafile["block_size"] is not None and file_online_states_params and \
 257                df_file_online_status in file_online_states_params:
 258
 259                 file_online_states.setdefault(df_file_online_status, {
 260                     "state": file_online_states_params[df_file_online_status],
 261                     "sids": [],
 262                 })
 263                 file_online_states[df_file_online_status]["sids"].append(sid)
 264
 265             else:
 266                 yield 2, "One or more datafiles OFFLINE or RECOVER"
 267                 return
 268
 269         num_files += 1
 270         if datafile["status"] in ["AVAILABLE", "ONLINE", "READONLY"]:
 271             df_size = datafile["size"]
 272             df_free_space = datafile["free_space"]
 273             df_max_size = datafile["max_size"]
 274
 275             num_avail += 1
 276             current_size += df_size
 277             used_size += df_size - df_free_space
 278
 279             # Autoextensible? Honor max size. Everything is computed in
 280             # *Bytes* here!
 281             if datafile["autoextensible"]:
 282                 num_extensible += 1
 283                 incsize = datafile["increment_size"]
 284
 285                 if df_size > df_max_size:
 286                     max_size += df_size
 287                     # current file size > df_max_size => no more extents available
 288                     free_extension = 0
 289                 else:
 290                     max_size += df_max_size
 291                     free_extension = df_max_size - df_size  # free extension space
 292
 293                 if incsize == datafile["block_size"]:
 294                     uses_default_increment = True
 295
 296                 num_increments += (free_extension / incsize)
 297                 increment_size += free_extension
 298
 299                 if db_version >= 11:
 300                     # Newer versions of Oracle uses every time the remaining space of the
 301                     # datafile. There is no need for calculation of remaing space with
 302                     # next extend anymore!
 303                     free_space += free_extension + df_free_space
 304                 else:
 305                     # The free space in this table is the current free space plus
 306                     # the additional space that can be gathered by using all available
 307                     # remaining increments
 308                     free_space += increment_size + df_free_space
 309
 310             # not autoextensible: take current size as maximum
 311             else:
 312                 max_size += df_size
 313                 free_space += df_free_space
 314
 315     yield 0, "%s (%s), Size: %s, %s used (%s of max. %s), Free: %s" % \
 316         (ts_status, ts_type, get_bytes_human_readable(current_size),
 317          get_percent_human_readable(100.0 * used_size / max_size),
 318          get_bytes_human_readable(used_size),
 319          get_bytes_human_readable(max_size),
 320          get_bytes_human_readable(free_space))
 321
 322     if num_extensible > 0 and db_version <= 10:
 323         # only display the number of remaining extents in Databases <= 10g
 324         yield 0, "%d increments (%s)" % \
 325                         (num_increments, get_bytes_human_readable(increment_size))
 326
 327     if ts_status != "READONLY":
 328         warn, crit, levels_text, _output_as_percentage = \
 329             db_get_tablespace_levels_in_bytes(max_size, params)
 330
 331         yield 0, "", \
 332             [("size", current_size, max_size - (warn or 0), max_size - (crit or 0)),\
 333             ("used", used_size), ("max_size", max_size)]
 334
 335         # Check increment size, should not be set to default (1)
 336         if params.get("defaultincrement"):
 337             if uses_default_increment:
 338                 yield 1, "DEFAULT INCREMENT"
 339
 340     # Check autoextend status if parameter not set to None
 341     if autoext is not None and ts_status != "READONLY":
 342         if autoext and num_extensible == 0:
 343             autoext_info = "NO AUTOEXTEND"
 344         elif not autoext and num_extensible > 0:
 345             autoext_info = "AUTOTEXTEND"
 346         else:
 347             autoext_info = None
 348
 349         if autoext_info:
 350             yield params.get("autoextend_severity", 2), autoext_info
 351
 352     elif num_extensible > 0:
 353         yield 0, "autoextend"
 354
 355     else:
 356         yield 0, "no autoextend"
 357
 358     # Check free space, but only if status is not READONLY
 359     # and Tablespace-Type must be PERMANENT or TEMPORARY, when temptablespace is True
 360     # old plugins without v$tempseg_usage info send TEMP as type.
 361     # => Impossible to monitor old plugin with TEMP instead TEMPORARY
 362     if ts_status != "READONLY" and \
 363        (ts_type == 'PERMANENT' or (ts_type == 'TEMPORARY' and params.get("temptablespace"))):
 364
 365         status = 0
 366         if crit is not None and free_space < crit:
 367             status = 2
 368         elif warn is not None and free_space < warn:
 369             status = 1
 370
 371         if status:
 372             yield status, "only %s left%s" % (get_bytes_human_readable(free_space), levels_text)
 373
 374     if num_files != 1 or num_avail != 1 or num_extensible != 1:
 375         yield 0, "%d data files (%d avail, %d autoext)" % \
 376                   (num_files, num_avail, num_extensible)
 377
 378     for file_online_state, attrs in file_online_states.items():
 379         this_state = attrs["state"]
 380         yield this_state, "Datafiles %s: %s" % (file_online_state, ", ".join(attrs["sids"]))
 381
 382
 383 # If something changes adapt calculations in related inventory plugin
 384 check_info['oracle_tablespaces'] = {
 385     "parse_function": parse_oracle_tablespaces,
 386     "inventory_function": inventory_oracle_tablespaces,
 387     "check_function": check_oracle_tablespaces,
 388     "service_description": "ORA %s Tablespace",
 389     "has_perfdata": True,
 390     "node_info": True,
 391     "group": "oracle_tablespaces",
 392     "default_levels_variable": "oracle_tablespaces_defaults",
 393     "includes": ["oracle.include", "db.include"]
 394 }