We have setup Debezium (ver 1.9.7) to read CDC data fro Oracle (19c)

The CDC is working properly and we don't see data loss, but our issue is huge RedoLog file Read when we start the Debezium using LogMiner

The redo log file is only 56 GB, but the Debezium user is reading around 40 TB of data daily

This is causing DB performance

Bug report

For bug reports, provide this information, please:

What Debezium connector do you use and what version?

Debezium 1.9.7

What is the connector configuration?

1. -----------------------------
2. Oracle Logminer Specific configuration
3. -----------------------------

log.mining.strategy : online_catalog
log.mining.scn.gap.detection.gap.size.min : 100000
log.mining.scn.gap.detection.time.interval.max.ms : 3000
log.mining.view.fetch.size : 10000
log.mining.batch.size.default: 20000
log.mining.batch.size.max: 100000
log.mining.batch.size.min: 1000
log.mining.log.query.max.retries: 200
log.mining.archive.log.hours : 72
log.mining.archive.log.only.mode: false
log.mining.continuous.mine: false

1. log.mining.transaction.retention.ms: 600000 — This setting availing in 2.5.0
   log.mining.transaction.retention.hours: 1

   What is the captured database version and mode of deployment?

Oracle 19c

What behavior do you expect?

Minimize the total data READ from Redo Log file

What behavior do you see?

The same Redo logs are being read multiple times.

Our REDO log file size is around 56 GB daily, but Debezium user is reading these Files over and over again and reading around 44 TB of data from the data base. This causing performance issue on the Oracle source database

Do you see the same behaviour using the latest released Debezium version?

Only used Debezium 1.9.7 where we use the behavour

Do you have the connector logs, ideally from start till finish?

AWR Report extact

 
SQL ordered by Physical Reads (UnOptimized)

         SQL statement with SQL_ID ad6880zj7dt6h.
{{         SELECT SCN, SQL_REDO, OPERATION_CODE, TIMESTAMP, XID, CSF, }}
{{         TABLE_NAME, SEG_OWNER, OPERATION, USERNAME, ROW_ID, ROLLBACK, RS_ID, }}
{{         STATUS, INFO, SSN, THREAD# FROM V$LOGMNR_CONTENTS WHERE SCN > :1  AND }}
{{         SCN <= :2  AND (SEG_OWNER IS NULL OR SEG_OWNER NOT IN }}
         ('APPQOSSYS','AUDSYS','CTXSYS','DVSYS','DBSFWUSER','DBSNMP','GSMADMIN
         _INTERNAL','LBACSYS','MDSYS','OJVMSYS','OLAPSYS','ORDDATA','ORDSYS','
{{         OUTLN','SYS','SYSTEM','WMSYS','XDB')) AND ((OPERATION_CODE IN }}
{{         (6,7,34,36)) OR ((OPERATION_CODE IN (1,2,3,255) OR (OPERATION_CODE = }}
{{         5 AND USERNAME NOT IN ('SYS','SYSTEM') AND INFO NOT LIKE 'INTERNAL }}
{{         DDL%' AND (TABLE_NAME IS NULL OR TABLE_NAME NOT LIKE 'ORA_TEMP_%'))) }}
{{         AND TABLE_NAME != 'LOG_MINING_FLUSH' AND (REGEXP_LIKE(SEG_OWNER || }}
         '.' || TABLE_NAME,'^ORADB.test
_table$','i')) ))

How to reproduce the issue using our tutorial deployment?

Should be reproduced in any Oracle intance. Check the Oracle AWR which would confirm that Datafile read is very very high for Debezium user

Feature request or enhancement

For feature requests or enhancements, provide this information, please:

Which use case/requirement will be addressed by the proposed feature?

<Your answer>

Implementation ideas (optional)

<Your answer>