Troubleshooting Oracle XStream CDC Source Connector for Confluent Platform

This section contains troubleshooting information for the Confluent Oracle XStream CDC Source connector.

Connector diagnostics script

The Confluent Oracle XStream CDC connector diagnostics script (orclcdc_diag.sql) is a read-only script that collects key diagnostic information about the Oracle database and XStream configuration. This script helps troubleshoot connector issues and is provided solely for diagnostic purposes. The script output is an HTML report that you can upload to Confluent Support for further analysis.

This script does not collect or process any message content.

Prerequisites

  • Oracle Database version 19c and later.

  • Oracle database user with SYSDBA privileges.

  • Access to SQL*Plus command-line utility for running the script.

Usage instructions

Run the script

  • Connect to the Oracle database as a user with SYSDBA privileges. Note that if you are using a Container Database (CDB), you must connect to the root container.

  • Run the script using sqlplus. The script generates a diagnostic HTML report.

Upload the report

After you generate the report, you must upload it securely to Confluent.

  • Go to Secure File Transfer site. This site enables file encryption and tracks who accesses the files.

  • Upload the report.

Warning

Do not upload sensitive information to Confluent.

For more information, see Required Access to Confluent Network Sites.

Oracle XStream Out process hang during LOB processing

The Oracle XStream Out process can hang indefinitely when processing Large Objects (LOBs) data, especially during high ingestion rates with frequent LOB changes. The Apply Reader enters a Waiting for memory state, data streaming halts completely, and the process does not recover automatically.

Affected versions

This issue can occur on Oracle Database versions Oracle 19c and Oracle 21c when processing large LOB data.

Confirmed affected versions:

  • Oracle 19c: Oracle 19.30.0, 19.29.0, 19.28.0, 19.27.0, 19.26.0, 19.25.0, 19.22.0, 19.18.0, 19.11.0

  • Oracle 21c: Oracle 21.20.0

Other intermediate versions may also be affected.

Symptoms

  • XStream Out process (outbound server) stalls with no records flowing to the connector.

  • Apply Reader process shows state Waiting for memory in V$PROPAGATION_RECEIVER.

  • Heap analysis shows continued growth of knalf:txn_cnk allocations over time, indicating a memory leak in the XStream Out process.

Solution

  1. Apply Oracle patch 38090903 to resolve the memory leak.

  2. If the issue persists after applying the patch, tune the STREAMS_POOL_SIZE parameter to accommodate your LOB processing requirements. For more information, see Configure Streams pool size.

For further help, run the connector diagnostics script and contact Confluent Support with the diagnostic report.

Throughput degradation after connector restart

Problem

After restarting the connector under high workload, you may observe lower throughput and increasing consumer lag. A subsequent connector restart restores throughput, but the issue can recur.

Affected versions

This issue can occur on Oracle Database 19c. Other versions might also experience this issue.

Symptoms

  • Connector throughput drops noticeably after a restart.

  • Consumer lag increases and does not recover without a further restart.

  • A subsequent connector restart restores normal throughput temporarily.

Solution

Apply Oracle patch 29541222 for your platform and database version. This patch resolves the underlying Oracle issue that causes throughput degradation on restart.

As a temporary workaround until you apply the patch, restart the connector a second time to restore throughput.

For further help, run the connector diagnostics script and contact Confluent Support with the diagnostic report.

Downstream capture monitoring and troubleshooting

The queries in this section apply to both Downstream capture and Cascading downstream capture topologies.

Health checks

After START_CAPTURE and the log switch in Downstream capture prerequisites Step 4.11, verify all three layers: redo transport, capture, and the outbound server. Note that the DEST_ID can change according to the setup.

-- On the source: redo transport status to the downstream.
SELECT DEST_ID, STATUS, DESTINATION, GAP_STATUS, ERROR
  FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID = 2;

-- On the source: shipped sequences.
SELECT THREAD#, SEQUENCE#, FIRST_TIME
  FROM V$ARCHIVED_LOG
 WHERE DEST_ID = 2 AND ARCHIVED = 'YES'
 ORDER BY SEQUENCE# DESC FETCH FIRST 10 ROWS ONLY;

-- On the downstream (real-time mode): standby redo logs.
SELECT THREAD#, GROUP#, SEQUENCE#, STATUS
  FROM V$STANDBY_LOG ORDER BY THREAD#, GROUP#;

-- On the downstream (archived-log mode): foreign archived logs.
SELECT THREAD#, SEQUENCE#, FIRST_TIME, STATUS
  FROM V$FOREIGN_ARCHIVED_LOG
 ORDER BY SEQUENCE# DESC FETCH FIRST 10 ROWS ONLY;

-- On the downstream: RFS process state.
SELECT THREAD#, PROCESS, PID, STATUS, CLIENT_PROCESS, SEQUENCE#
  FROM GV$MANAGED_STANDBY
 WHERE PROCESS LIKE 'RFS%'
 ORDER BY THREAD#, SEQUENCE#;

-- On the downstream: capture state.
SELECT CAPTURE_NAME, STATE FROM V$XSTREAM_CAPTURE;

-- On the downstream: capture mode verification.
SELECT PARAMETER, VALUE FROM DBA_CAPTURE_PARAMETERS
 WHERE CAPTURE_NAME = '<capture-name>'
   AND PARAMETER    = 'DOWNSTREAM_REAL_TIME_MINE';

-- On the downstream: outbound server state.
SELECT SERVER_NAME, CONNECT_USER, CAPTURE_NAME, STATUS
  FROM ALL_XSTREAM_OUTBOUND;

Expected steady state:

  • Source DEST_ID=2: STATUS=VALID, GAP_STATUS=NO GAP.

  • At least one standby redo log group is ACTIVE (real-time mode), or foreign archived log sequences are advancing (archived-log mode).

  • RFS process is in IDLE or RECEIVING state.

  • Capture process state reaches WAITING FOR TRANSACTION or WAIT FOR n SUBSCRIBER(S)… or WAITING FOR INACTIVE DEQUEUERS.

  • Outbound server STATUS is ATTACHED once the connector is running.

Detecting and resolving archive gaps

A gap occurs when one or more archived redo log files are missing on the downstream. Network interruptions or transient transport errors are common causes.

-- On the downstream: check for open gaps.
SELECT * FROM V$ARCHIVE_GAP;

-- Sequences archived on the source but missing on the downstream.
SELECT THREAD#, SEQUENCE#
  FROM V$ARCHIVED_LOG
 WHERE DEST_ID = 1
   AND (THREAD#, SEQUENCE#) NOT IN (
       SELECT THREAD#, SEQUENCE#
         FROM V$ARCHIVED_LOG
        WHERE DEST_ID = 2);

To resolve a gap manually, copy the missing archived log from the source to the downstream’s foreign-archive directory and register it:

-- 1. On the source: locate the missing file.
SELECT THREAD#, SEQUENCE#, NAME
  FROM V$ARCHIVED_LOG
 WHERE SEQUENCE# = <missing_sequence>
   AND THREAD#   = <thread>
   AND DEST_ID   = 1;

-- 2. Copy the file to the downstream host.

-- 3. On the downstream: register the file with the capture process.
ALTER DATABASE REGISTER LOGICAL LOGFILE
  '/opt/oracle/oradata/CAPCDB/arc_dest/SRCCDB/<filename>'
  FOR 'xs_capture';

DBA_REGISTERED_ARCHIVED_LOG shows all registered files.

Measuring source-to-downstream lag

Compare the source’s current SCN with the latest SCN received on the downstream (requires a DB link to the source):

-- Real-time mode.
SELECT SRC.CURRENT_SCN              AS SOURCE_CURRENT_SCN,
       DST.LATEST_SCN_RECEIVED      AS DOWNSTREAM_RECEIVED_SCN,
       ROUND((CAST(SCN_TO_TIMESTAMP(SRC.CURRENT_SCN)         AS DATE)
            - CAST(SCN_TO_TIMESTAMP(DST.LATEST_SCN_RECEIVED) AS DATE))
            * 86400) AS LAG_SECONDS
  FROM (SELECT CURRENT_SCN FROM V$DATABASE@SRCCDB.EXAMPLE.COM) SRC,
       (SELECT NVL(MAX(LAST_CHANGE#), 0) AS LATEST_SCN_RECEIVED
          FROM V$STANDBY_LOG WHERE STATUS = 'ACTIVE') DST;

-- Archived-log mode: substitute V$FOREIGN_ARCHIVED_LOG,
-- using NEXT_CHANGE# and STATUS = 'A'.

Alert log and Data Guard status

Most redo-transport problems appear first in the database alert log and in V$DATAGUARD_STATUS:

-- Locate alert log and trace directories.
SELECT NAME, VALUE FROM V$DIAG_INFO
 WHERE NAME IN ('Diag Trace', 'Diag Alert', 'Default Trace File');

-- Recent Data Guard messages on the downstream.
SELECT TIMESTAMP, SEVERITY, ERROR_CODE, MESSAGE
  FROM V$DATAGUARD_STATUS
 ORDER BY TIMESTAMP DESC;

Alert log patterns to watch for:

  • On the source: ORA-16xxx (Data Guard), LGWR errors, or Failed to archive messages tied to LOG_ARCHIVE_DEST_2.

  • On the downstream: RFS process errors, archive gap messages, or standby redo log errors.

Detailed tracing for redo transport

LOG_ARCHIVE_TRACE enables targeted tracing in the LGWR, ARC, and RFS code paths. Bitmask values can be combined:

Value

What it traces

Use when

1

High-level transport activity

Basic visibility

4

Common redo-transport services

Locking, control file, or process issues

16

Disk and network I/O

Performance issues

32

Redo-transport destinations

Checking destination status

64

Redo-transport archive engine

Archive process issues

128

FAL (Fetch Archive Log) engine

Gap resolution

512

LGWR, SYNC, ASYNC processes

Real-time transport

1024

RFS process

Redo receive

2048

Redo-transport gap resolution

Archive gap problems

4096

Real-time apply

Apply process

8192

Recovery process

MRP / recovery

16384

Buffer management

Buffer or memory issues

32768

LogMiner dictionary

LogMiner issues

ALTER SYSTEM SET LOG_ARCHIVE_TRACE=<level> SCOPE=BOTH;

For the complete bitmask reference, see the LOG_ARCHIVE_TRACE documentation in the Oracle documentation.