(888) 884 6405
Press Enter to see full results

ELT & CDC
Knowledge Base

Sources - Oracle CDC - AWS RDS Oracle

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 "uvP4fNnxatWGV1SrPTGX"
  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 and grant the necessary privileges for LogMiner and table access by running the following queries as a DBA user:

-- RDS: Grant LogMiner and DBA privileges via rdsadmin
BEGIN
  rdsadmin.rdsadmin_util.grant_sys_object('DBMS_LOGMNR', 'INTEGRATEIO', 'EXECUTE');
  rdsadmin.rdsadmin_util.grant_sys_object('DBMS_LOGMNR_D', 'INTEGRATEIO', 'EXECUTE');
  rdsadmin.rdsadmin_util.grant_sys_object('V_$LOG', 'INTEGRATEIO', 'SELECT');
  rdsadmin.rdsadmin_util.grant_sys_object('V_$LOGFILE', 'INTEGRATEIO', 'SELECT');
  rdsadmin.rdsadmin_util.grant_sys_object('V_$LOGMNR_CONTENTS', 'INTEGRATEIO', 'SELECT');
  rdsadmin.rdsadmin_util.grant_sys_object('V_$LOGMNR_LOGS', 'INTEGRATEIO', 'SELECT');
  rdsadmin.rdsadmin_util.grant_sys_object('V_$ARCHIVED_LOG', 'INTEGRATEIO', 'SELECT');
  rdsadmin.rdsadmin_util.grant_sys_object('V_$DATABASE', 'INTEGRATEIO', 'SELECT');
  rdsadmin.rdsadmin_util.grant_sys_object('V_$INSTANCE', 'INTEGRATEIO', 'SELECT');
  rdsadmin.rdsadmin_util.grant_sys_object('DBA_OBJECTS', 'INTEGRATEIO', 'SELECT');
  rdsadmin.rdsadmin_util.grant_sys_object('DBA_TABLES', 'INTEGRATEIO', 'SELECT');
  rdsadmin.rdsadmin_util.grant_sys_object('DBA_TAB_COLUMNS', 'INTEGRATEIO', 'SELECT');
  rdsadmin.rdsadmin_util.grant_sys_object('DBA_CONSTRAINTS', 'INTEGRATEIO', 'SELECT');
  rdsadmin.rdsadmin_util.grant_sys_object('DBA_CONS_COLUMNS', 'INTEGRATEIO', 'SELECT');
  rdsadmin.rdsadmin_util.grant_sys_object('DBA_LOG_GROUPS', 'INTEGRATEIO', 'SELECT');
  rdsadmin.rdsadmin_util.grant_sys_object('DBA_LOG_GROUP_COLUMNS', 'INTEGRATEIO', 'SELECT');
END;
/

GRANT SELECT ANY TABLE TO INTEGRATEIO;
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):

-- RDS: Enable ARCHIVELOG mode
exec rdsadmin.rdsadmin_util.set_configuration('archivelog retention hours', 24);

2. Enable Database-Level Supplemental Logging

-- RDS: Enable supplemental logging
exec rdsadmin.rdsadmin_util.alter_supplemental_logging('ADD');
exec rdsadmin.rdsadmin_util.alter_supplemental_logging('ADD', 'PRIMARY KEY');

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