Oracle to ClickHouse: CDC, Batch & Type Mapping Guide

Oracle data reaches ClickHouse in four ways: export files and load them, copy tables on a schedule with a batch tool, run your own change data capture (CDC) through Debezium and Kafka, or use a managed CDC platform. Exports and batch copies suit snapshots and nightly reports. Tables that keep changing need log-based CDC, which reads Oracle's redo log through LogMiner and, unlike queries, sees hard deletes.

Three decisions follow at the two ends. On the Oracle side, redo must be archived (ARCHIVELOG mode), carry supplemental logging, and stay on disk longer than any outage. ClickHouse is append-optimized: each change is inserted as a new row version, a ReplacingMergeTree table keeps the newest per key, and Oracle's NUMBER and DATE types need deliberate mapping. Estuary supports this route with an Oracle capture that reads changes through LogMiner and a native ClickHouse materialization that writes them on a schedule you set.

Choosing an Oracle to ClickHouse method

Pick by whether updates and deletes must replicate continuously.

ClickPipes, ClickHouse Cloud's ingestion service, has CDC sources for PostgreSQL, MySQL and MongoDB but none for Oracle, so it can only consume Oracle changes that something else has put on a Kafka topic. Older guides suggest reading Oracle through ClickHouse's JDBC bridge, which ClickHouse now says "is no longer supported"; ClickHouse Cloud doesn't support JDBC or ODBC table functions either. ETL tools for ClickHouse compares vendors in more depth.

From redo logs to ClickHouse tables: how replication works

CDC reads Oracle's redo log, which records every change in system change number (SCN) order, instead of querying tables.

• ARCHIVELOG mode copies each online redo log before reuse, so a lagging pipeline can still read it.
• Supplemental logging adds the column values needed to identify a changed row; Oracle enables none by default.
• LogMiner (DBMS_LOGMNR) returns an SCN range's changes from V$LOGMNR_CONTENTS, naming tables through a dictionary: the online catalog, or a copy extracted into redo.
• Checkpoint: the pipeline resumes from the last SCN it processed, if those archived logs still exist.

Mining starts at the SCN where the initial copy began, so nothing is missed or doubled, and holds each transaction until commit, so only committed transactions reach ClickHouse. In ClickHouse an update is a new row with a higher version and a delete a row marking the key; ReplacingMergeTree keeps the newest per sorting key as parts merge.

Oracle requirements

Log-based CDC needs four things from the source. The statements below assume a self-managed, non-container database, 12c or later:

sql
-- ARCHIVELOG mode (as SYSDBA; take a backup before and after)
SELECT LOG_MODE FROM V$DATABASE;
SHUTDOWN IMMEDIATE
STARTUP MOUNT
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

-- Minimal supplemental logging, plus all columns for captured tables
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER TABLE sales.orders ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

-- A capture user that can mine redo and read the tables
CREATE USER cdc_user IDENTIFIED BY "<password>";
GRANT CREATE SESSION, LOGMINING, SELECT_CATALOG_ROLE, EXECUTE_CATALOG_ROLE TO cdc_user;
GRANT SELECT ON sales.orders TO cdc_user;
GRANT SELECT ON V_$DATABASE TO cdc_user;
GRANT SELECT ON V_$LOG TO cdc_user;

The fourth is archived-log retention longer than your worst outage plus repair time. The capture user isn't strictly read-only: most LogMiner tools write a small watermark table.

Two more rules matter for ClickHouse in particular:

1. Every replicated table needs a primary key. It becomes the ClickHouse sorting key that collapses versions. ROWID isn't a substitute: Oracle can change it when a row is moved, exported and imported, or deleted and reinserted.
2. Keep names short. LogMiner can't mine a table or column whose name exceeds 30 characters.

Triggers and PL/SQL don't move; rebuild that logic as SQL or materialized views.

ClickHouse requirements

Plan the ClickHouse tables around Oracle's keys and change versions.

• A target database, plus a user with rights to create, alter, truncate, insert and select in it. Self-hosted servers and ClickHouse Cloud both take native-protocol connections on 9440 with TLS, and Cloud refuses plaintext.
• ReplacingMergeTree tables with a version column that increases with each change, and optionally an is_deleted column (ClickHouse 23.2 and later) so a delete hides the key.
• The Oracle primary key as ORDER BY. ClickHouse can extend a sorting key only with newly added columns, so if the Oracle key changes, the table is rebuilt.

