Onboarding Data from Oracle XStream

For onboarding data from an Oracle XStream source, see Onboarding a RDBMS Source. The source type must be Oracle XStream.

Oracle XStream sources use Oracle's native XStream Out interface for change data capture. Changes are pushed by Oracle to a long-lived consumer as they commit, rather than being mined from redo logs on a schedule. For log-based change data capture using LogMiner, see Oracle Log-Based Ingestion.

NOTE XStream Out is a licensed Oracle feature. Confirm your entitlement with your Oracle account team before enabling it.

NOTE Change data capture for a Oracle XStream source requires the Oracle thick (OCI) client on the compute that runs the consumer job. Full refresh uses the thin ojdbc8 jar and requires nothing additional.

Creating a Oracle XStream Source

An Oracle outbound server must already exist on the source database before the source is created. See Oracle Prerequisites.

Oracle XStream Configurations

Field	Description
Source Name	The name of the source.
Fetch Data Using	The mechanism used to read data from the source.
Connection URL	The JDBC connection string in the format `jdbc:oracle:thin:@<ip>:<port>:<serviceid>`. NOTE On a multitenant database, this must point to the CDB root service, not the PDB service. The outbound server is a root-level object and the change data capture job attaches there.
Username	The user name used to connect to the source. This must be the XStream administrator user.
Authentication Type for Password	The source of the password. Options include Uniphore Managed and External Secret Store. NOTE Only the secrets accessible to the user will be available in the drop-down list.
XStream Server Name	The name of the Oracle outbound server to attach to, for example `XOUT_SALES`. NOTE This field is mandatory. The change data capture job fails at startup if it is not set.
Additional Connection Parameters	The additional JDBC connection properties, entered as key and value pairs. NOTE The change data capture properties are set here. See XStream Connection Parameters.
Custom Tags	The tags applied to the source.

NOTE The Connection URL is stored once and used for both full refresh and change data capture. For the change data capture attach, the driver token is substituted (thin to oci) and the host and service are left unchanged. To use a different URL for the attach, set the advanced configuration xstream.oracle.jdbc.url.

NOTE The log-based change data capture fields — Staging Database, Dictionary Type, Dictionary Path and Rebuild Dictionary for each Job — do not apply to a Oracle XStream source. Change capture is performed by Oracle's own capture process and no dictionary is built.

NOTE Set oracle.jdbc.mapDateToTimestamp to false before the schema crawl so that Oracle date columns map to the date type.

Once the settings are saved, you can test the connection.

XStream Connection Parameters

The following properties tune the change data capture job. They are entered as key and value pairs in Additional Connection Parameters on the source. The defaults are suitable for most workloads.

Key	Description
xstream.oracle.jdbc.url	The JDBC URL used for the change data capture attach. The default is derived from the Connection URL by substituting the driver token.
xstream.batch.interval.seconds	The time in seconds that one receive batch continues while changes are flowing. The default is 30.
xstream.idle.timeout.seconds	The time in seconds to wait before ending a batch once the source goes quiet. The default is 1. NOTE The end of a batch is where the consumer checks for cancellation, flushes staged rows, and acknowledges consumed changes so Oracle can release redo. Large values delay all three.
xstream.max.buffered.rows	The number of rows buffered before a flush to the staging table. The default is 10000.
xstream.max.buffered.txns	The number of transactions buffered before a flush. The default is 500.
xstream.flush.interval.ms	The maximum time in milliseconds between flushes. The default is 5000.
xstream.small.txn.threshold	The number of changes above which a transaction spills to disk instead of being held in memory. The default is 1000.
xstream.spill.chunk.size	The chunk size used for spilled transactions. The default is 5000.
xstream.max.txn.size	The maximum number of changes in a single transaction. The default is 1000000.
xstream.spill.dir	The directory where spill files are written. The default is the temporary directory.
xstream.lcr.allowed.types	The comma-separated list of change types to process, for example `INSERT,UPDATE`. The default is all types.
xstream.lcr.denied.types	The comma-separated list of change types to ignore, for example `DDL`. This takes precedence over the allowed types. NOTE Excluding `DELETE` means source deletes are not reflected at the target.
xstream.retry.backoff.ms	The time in milliseconds to wait before retrying a transient receive failure. The default is 5000.
xstream.commit.queue.size	The number of transactions queued awaiting staging. The default is 1000.
xstream.commit.thread.pool.size	The parallelism used for staging writes. The default is 4.
xstream.staged.scn.advance.flushes	The number of flushes between watermark writes. The default is 10. NOTE This throttles how often the write is attempted and cannot affect correctness.
xstream.checkpoint.dir	The directory where the consumer checkpoint is kept. The default is the temporary directory.

