(888) 884 6405
Press Enter to see full results

ELT & CDC
Knowledge Base

Sources - Oracle CDC - Self Hosted

Overview

ELT & CDC uses Oracle LogMiner for change data capture from Oracle databases.

Requirements

  • Oracle Database 11g Release 2 or above.
  • The database must be running in ARCHIVELOG mode.
  • Supplemental logging must be enabled at the database level.
  • Supplemental logging must be enabled for the tables you want to sync.
  • A user with privileges to access LogMiner and the source tables.

Supported Features

  • Initial full table sync
  • Real-time change data capture (INSERT, UPDATE, DELETE)
  • Resume from last position after restart

Create sync user

Create a sync user for ELT & CDC by executing the following SQL as a DBA user:

-- Create the sync user
CREATE USER integrateio IDENTIFIED BY "BxI8NPnFGVdwLXi4CS7M"
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp
  QUOTA UNLIMITED ON users;

-- Grant basic connection privileges
GRANT CREATE SESSION TO integrateio;

Grant LogMiner privileges

Specify the schema containing the tables you want to sync:

-- Grant LogMiner privileges
GRANT EXECUTE ON DBMS_LOGMNR TO integrateio;
GRANT EXECUTE ON DBMS_LOGMNR_D TO integrateio;

-- Grant access to LogMiner views
GRANT SELECT ON V_$LOG TO integrateio;
GRANT SELECT ON V_$LOGFILE TO integrateio;
GRANT SELECT ON V_$LOGMNR_CONTENTS TO integrateio;
GRANT SELECT ON V_$LOGMNR_LOGS TO integrateio;
GRANT SELECT ON V_$ARCHIVED_LOG TO integrateio;
GRANT SELECT ON V_$DATABASE TO integrateio;
GRANT SELECT ON V_$INSTANCE TO integrateio;

-- Grant access to dictionary views
GRANT SELECT ON DBA_OBJECTS TO integrateio;
GRANT SELECT ON DBA_TABLES TO integrateio;
GRANT SELECT ON DBA_TAB_COLUMNS TO integrateio;
GRANT SELECT ON DBA_CONSTRAINTS TO integrateio;
GRANT SELECT ON DBA_CONS_COLUMNS TO integrateio;
GRANT SELECT ON DBA_LOG_GROUPS TO integrateio;
GRANT SELECT ON DBA_LOG_GROUP_COLUMNS TO integrateio;

-- Grant SELECT on all tables in the source schema
GRANT SELECT ANY TABLE TO integrateio;

-- For Oracle 12c and above, grant LOGMINING privilege
GRANT LOGMINING TO integrateio;

Enable supplemental logging

Enable supplemental logging to capture change data. Run the following queries as a DBA user:

1. Check ARCHIVELOG Mode

First, verify that the database is running in ARCHIVELOG mode:

-- Check if database is in ARCHIVELOG mode
SELECT LOG_MODE FROM V$DATABASE;

If not in ARCHIVELOG mode, enable it (requires database restart):

-- Enable ARCHIVELOG mode (if needed)
-- WARNING: This requires a database restart
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

2. Enable Database-Level Supplemental Logging

-- Enable minimal supplemental logging at database level
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

-- Optionally, enable primary key and unique index logging
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE) COLUMNS;

3. Enable Table-Level Supplemental Logging

Enable supplemental logging for each table you want to sync. You can enable logging for all columns or just primary key columns:

-- For each table you want to sync
ALTER TABLE <schema_name>.<table_name> ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

4. Verify Supplemental Logging

-- Verify database-level supplemental logging
SELECT SUPPLEMENTAL_LOG_DATA_MIN,
       SUPPLEMENTAL_LOG_DATA_PK,
       SUPPLEMENTAL_LOG_DATA_ALL
FROM V$DATABASE;

-- Verify table-level supplemental logging
SELECT OWNER, TABLE_NAME, LOG_GROUP_NAME, LOG_GROUP_TYPE
FROM DBA_LOG_GROUPS
WHERE OWNER = '<schema_name>';

See Also