sql
CREATE TABLE sales.orders
(
    order_id     Int64,
    customer_id  Int64,
    amount       Decimal(12, 2),
    order_date   DateTime64(0),
    version      UInt64,          -- SCN or commit time of the change
    is_deleted   UInt8 DEFAULT 0
)
ENGINE = ReplacingMergeTree(version, is_deleted)
ORDER BY order_id;

Setting up Oracle to ClickHouse replication

1. Prepare redo: switch to ARCHIVELOG mode, add supplemental logging, and check LOG_MODE and SUPPLEMENTAL_LOG_DATA_MIN in V$DATABASE.
2. Set archived-log retention and size the archive destination for peak redo.
3. Create the capture user with mining grants, and open network access from the pipeline.
4. Check LogMiner limits before you build: names over 30 characters, LOB columns, container database access.
5. Create the ClickHouse tables with the Oracle key as ORDER BY, typed per the mapping below.
6. Copy existing rows while holding every archived log written since the copy's starting SCN; mining needs them to catch up.
7. Mine redo from that SCN and choose how often ClickHouse receives inserts.
8. Validate.count() under FINAL should match Oracle's COUNT(*), and sums of high-precision NUMBER columns should agree to the last digit. Update, delete and re-key a row and check each; check a DATE with a time of day.

What happens after the initial load?

• Updates become new rows for the same key. Until a merge, a plain SELECT can return both; SELECT * FROM sales.orders FINAL returns the latest, at extra query cost.
• Key updates have to become a delete of the old key plus an insert of the new one. Otherwise the old key's row stays in ClickHouse.
• Soft deletes keep the row with a delete marker, so FINAL still returns it and queries filter on the marker; audit trails favour them. Hard deletes write a tombstone (is_deleted = 1) that FINAL hides and a later CLEANUP merge removes, so a GDPR erasure isn't physically complete at once.
• TRUNCATE is DDL, not row deletes, so truncated rows stay in ClickHouse until a reload.
• Schema changes need a LogMiner dictionary that knows the new shape (the online catalog decodes only current versions; an extracted one handles DDL). ClickHouse adds columns with ALTER TABLE … ADD COLUMN; other type changes mean a rebuild.

Oracle to ClickHouse data type mapping

The two traps are DATE, which stores a time of day, and NUMBER without precision, which forces a choice between exact and fast.

Plain DateTime covers only 1970 to 2106, so old dates and 9999-12-31 placeholders need DateTime64: 1900 to 2299 before ClickHouse 26.7, years 0000 to 9999 from 26.7 (precision 7 or lower). Declare Nullable only where Oracle allows nulls; it costs performance.

NUMBER precision and Oracle SQL in ClickHouse

ClickHouse recommends Decimal for financial data, converted from text: its docs show toDecimal64(1.15, 2) returning 1.14 but toDecimal64('1.15', 2) returning 1.15. Queries need translating too:

Performance, latency and ClickHouse cost

Freshness depends on redo volume, how much redo each LogMiner pass reads, how often ClickHouse receives inserts, and what FINAL costs at query time. Oracle releases a transaction's changes only at commit, so a long batch job lands at once.

Inserts, parts and merges

• Insert in batches. Each insert creates a part on disk. ClickHouse asks for at least 1,000 rows per insert, ideally 10,000 to 100,000, at about one insert query per second; past that, merges fall behind and inserts fail with Too many parts.
• Pay for FINAL only where needed. It deduplicates in memory at query time. On large tables, filter on the sorting key, or partition by a month that never changes for a row (ClickHouse merges only within a partition) and set do_not_merge_across_partitions_select_final = 1.

Production considerations

• Retries and recovery. A restarted pipeline mines again from its checkpointed SCN. ReplacingMergeTree tables absorb re-inserted versions; plain MergeTree tables get duplicates. Archive retention is the recovery window.
• Deletes. Choose per table between delete-marked rows and tombstones that FINAL hides.
• Monitoring. Watch how far the pipeline's checkpointed SCN trails V$DATABASE.CURRENT_SCN, how full the archive destination is, and how long since ClickHouse last received an insert.

Common problems and troubleshooting

Archived logs deleted before the pipeline read them

Retention or an RMAN job removed a log the pipeline still needs, and mining fails with ORA-01291: missing logfile. See which logs remain:

sql
SELECT THREAD#, SEQUENCE#, FIRST_CHANGE#, NEXT_CHANGE#, FIRST_TIME, NAME, DELETED
FROM V$ARCHIVED_LOG WHERE FIRST_TIME > SYSDATE - 2
ORDER BY THREAD#, SEQUENCE#;

Recover in this order: restore the logs from backup (on RDS, rdsadmin.rdsadmin_archive_log_download within backup retention); otherwise re-read rows changed since the gap; otherwise reload the tables. ClickHouse dedups reloads by key, but rows deleted during the gap remain. Then raise retention.

Keyless Oracle tables multiplying rows in ClickHouse

Without a primary key, a pipeline that keys rows by redo position makes every change a new key: FINAL collapses nothing and deletes remove nothing, so ClickHouse's count() outgrows Oracle's. Add a primary key (or a unique non-null column) and reload, or accept an append-only MergeTree history.

LogMiner errors on the source: ORA-01950 and ORA-04036

• ORA-01950: no privileges on tablespace 'USERS': the capture user can't write its watermark table. Run ALTER USER cdc_user QUOTA UNLIMITED ON USERS.
• ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT: Oracle's remedy is a higher limit or less memory use, such as smaller mining passes.
• A table or column never appears: its name is over 30 characters, or supplemental logging is off for it.

Using Estuary for Oracle to ClickHouse

On this route, Estuary runs the Oracle CDC capture and the ClickHouse materialization as one pipeline. The capture mines redo with LogMiner sessions (no XStream or GoldenGate) for Oracle 11g and later, and the materialization writes ReplacingMergeTree tables over ClickHouse's native protocol. Against the concepts above:

• Backfill and redo stay in step: the capture copies existing rows while mining, using a watermarks table to fence each backfill chunk against the redo stream, so the copy and the changes neither gap nor double.
• Mining: back-to-back passes from the checkpointed SCN, each reading up to about 2 GiB of online and archived redo, with per-thread ranges for RAC; a restart resumes from the checkpointed SCN and open transactions. Dictionary Mode defaults to smart: the online catalog until a schema change breaks decoding, then extracted dictionaries until mining passes the DDL. The capture fails if the oldest archived log is under 24 hours old and warns under 7 days.
• Versions and deletes: tables use ReplacingMergeTree(flow_published_at), ordered by the Oracle key. By default a delete is one more row version whose _meta/op is d, so queries filter it out; Hard Delete writes a tombstone instead (_is_deleted = 1), which FINAL hides and a CLEANUP merge can remove once every part in its partition is a week old. A key update arrives as a delete plus an insert when the key columns are supplementally logged.
• Write schedule: the default sync is every 30 minutes; set Sync Frequency to 0s to write each transaction as soon as it completes.
• Types: decimal NUMBER and FLOAT land as Float64 (castToString keeps the exact text for toDecimal128 in a view); NUMBER(p,0) as Int64 or wider; NUMBER keys as String. TIMESTAMP WITH TIME ZONE becomes DateTime64(6, 'UTC'), values before 1925 clamped to 1925-01-01; DATE and other timestamps land as ISO 8601 String, parsed with parseDateTime64BestEffort. LOB, RAW, LONG and binary-float columns are skipped.
• Schema changes: new columns are added with ALTER TABLE … ADD COLUMN; in place, Int64 columns can widen to Int128, Int256 or Float64, and any column can become String.
• Audits: Estuary's security page shows SOC 2 Type II certification and HIPAA and GDPR compliance.

Implementing Oracle to ClickHouse with Estuary

These steps adapt Estuary's general data flow guide to this route.

1. Complete the prerequisites in Oracle requirements and ClickHouse requirements. Estuary also needs:
   • A FLOW_WATERMARKS table in the capture user's schema that the user can insert into and update (setup SQL; container databases differ). RDS container databases aren't supported, because the capture needs the root container.
   • ClickHouse 25.3 or later, and the materialization's grants, including SELECT on system.columns, system.parts and system.tables.
   • Allowlisted Estuary IP addresses on both ends. Oracle can use an SSH tunnel instead; the ClickHouse connector has no tunnel option.
2. Create the Oracle capture. On the Sources page, click New Capture, choose Oracle Database, name it, and fill in the endpoint properties:
   • Server Address: host:port; :1521 is assumed without a port. Use Network Tunnel for an SSH tunnel.
   • User and Password: the capture user (cdc_user above).
   • Database: the service name, or the PDB name on a CDB.
   • Advanced Options: leave Dictionary Mode at its default; set Watermarks Table if the table isn't <user>.flow_watermarks.

