Overview

What is Change Data Capture (CDC)?

What is Snowflake Openflow?

What You'll Learn

What You'll Build

Prerequisites

Architecture

Journal Table and Merge Pattern

Supported Environments

Note: XStream requires SYSDBA-level access and control over redo log configuration. Environments that restrict DBA privileges or do not expose redo logs (multi-tenant RDS, Autonomous, SaaS) are not compatible.

Accept Oracle Connector Terms (ORGADMIN)

Steps

Important: If the Oracle connector does not appear in the Openflow connector catalog when you reach the connector deployment step, it is almost certainly because this step was skipped. The connector will remain hidden until the ORGADMIN accepts the terms.

Note: If you do not have access to the ORGADMIN role, contact your Snowflake organization administrator to complete this step.

Choose Your Licensing Model

Warning: The Embedded license 60-day trial automatically converts to a 36-month non-cancelable commitment if not cancelled before Day 61. Ensure you have internal approval before the trial expires.

Setup Oracle Config

Enable ARCHIVELOG Mode

sqlplus -L / as sysdba

SELECT log_mode FROM v$database;

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

SELECT log_mode FROM v$database;

Note: Enabling ARCHIVELOG mode requires a database restart. Plan accordingly for production systems.

Enable GoldenGate Replication

ALTER SYSTEM SET enable_goldengate_replication=TRUE SCOPE=BOTH;

Set Streams Pool Size

ALTER SYSTEM SET STREAMS_POOL_SIZE = 2560M;

Important: This is critical for production databases. The STREAMS_POOL_SIZE parameter reserves a dedicated memory region for XStream capture and apply processes. The 2560 MB value is a recommended starting point — adjust based on the number of schemas, transaction volume, and available SGA memory. Monitor usage with SELECT * FROM V$SGASTAT WHERE pool = 'streams pool';.

Enable Supplemental Logging

ALTER SESSION SET CONTAINER = CDB$ROOT;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

Production Recommendation: Database-wide supplemental logging captures all columns on every table, which increases redo log volume. For production systems, consider enabling supplemental logging only on the specific tables you plan to replicate:

ALTER TABLE schema_name.table_name ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

This reduces redo generation overhead on tables that are not part of the replication scope.

Create XStream Tablespaces

CREATE TABLESPACE xstream_adm_tbs
  DATAFILE '/opt/oracle/oradata/FREE/xstream_adm_tbs.dbf'
  SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

ALTER SESSION SET CONTAINER = FREEPDB1;

CREATE TABLESPACE xstream_adm_tbs
  DATAFILE '/opt/oracle/oradata/FREE/FREEPDB1/xstream_adm_tbs.dbf'
  SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

ALTER SESSION SET CONTAINER = CDB$ROOT;

Note: Replace FREEPDB1 and file paths with your actual PDB name and Oracle data directory.

Create XStream Administrator

CREATE USER c##xstreamadmin IDENTIFIED BY "XStreamAdmin123!"
  DEFAULT TABLESPACE xstream_adm_tbs
  QUOTA UNLIMITED ON xstream_adm_tbs
  CONTAINER=ALL;

GRANT CREATE SESSION, SET CONTAINER, EXECUTE ANY PROCEDURE, LOGMINING
  TO c##xstreamadmin CONTAINER=ALL;

GRANT SELECT ANY TABLE TO c##xstreamadmin CONTAINER=ALL;
GRANT FLASHBACK ANY TABLE TO c##xstreamadmin CONTAINER=ALL;
GRANT SELECT ANY TRANSACTION TO c##xstreamadmin CONTAINER=ALL;

GRANT XSTREAM_CAPTURE TO c##xstreamadmin CONTAINER=ALL;
GRANT XSTREAM_ADMIN TO c##xstreamadmin CONTAINER=ALL;

BEGIN
  DBMS_XSTREAM_AUTH.GRANT_ADMIN_PRIVILEGE(
    grantee                => 'c##xstreamadmin',
    privilege_type         => 'CAPTURE',
    grant_select_privileges => TRUE,
    container              => 'ALL'
  );
END;
/

Important: The majority of production Oracle deployments run 19c or 21c. If you use the 23ai-style GRANT XSTREAM_CAPTURE on these versions, the grant silently fails and the XStream outbound server will not start. Verify your Oracle version with SELECT * FROM v$version; before proceeding.

Create XStream Connect User

CREATE USER c##connectuser IDENTIFIED BY "ConnectUser123!"
  CONTAINER=ALL;

