Oracle

CDC source setup for Oracle

Sling supports Change Data Capture from Oracle by connecting to Oracle GoldenGate Data Streams over WebSocket. Each run reads row-level inserts, updates, and deletes from the GoldenGate Data Stream and merges them into the target table. Sling uses GoldenGate Data Streams — not LogMiner — because Data Streams provide a reliable, low-overhead, real-time change feed without requiring direct access to redo logs from the client.

For general CDC concepts, the two-phase process, and all available options, see the Change Capture overview.

Prerequisites

1. Oracle Database Requirements

Oracle Database 19c or later is required. The following settings must be configured on the source database:

-- Verify ARCHIVELOG mode is enabled
SELECT LOG_MODE FROM V$DATABASE;  -- Must be ARCHIVELOG

-- Enable ARCHIVELOG mode (if not already enabled)
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

-- Enable force logging (recommended)
ALTER DATABASE FORCE LOGGING;

-- Enable supplemental logging
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

-- Enable GoldenGate replication parameter
ALTER SYSTEM SET enable_goldengate_replication = TRUE SCOPE=BOTH;

Verify the configuration:

SELECT SUPPLEMENTAL_LOG_DATA_MIN, FORCE_LOGGING FROM V$DATABASE;
-- Both should return YES

2. GoldenGate Requirements

Oracle GoldenGate 23ai or later is required. The Data Streams feature — which Sling uses to consume changes over WebSocket — was introduced in GoldenGate 23ai.

Before running Sling, you must have:

1. A GoldenGate deployment connected to the source Oracle database.

2. An Extract process configured to read the source database's redo logs.

3. A Data Stream provisioned and attached to the Extract's trail file.

GoldenGate Free is available at no cost for Oracle databases under 20 GB. It includes the Data Streams feature and is sufficient for development and small workloads. For production use with larger databases, a commercial GoldenGate license is required.

Configuring GoldenGate Extract and Data Streams is done through the GoldenGate Admin Console or REST API. Refer to the Oracle GoldenGate documentation for detailed setup instructions.

3. GoldenGate Connection Properties

The following properties are added to the Oracle connection definition and are only used when mode: change-capture is active:

Property	Description
gg_host (required)	GoldenGate REST/WebSocket hostname or IP address.
gg_port	GoldenGate REST/WebSocket port. Default is 443.
gg_user	GoldenGate REST API user. Default is oggadmin.
gg_password (required)	GoldenGate REST API password.
gg_stream (required)	Name of the pre-provisioned Data Stream in GoldenGate.
gg_tls_skip_verify	Skip TLS certificate verification. Default is false. Set to true for self-signed certificates.
gg_tls_ca_cert	Path to a custom CA certificate file for TLS verification.

4. User Permissions

The Oracle database user used by Sling needs SELECT access on the source tables (used during the initial snapshot):

GRANT SELECT ON HR.EMPLOYEES TO sling_user;
GRANT SELECT ON HR.DEPARTMENTS TO sling_user;

-- Or grant schema-wide access
GRANT SELECT ANY TABLE TO sling_user;

The GoldenGate Extract process typically runs under a dedicated GoldenGate admin user with broader privileges. The Sling connection user only needs SELECT on the source tables for the initial snapshot — change events are consumed from the GoldenGate Data Stream, not directly from the database.

Quick Start

# Source Oracle (with GoldenGate CDC properties)
sling conns set MY_ORACLE type=oracle host=oracle.example.com user=sling_user password=secret sid=ORCL \
  gg_host=gg.example.com gg_password=gg_secret gg_stream=my_data_stream

# Target PostgreSQL
sling conns set MY_POSTGRES type=postgres host=pg.example.com user=postgres password=secret database=analytics

# State store (required for CDC)
export SLING_STATE='MY_POSTGRES/sling_state'

# replication.yaml
source: MY_ORACLE
target: MY_POSTGRES