3. Select tables. Click Next. Output Collections lists one binding per table the user can see.
   • Remove tables you don't need; keyless ones arrive with Backfill Mode set to Without Primary Key.
   • Raise Backfill Priority for tables you want first, and keep Automatically keep schemas up to date on.

Click Save and publish. The backfill and redo mining start together.

4. Create the ClickHouse materialization. On the capture's details page, click Materialize, choose ClickHouse, and fill in the endpoint properties:
   • Address: host:port; 9440 (TLS) is assumed without a port.
   • Database and Authentication (Username and Password).
   • Hard Delete: on to hide deleted keys from FINAL; off keeps delete-marked rows.

5. Choose the sync schedule.Sync Frequency defaults to 30 minutes; shorter means fresher tables and more parts to merge. Timezone, Fast Sync Start Time and Fast Sync Stop Time speed up business hours; outside them, syncs run every 4 hours.

6. Map collections to tables and keep decimals exact. Click Next, choose a naming convention, and review each binding under Source Collections:
   • Table: the ClickHouse table name.
   • Partition By (advanced): only for large tables, and only on a value that never changes for a row. flow_published_at changes with every update, so on standard tables versions of a row would land in different partitions and never merge; keep it for Delta Update tables. Changing it later re-creates the table on backfill.
   • Delta Update: for append-only tables; the table becomes a plain MergeTree and keeps every change.
   • For money and rates, set castToString: true on the field under the binding's fields.include in the spec editor (custom column types).
7. Publish. Click Save and publish. Estuary creates the tables and writes the backfill without waiting for the schedule.
8. Verify that data is landing.
   • The capture details page shows backfill progress in Connector Status and PRIMARY in the Shard Information card; a collection from its Collections list shows Data Preview.
   • The materialization details page charts Data Read.
   • In ClickHouse, run the checks from Setting up Oracle to ClickHouse replication, with WHERE `_meta/op` != 'd' if Hard Delete is off.

How Estuary handles redo gaps, table rebuilds and extra destinations

Changes from Oracle land first in a collection: an append-only log of JSON documents kept in a cloud storage bucket. ClickHouse tables are written from the collection, not from Oracle, so a backfill that rebuilds a table doesn't touch the database.

What a rebuild costs Oracle

Collections on trial storage keep about 20 days; a bucket you own keeps everything. A redo gap still needs a source read.

CDC and batch from one Oracle source

Views, replicas without LogMiner and custom queries can't be mined. Estuary's Oracle batch connector polls them (every 5 minutes by default, by ORA_ROWSCN for tables) into collections the same ClickHouse materialization writes. Polling misses hard deletes.

When another approach may make more sense

• A one-time migration with no ongoing changes: export files. Data Pump writes a proprietary binary format only Data Pump Import reads, so spool CSV with SQL*Plus (SET MARKUP CSV ON, 12.2 and later) or SQLcl (SET SQLFORMAT csv) instead.
• Kafka and Debezium already run well: add the ClickHouse Kafka Connect sink. It writes only into tables you have already created and doesn't support deletes, so set Debezium's ExtractNewRecordState transform to delete.tombstone.handling.mode=rewrite, which marks deletes with __deleted set to true, and map that onto each table's is_deleted column yourself.
• Nightly reporting, no deletes needed: a scheduled batch tool is enough; Sling's delete_missing option can delete target rows whose keys disappeared, by comparing keys each run.
• Ingestion has to run through ClickPipes: ClickPipes can't read Oracle, but it can read the Oracle collections that Estuary's ClickHouse Kafka API connector (Dekaf) exposes as topics, with no Kafka cluster to run.
• GoldenGate already licensed: XStream Out can capture on a downstream database, off the primary, but the ClickHouse writer is yours.

Related resources

• Estuary: Oracle CDC capture, Oracle batch capture, ClickHouse materialization, ClickHouse via Dekaf, sync schedules, backfilling data, Oracle connector, ClickHouse destination
• Oracle: LogMiner, managing archived redo logs, licensing information
• ClickHouse: ReplacingMergeTree, Decimal, DateTime64, ClickPipes

To try the route, see the Oracle to ClickHouse integration or create an Oracle capture in the Estuary dashboard. Pricing: estuary.dev/pricing.