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.

Capture process disabled after a database restart

Problem

After a database restart, the capture process associated with the outbound server can be left in a DISABLED status.

Symptoms

  • The connector stops receiving new change events after the source database restarts.

  • The STATUS column for the capture process in the DBA_CAPTURE view shows DISABLED.

Cause

Oracle’s documentation states that a capture process automatically restarts when the database restarts. In practice, this automatic restart succeeds only if performed by a session that holds CAPTURE administrator privileges, that is, the privileges granted by the DBMS_XSTREAM_AUTH.GRANT_ADMIN_PRIVILEGE procedure with privilege_type => 'CAPTURE'.

The connector authenticates as the connect user, which intentionally does not hold CAPTURE administrator privileges, so that the connector’s database access stays limited to what streaming changes requires. As a result, the connector cannot, and should not, re-enable a disabled capture process itself.

Solution

A database administrator, using the capture user or another account with CAPTURE administrator privileges, must re-enable the capture process with the DBMS_CAPTURE_ADM.START_CAPTURE procedure:

BEGIN
  DBMS_CAPTURE_ADM.START_CAPTURE(
    capture_name => '<capture_name>');
END;

For more information, see the START_CAPTURE Procedure in the Oracle documentation.

No change events for pre-existing tables

Problem

In a downstream capture or cascading downstream capture topology, the connector never streams change events for captured tables whose CREATE TABLE SCN precedes the capture process’s first SCN. When downstream capture is set up against a source database that already contains every table you capture, every captured table is affected and none streams at all. When some tables were created after the first SCN, only those tables stream and the rest are silently dropped.

If the connector took an initial snapshot, the affected tables still have their snapshot records, because a snapshot reads the source tables directly with an Oracle Flashback query and does not go through XStream. Only the streaming changes that follow are missing.

Symptoms

  • Captured tables produce no streaming change events, either all of them or every one that predates the capture process’s first SCN.

  • A single transaction that changes both a pre-existing table and a table created after the first SCN produces a change event only for the latter.

  • No error is raised in DBA_CAPTURE or DBA_APPLY, the capture process state and the outbound server status are healthy, and the connector task stays in the RUNNING state.

  • The following query returns no rows, or a row whose VALUE is not INFINITE. DBA_CAPTURE_PARAMETERS stores capture names in uppercase:

    -- On the downstream: data dictionary parameter verification.
SELECT PARAMETER, VALUE, SET_BY_USER FROM DBA_CAPTURE_PARAMETERS
 WHERE CAPTURE_NAME = UPPER('<capture-name>')
   AND PARAMETER    = '_SEND_STREAMS_DICTIONARY';

  • A VALUE of INFINITE does not by itself rule out this issue. The parameter takes effect only if it was set before the capture process was started for the first time. If the value is INFINITE and pre-existing tables still produce no streaming change events, the parameter was set too late.

To list the captured tables that are affected, compare each table’s creation time against the capture process’s first SCN:

-- On the downstream: the capture process's first SCN.
SELECT CAPTURE_NAME, FIRST_SCN FROM DBA_CAPTURE
 WHERE CAPTURE_NAME = UPPER('<capture-name>');

-- On the source: captured tables created before that SCN.
SELECT OWNER, OBJECT_NAME, CREATED FROM DBA_OBJECTS
 WHERE OBJECT_TYPE = 'TABLE'
   AND OWNER       = UPPER('<schema>')
   AND CREATED     < SCN_TO_TIMESTAMP(<first-scn>);

If SCN_TO_TIMESTAMP raises ORA-08181, the first SCN is older than the SCN-to-timestamp mapping that the database retains. In that case treat every table created before the capture process was created as affected.

Cause

Oracle Support attributes this to the capture process not sending a data dictionary entry for source objects that were created before its first SCN. The outbound server then has no object and column information for those objects, and no change event is produced for them.

Solution

Confirm the symptom before you change anything. Use the preceding query to find a captured table that predates the capture process’s first SCN, commit a change to it, and verify that no change event reaches its topic. A missing or non-INFINITE parameter causes no data loss on a downstream where every captured table was created after the capture process’s first SCN, and no recovery is needed there.

If the capture process has been created but not started yet, set the parameter now and then start the capture process. No other action is needed.

