> ## 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.

# Self-hosted Oracle CDC source for ELT & CDC

> Configure a self-hosted Oracle database as a CDC source in Integrate.io ELT & CDC. Set up LogMiner-based change data capture for replication.

## 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:

```sql theme={null}
-- 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:

```sql expandable theme={null}
-- 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:

<Steps>
  <Step>
    Check ARCHIVELOG Mode

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

    ```sql theme={null}
    -- Check if database is in ARCHIVELOG mode
    SELECT LOG_MODE FROM V$DATABASE;
    ```

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

    ```sql theme={null}
    -- Enable ARCHIVELOG mode (if needed)
    -- WARNING: This requires a database restart
    SHUTDOWN IMMEDIATE;
    STARTUP MOUNT;
    ALTER DATABASE ARCHIVELOG;
    ALTER DATABASE OPEN;
    ```
  </Step>

  <Step>
    Enable Database-Level Supplemental Logging

    ```sql theme={null}
    -- 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;
    ```
  </Step>

  <Step>
    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:

    ```sql theme={null}
    -- For each table you want to sync
    ALTER TABLE <schema_name>.<table_name> ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
    ```
  </Step>

  <Step>
    Verify Supplemental Logging

    ```sql theme={null}
    -- 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>';
    ```
  </Step>
</Steps>