Advanced Configurations

From Data Sources, select the source and click View Source.
Go to Configuration, then Advanced Configuration, then Add Configuration.
Enter key, value, and description. You can also select the configuration from the list displayed.

The following advanced configuration applies to a Oracle XStream source. It is set at the source level.

Key	Description
oracle_xstream_full_refresh_advance_scn	The option to capture a fresh SCN and re-seed both watermarks on the next full refresh, instead of reusing the existing one. This is a source-level configuration. The default is false. See Re-baselining a Source.

NOTE The xstream. properties are not set here. They are entered as key and value pairs in Additional Connection Parameters on the source. See XStream Connection Parameters.

Configuring an Oracle XStream Table

With the source metadata in the catalog, you can now configure the table for change data capture and incremental synchronization.

Click the Configuration link, for the desired table.
Enter the ingestion configuration details as listed in the table below:

Field	Description
Query	The query used to build the table. NOTE This field is only visible if the table is ingested using Add Query as Table.
Ingest Type	The ingestion type for the table. Options include incremental.
Natural Keys	The columns that uniquely identify a row. NOTE This field is mandatory for a Oracle XStream table. The merge upserts and deletes by natural key, and the merge job fails if no natural key is configured. At least one natural-key column must be non-null.
Incremental Mode	The mode used to apply incremental changes. Options include merge. NOTE A Oracle XStream table is applied as current state with delete handling. Append and insert overwrite do not apply.
Incremental Fetch Mechanism	The mechanism used to fetch changes. Options include XStream.
Sorting Columns	The columns used to sort the target table. NOTE This field applies to Snowflake targets only.

Target Configuration

Field	Description
Target Table Name	The name of the target table.
Catalog Name	The name of the target catalog. NOTE This field applies to Unity Catalog environments only.
Staging Catalog Name	The name of the staging catalog.
Staging Schema Name	The name of the staging schema. NOTE This field applies to Azure Databricks Unity Catalog only.
Storage Format	The storage format of the target table. Options include Parquet, ORC, Avro, UniForm, and delimited text. NOTE UniForm is limited to Unity Catalog.
Partition Column	The column used to partition the target table. A column can be derived when the datatype is date or timestamp.

NOTE A Oracle XStream table has a second table at the target, named <target table name>_cdc, which holds the staged changes. It is written continuously by the consumer job and is not dropped after a merge. Include it when planning storage.

Optimization Configuration

Field	Description
Split By Column	The key used to split the full refresh read. Any column with computable min and max values can serve as the key. A derived split column is supported for date and timestamp types.

Sync Data to Target

From Data Sources, pick a table and click View Source or Ingest.
Select the table to sync.
Click Sync Data to Target.
Enter the mandatory fields as listed in the table below:

Field	Description
Job Name	The name of the job.
Max Parallel Tables	The number of tables processed in parallel.
Compute Cluster	The cluster on which the job runs.
Overwrite Worker Count	The option to override the cluster worker count.
Number of Worker Nodes	The number of worker nodes used.
Save as a Table Group	The option to save the selection as a table group.

NOTE A Oracle XStream source is synchronized by three jobs, not one. See Job Sequence for a Oracle XStream Source.

Click Onboarding a RDBMS Source to navigate back to complete the onboarding process.

Job Sequence for a Oracle XStream Source

A Oracle XStream source is synchronized by three jobs. The first is run once per table, the second runs continuously, and the third runs on a schedule.

Job	Scope	Frequency	Description
Full refresh	Table	Once per table	The snapshot of the table, read with Oracle flashback as of a single source SCN, written to the target table.
Change data capture	Source	Continuous	The long-lived consumer attached to the Oracle outbound server. It writes changes to the `<target table name>_cdc` table and does not update the target table.
Merge	Source	Scheduled	The upsert of staged changes into the target table, applying inserts, updates and deletes.

Run them in this order on first use:

Run a full refresh for every table to be replicated. This establishes the SCN at which the change stream begins.
Start the change data capture job for the source. Leave it running.
Schedule the merge job for the source at the required frequency.

