-
Problem report
-
Resolution: Fixed
-
Minor
-
5.0.17
-
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), Sprint 98 (Mar 2023), Sprint 99 (Apr 2023), Sprint 100 (May 2023), Sprint 101 (Jun 2023)
-
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.
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”
[ZBX-20202] templates for Oracle monitoring need updates to support v 12.2.0.1
Is the issue still present with the latest template and using the updated permissions for the monitoring user? In my setup, the expected PDB tablespaces are being discovered.
If the issue persists, would it be possible to share more information about what tablespaces are missing from the official template query that are visible in the proposed query?
I did a fresh setup of zabbix-6.0.8 in Fedora 37, and i attached the template "Oracle by ODBC" to my db host and it only discovers the CDB tablespaces, actually on the template "Oracle by ODBC" i don't see any discovery item for PDB tablespaces.
Maybe was implemented only for the zabbix agent 2?
Hello again,
Thank you for the additional information. I have more clear understanding of the discovery issue now.
The "Tablespace discovery" (both in Agent 2 and ODBC templates) is getting information from table "DBA_TABLESPACES". This table shows tablespaces in current container which are accessible to the current user. This means that if you have directed the {$ORACLE.SERVICE} macro to a CDB, it will show available tablespaces for CDB and in turn if you direct the macro to a PDB1, for example, Zabbix will discover tablespaces under PDB1, that are accessible to monitoring user.
I am currently researching options on how to implement this fix in templates the best way possible, to discover all tablespaces and also the initially mentioned PDB discovery. Issue has been reopened.
Fixed in:
- 6.0.18rc1 40bcd7919cb
- 6.4.3rc1 df3fd14809b
- 7.0.0alpha1 (master) 3481b68df28
resolved in https://support.zabbix.com/browse/ZBX-21296