Documentation Index Fetch the complete documentation index at: https://www.integrate.io/docs/llms.txt
Use this file to discover all available pages before exploring further.
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:
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 );
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' );
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;
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>' ;