Change latency into the staging table is continuous and independent of the merge schedule. Change latency into the target table is governed by the merge schedule.

NOTE A full refresh and the change data capture job for the same source are not intended to run at the same time. This is not currently enforced.

Adding a Table to a Running Source

Run a full refresh for the new table only. The change data capture job does not need to be stopped and the source does not need to be re-baselined. The new table is snapshotted as of the point the stream has currently reached, so the snapshot and the stream meet with no gap.

NOTE A table added later lands on a different snapshot SCN than the tables loaded earlier. A transaction that touched both can appear in one table and not the other until the next merge. It converges and loses nothing.

Stopping and Restarting

Cancelling the change data capture job detaches from Oracle and the job reports as canceled. On restart it resumes from where it stopped.

NOTE Restarts must fall inside the archived redo retention window on the source. If the consumer is stopped long enough for Oracle to discard redo it had not yet consumed, the stream cannot be resumed and the affected tables must be re-baselined.

Re-baselining a Source

To discard the current change window and start from a fresh snapshot:

Set the source-level advanced configuration oracle_xstream_full_refresh_advance_scn to true.
Run a full refresh.
Set the configuration back to false.

NOTE If the configuration is left at true, every subsequent full refresh of an individual table re-captures the SCN instead of reusing the existing one.

Oracle Prerequisites

The following are configured on the source database by a database administrator. The connector cannot create them.

Requirement	Description
ARCHIVELOG mode	The database must be in ARCHIVELOG mode. XStream capture mines redo and archived redo must be retained.
Minimum supplemental logging	Supplemental logging must be enabled so that complete row images can be reconstructed.
Primary-key supplemental logging	Each captured table must log primary-key values so that updates and deletes carry key values. Adding a table to an XStream rule set normally arranges this implicitly.
XStream administrator user	A user with XStream privileges granted through `DBMS_XSTREAM_AUTH`. On a multitenant database this is typically a common user.
Outbound server	An outbound server created with `DBMS_XSTREAM_ADM.CREATE_OUTBOUND`, with its rule set scoped to the schemas or tables to be replicated.
Prepared tables	The tables in scope must be prepared for instantiation. Creating the rules normally handles this.
EXECUTE on DBMS_FLASHBACK	The connection user requires `EXECUTE` on `DBMS_FLASHBACK`. The full refresh snapshot reads as of an SCN.
Undo retention	Undo must be retained long enough to cover the longest full refresh. If undo for the snapshot SCN has aged out, the full refresh fails with `ORA-01555`.
Archived redo retention	Redo must be retained long enough to cover the longest period the consumer may be stopped.

NOTE On a multitenant database, capture, apply and the outbound server are root-level objects even when the captured tables are in a PDB. The XStream dictionary views return no rows if queried from inside the PDB.

Scoping the Outbound Server

The rule set on the outbound server determines what Oracle sends.

Rule scope	Description
Schema-level	Oracle sends changes for every table in the schema. Changes for tables not configured in the source are discarded on arrival. Adding a table requires no database change.
Table-level	Oracle sends only the listed tables. Adding a table requires a database administrator to change the rule set.

NOTE A table absent from the outbound server's rule set never produces changes, regardless of how it is configured in the source. This is the most common cause of a change data capture job running without any changes arriving.

Verifying the Source Side

Three conditions must hold on the source database:

Object	Expected state
Capture process	`ENABLED`. It does not always survive a database restart.
Apply process	`ENABLED`.
Outbound server	`DETACHED` when no consumer is running, which is the healthy idle state, and `ATTACHED` once the consumer job is up.

Limitations

There is one change data capture job per source, not per table. All tables under the source are served by a single outbound server attachment.
A full refresh and the change data capture job for the same source are not intended to run at the same time, and this is not enforced.
Natural keys are mandatory. There is no append-only fallback.
The target table holds current state only. Historical versions remain in the staging table but are not materialized as SCD2.
The staging table is not dropped after a merge and grows continuously.
A single outbound server attachment is exclusive. If a previous consumer terminated abnormally, Oracle may still consider a session attached and reject the new one.
Deleting and re-adding a table reuses the existing source-level watermark as the snapshot point rather than capturing a fresh one. If significant time has passed, that SCN may fall outside undo retention and the snapshot fails.

Last updated on Oct 5, 2026

Was this page helpful?