If the capture process has already been started, setting the parameter does not recover the tables it is already skipping. Recover by recreating XStream Out on the downstream database:

  1. Record the capture configuration before you change it: FIRST_SCN from DBA_CAPTURE, the list of captured tables, and the output of the preceding parameter query. Dropping the capture process removes it, and Confluent Support needs it if you escalate.

  2. Decide how to backfill, because step 5 depends on it:

    • To re-snapshot every captured table, leave snapshot.mode at its default of initial. The connector then re-emits records for the tables that were streaming correctly and the affected ones.

    • To backfill only the affected tables, set snapshot.mode to no_data and plan to send an ad-hoc blocking snapshot signal that lists them after the connector is streaming again. Streaming pauses until that snapshot completes. Any captured table you do not list keeps the gap for the changes committed while XStream Out was being recreated.

  3. Stop the connector. It must not be attached to the outbound server while the outbound server is dropped.

  4. Stop and drop the outbound server, the capture process, and the queue. The downstream prerequisites create these with SET_UP_QUEUE, CREATE_CAPTURE, and ADD_OUTBOUND rather than with CREATE_OUTBOUND, so DBMS_XSTREAM_ADM.DROP_OUTBOUND does not drop the queue or the capture process here. Drop each component explicitly, in this order:

    -- On the downstream: remove the existing XStream Out components.
BEGIN
  DBMS_XSTREAM_ADM.STOP_OUTBOUND(server_name => 'xout');
  DBMS_XSTREAM_ADM.DROP_OUTBOUND(server_name => 'xout');
  DBMS_CAPTURE_ADM.STOP_CAPTURE(capture_name => 'xs_capture');
  DBMS_CAPTURE_ADM.DROP_CAPTURE(
    capture_name          => 'xs_capture',
    drop_unused_rule_sets => true);
  DBMS_XSTREAM_ADM.REMOVE_QUEUE(
    queue_name              => 'c##cfltadmin.xs_queue',
    drop_unused_queue_table => true);
END;
/

    For the conditions that apply to each procedure, see Drop an outbound server, queue, and capture process.

  5. Repeat downstream capture prerequisites Step 4.6 through Step 4.11, starting with SET_UP_QUEUE:

    • Set _SEND_STREAMS_DICTIONARY to INFINITE in Step 4.7, after CREATE_CAPTURE and before START_CAPTURE in Step 4.10. Setting it after START_CAPTURE means repeating this whole procedure.

    • On an Oracle RAC downstream, set use_rac_service to Y in the same place, immediately after CREATE_CAPTURE.

    • Re-add every schema and table you capture to the new capture rule set in Step 4.8, and to the apply rule set in Step 4.10, including the signaling table if you use signals.

    • Apply Step 4.9 only if you use real-time mode.

    • Do not omit the log switch in Step 4.11. Skipping it can leave the capture process in the state described in the Step 4.11 warning.

  6. Reset the connector’s offsets, or recreate the connector. The recreated capture process has a new first SCN that is ahead of the connector’s stored position, so the connector cannot resume from it.

  7. Start the connector, then re-verify: the preceding parameter query returns INFINITE with SET_BY_USER set to YES, and a change committed to a previously affected table now reaches its topic. If you chose no_data in step 2, send the ad-hoc blocking snapshot signal now.

Send the snapshot signal only after XStream Out has been recreated. A signal sent through the source signaling channel beforehand is skipped by the same defect if the signaling table itself predates the old capture process’s first SCN. Signals sent through the Kafka signaling channel do not pass through XStream and are not affected.

For further help, run the connector diagnostics script and contact Confluent Support with the diagnostic report. Include the output of the preceding DBA_CAPTURE_PARAMETERS query, because the diagnostics script reports this parameter only when it is set.

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: data dictionary parameter verification.
SELECT PARAMETER, VALUE, SET_BY_USER FROM DBA_CAPTURE_PARAMETERS
 WHERE CAPTURE_NAME = UPPER('<capture-name>')
   AND PARAMETER    = '_SEND_STREAMS_DICTIONARY';

-- 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.

  • _SEND_STREAMS_DICTIONARY is INFINITE with SET_BY_USER set to YES. If the query returns no rows, or a row whose value is not INFINITE, then any captured table whose CREATE TABLE SCN precedes the capture process’s first SCN would be silently skipped. INFINITE rules this out only if the parameter was set before the capture process was first started. To confirm whether any of your captured tables are affected, and to recover, see No change events for pre-existing tables.

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.