GRANT CREATE SESSION, SELECT_CATALOG_ROLE TO c##connectuser CONTAINER=ALL;
GRANT SELECT ANY TABLE TO c##connectuser CONTAINER=ALL;
GRANT LOCK ANY TABLE TO c##connectuser CONTAINER=ALL;
GRANT SELECT ANY DICTIONARY TO c##connectuser CONTAINER=ALL;

Create XStream Outbound Server

SET SERVEROUTPUT ON;

DECLARE
    tables  DBMS_UTILITY.UNCL_ARRAY;
    schemas DBMS_UTILITY.UNCL_ARRAY;
BEGIN
    tables(1)  := NULL;
    schemas(1) := 'HR';
    schemas(2) := 'CO';

    DBMS_XSTREAM_ADM.CREATE_OUTBOUND(
        server_name           => 'XOUT1',
        table_names           => tables,
        schema_names          => schemas,
        source_container_name => 'FREEPDB1',
        include_ddl           => TRUE
    );
END;
/

Note: Replace HR and CO with the schemas you want to replicate.

Assign Connect User to the Outbound Server

BEGIN
    DBMS_XSTREAM_ADM.ALTER_OUTBOUND(
        server_name  => 'XOUT1',
        connect_user => 'c##connectuser');
END;
/

Verify Oracle Configuration

SELECT server_name, status, connect_user, capture_user
FROM dba_xstream_outbound;

SELECT supplemental_log_data_min, supplemental_log_data_pk, supplemental_log_data_all
FROM v$database;

SELECT con_id, username, account_status
FROM cdb_users
WHERE username IN ('C##XSTREAMADMIN', 'C##CONNECTUSER')
ORDER BY con_id;

SELECT name, value FROM v$parameter
WHERE name = 'enable_goldengate_replication';

SELECT CAPTURE_NAME, STATE, TOTAL_MESSAGES_CAPTURED, TOTAL_MESSAGES_ENQUEUED
FROM V$XSTREAM_CAPTURE;

Connection Parameters

Tip: The XStream outbound server URL requires the Oracle SID, not the GLOBAL_DB_NAME. If your GLOBAL_DB_NAME includes a domain suffix (e.g., ORCL.WORLD), strip the domain and use only the SID portion (e.g., ORCL). The connection will fail if the domain suffix is included.

Set Up Openflow in Snowflake

Create the Openflow Admin Role

USE ROLE ACCOUNTADMIN;

CREATE ROLE IF NOT EXISTS OPENFLOW_ADMIN;
GRANT ROLE OPENFLOW_ADMIN TO USER <OPENFLOW_USER>;

GRANT CREATE OPENFLOW DATA PLANE INTEGRATION ON ACCOUNT TO ROLE OPENFLOW_ADMIN;
GRANT CREATE OPENFLOW RUNTIME INTEGRATION ON ACCOUNT TO ROLE OPENFLOW_ADMIN;
GRANT CREATE COMPUTE POOL ON ACCOUNT TO ROLE OPENFLOW_ADMIN;

Note: Users with a default role of ACCOUNTADMIN cannot log in to Openflow runtimes. Set a different default role for Openflow users.

Create an Openflow Deployment

Alternative — BYOC Deployment: Enterprise customers who need to run Openflow in their own cloud environment can use a Bring Your Own Cloud (BYOC) deployment instead of SPCS. BYOC requires Openflow version 0.55.0 or later. This guide covers the SPCS deployment model; for BYOC setup, refer to the Openflow documentation.

Important — Runtime Sizing: The Oracle connector requires a Medium runtime at minimum. Small runtimes do not have sufficient resources and will fail. Multi-node runtimes are also not supported — set both minimum and maximum nodes to 1. Do not enable auto-scaling.

Create a Snowflake Role for the Connector

CREATE ROLE IF NOT EXISTS openflow_oracle_role;

GRANT USAGE ON DATABASE <destination_database> TO ROLE openflow_oracle_role;
GRANT CREATE SCHEMA ON DATABASE <destination_database> TO ROLE openflow_oracle_role;
GRANT USAGE ON WAREHOUSE <warehouse_name> TO ROLE openflow_oracle_role;

Note: The connector automatically creates target schemas and tables at runtime. Database-level CREATE SCHEMA is required — schema-level grants alone will cause permission errors because the schemas do not exist yet when the connector starts.

Configure Network Access

CREATE OR REPLACE NETWORK RULE oracle_network_rule
  MODE = EGRESS
  TYPE = HOST_PORT
  VALUE_LIST = ('<oracle_host>:1521');

Create External Access Integration

CREATE OR REPLACE EXTERNAL ACCESS INTEGRATION oracle_eai
  ALLOWED_NETWORK_RULES = (oracle_network_rule)
  ENABLED = TRUE;

GRANT USAGE ON INTEGRATION oracle_eai TO ROLE OPENFLOW_ADMIN;

Note: Attach this EAI to your Openflow runtime when creating or updating the runtime configuration. Without it, the connector will fail to connect to the Oracle host at runtime. See Create runtime for details on configuring external access integrations for an Openflow runtime.

Configure the Oracle Connector

Open the Openflow UI

Deploy the Connector

Configure the Connection

Important: The Oracle Connection URL uses the thin JDBC driver (jdbc:oracle:thin:@) and is used for snapshot operations. The XStream Out Server URL uses the OCI JDBC driver (jdbc:oracle:oci:@) and is used for the CDC stream. Both are required — omitting the XStream Out Server URL will cause errors once the snapshot phase completes and incremental replication begins.

Configure the Replication Scope

Tip: Snowflake target tables in the connector ingestion parameters must be specified as three-part fully qualified names: DATABASE.SCHEMA.TABLE. Two-part names (e.g., SCHEMA.TABLE) will cause errors.

Configure the Snowflake Target

Verify Configuration Before Starting

Tip: Fixing issues at this stage is far easier than debugging a failed flow at runtime. Verify Config validates connectivity and credentials without starting data movement.

Start the Connector

Verify and Monitor

Verify Data in Snowflake

SHOW TABLES IN SCHEMA <target_db>.<target_schema>;

SELECT * FROM <target_db>.<target_schema>.<table_name> LIMIT 10;

SELECT COUNT(*) FROM <target_db>.<target_schema>.<table_name>;

Test CDC Replication

-- On Oracle
INSERT INTO hr.employees (employee_id, first_name, last_name, email)
VALUES (9999, 'Test', 'User', '[email protected]');
COMMIT;

-- On Snowflake (should appear within seconds)
SELECT * FROM <target_db>.<target_schema>.employees
WHERE employee_id = 9999;

Note: Deletes in Oracle are replicated as soft deletes in Snowflake. The row remains in the target table with the metadata column _SNOWFLAKE_DELETED set to TRUE rather than being physically removed.

Monitor the Pipeline

SELECT CAPTURE_NAME, STATE, TOTAL_MESSAGES_CAPTURED, TOTAL_MESSAGES_ENQUEUED
FROM V$XSTREAM_CAPTURE;

SELECT server_name, status, connect_user, capture_user
FROM dba_xstream_outbound;

Troubleshooting

XStream Outbound Server Not Running

SELECT server_name, status FROM dba_xstream_outbound;

BEGIN
    DBMS_XSTREAM_ADM.ALTER_OUTBOUND(
        server_name => 'XOUT1',
        status      => 'ENABLED'
    );
END;
/

Capture Process Not Starting

SELECT capture_name, status FROM dba_capture;

BEGIN
    DBMS_CAPTURE_ADM.START_CAPTURE('XSTREAM_CAPTURE');
END;
/

Supplemental Logging Issues

SELECT supplemental_log_data_min, supplemental_log_data_pk, supplemental_log_data_all
FROM v$database;

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

Network Connectivity

Cleanup

Caution: The commands below permanently remove the XStream configuration, users, and supplemental logging. Before proceeding:

• Stop the Openflow connector in the Openflow UI first. Dropping the outbound server while the connector is running will cause unrecoverable errors and may require a full re-snapshot.
• Verify licensing implications. If you are using the Embedded licensing model, dropping and re-creating the XStream outbound server may require re-provisioning the Oracle license at additional cost.
• Confirm no other applications depend on the XStream outbound server, supplemental logging, or GoldenGate replication settings before disabling them.

BEGIN
    DBMS_XSTREAM_ADM.DROP_OUTBOUND('XOUT1');
END;
/

DROP USER c##xstreamadmin CASCADE;
DROP USER c##connectuser CASCADE;

ALTER DATABASE DROP SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

ALTER SYSTEM SET enable_goldengate_replication=FALSE SCOPE=BOTH;

Tips for Production

Check I/O Headroom

Resize Redo Logs

-- Check current redo log sizes
SELECT group#, bytes/1024/1024 AS size_mb, status FROM v$log;

Monitor V$XSTREAM_CAPTURE

SELECT CAPTURE_NAME, STATE, TOTAL_MESSAGES_CAPTURED,
       TOTAL_MESSAGES_ENQUEUED, CAPTURE_MESSAGE_CREATE_TIME,
       ENQUEUE_MESSAGE_CREATE_TIME
FROM V$XSTREAM_CAPTURE;

Consider Downstream Capture for Extreme Scale

Conclusion And Resources

What You Learned

Related Resources