[#ZBX-25944] usage of con$name causes broken collection of Oracle tablespaces metrics after DB cloning

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.

[ZBX-25944] usage of con$name causes broken collection of Oracle tablespaces metrics after DB cloning Created: 2025 Jan 28 Updated: 2025 Apr 22
Status:	In Progress
Project:	ZABBIX BUGS AND ISSUES
Component/s:	Agent2 plugin (G)
Affects Version/s:	7.0.8
Fix Version/s:	7.0.13rc1, 7.2.7rc1, 7.4.0rc1 (master)

[ZBX-25944] usage of con$name causes broken collection of Oracle tablespaces metrics after DB cloning Created: 2025 Jan 28 Updated: 2025 Apr 22