defaults:
  mode: change-capture
  primary_key: [id]
  object: public.{stream_table}

streams:
  HR.EMPLOYEES:
  HR.DEPARTMENTS:

# First run: performs the initial snapshot
sling run -r replication.yaml

# Subsequent runs: captures and applies changes
sling run -r replication.yaml

How It Works

Unlike MySQL (binlog streaming) and PostgreSQL (WAL logical replication), Oracle CDC uses an external middleware layer:

1. The GoldenGate Extract process reads the Oracle redo logs and writes change records to a trail file.

2. The GoldenGate Data Stream exposes the trail file as a WebSocket endpoint, streaming row-level change events in real time.

3. On each run, Sling connects to the Data Stream, reads change events from the last saved position, and merges them into the target table.

4. The GoldenGate position is saved in the state store.

Because Sling connects to GoldenGate (not directly to Oracle redo logs), the Oracle database sees no additional load from CDC reads. All redo log parsing is handled by the GoldenGate Extract process.

Examples

Large Tables with Custom Chunk Size

For very large tables, adjust the chunk size to control memory usage and checkpointing frequency during the initial snapshot.

source: MY_ORACLE
target: MY_POSTGRES

defaults:
  mode: change-capture
  primary_key: [id]
  object: analytics.{stream_table}
  change_capture_options:
    snapshot_chunk_size: 50000  # 50k rows per chunk

streams:
  HR.TRANSACTIONS:
    # This 10M-row table will be loaded in ~200 chunks
    # If interrupted, it resumes from the last completed chunk

Time-Bounded Snapshots for Very Large Tables

For tables with hundreds of millions of rows, the initial snapshot can take hours. Use snapshot_run_duration to cap how long each run spends on the snapshot. The next run automatically resumes from the last completed chunk.

source: MY_ORACLE
target: MY_POSTGRES

defaults:
  mode: change-capture
  primary_key: [id]
  object: analytics.{stream_table}
  change_capture_options:
    snapshot_chunk_size: 50000
    snapshot_run_duration: 30m  # spend at most 30 minutes per run on the snapshot

streams:
  HR.HUGE_EVENTS:
    # 500M rows — will take multiple runs to complete the initial load
    # Each run processes ~30 minutes worth of chunks, then exits cleanly

High-Throughput Workloads

For tables with heavy write activity, increase run_max_events and run_max_duration so each run captures more changes.

source: MY_ORACLE
target: MY_POSTGRES

defaults:
  mode: change-capture
  primary_key: [id]
  object: warehouse.{stream_table}
  change_capture_options:
    run_max_events: 50000  # Process up to 50k events per run
    run_max_duration: 60s  # Wait up to 60 seconds for events

streams:
  HR.CLICK_EVENTS:
  HR.PAGE_VIEWS:

Soft Deletes

Keep deleted rows in the target instead of physically removing them. Deleted rows are marked with _sling_synced_op = 'D'. Useful for audit trails or when downstream queries need to detect deletions.

source: MY_ORACLE
target: MY_POSTGRES

defaults:
  mode: change-capture
  primary_key: [id]
  object: public.{stream_table}
  change_capture_options:
    soft_delete: true

streams:
  HR.CUSTOMERS:
  HR.SUBSCRIPTIONS:

When a row is deleted in Oracle, the target row is preserved with _sling_synced_op set to 'D' and _sling_synced_at updated to the current timestamp. A subsequent re-insert of the same primary key restores the row with the appropriate operation type.

Mixed Streams with Per-Stream Overrides

Different tables can have different CDC options.

source: MY_ORACLE
target: MY_POSTGRES

defaults:
  mode: change-capture
  primary_key: [id]
  object: public.{stream_table}

streams:
  # High-volume table: larger batches
  HR.EVENTS:
    change_capture_options:
      run_max_events: 100000
      run_max_duration: 2m

  # Audit table: keep soft deletes
  HR.USER_ACCOUNTS:
    change_capture_options:
      soft_delete: true

  # Standard table: uses defaults
  HR.PRODUCTS:

Replay / Backfill from a Point in Time

If target data becomes inconsistent, you can replay changes from an earlier position. The replay_from value is applied exactly once per unique value.

source: MY_ORACLE
target: MY_POSTGRES

defaults:
  mode: change-capture
  primary_key: [id]
  object: public.{stream_table}
  change_capture_options:
    replay_from: "2025-06-01T00:00:00Z"  # Re-process all changes since June 1

streams:
  HR.ORDERS:

After the replay run completes, remove or change the replay_from value. Leaving it unchanged has no effect (it is only applied once).

Replay Formats

The replay_from option accepts these Oracle-specific position formats:

• RFC 3339 timestamp: 2025-06-01T00:00:00Z

• GoldenGate position: A GoldenGate position string as reported by the Data Stream (e.g., the position value from the state store)

Why GoldenGate (Not LogMiner)

Sling uses GoldenGate Data Streams instead of LogMiner for Oracle CDC. Here's why:

• LogMiner is resource-intensive: LogMiner parses redo logs via SQL sessions on the source database, consuming CPU and memory on the production system. GoldenGate Extract offloads this work to a separate process.

• LogMiner has version-specific limitations: The CONTINUOUS_MINE option — which enabled continuous redo log reading — was deprecated in Oracle 19c. Without it, LogMiner requires manual log file management.

• LogMiner is single-threaded: It does not scale well for high-volume, low-latency CDC workloads.

• GoldenGate Data Streams are purpose-built: They provide a real-time, WebSocket-based change feed with built-in position tracking, designed specifically for CDC consumers.

• GoldenGate Free is available: For databases under 20 GB, GoldenGate Free can be used at no cost, making it accessible for development and small deployments.

GoldenGate Setup Overview

Setting up GoldenGate is a one-time process. Here is a condensed checklist:

1. Install GoldenGate 23ai (or later). The quickest method is Docker:

   docker login container-registry.oracle.com
   docker pull container-registry.oracle.com/goldengate/goldengate-free:latest
   
   docker run --name ogg-free -p 443:443 \
     -e OGG_ADMIN_PWD='YourPassword1!' \
     container-registry.oracle.com/goldengate/goldengate-free:latest

2. Connect GoldenGate to the source Oracle database via the GoldenGate Admin Console at https://localhost:443.

3. Create an Extract process that reads the source database's redo logs.

4. Create a Data Stream in the GoldenGate Console under Distribution Service > Data Streams, and point it to the Extract's trail file.

5. Note the Data Stream name — this becomes the gg_stream connection property in Sling.

6. Ensure the GoldenGate REST API is accessible from the machine running Sling (port 443 by default).

For detailed instructions, see:

• Oracle GoldenGate Free Getting Started Guide

• Data Streams Components

Troubleshooting

"CDC not supported for <type>"

Ensure your source connection is configured as type=oracle and includes the required gg_host, gg_password, and gg_stream properties.

"could not connect to GoldenGate Data Stream"

Verify gg_host and gg_port are correct and that the GoldenGate REST API is reachable from the machine running Sling. Check firewall rules and TLS settings. For self-signed certificates, set gg_tls_skip_verify: true for testing.

"GoldenGate Data Stream not found"

The gg_stream value must match an existing Data Stream name in GoldenGate. Verify the stream exists in the GoldenGate Admin Console under Distribution Service > Data Streams.

"authentication failed for GoldenGate"

Check gg_user and gg_password. The default GoldenGate admin user is oggadmin. Ensure the password meets Oracle's complexity requirements.

Initial snapshot keeps restarting

Ensure SLING_STATE is configured. Without state persistence, Sling cannot track that the snapshot completed and will restart it on every run.

"could not resolve replay_from position"

The replay_from value must be a valid RFC 3339 timestamp or GoldenGate position string. Ensure the position is still available in the GoldenGate trail files.