Uploaded image for project: 'ZABBIX BUGS AND ISSUES'
  1. ZABBIX BUGS AND ISSUES
  2. ZBX-20202

templates for Oracle monitoring need updates to support v 12.2.0.1

    XMLWordPrintable

Details

    • Team INT
    • Sprint 82 (Nov 2021), Sprint 83 (Dec 2021)
    • 1

    Description

      It's turned out that for Oracle version 12.2.0.1 these changes should be performed:

      The PDB discovery issue can be solved by giving more permissions to the user. The monitoring user can’t see all the containers that is why the PDB discovery was empty for version 12.2.0.1.

      This is the executed command:

      alter user $user set container_data = all CONTAINER=CURRENT;
      

       
      This should be included in documentation, as now is not described as a requirement (https://www.zabbix.com/documentation/5.0/manual/config/templates_out_of_the_box/zabbix_agent2).

       
      For the PDB tablespaces, existing query for the "Template DB Oracle by Zabbix Agent 2" is not getting this information.
      We “solved” this by creating a Template to get this information by ODBC. The queries are:
      PDB tablespace discovery:

      select pdb.name PDB_NAME, tbs.name PDB_TABLESPACE from v$tablespace tbs, v$pdbs pdb where tbs.con_id=pdb.con_id order by 1;
      

      PDB tablespace stats:

      SELECT
        c.name as PDB_NAME,
        df.tablespace_name AS PDB_TABLESPACE,
        df.type AS TYPE,
        NVL(SUM(df.BYTES), 0) AS FILE_BYTES,
        NVL(SUM(df.MAX_BYTES), 0) AS MAX_BYTES,
        NVL(SUM(f.FREE), 0) AS FREE_BYTES,
        SUM(df.BYTES)-SUM(f.FREE) AS USED_BYTES,
        ROUND(DECODE(SUM(df.MAX_BYTES), 0, 0, (SUM(df.BYTES) / SUM(df.MAX_BYTES) * 100)), 2) AS USED_PCT_MAX,
        ROUND(DECODE(SUM(df.BYTES), 0, 0,(SUM(df.BYTES)-SUM(f.FREE))/ SUM(df.BYTES)* 100), 2) AS USED_FILE_PCT,
        DECODE(df.STATUS, 'ONLINE', 1, 'OFFLINE', 2, 'READ ONLY', 3, 0) AS STATUS
      FROM ( SELECT ddf.con_id, ddf.file_id, dt.contents AS TYPE, dt.STATUS , ddf.file_name, ddf.tablespace_name,
                    TRUNC(ddf.bytes) AS bytes, TRUNC(GREATEST(ddf.bytes, ddf.maxbytes)) AS max_bytes
               FROM cdb_data_files ddf,
                    cdb_tablespaces dt 
              WHERE ddf.tablespace_name = dt.tablespace_name
                and ddf.con_id = dt.con_id ) df,
           ( SELECT TRUNC(SUM(bytes)) AS FREE, file_id FROM cdb_free_space GROUP BY file_id ) f,
             (select con_id, name from V$CONTAINERS) c
         WHERE df.file_id = f.file_id 
           and df.con_id = c.con_id 
         GROUP BY c.name, df.tablespace_name, df.TYPE, df.status
      

      This should be considered to improve templates:
      “Template DB Oracle by Zabbix Agent 2”
      “Template DB Oracle by ODBC”

      Attachments

        Activity

          People

            zabbix.dev Zabbix Development Team
            zalex_ua Oleksii Zagorskyi
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

              Created:
              Updated: