Enable change capture on Oracle

Oracle's free, built-in log-based CDC is LogMiner: Elglide mines the redo / archive logs to sync only what changed — including deletes, which a date/sequence watermark can't see. Once the prerequisites below are in place, Elglide manages the resume position (the SCN), the first consistent snapshot, and self-healing for you — per task, automatically.

Oracle has the highest setup bar of our connectors: LogMiner needs ARCHIVELOG mode (a one-time change that requires a database restart), supplemental logging, and several grants. On a multitenant (CDB) database it also needs a common user (see the CDB box below). If any piece is missing, the table simply loads Full or via the date/sequence watermark instead — no data is ever lost.

Permissions required

The role Elglide connects with needs all of the following (one-time, by a DBA):

CREATE SESSION — connect.
LOGMINING — run LogMiner.
SELECT ANY TRANSACTION — mine committed transactions.
SELECT_CATALOG_ROLE — read V$DATABASE / V$LOGMNR_CONTENTS (without it, Elglide can't even detect that LogMiner is available and the table stays on a Full / watermark sync).
EXECUTE on DBMS_LOGMNR — the LogMiner package itself.
SELECT on each synced table — the initial snapshot + reads (or SELECT ANY TABLE).
SET CONTAINER — multitenant (CDB) only: LogMiner runs from CDB$ROOT, so the connection must be a common user able to switch containers (see the CDB box below). Not needed on a non-container database.

Complete grant set (non-CDB — for a CDB, use the CONTAINER=ALL version in the box below):

GRANT CREATE SESSION         TO elglide_user;
GRANT LOGMINING              TO elglide_user;
GRANT SELECT ANY TRANSACTION TO elglide_user;
GRANT SELECT_CATALOG_ROLE    TO elglide_user;
GRANT EXECUTE ON DBMS_LOGMNR TO elglide_user;
GRANT SELECT ANY TABLE       TO elglide_user;   -- or SELECT on each synced table

1. Turn on ARCHIVELOG mode (one-time, needs a restart)

On a self-managed Oracle, as a DBA:

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

-- confirm (expect ARCHIVELOG):
SELECT log_mode FROM v$database;

2. Add supplemental logging

LogMiner needs supplemental logging so the redo carries enough column data to rebuild each changed row and identify it by primary key:

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

-- confirm (expect YES / YES):
SELECT supplemental_log_data_min, supplemental_log_data_pk FROM v$database;

3. Grant the Elglide connection role

GRANT CREATE SESSION         TO elglide_user;
GRANT LOGMINING              TO elglide_user;
GRANT SELECT ANY TRANSACTION TO elglide_user;
GRANT SELECT_CATALOG_ROLE    TO elglide_user;
GRANT EXECUTE ON DBMS_LOGMNR TO elglide_user;
GRANT SELECT ON yourschema.your_table TO elglide_user;   -- or SELECT ANY TABLE

Multitenant (CDB / PDB) databases — use a COMMON user. On a container database, LogMiner can only run from the root (CDB$ROOT) — running it inside a pluggable database fails with ORA-65040. Only a common user (one whose name starts with C##) can switch to the root, so the Elglide connection for an Oracle CDB must use a common user, granted with CONTAINER=ALL:

CREATE USER c##elglide IDENTIFIED BY <pwd> CONTAINER=ALL;
GRANT CREATE SESSION, SET CONTAINER, LOGMINING, SELECT ANY TRANSACTION,
      SELECT_CATALOG_ROLE TO c##elglide CONTAINER=ALL;
GRANT EXECUTE ON DBMS_LOGMNR TO c##elglide CONTAINER=ALL;
GRANT SELECT ANY TABLE       TO c##elglide CONTAINER=ALL;

The extra SET CONTAINER privilege is what lets Elglide mine from the root and filter back to your PDB. Elglide auto-detects CDB vs non-CDB — on a non-container database a plain local user with the grants in step 3 is enough.

Amazon RDS for Oracle (no SYSDBA shell): ARCHIVELOG is on whenever automated backups are enabled. Use the RDS admin procedures instead of the DDL above:

retention: exec rdsadmin.rdsadmin_util.set_configuration('archivelog retention hours', 72);
supplemental logging: exec rdsadmin.rdsadmin_util.alter_supplemental_logging('ADD'); and ('ADD','PRIMARY KEY');
package + dictionary grants: exec rdsadmin.rdsadmin_util.grant_sys_object('DBMS_LOGMNR','ELGLIDE_USER','EXECUTE'); (repeat for V_$DATABASE, V_$LOGMNR_CONTENTS, …).

4. Make sure each table has a PRIMARY KEY

Elglide MERGEs changes (and deletes) into Snowflake by key, so each synced table needs a PRIMARY KEY. A table with no primary key stays on a Full / watermark sync.

ALTER TABLE yourschema.your_table ADD CONSTRAINT pk_your_table PRIMARY KEY (id);

5. That's it — Elglide handles the rest

When you create an Incremental task on a LogMiner-ready table, Elglide automatically:

takes a consistent snapshot for the first Full load and records the SCN at that point (no gaps, no duplicates);
mines inserts / updates / deletes from the redo / archive logs on each run and MERGEs them into Snowflake;
tracks its SCN and advances it only after data lands in Snowflake (at-least-once);
self-heals — if the source recycles the redo/archive logs the task still needs (e.g. it was paused past retention), the next run re-baselines with a full sync and resumes.

Data types in change capture

For the types LogMiner can capture, change-captured rows are cast to Snowflake exactly the same way as the first Full load — same target types, same representation — so a changed row matches a row from the initial snapshot. That covers NUMBER (full precision), every TIMESTAMP / TIMESTAMP WITH TIME ZONE variant, INTERVAL, RAW, DATE, and all character types. See the Oracle connector data-type reference (sign-in required) for the per-type rules.

LOB & XMLTYPE columns are not supported by LogMiner. Oracle's redo logs do not carry usable values for CLOB, NCLOB, BLOB, XMLTYPE, or JSON columns — LogMiner reports the whole row change as unsupported. A table that contains any of these columns therefore cannot be change-captured incrementally; Elglide keeps it on a RowHash / Full sync instead (which reads every column via a cast-aware query and handles all types correctly). This is a LogMiner limitation, not an Elglide one — if you need low-latency CDC on a table with LOB columns, GoldenGate is the Oracle-supported path.

Spatial columns. On the Full load, SDO_GEOMETRY is stored as WKT text; over change capture it arrives as the raw geometry literal. The value is preserved; only the text shape differs. If you sync spatial columns over CDC and need WKT, let us know.

Keep an eye on redo retention

The source keeps archived redo for its retention window (RDS: the archivelog retention hours setting). As long as a CDC task keeps running it stays well inside that window. If you pause a task longer than retention, the logs it needs are gone — Elglide detects the stale SCN and automatically re-baselines with a full sync, so you don't lose data, but the next run is a full reload.

Verify in Elglide

Open the connection's Pipelines tab. A LogMiner-ready table will create an Incremental task using the LogMiner mechanism — a first Full load to seed Snowflake, then change-only syncs. If a table still loads Full, re-check the boxes above (ARCHIVELOG, supplemental logging, the grants — especially SELECT_CATALOG_ROLE and EXECUTE ON DBMS_LOGMNR, a common user on a CDB, and a primary key).