Oracle CDC: Supplemental Logging, LogMiner Configuration, and Production Setup

Getting CDC out of Oracle is harder than getting it out of PostgreSQL or MySQL. The architecture is genuinely different, and the setup steps that matter, supplemental logging and archive log mode, are database-level configuration that DBAs have historically managed separately from application-level access. A CDC connector shows up and needs both. The DBA either hasn’t heard of supplemental logging or enabled the wrong level for CDC. Weeks later, silently missing records surface in a data warehouse discrepancy report.

This guide covers the setup that has to happen before a connector can capture Oracle changes correctly.

Why Oracle CDC Is Different: LogMiner and the Redo Log

PostgreSQL and MySQL expose change data through a dedicated replication API (logical replication slots, binlog). Oracle doesn’t have that. Instead, Oracle writes every committed change to a redo log, and Oracle LogMiner is the interface that lets you read and interpret those records.

LogMiner isn’t a streaming API. It reads archived redo log files and translates the binary redo record format into SQL-like representations of each change. CDC tools layer on top of LogMiner to extract those SQL events and stream them downstream.

Two Oracle configurations are prerequisites for this to work:

Archive log mode means Oracle, instead of overwriting redo log segments when they fill, archives them to a configurable destination before reuse. Without it, redo records are gone as soon as Oracle cycles to the next log group. CDC tools need to be able to read historical logs, so archive log mode is non-negotiable. Check whether it’s enabled:

SELECT LOG_MODE FROM V$DATABASE;

If it returns NOARCHIVELOG, the DBA needs to switch the database to archive log mode, which requires a brief downtime to bounce the instance.

Supplemental logging controls how much column data the redo record captures. By default, Oracle writes the minimum needed to redo or undo a transaction: often just the changed columns and the rowid. A CDC connector needs more: it needs to reconstruct the full row state, identify rows by primary key, and (for deletes) know which row was removed. Supplemental logging adds that data to the redo record. Without the right level enabled, updates and deletes arrive at the connector with insufficient data to route or apply correctly.

Enabling Supplemental Logging: Minimal, Primary Key, and All Columns

Oracle has four supplemental logging levels, each adding more column data to the redo record:

Minimal supplemental logging enables the infrastructure that other levels build on. It doesn’t write any additional column data itself:

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

You need this before any other supplemental logging level takes effect. Verify it’s active:

SELECT NAME, SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

Primary key supplemental logging ensures that the primary key columns are always present in the redo record, even when they weren’t modified:

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

This is the minimum useful level for CDC. Without it, the connector receives change events with no reliable way to identify which row changed, especially for deletes, where the redo record may carry only the rowid, which becomes meaningless once the row is gone.

All columns supplemental logging writes the complete before-image of every row on every change:

ALTER TABLE schema.orders ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

For CDC, ALL COLUMNS is the most common production choice on the tables you’re capturing. It ensures that UPDATE events carry both the old and new values for every column, and that DELETE events include the full row. The cost is redo volume, roughly proportional to average row width times update rate on those tables.

A practical pattern for production: enable minimal logging at the database level, then ALL COLUMNS selectively on the tables the CDC pipeline needs. This bounds the redo overhead to only the captured tables.

After making these changes, confirm the state with:

SELECT LOG_GROUP_NAME, LOG_GROUP_TYPE, LOG_GROUP_CONDITION, LOG_GROUP_COLUMNS
FROM DBA_LOG_GROUPS
WHERE LOG_GROUP_CONDITION = 'ALWAYS';

LogMiner Configuration and User Privileges

The CDC connector connects to Oracle with a dedicated database user. That user needs specific privileges to open a LogMiner session and read redo data.

On Oracle 12c R2 and later, the minimal grant set is:

GRANT CREATE SESSION TO cdc_user;
GRANT LOGMINING TO cdc_user;
GRANT SELECT ON V_$DATABASE TO cdc_user;
GRANT SELECT ON V_$LOG TO cdc_user;
GRANT SELECT ON V_$LOGFILE TO cdc_user;
GRANT SELECT ON V_$ARCHIVED_LOG TO cdc_user;
GRANT SELECT ON V_$ARCHIVE_DEST_STATUS TO cdc_user;
GRANT SELECT ANY TABLE TO cdc_user;

