[#ZBX-20202] templates for Oracle monitoring need updates to support v 12.2.0.1

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.

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

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;

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”

[ZBX-20202] templates for Oracle monitoring need updates to support v 12.2.0.1 Created: 2021 Nov 10 Updated: 2024 Apr 10 Resolved: 2023 May 31
Status:	Closed
Project:	ZABBIX BUGS AND ISSUES
Component/s:	Templates (T)
Affects Version/s:	5.0.17
Fix Version/s:	6.0.18rc1, 6.4.3rc1, 7.0.0alpha1, 7.0 (plan)

[ZBX-20202] templates for Oracle monitoring need updates to support v 12.2.0.1 Created: 2021 Nov 10 Updated: 2024 Apr 10 Resolved: 2023 May 31