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

    • Problem report
    • Status: Closed
    • Minor
    • Resolution: Duplicate
    • 5.0.17
    • None
    • Templates (T)
    • None
    • Sprint 82 (Nov 2021), Sprint 83 (Dec 2021), Sprint 84 (Jan 2022), Sprint 85 (Feb 2022), Sprint 86 (Mar 2022), Sprint 87 (Apr 2022), Sprint 88 (May 2022), Sprint 89 (Jun 2022), Sprint 90 (Jul 2022), Sprint 91 (Aug 2022), Sprint 92 (Sep 2022), Sprint 93 (Oct 2022), Sprint 94 (Nov 2022)
    • 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

        Issue Links

          Activity

            People

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

              Dates

                Created:
                Updated:
                Resolved: