-
Problem report
-
Resolution: Unresolved
-
Trivial
-
7.0.8
-
None
-
Support backlog
Zabbix is used to monitor the size of Oracle tablespaces. But when a clone of the Production DB to the Staging DB is made, we see a problem with the metrics of tablespaces.
The problem is - data comes only one of two.
When the clone is performed each day, on graphs there are big daily repeating gaps, except of weekends, see picture.
After getting to Oracle help, it's clarified there is a bug with using the parameter con$name. Recommendation:
As per unpublished Bug 34774004 , it is not recommended to use con$name directly. The right usage is to use "con_id_to_con_name(con_id)" instead of con$name as follows:
select con_id_to_con_name(con_id) from containers(dual) where con_id_to_con_name(con_id)='TPAP1';
See below the output without using the recommendation:
SELECT df.TABLESPACE_NAME AS TABLESPACE_NAME, df.CONTENTS AS CONTENTS, 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, NVL(SUM(df.BYTES) - SUM(f.FREE), 0) AS USED_BYTES, ROUND(CASE SUM(df.MAX_BYTES) WHEN 0 THEN 0 ELSE (SUM(df.BYTES) / SUM(df.MAX_BYTES) * 100) END, 2) AS USED_PCT_MAX, ROUND(CASE SUM(df.BYTES) WHEN 0 THEN 0 ELSE (NVL(SUM(df.BYTES) - SUM(f.FREE), 0)) / SUM(df.BYTES) * 100 END, 2) AS USED_FILE_PCT, ROUND(CASE SUM(df.MAX_BYTES) WHEN 0 THEN 0 ELSE NVL(SUM(df.BYTES) - SUM(f.FREE), 0) / SUM(df.MAX_BYTES) * 100 END, 2) AS USED_FROM_MAX_PCT, CASE df.STATUS WHEN 'ONLINE' THEN 1 WHEN 'OFFLINE' THEN 2 WHEN 'READ ONLY' THEN 3 ELSE 0 END AS STATUS FROM (SELECT ct.CON$NAME AS CON_NAME, cdf.FILE_ID, ct.CONTENTS, ct.STATUS, cdf.FILE_NAME, cdf.TABLESPACE_NAME, TRUNC(cdf.BYTES) AS BYTES, TRUNC(GREATEST(cdf.BYTES, cdf.MAXBYTES)) AS MAX_BYTES FROM CDB_DATA_FILES cdf, CDB_TABLESPACES ct WHERE cdf.TABLESPACE_NAME = ct.TABLESPACE_NAME AND cdf.CON_ID = ct.CON_ID AND (ct.CON$NAME = :1 or (ct.CON$NAME is null and ct.CON_ID = 0))) df, (SELECT TRUNC(SUM(BYTES)) AS FREE, FILE_ID FROM CDB_FREE_SPACE GROUP BY FILE_ID) f WHERE df.FILE_ID = f.FILE_ID (+) AND df.TABLESPACE_NAME = :2 GROUP BY df.CON_NAME, df.TABLESPACE_NAME, df.CONTENTS, df.STATUS no rows selected
The output with the recommendation
SELECT df.TABLESPACE_NAME AS TABLESPACE_NAME, df.CONTENTS AS CONTENTS, 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, NVL(SUM(df.BYTES) - SUM(f.FREE), 0) AS USED_BYTES, ROUND(CASE SUM(df.MAX_BYTES) WHEN 0 THEN 0 ELSE (SUM(df.BYTES) / SUM(df.MAX_BYTES) * 100) END, 2) AS USED_PCT_MAX, ROUND(CASE SUM(df.BYTES) WHEN 0 THEN 0 ELSE (NVL(SUM(df.BYTES) - SUM(f.FREE), 0)) / SUM(df.BYTES) * 100 END, 2) AS USED_FILE_PCT, ROUND(CASE SUM(df.MAX_BYTES) WHEN 0 THEN 0 ELSE NVL(SUM(df.BYTES) - SUM(f.FREE), 0) / SUM(df.MAX_BYTES) * 100 END, 2) AS USED_FROM_MAX_PCT, CASE df.STATUS WHEN 'ONLINE' THEN 1 WHEN 'OFFLINE' THEN 2 WHEN 'READ ONLY' THEN 3 ELSE 0 END AS STATUS FROM (SELECT ct.CON$NAME AS CON_NAME, cdf.FILE_ID, ct.CONTENTS, ct.STATUS, df.FILE_NAME, cdf.TABLESPACE_NAME, TRUNC(cdf.BYTES) AS BYTES, TRUNC(GREATEST(cdf.BYTES, cdf.MAXBYTES)) AS MAX_BYTES FROM CDB_DATA_FILES cdf, CDB_TABLESPACES ct WHERE cdf.TABLESPACE_NAME = ct.TABLESPACE_NAME AND cdf.CON_ID = ct.CON_ID --AND (ct.CON$NAME = :1 or (ct.CON$NAME is null and ct.CON_ID = 0))) df, AND (con_id_to_con_name(ct.con_id)= :1 or (ct.CON$NAME is null and ct.CON_ID = 0))) df, (SELECT TRUNC(SUM(BYTES)) AS FREE, FILE_ID FROM CDB_FREE_SPACE GROUP BY FILE_ID) f WHERE df.FILE_ID = f.FILE_ID (+) AND df.TABLESPACE_NAME = :2 GROUP BY df.CON_NAME, df.TABLESPACE_NAME, df.CONTENTS, df.STATUS; TABLESPACE_NAME CONTENTS FILE_BYTES MAX_BYTES FREE_BYTES USED_BYTES USED_PCT_MAX USED_FILE_PCT USED_FROM_MAX_PCT STATUS ------------------------------ --------------------- ---------- ---------- ---------- ---------- ------------ ------------- ----------------- ---------- ACTIVITI PERMANENT 1.2616E+10 3.2212E+10 745603072 1.1871E+10 39.17 94.09 36.85 1
The change in the request is
the following line :
AND (ct.CON$NAME = :1 or (ct.CON$NAME is null and ct.CON_ID = 0))) df,is replaced by:
AND (con_id_to_con_name(ct.con_id)= :1 or (ct.CON$NAME is null and ct.CON_ID = 0))) df,
OS version:
[root@sss]# cat /etc/os-release NAME="Oracle Linux Server" VERSION="8.10" ID="ol" ID_LIKE="fedora" VARIANT="Server" VARIANT_ID="server" VERSION_ID="8.10" PLATFORM_ID="platform:el8" PRETTY_NAME="Oracle Linux Server 8.10" ANSI_COLOR="0;31" CPE_NAME="cpe:/o:oracle:linux:8:10:server" HOME_URL="https://linux.oracle.com/" BUG_REPORT_URL="https://github.com/oracle/oracle-linux" ORACLE_BUGZILLA_PRODUCT="Oracle Linux 8" ORACLE_BUGZILLA_PRODUCT_VERSION=8.10 ORACLE_SUPPORT_PRODUCT="Oracle Linux" ORACLE_SUPPORT_PRODUCT_VERSION=8.10
Zabbix Oracle Agent2 versions :
Name : zabbix-agent2 Version : 7.0.4 Name : zabbix-agent2 Version : 7.0.6 Name : zabbix-agent2 Version : 7.0.8
The problem is with all version of Zabbix Oracle Agent2.
