Delta Migration from Oracle

A uses an initial data load, followed by , to . In this approach, you migrate most application data to the target using before stopping application traffic to the source database. You then use to keep the target database in sync with any changes in the source database (the migration delta), before finally halting traffic to the source and cutting over to the target after schema finalization and data verification.

• All source data is migrated to the target .

• This approach utilizes .

• is supported, though this example will not use it. See for an example of a migration that uses failback replication.

This approach is best for production environments that need to .

This page describes an example scenario. While the commands provided can be copy-and-pasted, they may need to be altered or reconsidered to suit the needs of your specific environment.

Example scenario

You have a small (300 GB) database that provides the data store for a web application. You want to migrate the entirety of this database to a new CockroachDB cluster. Business cannot accommodate a full maintenance window, but it can accommodate a brief (<60 second) halt in traffic.

The application runs on a Kubernetes cluster.

Estimated system downtime: 3-5 minutes.

Before the migration

• Install the tools.
• Review the and documentation.
• and .
• Recommended: Perform a dry run of this full set of instructions in a development environment that closely resembles your production environment. This can help you get a realistic sense of the time and complexity it requires.
• Understand the prerequisites and limitations of the MOLT tools:

Prerequisites

Oracle Instant Client

Install Oracle Instant Client on the machine that will run molt and replicator. If using the MOLT Replicator binary (instead of Docker), the Oracle Instant Client libraries must be accessible at /usr/lib.

• On macOS ARM machines, download the Oracle Instant Client. After installation, you should have a new directory at /Users/$USER/Downloads/instantclient_23_3 containing .dylib files. Set the LD_LIBRARY_PATH environment variable to this directory:

  export LD_LIBRARY_PATH=/Users/$USER/Downloads/instantclient_23_3
  

• On Linux machines, install the Oracle Instant Client dependencies and set the LD_LIBRARY_PATH to the client library path:

  sudo apt-get install -yqq --no-install-recommends libaio1t64
  sudo ln -s /usr/lib/x86_64-linux-gnu/libaio.so.1t64 /usr/lib/x86_64-linux-gnu/libaio.so.1
  unzip -d /tmp /tmp/instantclient-basiclite-linux-amd64.zip
  sudo mv /tmp/instantclient_21_13/* /usr/lib
  export LD_LIBRARY_PATH=/usr/lib
  
  You can also download Oracle Instant Client directly from the Oracle site for Linux ARM64 or Linux x86-64.

Limitations

MOLT Fetch limitations

• Only tables with types of , , or can be sharded with .

• GEOMETRY and GEOGRAPHY types are not supported.

• Migrations must be performed from a single Oracle schema. You must include so that MOLT Fetch only loads data from the specified schema. Refer to .

  • Specifying is also strongly recommended to ensure that only necessary tables are migrated from the Oracle schema.

• Oracle advises against LONG RAW columns and recommends converting them to BLOB. LONG RAW can only store binary values up to 2GB, and only one LONG RAW column per table is supported.

MOLT Replicator limitations

• Replication modes require connection to the primary instance (PostgreSQL primary, MySQL primary/master, or Oracle primary). MOLT cannot obtain replication checkpoints or transaction metadata from replicas.

• Running DDL on the source or target while replication is in progress can cause replication failures.

• TRUNCATE operations on the source are not captured. Only INSERT, UPDATE, UPSERT, and DELETE events are replicated.

• Changes to virtual columns are not replicated automatically. To migrate these columns, you must define them explicitly with .

• Replication will not work for tables or column names exceeding 30 characters. This is a limitation of Oracle LogMiner.

• The following data types are not supported for replication:

  • User-defined types (UDTs)
  • Nested tables
  • VARRAY
  • LONGBLOB/CLOB columns (over 4000 characters)

• If your Oracle workload executes UPDATE statements that modify only LOB columns, these UPDATE statements are not supported by Oracle LogMiner and will not be replicated.

• If you are using Oracle 11 and execute UPDATE statements on XMLTYPE or LOB columns, those changes are not supported by Oracle LogMiner and will be excluded from ongoing replication.

• If you are migrating LOB columns from Oracle 12c, use AWS DMS Binary Reader instead of LogMiner. Oracle LogMiner does not support LOB replication in 12c.

MOLT Verify limitations

• MOLT Verify compares 20,000 rows at a time by default, and row values can change between batches, potentially resulting in temporary inconsistencies in data. To configure the row batch size, use the --row_batch_size .
• MOLT Verify checks for collation mismatches on columns. This may cause validation to fail when a is used as a primary key and the source and target databases are using different .
• MOLT Verify might give an error in case of schema changes on either the source or target database.
• cannot yet be compared.

Step 1: Prepare the source database

In this step, you will:

• Create a dedicated migration user on your source database.
• Configure the source database for replication.

Create migration user on source database

Create a dedicated migration user (for example, MIGRATION_USER) on the source database. This user is responsible for reading data from source tables during the migration. You will pass this username in the source connection string.

CREATE USER MIGRATION_USER IDENTIFIED BY 'password';

When migrating from Oracle Multitenant (PDB/CDB), this should be a common user. Prefix the username with C## (e.g., C##MIGRATION_USER).

Grant the user privileges to connect, read metadata, and SELECT and FLASHBACK the tables you plan to migrate. The tables should all reside in a single schema (for example, migration_schema). For details, refer to Schema and table filtering.

Oracle Multitenant (PDB/CDB) user privileges

Connect to the Oracle CDB as a DBA and grant the following:

-- Basic access
GRANT CONNECT TO C##MIGRATION_USER;
GRANT CREATE SESSION TO C##MIGRATION_USER;

-- General metadata access
GRANT EXECUTE_CATALOG_ROLE TO C##MIGRATION_USER;
GRANT SELECT_CATALOG_ROLE TO C##MIGRATION_USER;

-- Access to necessary V$ views
GRANT SELECT ON V_$LOG TO C##MIGRATION_USER;
GRANT SELECT ON V_$LOGFILE TO C##MIGRATION_USER;
GRANT SELECT ON V_$LOGMNR_CONTENTS TO C##MIGRATION_USER;
GRANT SELECT ON V_$ARCHIVED_LOG TO C##MIGRATION_USER;
GRANT SELECT ON V_$DATABASE TO C##MIGRATION_USER;
GRANT SELECT ON V_$LOG_HISTORY TO C##MIGRATION_USER;

-- Direct grants to specific DBA views
GRANT SELECT ON ALL_USERS TO C##MIGRATION_USER;
GRANT SELECT ON DBA_USERS TO C##MIGRATION_USER;
GRANT SELECT ON DBA_OBJECTS TO C##MIGRATION_USER;
GRANT SELECT ON DBA_SYNONYMS TO C##MIGRATION_USER;
GRANT SELECT ON DBA_TABLES TO C##MIGRATION_USER;

Connect to the Oracle PDB (not the CDB) as a DBA and grant the following:

-- Allow C##MIGRATION_USER to connect to the PDB and see active transaction metadata
GRANT CONNECT TO C##MIGRATION_USER;
GRANT CREATE SESSION TO C##MIGRATION_USER;

-- General metadata access
GRANT SELECT_CATALOG_ROLE TO C##MIGRATION_USER;

-- Access to necessary V$ views
GRANT SELECT ON V_$SESSION TO C##MIGRATION_USER;
GRANT SELECT ON V_$TRANSACTION TO C##MIGRATION_USER;

-- Grant these two for every table to migrate in the migration_schema
GRANT SELECT, FLASHBACK ON migration_schema.tbl TO C##MIGRATION_USER;

Single-tenant Oracle user privileges

Connect to the Oracle database as a DBA and grant the following:

-- Basic access
GRANT CONNECT TO MIGRATION_USER;
GRANT CREATE SESSION TO MIGRATION_USER;

-- General metadata access
GRANT SELECT_CATALOG_ROLE TO MIGRATION_USER;
GRANT EXECUTE_CATALOG_ROLE TO MIGRATION_USER;

-- Access to necessary V$ views
GRANT SELECT ON V_$DATABASE TO MIGRATION_USER;
GRANT SELECT ON V_$SESSION TO MIGRATION_USER;
GRANT SELECT ON V_$TRANSACTION TO MIGRATION_USER;

-- Direct grants to specific DBA views
GRANT SELECT ON ALL_USERS TO MIGRATION_USER;
GRANT SELECT ON DBA_USERS TO MIGRATION_USER;
GRANT SELECT ON DBA_OBJECTS TO MIGRATION_USER;
GRANT SELECT ON DBA_SYNONYMS TO MIGRATION_USER;
GRANT SELECT ON DBA_TABLES TO MIGRATION_USER;

-- Grant these two for every table to migrate in the migration_schema
GRANT SELECT, FLASHBACK ON migration_schema.tbl TO MIGRATION_USER;

Configure source database for replication

Connect to the primary instance (PostgreSQL primary, MySQL primary/master, or Oracle primary), not a replica. Replicas cannot provide the necessary replication checkpoints and transaction metadata required for ongoing replication.

Enable ARCHIVELOG and FORCE LOGGING

Enable ARCHIVELOG mode for LogMiner to access archived redo logs:

-- Check current log mode
SELECT log_mode FROM v$database;

-- Enable ARCHIVELOG (requires database restart)
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

-- Verify ARCHIVELOG is enabled
SELECT log_mode FROM v$database; -- Expected: ARCHIVELOG

Enable supplemental logging for primary keys:

-- Enable minimal supplemental logging for primary keys
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

-- Verify supplemental logging status
SELECT supplemental_log_data_min, supplemental_log_data_pk FROM v$database;
-- Expected:
--   SUPPLEMENTAL_LOG_DATA_MIN: IMPLICIT (or YES)
--   SUPPLEMENTAL_LOG_DATA_PK: YES

Enable FORCE LOGGING to ensure all changes are logged:

ALTER DATABASE FORCE LOGGING;

-- Verify FORCE LOGGING is enabled
SELECT force_logging FROM v$database; -- Expected: YES

Create source sentinel table

Create a checkpoint table called REPLICATOR_SENTINEL in the Oracle schema you will migrate:

CREATE TABLE migration_schema."REPLICATOR_SENTINEL" (
  keycol NUMBER PRIMARY KEY,
  lastSCN NUMBER
);

Grant privileges to modify the checkpoint table. In Oracle Multitenant, grant the privileges on the PDB:

GRANT SELECT, INSERT, UPDATE ON migration_schema."REPLICATOR_SENTINEL" TO C##MIGRATION_USER;

Grant LogMiner privileges

Grant LogMiner privileges. In Oracle Multitenant, grant the permissions on the CDB:

-- Access to necessary V$ views
GRANT SELECT ON V_$LOG TO C##MIGRATION_USER;
GRANT SELECT ON V_$LOGFILE TO C##MIGRATION_USER;
GRANT SELECT ON V_$LOGMNR_CONTENTS TO C##MIGRATION_USER;
GRANT SELECT ON V_$ARCHIVED_LOG TO C##MIGRATION_USER;
GRANT SELECT ON V_$LOG_HISTORY TO C##MIGRATION_USER;

-- SYS-prefixed views (for full dictionary access)
GRANT SELECT ON SYS.V_$LOGMNR_DICTIONARY TO C##MIGRATION_USER;
GRANT SELECT ON SYS.V_$LOGMNR_LOGS TO C##MIGRATION_USER;
GRANT SELECT ON SYS.V_$LOGMNR_PARAMETERS TO C##MIGRATION_USER;
GRANT SELECT ON SYS.V_$LOGMNR_SESSION TO C##MIGRATION_USER;

-- Access to LogMiner views and controls
GRANT LOGMINING TO C##MIGRATION_USER;
GRANT EXECUTE ON DBMS_LOGMNR TO C##MIGRATION_USER;

The user must:

• Query redo logs from LogMiner.
• Retrieve active transaction information to determine the starting point for ongoing replication.
• Update the internal REPLICATOR_SENTINEL table created on the Oracle source schema by the DBA.

Verify LogMiner privileges

Query the locations of redo files in the Oracle database:

SELECT
    l.GROUP#,
    lf.MEMBER,
    l.FIRST_CHANGE# AS START_SCN,
    l.NEXT_CHANGE# AS END_SCN
FROM
    V$LOG l
JOIN
    V$LOGFILE lf
ON
    l.GROUP# = lf.GROUP#;

   GROUP# MEMBER                                       START_SCN                END_SCN
_________ _________________________________________ ____________ ______________________
        3 /opt/oracle/oradata/ORCLCDB/redo03.log         1232896    9295429630892703743
        2 /opt/oracle/oradata/ORCLCDB/redo02.log         1155042                1232896
        1 /opt/oracle/oradata/ORCLCDB/redo01.log         1141934                1155042

3 rows selected.

Get the current snapshot System Change Number (SCN):

SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN
-----------
2358840

1 row selected.

Add the redo log files to LogMiner, using the redo log file paths you queried:

EXEC DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/opt/oracle/oradata/ORCLCDB/redo01.log', OPTIONS => DBMS_LOGMNR.NEW);
EXEC DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/opt/oracle/oradata/ORCLCDB/redo02.log', OPTIONS => DBMS_LOGMNR.ADDFILE);
EXEC DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/opt/oracle/oradata/ORCLCDB/redo03.log', OPTIONS => DBMS_LOGMNR.ADDFILE);

Start LogMiner, specifying the SCN you queried:

EXEC DBMS_LOGMNR.START_LOGMNR(
  STARTSCN => 2358840,
  OPTIONS  => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG
);

PL/SQL procedure successfully completed.

If you receive ORA-01435: user does not exist, the Oracle user lacks sufficient LogMiner privileges. Refer to Grant LogMiner privileges.

Step 2: Prepare the target database

Define the target tables

Convert the source table definitions into CockroachDB-compatible equivalents. CockroachDB supports the PostgreSQL wire protocol and is largely .

• The source and target table definitions must match. Review to understand which source types can be mapped to CockroachDB types.

  For example, an Oracle source table defined as CREATE TABLE migration_schema.tbl (pk INT PRIMARY KEY); must have a corresponding schema and table in CockroachDB:

  CREATE SCHEMA migration_schema;
  CREATE TABLE migration_schema.tbl (pk INT PRIMARY KEY);
  

  • MOLT Fetch can automatically define matching CockroachDB tables using the option.

  • If you define the target tables manually, review how MOLT Fetch handles . You can use the MOLT Schema Conversion Tool to create matching table definitions.

  • By default, table and column names are case-insensitive in MOLT Fetch. If using the flag, schema, table, and column names must match Oracle’s default uppercase identifiers. Use quoted names on the target to preserve case. For example, the following CockroachDB SQL statement will error:

    CREATE TABLE co.stores (... store_id ...);
    

    It should be written as:

    CREATE TABLE "CO"."STORES" (... "STORE_ID" ...);
    

    When using --case-sensitive, quote all identifiers and match the case exactly (for example, use "CO"."STORES" and "STORE_ID").

• Every table must have an explicit primary key.

  Avoid using sequential keys. To learn more about the performance issues that can result from their use, refer to the . If a sequential key is necessary in your CockroachDB table, you must create it manually, after using to load and replicate the data.

• Review to understand how computed columns and partitioned tables can be mapped to the target, and how target tables can be renamed.

• By default on CockroachDB, INT is an alias for INT8, which creates 64-bit signed integers. PostgreSQL and MySQL default to 32-bit integers. Depending on your source database or application requirements, you may need to change the integer size to 4. For more information, refer to .

Schema Conversion Tool

The (SCT) converts source table definitions to CockroachDB-compatible syntax. It requires a free .

1. Upload a source .sql file to convert the syntax and identify in the table definitions.

2. Import the converted table definitions to a CockroachDB cluster:

   • When migrating to CockroachDB Cloud, the Schema Conversion Tool automatically .
   • When migrating to a self-hosted CockroachDB cluster, and pipe the directly into .

Drop constraints and indexes

To optimize data load performance, drop all non-PRIMARY KEY and on the target CockroachDB database before migrating:

• (you do not need to drop this constraint when using drop-on-target-and-recreate for table handling)

Do not drop constraints.

You can recreate the constraints and indexes after loading the data.

Create the SQL user

Create a SQL user in the CockroachDB cluster that has the necessary privileges.

To create a user crdb_user in the default database (you will pass this username in the target connection string):

CREATE USER crdb_user WITH PASSWORD 'password';

Grant database-level privileges for schema creation within the target database:

GRANT ALL ON DATABASE defaultdb TO crdb_user;

Grant permission to modify cluster settings:

GRANT SYSTEM MODIFYCLUSTERSETTING TO crdb_user;

Grant usage on schemas being migrated:

GRANT USAGE ON SCHEMA migration_schema TO crdb_user;
GRANT USAGE ON SCHEMA public TO crdb_user;

Grant user privileges to create tables in the migration_schema schema and internal MOLT tables like _molt_fetch_exceptions in the public CockroachDB schema:

Ensure that you are connected to the target database.

GRANT CREATE ON SCHEMA migration_schema TO crdb_user;
GRANT CREATE ON SCHEMA public TO crdb_user;

If you manually defined the target tables (which means that drop-on-target-and-recreate will not be used), grant the following privileges on the schema:

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA migration_schema TO crdb_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA migration_schema
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO crdb_user;

Grant the same privileges for internal MOLT tables in the public CockroachDB schema:

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO crdb_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO crdb_user;

If you will be running Fetch with drop-on-target-and-recreate, and the target schema has pre-existing tables that were created by a different user, you may need to change table ownership to allow the migration user to drop those tables. Make the following alteration for each table:

ALTER TABLE migration_schema.table1 OWNER TO crdb_user;

Depending on the MOLT Fetch you will use, grant the necessary privileges to run either IMPORT INTO or COPY FROM on the target tables:

IMPORT INTO privileges

Grant SELECT, INSERT, and DROP (required because the table is taken offline during the IMPORT INTO) privileges on all tables being migrated:

GRANT SELECT, INSERT, DROP ON ALL TABLES IN SCHEMA migration_schema TO crdb_user;

If you plan to use cloud storage with implicit authentication for data load, grant the EXTERNALIOIMPLICITACCESS :

GRANT SYSTEM EXTERNALIOIMPLICITACCESS TO crdb_user;

COPY FROM privileges

Grant privileges to the user:

GRANT admin TO crdb_user;

Set session variable

Ensure that the statement_timeout is set to 0s for the user:

ALTER ROLE crdb_user SET statement_timeout = '0s';

Replication privileges

Grant permissions to create the staging schema for replication:

ALTER USER crdb_user CREATEDB;

Configure GC TTL

Before starting the initial data load, configure the on the source CockroachDB cluster to ensure that historical data remains available when replication begins. The GC TTL must be long enough to cover the full duration of the data load.

Increase the GC TTL before starting the data load. For example, to set the GC TTL to 24 hours:

ALTER DATABASE defaultdb CONFIGURE ZONE USING gc.ttlseconds = 86400;

The GC TTL duration must be higher than your expected time for the initial data load.

Once replication has started successfully (which automatically protects its own data range), you can restore the GC TTL to its original value. For example, to restore to 5 minutes:

ALTER DATABASE defaultdb CONFIGURE ZONE USING gc.ttlseconds = 300;

For details, refer to .

Step 3: Load data into CockroachDB

In this step, you will:

• Configure MOLT Fetch with the flags needed for your migration.
• Run MOLT Fetch.
• Understand how to continue a load after an interruption.

Configure MOLT Fetch

The includes detailed information about how to , and how to .

When you run molt fetch, you can configure the following options for data load:

• : Specify URL‑encoded source and target connections.
• : Specify schema and table names to migrate.
• : Export data to cloud storage or a local file server.
• : Specifies whether data will only be loaded into/from intermediate storage.
• : Divide larger tables into multiple shards during data export.
• : Choose between IMPORT INTO and COPY FROM.
• : Determine how existing target tables are initialized before load.
• : Define any row-level transformations to apply to the data before it reaches the target.
• : Configure metrics collection during initial data load.

Read through the documentation to understand how to configure your molt fetch command and its flags. Follow , especially those related to security.

At minimum, the molt fetch command should include the source, target, data path, and flags:

molt fetch \
--source $SOURCE \
--target $TARGET \
--bucket-path 's3://bucket/path' \
--ignore-replication-check

However, depending on the needs of your migration, you may have many more flags set, and you may need to prepare some accompanying .json files.

Run MOLT Fetch

Perform the initial load of the source data.

1. Issue the command to move the source data to CockroachDB. This example command passes the source and target connection strings as environment variables, writes intermediate files to S3 storage, and uses the truncate-if-exists table handling mode to truncate the target tables before loading data. It also limits the migration to a single schema and filters three specific tables to migrate. The defaults to IMPORT INTO.

   The command assumes an Oracle Multitenant (CDB/PDB) source. specifies the container database (CDB) connection string.

   molt fetch \
   --source $SOURCE \
   --source-cdb $SOURCE_CDB \
   --target $TARGET \
   --schema-filter 'migration_schema' \
   --table-filter 'employees|payments|orders' \
   --bucket-path 's3://migration/data/cockroach' \
   --table-handling truncate-if-exists
   

2. Check the output to observe fetch progress.

   A starting fetch message indicates that the task has started:

   {"level":"info","type":"summary","num_tables":3,"cdc_cursor":"backfillFromSCN=26685444,scn=26685786","time":"2025-02-10T14:28:11-05:00","message":"starting fetch"}
   

   data extraction messages are written for each table that is exported to the location in --bucket-path:

   {"level":"info","table":"migration_schema.employees","time":"2025-02-10T14:28:11-05:00","message":"data extraction phase starting"}
   
   {"level":"info","table":"migration_schema.employees","type":"summary","num_rows":200000,"export_duration_ms":1000,"export_duration":"000h 00m 01s","time":"2025-02-10T14:28:12-05:00","message":"data extraction from source complete"}
   

   data import messages are written for each table that is loaded into CockroachDB:

   {"level":"info","table":"migration_schema.employees","time":"2025-02-10T14:28:12-05:00","message":"starting data import on target"}
   
   {"level":"info","table":"migration_schema.employees","type":"summary","net_duration_ms":1899.748333,"net_duration":"000h 00m 01s","import_duration_ms":1160.523875,"import_duration":"000h 00m 01s","export_duration_ms":1000,"export_duration":"000h 00m 01s","num_rows":200000,"cdc_cursor":"backfillFromSCN=26685444,scn=26685786","time":"2025-02-10T14:28:13-05:00","message":"data import on target for table complete"}
   

   A fetch complete message is written when the fetch task succeeds:

   {"level":"info","type":"summary","fetch_id":"f5cb422f-4bb4-4bbd-b2ae-08c4d00d1e7c","num_tables":3,"tables":["migration_schema.employees","migration_schema.payments","migration_schema.payments"],"skipped_unmigratable_tables":[],"cdc_cursor":"backfillFromSCN=26685444,scn=26685786","net_duration_ms":6752.847625,"net_duration":"000h 00m 06s","time":"2024-03-18T12:30:37-04:00","message":"fetch complete"}
   

   This message shows the appropriate values for the --backfillFromSCN and --scn flags to use when starting Replicator:

   --backfillFromSCN 26685444
   --scn 26685786
   

Continue MOLT Fetch after an interruption

If MOLT Fetch fails while loading data into CockroachDB from intermediate files, it exits with an error message, fetch ID, and continuation token for each table that failed to load on the target database.

{"level":"info","table":"public.employees","file_name":"shard_01_part_00000001.csv.gz","message":"creating or updating token for duplicate key value violates unique constraint \"employees_pkey\"; Key (id)=(1) already exists."}
{"level":"info","table":"public.employees","continuation_token":"a1b2c3d4-e5f6-7890-abcd-ef1234567890","message":"created continuation token"}
{"level":"info","fetch_id":"f5cb422f-4bb4-4bbd-b2ae-08c4d00d1e7c","message":"continue from this fetch ID"}
{"level":"error","message":"Error: error from fetching table for public.employees: error importing data: duplicate key value violates unique
  constraint \"employees_pkey\" (SQLSTATE 23505)"}

You can use this information to where it was interrupted.

Continuation is only possible under the following conditions:

• All data has been exported from the source database into intermediate files on or .
• The initial load of source data into the target CockroachDB database is incomplete.
• The load uses IMPORT INTO rather than COPY FROM.

Only one fetch ID and set of continuation tokens, each token corresponding to a table, are active at any time. See .

The following command reattempts the data load starting from a specific continuation file, but you can also use individual continuation tokens to .

The command assumes an Oracle Multitenant (CDB/PDB) source. specifies the container database (CDB) connection string.

molt fetch \
--source $SOURCE \
--source-cdb $SOURCE_CDB \
--target $TARGET \
--schema-filter 'migration_schema' \
--table-filter 'employees|payments|orders' \
--bucket-path 's3://migration/data/cockroach' \
--table-handling truncate-if-exists \
--ignore-replication-check \
--fetch-id f5cb422f-4bb4-4bbd-b2ae-08c4d00d1e7c \
--continuation-file-name shard_01_part_00000001.csv.gz

Step 4: Verify the initial data load

Use to confirm that the source and target data is consistent. This ensures that the data load was successful.

Run MOLT Verify

1. Run the command, specifying the source and target connection strings and the tables to validate.

   molt verify \
   --source $SOURCE \
   --target $TARGET \
   --table-filter 'employees|payments|orders'
   
   With Oracle Multitenant deployments, while --source-cdb is required for fetch, it is not necessary for verify.

2. Check the output to observe verify progress.

   A verification in progress indicates that the task has started:

   {"level":"info","time":"2025-02-10T15:35:04-05:00","message":"verification in progress"}
   

   starting verify messages are written for each specified table:

   {"level":"info","time":"2025-02-10T15:35:04-05:00","message":"starting verify on public.employees, shard 1/1"}
   

   A finished row verification message is written after each table is compared. If num_success equals num_truth_rows and the error counters (num_missing, num_mismatch, num_extraneous, and num_column_mismatch) are all 0, the table verified successfully. Any non-zero values in the error counters indicate data discrepancies that need investigation. For details on each field, refer to the page.

   {"level":"info","type":"summary","table_schema":"public","table_name":"employees","num_truth_rows":200004,"num_success":200004,"num_conditional_success":0,"num_missing":0,"num_mismatch":0,"num_extraneous":0,"num_live_retry":0,"num_column_mismatch":0,"time":"2025-02-10T15:35:05-05:00","message":"finished row verification on public.employees (shard 1/1)"}
   

   A verification complete message is written when the verify task succeeds:

   {"level":"info","net_duration_ms":699.804875,"net_duration":"000h 00m 00s","time":"2025-02-10T15:35:05-05:00","message":"verification complete"}
   

Step 5: Finalize the target schema

Add constraints and indexes

Add any constraints or indexes that you previously removed from the CockroachDB schema to facilitate data load.

If you used the --table-handling drop-on-target-and-recreate option for data load, only and constraints are preserved. You must manually recreate all other constraints and indexes.

For the appropriate SQL syntax, refer to and . Review the on CockroachDB.

Step 6: Begin forward replication

In this step, you will:

• Configure MOLT Replicator with the flags needed for your migration.
• Start MOLT Replicator.
• Understand how to continue replication after an interruption.

Configure MOLT Replicator

When you run replicator, you can configure the following options for replication:

• Replication connection strings: Specify URL-encoded source and target database connections.

• Replicator flags: Specify required and optional flags to configure replicator behavior.

• Tuning parameters: Optimize replication performance and resource usage.

• Replicator metrics: Monitor replication progress and performance.

Replication connection strings

MOLT Replicator uses --sourceConn and --targetConn to specify the source and target database connections.

--sourceConn specifies the connection string of the source database:

--sourceConn 'oracle://{username}:{password}@{host}:{port}/{service_name}'

For Oracle Multitenant databases, also specify --sourcePDBConn with the PDB connection string:

--sourcePDBConn 'oracle://{username}:{password}@{host}:{port}/{pdb_service_name}'

--targetConn specifies the target CockroachDB connection string:

--targetConn 'postgresql://{username}:{password}@{host}:{port}/{database}'

Follow best practices for securing connection strings. Refer to Secure connections.

Replicator flags

Configure the following flags for continuous replication. For details on all available flags, refer to .

Flag	Description
	Required. Oracle user that owns the tables to replicate. Oracle capitalizes identifiers by default, so use uppercase (for example, MIGRATION_USER).
	Required. Target schema name on CockroachDB where tables will be replicated. Schema name must be fully qualified in the format database.schema.
	Required. Snapshot System Change Number (SCN) for the initial changefeed starting point.
	Required. SCN of the earliest active transaction at the time of the snapshot. Ensures no transactions are skipped.
	Required. Staging schema name on CockroachDB for replication metadata and checkpoints. Schema name must be fully qualified in the format database.schema.
	Automatically create the staging schema if it does not exist. Include this flag when starting replication for the first time.
	Enable Prometheus metrics at a specified {host}:{port}. Metrics are served at http://{host}:{port}/_/varz.
	Path to a that enables data filtering, routing, or transformations. For examples, refer to .

You can find the SCN values in the message replication-only mode should include the following replicator flags after the initial data load completes.

Tuning parameters

Configure the following to optimize replication throughput and resource usage. Test different combinations in a pre-production environment to find the optimal balance of stability and performance for your workload.

The following parameters apply to PostgreSQL, Oracle, and CockroachDB (failback) sources.

Flag	Description
	Control the maximum number of concurrent target transactions. Higher values increase throughput but require more target connections. Start with a conservative value and increase based on target database capacity.
	Balance throughput and latency. Controls how many mutations are batched into each query to the target. Increase for higher throughput at the cost of higher latency.
	Control memory usage during operation. Increase to allow higher throughput at the expense of memory; decrease to apply backpressure and limit memory consumption.
	Set larger than by a safety factor to avoid exhausting target pool connections. Replicator enforces setting parallelism to 80% of this value.
	Reduce the number of queries to the target by combining multiple mutations on the same primary key within each batch. Disable only if exact mutation order matters more than end state.
	Improve apply throughput for independent tables and table groups that share foreign key dependencies. Increases memory and target connection usage, so ensure you increase or reduce .
	Set to the maximum allowable time between flushes (for example, 10s if data must be applied within 10 seconds). Works with to control when buffered mutations are committed to the target.
	Lower this value if constraint violations resolve quickly on your workload to make retries more frequent and reduce latency. Do not lower if constraint violations take time to resolve.
	Applies to (replicator start) scenarios only. Balance memory usage and throughput. Increase to read more rows at once from the CockroachDB staging cluster for higher throughput, at the cost of memory pressure. Decrease to reduce memory pressure and increase stability.

Replicator metrics

MOLT Replicator metrics are not enabled by default. Enable Replicator metrics by specifying the flag with a port (or host:port) when you start Replicator. This exposes Replicator metrics at http://{host}:{port}/_/varz. For example, the following flag exposes metrics on port 30005:

--metricsAddr :30005

For guidelines on using and interpreting replication metrics, refer to .

Start MOLT Replicator

With initial load complete, start replication of ongoing changes on the source to CockroachDB using .

MOLT Fetch captures a consistent point-in-time checkpoint at the start of the data load (shown as cdc_cursor in the fetch output). Starting replication from this checkpoint ensures that all changes made during and after the data load are replicated to CockroachDB, preventing data loss or duplication. The following steps use the checkpoint values from the fetch output to start replication at the correct position.

1. Run the replicator command, specifying the backfill and starting SCN from the checkpoint recorded during data load. Use --stagingSchema to specify a unique name for the staging database, and include --stagingCreateSchema to have MOLT Replicator automatically create the staging database. If you filtered tables during the initial load, and specify the path with --userscript.

   replicator oraclelogminer \
   --sourceConn $SOURCE \
   --sourcePDBConn $SOURCE_PDB \
   --targetConn $TARGET \
   --sourceSchema MIGRATION_USER \
   --targetSchema defaultdb.migration_schema \
   --backfillFromSCN 26685444 \
   --scn 26685786 \
   --stagingSchema defaultdb._replicator \
   --stagingCreateSchema \
   --metricsAddr :30005 \
   --userscript table_filter.ts \
   -v
   
   When , replication performance may decrease because filtered tables are still included in LogMiner queries and processed before being discarded.

Check that replication is working

1. Verify that Replicator is processing changes successfully. To do so, check the MOLT Replicator logs. Since you enabled debug logging with -v, you should see connection and row processing messages:

   When transactions are read from the Oracle source, you should see registered transaction IDs (XIDs):

   DEBUG  [2025-07-03T15:55:12-05:00] registered xid 0f001f0040060000
   DEBUG  [2025-07-03T15:55:12-05:00] registered xid 0b001f00bb090000
   

   When rows are successfully replicated, you should see debug output like the following:

   DEBUG  [2025-07-03T15:55:12-05:00] upserted rows                                 conflicts=0 duration=2.620009ms proposed=13 target="\"molt_movies\".\"USERS\".\"CUSTOMER_CONTACT\"" upserted=13
   DEBUG  [2025-07-03T15:55:12-05:00] upserted rows                                 conflicts=0 duration=2.212807ms proposed=16 target="\"molt_movies\".\"USERS\".\"CUSTOMER_DEVICE\"" upserted=16
   

   These messages confirm successful replication. You can disable verbose logging after verifying the connection.

Continue MOLT Replicator after an interruption

Run the oraclelogminer command using the same --stagingSchema value from your initial replication command.

Replicator will automatically find the correct restart SCN (System Change Number) from the _oracle_checkpoint table in the staging schema. The restart point is determined by the non-committed row with the smallest startscn column value.

replicator oraclelogminer \
--sourceConn $SOURCE \
--sourcePDBConn $SOURCE_PDB \
--sourceSchema MIGRATION_USER \
--targetSchema defaultdb.migration_schema \
--targetConn $TARGET \
--stagingSchema defaultdb._replicator \
--metricsAddr :30005 \
--userscript table_filter.ts \
-v

When , replication performance may decrease because filtered tables are still included in LogMiner queries and processed before being discarded.

Replication resumes from the last checkpoint without performing a fresh load. Monitor the metrics endpoint at http://localhost:30005/_/varz to track replication progress.

Step 7: Stop application traffic

Once the inital data load has been verified and the target schema has been finalized, it’s time to begin the cutover process. First, stop application traffic to the source. Scale down the Kubernetes cluster to zero pods.

kubectl scale deployment app --replicas=0

Application downtime begins now.

It is strongly recommended that you perform a dry run of this migration in a test environment. This will allow you to practice using the MOLT tools in real time, and it will give you an accurate sense of how long application downtime might last.

Step 8: Stop forward replication

Before you can cut over traffic to the target, the changes to the source database need to finish being written to the target. Once the source is no longer receiving write traffic, MOLT Replicator will take some seconds to finish replicating the final changes. This is known as drainage.

1. Wait for replication to drain, which means that all transactions that occurred on the source database have been fully processed and replicated. There are several ways to determine that replication has fully drained:

   • When replication is caught up, you will not see new upserted rows logs.

   • If you set up the replication metrics endpoint with in the preceding steps, metrics are available at:

     http://{host}:{port}/_/varz
     

     Use the following Prometheus alert expression to observe when the combined rate of upserts and deletes is 0 for each schema:

     sum by (schema) (rate(apply_upserts_total[$__rate_interval]) + rate(apply_deletes_total[$__rate_interval]))
     

   • You can also check Prometheus metrics associated with replication lag, including .

2. Cancel replication by entering ctrl-c to issue a SIGTERM signal. This returns an exit code 0.

Step 9: Verify the replicated data

Repeat Step 4 to verify the updated data.

Step 10: Cut over application traffic

With the target cluster verified and finalized, it’s time to resume application traffic.

Modify application code

In the application back end, make sure that the application now directs traffic to the CockroachDB cluster. For example:

env:
  - name: DATABASE_URL
    value: postgres://root@localhost:26257/defaultdb?sslmode=verify-full

Resume application traffic

Scale up the Kubernetes deployment to the original number of replicas:

kubectl scale deployment app --replicas=3

This ends downtime.

See also