<a id="oracle-xstream-troubleshooting"></a>

# Troubleshooting Oracle XStream CDC Source Connector for Confluent Platform

<!-- We should share sections of the self-managed troubleshooting docs for both -->
<!-- the self-managed and fully-managed (cloud) connectors. There are some initiatives -->
<!-- for troubleshooting on the engineering side that need to be worked out -->
<!-- first. There is already a shared troubleshooting topic in docs-common for -->
<!-- this, but that doc is not shared for the fully-managed connector (yet). -->

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`](/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](https://secure-transfer.confluent.io/auth/) 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](https://support.confluent.io/hc/en-us/articles/115005750346-Required-Access-to-Confluent-Network-Sites-and-Secure-File-Transfer).

<a id="xstream-upgrade-1-6-raw-xmltype"></a>

## Upgrade from 1.5.x and earlier to 1.6.x and later

Connectors whose captured tables do not contain any `RAW` or `XMLType`
columns require no action and are not affected.

If any table currently in your connector’s capture set contains `RAW` or
`XMLType` columns, those columns begin flowing into your records after the
upgrade, and the schema for the affected subjects changes. Whether that
schema evolution is accepted automatically depends on the converter you use
(Avro, Protobuf, or JSON Schema) and your compatibility settings.

Compatibility rules:

1. **Avro and Protobuf** (and **JSON Schema** when `compatibilityPolicy` is
   set to `lenient`):
   - If the `RAW`/`XMLType` column is nullable, it is added as an
     optional field, which is compatible under all compatibility types.
   - If the `RAW`/`XMLType` column is `NOT NULL`, it is added as a
     required field, which is compatible only when compatibility is set to
     `FORWARD`.
2. **JSON Schema** when `compatibilityPolicy` is set to `strict`:
   - If your existing schema uses an open content model (the default),
     adding the `RAW`/`XMLType` column is compatible only when
     compatibility is set to `FORWARD`.
   - If your existing schema uses a closed content model, adding the
     `RAW`/`XMLType` column is compatible only when the column is
     nullable (added as an optional field) and compatibility is set to
     `BACKWARD`.

For more information, see
[Schema Evolution and Compatibility for Schema Registry](https://docs.confluent.io/platform/current/schema-registry/fundamentals/schema-evolution.html#schema-evolution-and-compatibility-for-sr-on-product).

This change also affects anything consuming these records. After `RAW` and
`XMLType` columns are captured, the new fields and their values propagate
to your consumers. Before you upgrade, ensure your downstream systems can
handle the new fields, for example, that sink connectors’ target schemas
can accommodate the new columns (with schema evolution enabled where needed)
and that consuming applications tolerate the additional data.

### What you should do before upgrade

1. Check whether any tables in your connector’s capture set contain `RAW`
   or `XMLType` columns.
2. If so, ensure your compatibility settings are configured to accept this
   schema evolution, as described above.
3. Ensure your Schema Registry is not set to read-only, at either the global
   or per-subject level. If it is, the schema cannot evolve and your
   connectors fail during the upgrade.

### If you prefer to opt out

If you do not want these columns captured, add them to
`column.exclude.list` (or scope `column.include.list` to omit them)
before the upgrade. Because these columns are not captured today, excluding
them keeps your schema unchanged and avoids any evolution.

## 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](prereqs-validation.md#oracle-xstream-prereqs-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](prereqs-validation.md#connect-oracle-xstream-cdc-source-prereqs-user-privileges), 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](prereqs-validation.md#connect-oracle-xstream-cdc-source-prereqs-user-privileges) or
another account with `CAPTURE` administrator privileges, must re-enable the capture
process with the `DBMS_CAPTURE_ADM.START_CAPTURE` procedure:

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

For more information, see the
[START_CAPTURE Procedure](https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_CAPTURE_ADM.html#GUID-F640B5B3-AD03-43A5-BBC9-1CAEA0E35051)
in the Oracle documentation.

<a id="xstream-downstream-missing-events-pre-existing-tables"></a>

## No change events for pre-existing tables

**Problem**

In a [downstream capture](downstream-capture.md#xstream-downstream-capture) or
[cascading downstream capture](cascading-downstream-capture.md#xstream-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:
  ```sql
  -- 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.

<a id="xstream-downstream-identify-affected-tables"></a>

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

```sql
-- 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](signals-actions.md#xstream-signal-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:
   ```sql
   -- 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](examples.md#connect-oracle-xstream-cdc-examples-drop-outbound).
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](prereqs-validation.md#xstream-downstream-capture-send-streams-dictionary), after
     `CREATE_CAPTURE` and before `START_CAPTURE` in
     [Step 4.10](prereqs-validation.md#xstream-downstream-capture-step-4-10-outbound). 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](prereqs-validation.md#xstream-downstream-capture-step-4-10-outbound),
     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](prereqs-validation.md#xstream-downstream-capture-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.

<a id="xstream-downstream-capture-monitoring"></a>

## Downstream capture monitoring and troubleshooting

The queries in this section apply to both [Downstream capture](downstream-capture.md#xstream-downstream-capture) and
[Cascading downstream capture](cascading-downstream-capture.md#xstream-cascading-downstream-capture) topologies.

### Health checks

After `START_CAPTURE` and the log switch in [Downstream capture prerequisites Step 4.11](prereqs-validation.md#xstream-downstream-capture-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.

```sql
-- 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](#xstream-downstream-missing-events-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.

```sql
-- 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:

```sql
-- 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):

```sql
-- 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`:

```sql
-- 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                          |
```sql
ALTER SYSTEM SET LOG_ARCHIVE_TRACE=<level> SCOPE=BOTH;
```

For the complete bitmask reference, see the
[LOG_ARCHIVE_TRACE documentation](https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/LOG_ARCHIVE_TRACE.html)
in the Oracle documentation.