Oracle to Snowflake: CDC, Replication & Migration Guide

Oracle tables reach Snowflake in one of three ways: spool them to files and COPY INTO once, poll them with timestamp or ORA_ROWSCN queries, or mine the redo log for committed changes (log-based CDC). An export suits a one-off migration with a write freeze; for a live database, CDC fits better because it also sees hard deletes, which queries never do.

Oracle exposes redo through LogMiner, which is part of Oracle Database, and XStream, which Oracle licenses through GoldenGate. Either way the source needs ARCHIVELOG mode, supplemental logging and archived logs kept longer than your worst outage. In Snowflake, warehouse time per load and the mapping of decimal NUMBER columns decide cost and correctness. Estuary is one managed option here: its Oracle capture reads redo through LogMiner sessions into a durable collection, which its Snowflake materialization loads on a schedule you set.

Choosing an Oracle to Snowflake method

Polling never sees hard deletes, and ORA_ROWSCN is per block unless a table uses ROWDEPENDENCIES; the rest of this guide assumes redo-based CDC.

How Oracle to Snowflake CDC works

Oracle records every change in its redo log, ordered by system change number (SCN), and CDC reads that log instead of the tables.

• Archived logs. Online redo logs are reused in a cycle; ARCHIVELOG mode copies each filled log first, so a pipeline that falls behind can still read it.
• Supplemental logging adds the column values needed to identify rows. Oracle enables none by default, which leaves LogMiner unusable.
• LogMiner (DBMS_LOGMNR): the tool registers redo files for an SCN range and queries V$LOGMNR_CONTENTS, which returns each change as SQL.
• The LogMiner dictionary maps object IDs to names. The online catalog knows only each table's current version; a dictionary extracted into the redo handles DDL, at a resource cost.
• XStream Out: an in-database capture process mines redo and an outbound server streams changes in commit order.

The pipeline records a starting SCN, copies existing rows, then mines redo from that SCN, reconciling the overlap so a row updated mid-copy ends in its latest state. It holds each transaction until commit and checkpoints its SCN. Snowflake is loaded by staging files and running MERGE by primary key, or by appending through Snowpipe Streaming.

LogMiner vs XStream, and when a GoldenGate license applies

Oracle's licensing manual lists LogMiner as "available for use with all Oracle Database offerings", Standard Edition 2 included, and states that XStream "is licensed via the Oracle GoldenGate product". Snowflake's Openflow documentation quotes Oracle: "a license to Oracle Database Enterprise Edition is a prerequisite to license and use Oracle XStream." The same manual says LogMiner used with GoldenGate Supplemental Logging, which ENABLE_GOLDENGATE_REPLICATION = TRUE turns on, needs a GoldenGate license; Oracle's reference sets that parameter's default to FALSE. XStream can also capture on a downstream database. Check your Oracle agreement.

Oracle requirements

Every log-based method needs four things on the source. This SQL is for a self-managed non-CDB on 12c or later:

sql
-- 1. ARCHIVELOG mode (as SYSDBA; back up before and after)
SELECT LOG_MODE FROM V$DATABASE;
SHUTDOWN IMMEDIATE
STARTUP MOUNT
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

-- 2. Supplemental logging: minimal, plus all columns (database-wide or per table)
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
-- or, to limit redo growth: ALTER TABLE sales.orders ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

-- 3. A read-only capture user
CREATE USER cdc_user IDENTIFIED BY "<password>";
GRANT CREATE SESSION, LOGMINING TO cdc_user;
GRANT SELECT ON sales.orders TO cdc_user;   -- or SELECT ANY TABLE
GRANT SELECT_CATALOG_ROLE, EXECUTE_CATALOG_ROLE TO cdc_user;
GRANT SELECT ON V_$DATABASE TO cdc_user;   -- V$DATABASE is a public synonym for V_$DATABASE
GRANT SELECT ON V_$LOG TO cdc_user;

The fourth is archived-log retention longer than your worst outage plus repair time, with the archive destination sized for it at peak redo rate.

sql
-- Amazon RDS for Oracle
BEGIN
  rdsadmin.rdsadmin_util.alter_supplemental_logging(p_action => 'ADD');
  rdsadmin.rdsadmin_util.alter_supplemental_logging(p_action => 'ADD', p_type => 'ALL');
  rdsadmin.rdsadmin_util.set_configuration(name => 'archivelog retention hours', value => '168');
END;
/
COMMIT;   -- required for the retention change; retained logs use instance storage

Three rules prevent most incidents:

1. Enable supplemental logging before the pipeline's starting point. LogMiner needs it in the redo it mines.
2. Keep table and column names to 30 characters. LogMiner doesn't support longer ones; GoldenGate and XStream do.
3. Add a primary key to keyless tables. Without one, a Snowflake MERGE has nothing to match each Oracle change against.

Snowflake requirements

In Snowflake, the Oracle data needs a target database and schema, a virtual warehouse for loads, and a dedicated user and role granted access to all three, authenticated with a key pair as Snowflake phases out single-factor passwords for service users.

• Warehouse:AUTO_SUSPEND = 60 keeps the gaps between Oracle loads cheap, given per-second billing with a 60-second minimum per resume.
• Timestamp type:TIMESTAMP_TZ keeps a TIMESTAMP WITH TIME ZONE offset, TIMESTAMP_LTZ stores the instant, and TIMESTAMP_NTZ suits zone-less DATE and TIMESTAMP; an account-level TIMESTAMP_TYPE_MAPPING must match.
• Identifiers: both systems upper-case unquoted names; keep QUOTED_IDENTIFIERS_IGNORE_CASE off so quoted mixed-case Oracle columns resolve exactly.

Setting up Oracle to Snowflake replication

1. Enable ARCHIVELOG mode and supplemental logging, and confirm both in V$DATABASE.
2. Set archived-log retention above your worst outage.
3. Create the capture user with the LogMiner grants (a C## common user on a CDB, grant_sys_object on RDS) and network access: allowlisted IP, SSH bastion or private networking.
4. Prepare Snowflake as described above.
5. Decide per table between current state (MERGE) and an append-only history, and how to treat keyless tables and CLOB/BLOB columns.
6. Load existing data, keeping archived logs from the snapshot's starting SCN until the stream catches up.
7. Start mining redo from the snapshot's SCN, and choose a Snowflake load interval to match report freshness.
8. Validate. Compare row counts and SUM() of high-precision NUMBER columns. Update and delete a row, wait one load interval, and check Snowflake. Check a DATE with a time. Add a column. Stop the pipeline across a log switch and confirm a clean resume.

What happens after the initial load?

• Updates carry every column with all-column supplemental logging; with only primary-key logging, just the key and changed columns.
• Primary-key updates must become a delete plus an insert in Snowflake, or the old row lingers.
• Deletes arrive as redo DELETE operations; most tools flag the Snowflake row rather than remove it unless hard deletes are on.
• Rolled-back transactions never reach Snowflake; nothing appears before commit.
• DDL depends on the dictionary: with the online catalog, redo written before an ALTER TABLE comes back as undecodable hex.
• In Snowflake, most tools add new columns (earlier rows stay NULL) and keep dropped ones; narrowing a type usually forces a rebuild.

Oracle to Snowflake data type mapping

NUMBER precision

Oracle can hold 38 significant digits in a NUMBER, but a Snowflake FLOAT keeps about 15, so any pipeline that types decimal columns as floating point rounds them. Scaled and unconstrained NUMBER columns suffer most: money, high-scale rates, long identifiers. Map each column to NUMBER(p,s) within Snowflake's 38-digit, scale-37 limits, or VARCHAR, then reload the table; rounded digits can't be recovered in Snowflake.

Performance, latency and Snowflake cost

LogMiner can be polled continuously, but rows appear in Snowflake only when a load runs, so the load interval usually dominates latency. CDC releases a transaction only at commit, so a long batch job's changes arrive together.

Reducing MERGE cost

• Load less often. Each load wakes the warehouse and bills at least 60 seconds, however few changes it carries.
• Coalesce by key between loads, so a row updated 500 times is merged once.
• Lead with a write-ordered key. Snowflake prunes micro-partitions by per-column min/max, so a sequence-generated key touches few partitions and a random GUID scans most of the table.
• Append only where it fits. Snowpipe Streaming inserts without a MERGE, but updates arrive as new rows and deletes as marker rows, so current state needs a view on top.

Production considerations

• Retries. A restarted pipeline re-mines from its last checkpointed SCN, so merged tables absorb repeated changes and append-only tables duplicate them unless the SCN checkpoint and the Snowflake write commit together.
• Source impact. Mining uses CPU and PGA, extracted dictionaries add redo, and backfills add table reads.
• Monitoring. Track SCN lag (V$DATABASE.CURRENT_SCN against the pipeline's checkpoint), free space in the archive destination, and time since the last Snowflake load.

Common problems and troubleshooting

Archived logs purged before the pipeline read them

If retention deletes a log the pipeline still needs, LogMiner can't start on that SCN range (ORA-01291: missing logfile). LogMiner lists a sequence missing mid-range in V$LOGMNR_LOGS (MISSING_LOGFILE), and returns partial results on RAC if a thread's logs aren't added; check for gaps yourself. Check what is still on disk:

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

Recovery, cheapest first:

1. Restore the missing logs from backup (RMAN, or on RDS rdsadmin.rdsadmin_archive_log_download within backup retention) and resume.
2. Re-read only rows changed since the gap, by update timestamp or key range, and merge them. Rows deleted in that window stay in Snowflake.
3. With no reliable cutoff, re-snapshot the affected tables and merge into Snowflake rather than truncating.

Then raise retention above the outage you just had.

Redo log SCN gap after a restart or failover

A capture that checks SCN continuity can stop after a restart even when no log is missing. Compare sequences and incarnations:

sql
SELECT THREAD#, SEQUENCE#, FIRST_CHANGE#, NEXT_CHANGE#, RESETLOGS_ID
FROM V$ARCHIVED_LOG ORDER BY THREAD#, SEQUENCE#;
SELECT INCARNATION#, RESETLOGS_ID, STATUS FROM V$DATABASE_INCARNATION;

• Consecutive SEQUENCE#, same RESETLOGS_ID: nothing is missing; the SCN jump marks the restart.
• A missing sequence: a log was deleted; treat it as a purge.
• A new incarnation (opened with RESETLOGS): redo after the reset point was discarded, so Snowflake may hold changes Oracle no longer has. Re-read the tables.

LogMiner CPU and PGA pressure on the source

LogMiner runs inside the source database. Each dictionary build consumes database resources, so a pipeline that extracts one every mining cycle pays continuously; use the online catalog between schema changes. A mining query can also fail with ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT; Oracle's remedy is a higher limit or less memory use.

Using Estuary for Oracle to Snowflake

On this route, Estuary's Oracle CDC capture mines redo with LogMiner only, never XStream, and writes each table to a collection (below) that the Snowflake materialization reads. Each Snowflake load is staged in an internal stage, then applied with COPY INTO if every key is new or MERGE if any exists:

• Startup checks: the capture fails without supplemental logging and warns when a table has less than all-column logging. Under 24 hours of archived logs fails; under 7 days warns.
• Mining passes: each runs from the checkpointed SCN toward the current one, capped at 2 GiB of redo files.
• CDB: a C## common user, with the PDB as the database; the capture switches to CDB$ROOT to mine. RDS CDBs aren't supported.
• Load interval: once caught up, Snowflake syncs every 30 minutes by default; backfills don't wait, and repeated changes to a key are combined between syncs.
• Merge vs append: merge by default; delta updates per binding, through Snowpipe Streaming with key-pair auth; the sync schedule still applies to them.
• Deletes: a DELETE is soft (_meta/op = 'd') unless Hard Delete is on; a key-changing update arrives as a delete plus an insert, and TRUNCATE emits nothing, so truncated rows stay in Snowflake.
• Types:NUMBER(p,s), unconstrained NUMBER and FLOAT arrive as exact decimal strings but land as FLOAT. castToString: true on the field keeps the exact value in a TEXT column; a DDL override alone still converts through FLOAT. NUMBER(p,0) lands as INTEGER, but as TEXT in primary keys. DATE, TIMESTAMP and TIMESTAMP WITH LOCAL TIME ZONE land as TEXT without an offset; cast them in a view. TIMESTAMP WITH TIME ZONE is normalized to UTC. LOB, LONG, XMLTYPE, BINARY_FLOAT and BINARY_DOUBLE columns are left out of the discovered schema. Names over 30 characters aren't supported; a long table name fails the startup check.

Implementing Oracle to Snowflake with Estuary

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

1. Complete the prerequisites in Oracle requirements and Snowflake requirements. Estuary also needs:
   • A FLOW_WATERMARKS table in the capture user's schema (in the PDB, on a CDB) with INSERT and UPDATE, and quota on USERS (non-CDB and CDB SQL).
   • The Snowflake objects from the connector's setup script, with a public key registered for key-pair authentication. Password auth is deprecated.
2. Create the Oracle capture. On the Sources page, click New Capture, choose Oracle Database (not Oracle Database (Batch)), name the capture, and fill in the endpoint properties:
   • Server Address: host:port (port 1521 if omitted). Allowlist Estuary's IP addresses, or use Network Tunnel for an SSH tunnel. Private and BYOC deployments can use private networking.
   • User and Password: CDC_USER from the SQL above, or C##CDC_USER on a CDB.
   • Database: ORCL by default; on a CDB, the PDB name.
   • Advanced Options: leave Dictionary Mode empty so the capture uses smart.

3. Select tables. Click Next. Estuary discovers the tables the user can read, one binding per table, under Output Collections.
   • Remove tables you don't want. Each binding writes its own collection.
   • Keyless tables get Backfill Mode "Without Primary Key" automatically. Raise Backfill Priority to load key tables first.
   • Keep Automatically keep schemas up to date on.

Click Save and publish. The backfill starts, with redo mining alongside.

4. Create the Snowflake materialization. On the capture's details page, click Materialize, choose Snowflake, and fill in the endpoint properties:
   • Host (Account URL): orgname-accountname.snowflakecomputing.com, no protocol.
   • Database, Schema, Warehouse and Role from the setup script.
   • Snowflake Timestamp Type: used only for TIMESTAMP WITH TIME ZONE columns.
   • Authentication: Private Key (JWT), with User and Private Key.
   • Hard Delete: on to delete rows; off keeps soft deletes.

5. Choose the sync schedule. Under Sync Schedule, Sync Frequency defaults to 30 minutes. Shorter intervals give fresher tables and more warehouse resumes. For business-hours freshness, set Timezone, Fast Sync Start Time, Fast Sync Stop Time and optionally Fast Sync Enabled Days; outside that window, syncs run every 4 hours. See sync schedules.

6. Map collections to tables and keep decimals exact. Click Next. For the naming convention, Match source structure keeps Oracle schema and table names. Bindings under Source Collections can set Alternative Schema and Delta Updates. Before publishing, add castToString: true under each binding's fields.include in the specification editor for decimal NUMBER columns, such as AMOUNT: { castToString: true } (custom column types).
7. Publish. Click Save and publish. Estuary creates the Snowflake tables and loads the backfill without waiting for the schedule.
8. Verify that data is landing.
   • On the capture details page, the Shard Information card reads PRIMARY and Connector Status shows backfill progress; open a collection from the Collections list for its Data Preview.
   • The materialization details page shows Connector Status and charts Data Read.
   • In Snowflake, query a target table, then run the checks from Setting up Oracle to Snowflake replication.

How Estuary handles redo gaps, dictionaries and re-backfills

Every change mined from redo, and every backfilled row, lands in a collection: JSON files in your own cloud storage bucket that act as a durable log. Snowflake reads the collection, not Oracle, and the materialization commits its read position with each load, so a crash neither repeats nor skips a batch.

Redo gaps stop the capture instead of dropping changes

Before each mining pass, the capture checks that its redo files cover the whole SCN range, per redo thread. A purged log fails the capture with a redo log SCN gap error instead of yielding a partial result; a gap between consecutive sequence numbers in one incarnation, the signature of a restart, is tolerated. RAC writes one redo thread per instance, and the check covers each thread; test restarts and failover against your cluster.

Dictionary builds only after DDL

In smart mode, the default, the capture mines with the online catalog. After a dictionary mismatch from an ALTER TABLE, it uses an extracted dictionary until mining passes the most recent DDL, then switches back. extract mode builds a dictionary every pass; online can't follow schema changes. Auto-discovery picks up new tables and columns every 15 minutes by default.

What a re-backfill does and doesn't avoid

Limits: replay needs collection retention (trial storage keeps about 20 days); backfills emit no deletes, so rows deleted during a gap stay in Snowflake; delta-update tables get duplicates.

Tables without a primary key

Keyless tables backfill by ROWID range, without a keyed backfill's exact correctness guarantees. Their collection key is LogMiner's record-set ID and SQL sequence number, which identify a change rather than a row, so the Snowflake table accumulates one row per change. Add a primary key, then re-backfill, to get current-state rows.

When another approach may make more sense

• Snowflake-only, XStream licensing accepted: the Openflow Connector for Oracle needs a paid XStream license, embedded through Snowflake on a 36- or 12-month commitment, or your own entitlement. It runs against Oracle 11g+, including single-tenant but not multi-tenant RDS; needs a key per table; soft-deletes with _SNOWFLAKE_DELETED; and ignores TRUNCATE.
• GoldenGate already licensed: its Snowflake Stage and Merge handler applies full CDC through micro-batched MERGE.
• Kafka and Debezium already run well: Debezium mines with LogMiner by default and emits LOB values only when enabled; the Snowflake Kafka connector inserts rows, so you build the MERGE layer.
• One-time migration with a cutover window: spool with SQL*Plus SET MARKUP CSV ON QUOTE ON, set NLS_DATE_FORMAT to include the time, and keep COPY INTO's default ON_ERROR = ABORT_STATEMENT, since SKIP_FILE drops whole files.
• No LogMiner access (RDS CDBs, read replicas) or daily freshness: query-based capture, accepting missed hard deletes. Estuary's Oracle Batch connector covers this, polling on ORA_ROWSCN every 5 minutes by default, and reads CLOB and RAW as strings.

Related resources

• Estuary Oracle CDC connector, Oracle Batch, Snowflake materialization, sync schedules, backfilling data, schema evolution
• Oracle: LogMiner, XStream, licensing information, archived redo logs
• AWS: RDS for Oracle logging tasks, retaining archived redo logs
• Snowflake: Openflow Connector for Oracle, numeric types, date and time types

To try the route, follow the data flow guide or create an Oracle capture in the Estuary dashboard. Pricing: estuary.dev/pricing.