On Oracle 11g or 12c R1 (pre-LOGMINING privilege), use EXECUTE_CATALOG_ROLE and SELECT_CATALOG_ROLE instead. Check which Oracle version you’re on before choosing the grant path: the LOGMINING privilege doesn’t exist on older releases.

For multitenant (CDB/PDB) Oracle setups, the CDC user must be a common user (prefixed with C##) created at the CDB level, with grants applied to the CDB root and propagated to the pluggable databases being captured.

The LogMiner dictionary mode also matters. Two options exist:

Online catalog mode (recommended) uses the current data dictionary for decoding. It’s faster to start and requires no extra prep. The risk: if a table’s DDL changes and the connector is processing logs from before that change, it may decode some events incorrectly. For typical CDC pipelines with low DDL frequency, online catalog mode works well.

Redo log dictionary mode extracts the data dictionary into redo logs at the time of the extraction. It handles DDL changes more reliably but requires capturing the dictionary before starting LogMiner, which adds setup complexity and snapshot time.

Archive Log Retention and Destination Sizing

Archive logs must remain on disk long enough for the connector to read them. If Oracle deletes a log before the connector has processed it, the connector fails with a missing log sequence error and the only recovery is a full re-snapshot of the affected tables.

The retention window to plan for: at minimum, twice the longest expected connector outage. If you tolerate a 12-hour connector downtime, keep at least 24 hours of archive logs. For regulated environments with maintenance windows spanning weekends, 72-hour retention is common.

Configure the recovery area:

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/path/to/archive' SCOPE=BOTH;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 200G SCOPE=BOTH;
ALTER SYSTEM SET ARCHIVE_LAG_TARGET = 900 SCOPE=BOTH; -- force archive every 15 min

The ARCHIVE_LAG_TARGET parameter matters for low-latency CDC. Without it, Oracle only archives when a redo log fills. On a lightly-updated table, the current redo segment may sit unflushed for hours. Setting a target (900 seconds is common) forces a log switch, which triggers archival, which makes recent changes visible to LogMiner. Without this, a CDC connector on a quiet database sees a latency equal to however long Oracle takes to fill and switch the current redo log.

Monitor archive log space usage:

SELECT SPACE_LIMIT/1024/1024/1024 AS limit_gb,
       SPACE_USED/1024/1024/1024  AS used_gb,
       SPACE_RECLAIMABLE/1024/1024/1024 AS reclaimable_gb
FROM V$RECOVERY_FILE_DEST;

Alert when used_gb exceeds 70% of limit_gb. Oracle automatically deletes archive logs once the destination fills, regardless of whether the connector has read them.

Common Setup Failures and Their Symptoms

Missing before-images on updates. If you see UPDATE events arriving at the destination with null or absent columns, supplemental logging isn’t capturing enough column data. Check whether ALL COLUMNS supplemental logging is active for the affected tables.

Silent data gaps on deletes. A CDC pipeline that appears to process all events but doesn’t remove rows from the destination often has primary key supplemental logging absent. Without the primary key in the delete record, the connector can’t route the delete to the right destination row.

ORA-01291 / missing archived log. The connector has requested a log file that Oracle has already deleted. The only fix is to restore the archive from backup and resume, or to re-snapshot the affected tables. This happens when archive log retention is too short relative to connector downtime.

ORA-01403 / no data found in LogMiner dictionary. Usually means the LogMiner session is using the redo log dictionary mode but the dictionary capture didn’t complete before mining started. Switch to online catalog mode, or re-run the dictionary extraction.

Connector stuck on active redo log. The connector has caught up to the current redo segment but no new changes are visible. Usually caused by ARCHIVE_LAG_TARGET not being set: LogMiner can’t read the active redo log’s contents until it’s been archived. Setting a non-zero ARCHIVE_LAG_TARGET resolves this for most setups.

Excessive LogMiner session memory usage. LogMiner builds an in-memory transaction table while processing redo records. On databases with large, long-running transactions, this table can grow to consume significant PGA memory. If you see Oracle process memory spiking during CDC runs, check for open transactions and consider tuning MAX_SGA_SIZE for the LogMiner session, or using COMMITTED_DATA_ONLY mode to skip in-progress transactions.

CDB/PDB permission errors. In a multitenant setup, connecting to a PDB with a local user works for DML but fails for LogMiner: LogMiner requires access to the CDB’s redo logs, which are at the root container level. The C## common user with grants at the root is required. Connecting as a PDB-local user to a multitenant database produces ORA-65040: operation not allowed from within a pluggable database or similar errors when opening a LogMiner session.

Verifying LogMiner Setup Before Going Production

Running a quick verification before connecting your CDC tool saves the more painful debugging that comes from discovering a misconfiguration after you’ve started capturing changes.

Check archive log mode and minimal supplemental logging in one query:

SELECT
  d.LOG_MODE,
  d.SUPPLEMENTAL_LOG_DATA_MIN,
  d.SUPPLEMENTAL_LOG_DATA_PK,
  d.SUPPLEMENTAL_LOG_DATA_UI,
  d.SUPPLEMENTAL_LOG_DATA_FK,
  d.SUPPLEMENTAL_LOG_DATA_ALL
FROM V$DATABASE d;

LOG_MODE should return ARCHIVELOG. SUPPLEMENTAL_LOG_DATA_MIN should be YES. If you enabled primary key or all-column logging at the database level, those flags should also be YES.

Check that archive logs are being written and are accessible:

SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME, BLOCKS, BLOCK_SIZE,
       ARCHIVED, STATUS
FROM V$ARCHIVED_LOG
WHERE FIRST_TIME > SYSDATE - 1
ORDER BY SEQUENCE# DESC;

A healthy system shows recent archive log entries with ARCHIVED = 'YES' and STATUS = 'A' (available). Gaps in the sequence numbers indicate archive log generation failures.

Verify that the CDC user can open a LogMiner session:

-- Run as the CDC user
BEGIN
  DBMS_LOGMNR.ADD_LOGFILE(
    LOGFILENAME => (SELECT NAME FROM V$ARCHIVED_LOG WHERE ROWNUM = 1),
    OPTIONS     => DBMS_LOGMNR.NEW
  );
  DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
END;
/
-- If no error, LogMiner opened successfully
EXEC DBMS_LOGMNR.END_LOGMNR;

If DBMS_LOGMNR.START_LOGMNR succeeds without error, the user has the required privileges. Any ORA-01031: insufficient privileges error points to a missing grant.

Verify table-level supplemental logging for your specific capture tables:

SELECT LOG_GROUP_NAME, TABLE_NAME, LOG_GROUP_TYPE
FROM DBA_LOG_GROUPS
WHERE OWNER = 'YOUR_SCHEMA'
AND TABLE_NAME IN ('TABLE1', 'TABLE2');

Tables you expect to capture but that don’t appear here have no supplemental logging configured. Run ALTER TABLE to add it before starting the connector.

Connecting Streamkap to Oracle

Streamkap’s Oracle connector manages the LogMiner session lifecycle automatically, including log sequence tracking, supplemental logging verification at connection time, and archive log availability checks before each mining pass. When a log file is missing, it surfaces the error with enough context to determine whether a restore or re-snapshot is needed.

The connector setup in Streamkap walks through:

• The required SQL grants listed above
• Verifying archive log mode and supplemental logging state
• Selecting tables for capture and reviewing the supplemental logging state per table
• Configuring the archive log retention window relative to your replication requirements

The Streamkap Oracle documentation covers the connection wizard and the SQL required for both single-tenant and CDB/PDB Oracle environments in detail.

Where to next?

• Oracle connector overview
• Streamkap Oracle documentation
• Start a free 30-day trial