Oracle to ClickHouse: How to Replicate Your Data

Moving data from Oracle to ClickHouse is a familiar modernisation step: the system of record stays on Oracle, while the analytical workload moves somewhere built for fast scans over large tables. Before you plan the migration, there is one commercial fact to establish, because it determines whether this pipeline is available to you at all.

Airbyte's Oracle source is an Enterprise connector. It is a premium connector available to Enterprise customers at additional cost, and it is not offered on the Core, Standard, or Plus plans. If you are evaluating this on open source, that is the first thing to know.

Oracle to ClickHouse at a glance:

Why move Oracle data into ClickHouse?

Oracle is where a lot of transactional truth lives, and it is rarely where anyone wants to run a dashboard. Analytical queries compete with the workload the database exists to serve, licensing makes scaling for analytics expensive, and row-oriented storage is the wrong shape for aggregating hundreds of millions of rows.

ClickHouse answers all three. Columnar storage makes wide aggregations cheap, the analytical load is entirely off the Oracle instance, and you are no longer paying Oracle economics for reporting capacity. The pipeline in the middle is what this guide covers.

What does the Oracle source require?

The Oracle source supports full refresh and incremental syncs, replicates incremental deletes, and operates strictly read-only from the database's point of view. It needs Oracle 23ai, 21c, or 19c, and a dedicated read-only user with access to the tables you want.

For plain incremental syncs, that plus network access is the whole setup. CDC is where the work is.

How do you enable CDC on Oracle?

CDC relies on Debezium, which in turn relies on Oracle LogMiner. LogMiner is typically not enabled by default, and the steps differ depending on whether you are on Amazon RDS.

On RDS, confirm that the database is in ARCHIVELOG mode, set an archive log retention period long enough to cover your sync interval, and enable supplemental logging at the database level. If you sync daily, a retention window spanning several days is sensible, because CDC availability is bounded by how long the logs survive.

Elsewhere the work is heavier: provision redo log files, restart the instance, then enable archivelog and supplemental logging. Either way, the last step is the same, enabling supplemental logging on each table you intend to replicate with ALTER TABLE ... ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

The CDC user needs a specific and fairly broad set of privileges, including FLASHBACK ANY TABLE, SELECT ANY TABLE, SELECT_CATALOG_ROLE, EXECUTE_CATALOG_ROLE, SELECT ANY TRANSACTION, LOGMINING, CREATE TABLE, LOCK ANY TABLE, and CREATE SEQUENCE. On a multitenant CDB instance these need the CONTAINER=ALL clause plus SET CONTAINER. Budget time for a conversation with whoever owns the database, because this is more access than a typical reporting user gets.

What LogMiner limitations should you check first?

One of these will decide whether CDC is viable for your schema, and it is worth checking before anything else.

• Names over 30 characters are silently ignored. LogMiner skips tables with names longer than 30 characters, and columns with names longer than 30 characters, without raising an error. Older Oracle schemas with descriptive naming conventions run into this constantly. Query your data dictionary for long names before you build anything.
• Not every data type is CDC-capable. LogMiner does not support all types. BLOB, CLOB, NCLOB, RAW, LONG, ROWID, BFILE, and JSON columns are replicable in full refresh but not through CDC, as are user-defined types such as VARRAY. Numeric, character, date, and timestamp types are fine.
• Oracle DATE is a timestamp. The connector maps Oracle DATE to a timestamp rather than a date, which surprises people writing downstream queries that assume day granularity.
• Idle databases abort the sync. A heartbeat mechanism ends the sync if no change events appear within a configurable window, controlled by Initial Waiting Time in Seconds. It defaults to 1200 seconds with a valid range of 120 to 3600. Raise it if your database has long quiet periods and you would rather wait than fail.

How do you set up the ClickHouse destination?

Create a dedicated user rather than reusing an existing one. The ClickHouse destination needs grants to create and manage databases, to create, alter, drop and truncate tables, and to insert and select data. Server 21.8.10.19 or later is required.

If async_insert is enabled on your instance, disable it for the Airbyte user with ALTER USER airbyte_user SETTINGS async_insert = 0; Leave it on and the connection check fails with an error about rows not reaching the check table, which gives no hint that an insert-mode setting is the cause.

Enter the hostname without a protocol prefix, the port (8123 for HTTP or 8443 for HTTPS), the database, and credentials. On the Oracle side, choose an encryption scheme: unencrypted TCP, Native Network Encryption, or TLS with certificate verification. Airbyte Cloud only supports secured connections, so use SSL, an encryption scheme, or an SSH tunnel through a bastion.

Why do you need FINAL in your queries?

Deduplication in ClickHouse uses the ReplacingMergeTree engine, which collapses duplicate rows during background merges rather than at write time. Merges run when ClickHouse decides, not when your sync finishes. Until one completes, a plain SELECT can return duplicates or rows that CDC already deleted in Oracle.

Add the FINAL operator to guarantee deduplicated results: SELECT * FROM your_table FINAL Skip it and your ClickHouse numbers will disagree with Oracle in a way that looks exactly like a replication bug.

On types, Oracle numerics land as Decimal(38, 9), timestamps as DateTime64(3), and arrays, including VARRAYs, always become String regardless of the JSON setting. Parse those with ClickHouse JSON functions when you need structured access.

Frequently asked questions

Is the Oracle source available on Airbyte open source?

No. It is a premium Enterprise connector available at additional cost, offered on Pro and Enterprise Flex rather than Core, Standard, or Plus. Talk to Airbyte sales to confirm current availability for your plan.

Why are some of my Oracle tables missing from the sync?

If you are using CDC, check the name lengths first. LogMiner ignores tables and columns whose names exceed 30 characters, and does so silently. Also confirm supplemental logging is enabled on each table you selected.

Can I replicate BLOB and CLOB columns?

In full refresh, yes, arriving as base-64 encoded strings for binary types. Through CDC, no. LogMiner does not support those types, so large object columns need a full refresh strategy if you need them at all.

Why does my sync abort when nothing has changed?

The CDC heartbeat is ending the sync because no change events arrived inside the waiting window. Raise Initial Waiting Time in Seconds from its 1200-second default, up to a maximum of 3600, if your database has long idle stretches.

How long does CDC data stay available?

As long as your archive log retention allows. Set the retention period to comfortably exceed your sync interval, because once the logs are gone the changes they held are gone with them.

Get your Oracle data into ClickHouse

Audit your schema for long names, confirm the plan covers the Enterprise connector, disable async_insert, and remember FINAL. Replicating a different transactional database into the same warehouse? See our guides to PostgreSQL to ClickHouse and Oracle CDC.

Start syncing now →