Load and Replicate

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
  curl -o /tmp/ora-libs.zip https://replicator.cockroachdb.com/third_party/instantclient-basiclite-linux-amd64.zip
  unzip -d /tmp /tmp/ora-libs.zip
  sudo mv /tmp/instantclient_21_13/* /usr/lib
  export LD_LIBRARY_PATH=/usr/lib
  

Enable ARCHIVELOG

Enable ARCHIVELOG mode on the Oracle database. This is required for Oracle LogMiner, Oracle's built-in changefeed tool that captures DML events for replication.

SELECT log_mode FROM v$database;
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
SELECT log_mode FROM v$database;

LOG_MODE
--------
ARCHIVELOG

1 row selected.

Enable supplemental primary key logging for logical replication:

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
SELECT supplemental_log_data_min, supplemental_log_data_pk FROM v$database;

SUPPLEMENTAL_LOG_DATA_MIN SUPPLEMENTAL_LOG_DATA_PK
------------------------- ------------------------
IMPLICIT                  YES

1 row selected.

Enable FORCE_LOGGING to ensure that all data changes are captured for the tables to migrate:

ALTER DATABASE FORCE LOGGING;

• OID LOB types in PostgreSQL are not supported, although similar types like BYTEA are supported.

• Migrations must be performed from a single Oracle schema. You must include --schema-filter so that MOLT Fetch only loads data from the specified schema. Refer to Schema and table filtering.
  • Specifying --table-filter 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.

• Replication modes require write access to the PostgreSQL primary instance. MOLT cannot create replication slots or run replication against a read replica.

• MySQL replication is supported only with GTID-based configurations. Binlog-based features that do not use GTID are not supported.

• 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.

CREATE USER migration_user WITH PASSWORD 'password';

Grant the user privileges to connect, view schema objects, and select the tables you migrate.

GRANT CONNECT ON DATABASE source_database TO MIGRATION_USER;
GRANT USAGE ON SCHEMA migration_schema TO MIGRATION_USER;
GRANT SELECT ON ALL TABLES IN SCHEMA migration_schema TO MIGRATION_USER;
ALTER DEFAULT PRIVILEGES IN SCHEMA migration_schema GRANT SELECT ON TABLES TO MIGRATION_USER;

CREATE USER 'migration_user'@'%' IDENTIFIED BY 'password';

Grant the user privileges to select only the tables you migrate:

GRANT SELECT ON source_database.* TO MIGRATION_USER@'%';
FLUSH PRIVILEGES;

CREATE USER MIGRATION_USER IDENTIFIED BY 'password';

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 (e.g., 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;

Enable logical replication by setting wal_level to logical in postgresql.conf or in the SQL shell. For example:

ALTER SYSTEM SET wal_level = 'logical';

Create a publication for the tables you want to replicate. Do this before creating the replication slot.

To create a publication for all tables:

CREATE PUBLICATION molt_publication FOR ALL TABLES;

To create a publication for specific tables:

CREATE PUBLICATION molt_publication FOR TABLE employees, payments, orders;

Create a logical replication slot:

SELECT pg_create_logical_replication_slot('molt_slot', 'pgoutput');

Verify logical replication setup

Verify the publication was created successfully:

SELECT * FROM pg_publication;

  oid  |     pubname      | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate | pubviaroot
-------+------------------+----------+--------------+-----------+-----------+-----------+-------------+------------
 59084 | molt_publication |       10 | t            | t         | t         | t         | t           | f

Verify the replication slot was created:

SELECT * FROM pg_replication_slots;

 slot_name |  plugin  | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn | wal_status | safe_wal_size | two_phase
-----------+----------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------+------------+---------------+-----------
 molt_slot | pgoutput | logical   |  16385 | molt     | f         | f      |            |      |         2261 | 0/49913A20  | 0/49913A58          | reserved   |               | f

Enable global transaction identifiers (GTID) and configure binary logging. Set binlog-row-metadata or binlog-row-image to full to provide complete metadata for replication.

Verify MySQL GTID setup

Get the current GTID set to use as the starting point for replication:

-- For MySQL < 8.0:
SHOW MASTER STATUS;
-- For MySQL 8.0+:
SHOW BINARY LOG STATUS;

+---------------+----------+--------------+------------------+-------------------------------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
+---------------+----------+--------------+------------------+-------------------------------------------+
| binlog.000005 |      197 |              |                  | 77263736-7899-11f0-81a5-0242ac120002:1-38 |
+---------------+----------+--------------+------------------+-------------------------------------------+

Use the Executed_Gtid_Set value for the --defaultGTIDSet flag in MOLT Replicator.

To verify that a GTID set is valid and not purged, use the following queries:

-- Verify the GTID set is in the executed set
SELECT GTID_SUBSET('77263736-7899-11f0-81a5-0242ac120002:1-38', @@GLOBAL.gtid_executed) AS in_executed;

-- Verify the GTID set is not in the purged set
SELECT GTID_SUBSET('77263736-7899-11f0-81a5-0242ac120002:1-38', @@GLOBAL.gtid_purged) AS in_purged;

If in_executed returns 1 and in_purged returns 0, the GTID set is valid for 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:

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

-- Verify supplemental logging
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.

Get SCNs for replication startup

If you plan to use initial data load followed by replication, obtain the correct SCNs before starting the initial data load to ensure no active transactions are missed. Run the following queries on the PDB in the order shown:

-- Query the current SCN from Oracle
SELECT CURRENT_SCN FROM V$DATABASE;

-- Query the starting SCN of the earliest active transaction
SELECT MIN(t.START_SCNB) FROM V$TRANSACTION t;

Use the results as follows:

• --scn: Use the result from the first query (current SCN)
• --backfillFromSCN: Use the result from the second query (earliest active transaction SCN). If the second query returns no results, use the result from the first query instead.

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.

Syntax that cannot automatically be converted will be displayed in the Summary Report. These may include the following:

String case sensitivity

Strings are case-insensitive in MySQL and case-sensitive in CockroachDB. You may need to edit your MySQL data to get the results you expect from CockroachDB. For example, you may have been doing string comparisons in MySQL that will need to be changed to work with CockroachDB.

For more information about the case sensitivity of strings in MySQL, refer to Case Sensitivity in String Searches from the MySQL documentation. For more information about CockroachDB strings, refer to STRING.

Identifier case sensitivity

Identifiers are case-sensitive in MySQL and case-insensitive in CockroachDB. When using the Schema Conversion Tool, you can either keep case sensitivity by enclosing identifiers in double quotes, or make identifiers case-insensitive by converting them to lowercase.

AUTO_INCREMENT attribute

The MySQL AUTO_INCREMENT attribute, which creates sequential column values, is not supported in CockroachDB. When using the Schema Conversion Tool, columns with AUTO_INCREMENT can be converted to use sequences, UUID values with gen_random_uuid(), or unique INT8 values using unique_rowid(). Cockroach Labs does not recommend using a sequence to define a primary key column. For more information, refer to Unique ID best practices.

ENUM type

MySQL ENUM types are defined in table columns. On CockroachDB, ENUM is a standalone type. When using the Schema Conversion Tool, you can either deduplicate the ENUM definitions or create a separate type for each column.

TINYINT type

TINYINT data types are not supported in CockroachDB. The Schema Conversion Tool automatically converts TINYINT columns to INT2 (SMALLINT).

Geospatial types

MySQL geometry types are not converted to CockroachDB geospatial types by the Schema Conversion Tool. They should be manually converted to the corresponding types in CockroachDB.

FIELD function

The MYSQL FIELD function is not supported in CockroachDB. Instead, you can use the array_position function, which returns the index of the first occurrence of element in the array.

Example usage:

SELECT array_position(ARRAY[4,1,3,2],1);

  array_position
------------------
               2
(1 row)

While MySQL returns 0 when the element is not found, CockroachDB returns NULL. So if you are using the ORDER BY clause in a statement with the array_position function, the caveat is that sort is applied even when the element is not found. As a workaround, you can use the COALESCE operator.

SELECT * FROM table_a ORDER BY COALESCE(array_position(ARRAY[4,1,3,2],5),999);

--source 'postgres://{username}:{password}@{host}:{port}/{database}?sslmode=verify-full'

For example:

--source 'postgres://migration_user:password@localhost:5432/molt?sslmode=verify-full'

The source connection must point to the PostgreSQL primary instance, not a read replica.

--source 'mysql://{username}:{password}@{host}:{port}/{database}?sslmode=verify-full&sslcert={path_to_client_crt}&sslkey={path_to_client_key}&sslrootcert={path_to_ca_crt}'

For example:

--source 'mysql://migration_user:password@localhost/molt?sslcert=.%2fsource_certs%2fclient.root.crt&sslkey=.%2fsource_certs%2fclient.root.key&sslmode=verify-full&sslrootcert=.%2fsource_certs%2fca.crt'

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

In Oracle Multitenant, --source specifies the connection string for the PDB. --source-cdb specifies the connection string for the CDB. The username specified in both --source and --source-cdb must be a common user with the privileges described in Create migration user on source database.

--source 'oracle://{username}:{password}@{host}:{port}/{PDB_service_name}'
--source-cdb 'oracle://{username}:{password}@{host}:{port}/{CDB_service_name}'

Escape the C## prefix in the Oracle Multitenant username. For example, write C##MIGRATION_USER as C%23%23:

--source 'oracle://C%23%23MIGRATION_USER:password@host:1521/ORCLPDB1'
--source-cdb 'oracle://C%23%23MIGRATION_USER:password@host:1521/ORCLCDB'

Amazon S3

• Set the following environment variables in the terminal running molt fetch:

  export AWS_REGION='us-east-1'
  export AWS_SECRET_ACCESS_KEY='key'
  export AWS_ACCESS_KEY_ID='id'
  

  • To run molt fetch in a containerized environment (e.g., Docker), pass the required environment variables using -e. If your authentication method relies on local credential files, you may also need to volume map the host path to the appropriate location inside the container using -v. For example:

    docker run \
      -e AWS_ACCESS_KEY_ID='your-access-key' \
      -e AWS_SECRET_ACCESS_KEY='your-secret-key' \
      -v ~/.aws:/root/.aws \
      -it \
      cockroachdb/molt fetch \
      --bucket-path 's3://migration/data/cockroach' ...
    

• Alternatively, set --use-implicit-auth to use implicit authentication. When using assume role authentication, specify the service account with --assume-role. For example:

  --bucket-path 's3://migration/data/cockroach'
  --assume-role 'arn:aws:iam::123456789012:role/MyMigrationRole'
  --use-implicit-auth
  

• Set --import-region to specify an AWS_REGION (e.g., --import-region 'ap-south-1').

• Ensure the S3 bucket is created and accessible by authorized roles and users only.

Google Cloud Storage

• Authenticate your local environment with Application Default Credentials:

  Using gcloud:

  gcloud init
  gcloud auth application-default login
  

  Using the environment variable:

  export GOOGLE_APPLICATION_CREDENTIALS={path_to_cred_json}
  

  • To run molt fetch in a containerized environment (e.g., Docker), pass the required environment variables using -e. If your authentication method relies on local credential files, you may also need to volume map the host path to the appropriate location inside the container using -v. For example:

    docker run \
      -e GOOGLE_APPLICATION_CREDENTIALS='/root/.config/gcloud/application_default_credentials.json' \
      -v ~/.config/gcloud:/root/.config/gcloud \
      -it \
      cockroachdb/molt fetch \
      --bucket-path 'gs://migration/data/cockroach' ...
    

• Alternatively, set --use-implicit-auth to use implicit authentication. When using assume role authentication, specify the service account with --assume-role. For example:

  --bucket-path 'gs://migration/data/cockroach
  --use-implicit-auth
  --assume-role 'user-test@cluster-ephemeral.iam.gserviceaccount.com'
  

• Ensure the Google Cloud Storage bucket is created and accessible by authorized roles and users only.

Azure Blob Storage

• Set the following environment variables in the terminal running molt fetch:

  export AZURE_ACCOUNT_NAME='account'
  export AZURE_ACCOUNT_KEY='key'
  

  You can also speicfy client and tenant credentials as environment variables:

  export AZURE_CLIENT_SECRET='secret'
  export AZURE_TENANT_ID='id'
  

  • To run molt fetch in a containerized environment (e.g., Docker), pass the required environment variables using -e. If your authentication method relies on local credential files, you may also need to volume map the host path to the appropriate location inside the container using -v. For example:

    docker run \
      -e AZURE_ACCOUNT_NAME='account' \
      -e AZURE_ACCOUNT_KEY='key' \
      -e AZURE_CLIENT_SECRET='secret' \
      -e AZURE_TENANT_ID='id' \
      -v ~/.azure:/root/.azure \
      -it \
      cockroachdb/molt fetch \
      --bucket-path 'azure-blob://migration/data/cockroach' ...
    

• Alternatively, set --use-implicit-auth to use implicit authentication: For example:

  --bucket-path 'azure-blob://migration/data/cockroach'
  --use-implicit-auth
  

  This mode supports Azure managed identities and workload identities.

• Ensure the Azure Blob Storage container is created and accessible by authorized roles and users only.

When migrating from Oracle, you must include --schema-filter to name an Oracle schema to migrate. This prevents Fetch from attempting to load tables owned by other users. For example:

--schema-filter 'migration_schema'

Table filter userscript

When loading a subset of tables using --table-filter, you must provide a TypeScript userscript to specify which tables to replicate.

For example, the following table_filter.ts userscript filters change events to the specified source tables:

import * as api from "replicator@v1";

// List the source tables (matching source names and casing) to include in replication
const allowedTables = ["EMPLOYEES", "PAYMENTS", "ORDERS"];

// Update this to your target CockroachDB database and schema name
api.configureSource("defaultdb.migration_schema", {
  dispatch: (doc: Document, meta: Document): Record<Table, Document[]> | null => {
    // Replicate only if the table matches one of the allowed tables
    if (allowedTables.includes(meta.table)) {
      let ret: Record<Table, Document[]> = {};
      ret[meta.table] = [doc];
      return ret;
    }
    // Ignore all other tables
    return null;
  },
  deletesTo: (doc: Document, meta: Document): Record<Table, Document[]> | null => {
    // Optionally filter deletes the same way
    if (allowedTables.includes(meta.table)) {
      let ret: Record<Table, Document[]> = {};
      ret[meta.table] = [doc];
      return ret;
    }
    return null;
  },
});

Pass the userscript to MOLT Replicator with the --userscript flag:

--userscript table_filter.ts

Table filter userscript

When loading a subset of tables using --table-filter, you must provide a TypeScript userscript to specify which tables to replicate.

For example, the following table_filter.ts userscript filters change events to the specified source tables:

import * as api from "replicator@v1";

// List the source tables (matching source names and casing) to include in replication
const allowedTables = ["EMPLOYEES", "PAYMENTS", "ORDERS"];

// Update this to your target CockroachDB database and schema name
api.configureSource("defaultdb.migration_schema", {
  dispatch: (doc: Document, meta: Document): Record<Table, Document[]> | null => {
    // Replicate only if the table matches one of the allowed tables
    if (allowedTables.includes(meta.table)) {
      let ret: Record<Table, Document[]> = {};
      ret[meta.table] = [doc];
      return ret;
    }
    // Ignore all other tables
    return null;
  },
  deletesTo: (doc: Document, meta: Document): Record<Table, Document[]> | null => {
    // Optionally filter deletes the same way
    if (allowedTables.includes(meta.table)) {
      let ret: Record<Table, Document[]> = {};
      ret[meta.table] = [doc];
      return ret;
    }
    return null;
  },
});

Pass the userscript to MOLT Replicator with the --userscript flag:

--userscript table_filter.ts

Replication from MySQL requires --defaultGTIDSet, which sets the starting GTID for replication. You can find this value in the cdc_cursor field of the fetch complete message after the initial data load completes.

Replication from Oracle requires --sourceSchema, --scn, and --backfillFromSCN. The --sourceSchema specifies the Oracle schema to replicate from, while --scn and --backfillFromSCN specify the snapshot SCN and the earliest active transaction SCN, respectively. 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 replicator flags to optimize replication throughput and resource usage. Test different combinations in a staging environment to find the optimal balance of stability and performance for your workload.

1. Run the replicator command, specifying the same --slotName value that you configured during source database setup. Use --stagingSchema to specify a unique name for the staging database, and include --stagingCreateSchema to have MOLT Replicator automatically create the staging database:

   replicator pglogical \
   --sourceConn $SOURCE \
   --targetConn $TARGET \
   --targetSchema defaultdb.public \
   --slotName molt_slot \
   --stagingSchema _replicator \
   --stagingCreateSchema \
   --metricsAddr :30005 \
   --verbose
   

1. Get the executed GTID set from the MySQL source, which shows what transactions have been applied on the source database. Use the Executed_Gtid_Set value as your --defaultGTIDSet:

   -- For MySQL < 8.0:
   SHOW MASTER STATUS;
   -- For MySQL 8.0+:
   SHOW BINARY LOG STATUS;
   

   +---------------+----------+--------------+------------------+-------------------------------------------+
   | File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
   +---------------+----------+--------------+------------------+-------------------------------------------+
   | binlog.000005 |      197 |              |                  | 77263736-7899-11f0-81a5-0242ac120002:1-38 |
   +---------------+----------+--------------+------------------+-------------------------------------------+
   

2. Run the replicator command, specifying the GTID set from the previous step. Use --stagingSchema to specify a unique name for the staging database, and include --stagingCreateSchema to have MOLT Replicator automatically create the staging database:

   replicator mylogical \
   --sourceConn $SOURCE \
   --targetConn $TARGET \
   --targetSchema defaultdb.public \
   --defaultGTIDSet 77263736-7899-11f0-81a5-0242ac120002:1-38 \
   --stagingSchema _replicator \
   --stagingCreateSchema \
   --metricsAddr :30005 \
   --userscript table_filter.ts \
   --verbose
   

   Tip:

   For MySQL versions that do not support binlog_row_metadata, include --fetchMetadata to explicitly fetch column metadata. This requires additional permissions on the source MySQL database. Grant SELECT permissions with GRANT SELECT ON source_database.* TO 'migration_user'@'localhost';. If that is insufficient for your deployment, use GRANT PROCESS ON *.* TO 'migration_user'@'localhost';, though this is more permissive and allows seeing processes and server status.

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:

   replicator oraclelogminer \
   --sourceConn $SOURCE \
   --sourcePDBConn $SOURCE_PDB \
   --targetConn $TARGET \
   --sourceSchema migration_schema \
   --targetSchema defaultdb.public \
   --backfillFromSCN 26685444 \
   --scn 26685786 \
   --stagingSchema _replicator \
   --stagingCreateSchema \
   --metricsAddr :30005 \
   --userscript table_filter.ts \
   --verbose
   

   Note:

   When filtering out tables in a schema with a userscript, replication performance may decrease because filtered tables are still included in LogMiner queries and processed before being discarded.

ORA-01950: no privileges on tablespace

If you receive ORA-01950: no privileges on tablespace 'USERS', it means the Oracle table owner (migration_schema in the preceding examples) does not have sufficient quota on the tablespace used to store its data. By default, this tablespace is USERS, but it can vary. To resolve this issue, grant a quota to the table owner. For example:

-- change UNLIMITED to a suitable limit for the table owner
ALTER USER migration_schema QUOTA UNLIMITED ON USERS;

No tables to drop and recreate on target

When expecting a bulk load but seeing no tables to drop and recreate on the target, ensure the migration user has SELECT and FLASHBACK privileges on each table to be migrated. For example:

GRANT SELECT, FLASHBACK ON migration_schema.employees TO C##MIGRATION_USER;
GRANT SELECT, FLASHBACK ON migration_schema.payments TO C##MIGRATION_USER;
GRANT SELECT, FLASHBACK ON migration_schema.orders TO C##MIGRATION_USER;

Table or view does not exist

If the Oracle migration user lacks privileges on certain tables, you may receive errors stating that the table or view does not exist. Either use --table-filter to limit the tables to be migrated, or grant the migration user SELECT privileges on all objects in the schema. Refer to Create migration user on source database.

Missing redo logs or unavailable SCN

If the Oracle redo log files are too small or do not retain enough history, you may get errors indicating that required log files are missing for a given SCN range, or that a specific SCN is unavailable.

Increase the number and size of online redo log files, and verify that archived log files are being generated and retained correctly in your Oracle environment.

Missing replicator flags

If required --replicator-flags are missing, ensure that the necessary flags for your mode are included. For details, refer to Replication flags.

Replicator lag

If the replicator process is lagging significantly behind the current Oracle SCN, you may see log messages like: replicator is catching up to the current SCN at 5000 from 1000…. This indicates that replication is progressing but is still behind the most recent changes on the source database.

Oracle sessions remain open after forcefully stopping molt or replicator

If you shut down molt or replicator unexpectedly (e.g., with kill -9 or a system crash), Oracle sessions opened by these tools may remain active.

• Check your operating system for any running molt or replicator processes and terminate them manually.

• After confirming that both processes have stopped, ask a DBA to check for active Oracle sessions using:

  SELECT sid, serial#, username, status, osuser, machine, program
  FROM v$session
  WHERE username = 'C##MIGRATION_USER';
  

  Wait until any remaining sessions display an INACTIVE status, then terminate them using:

  ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
  

  Replace sid and serial# in the preceding statement with the values returned by the SELECT query.

Unable to create publication or slot

This error occurs when the source database does not support logical replication.

Resolution: Verify that the source database supports logical replication by checking the wal_level parameter on PostgreSQL:

SHOW wal_level;

If wal_level is not set to logical, update it and restart PostgreSQL:

ALTER SYSTEM SET wal_level = 'logical';

Replication slot already exists

ERROR: replication slot "molt_slot" already exists

Resolution: Either create a new slot with a different name, or drop the existing slot to start fresh:

SELECT pg_drop_replication_slot('molt_slot');

Publication does not exist

run CREATE PUBLICATION molt_fetch FOR ALL TABLES;

Resolution: Create the publication on the source database. Ensure you also create the replication slot:

CREATE PUBLICATION molt_publication FOR ALL TABLES;
SELECT pg_create_logical_replication_slot('molt_slot', 'pgoutput');

Could not connect to PostgreSQL

could not connect to source database: failed to connect to `user=migration_user database=source_database`

Resolution: Verify the connection details including user, host, port, and database name. Ensure the database name in your --sourceConn connection string matches exactly where you created the publication and slot. Verify you're connecting to the same host and port where you ran the CREATE PUBLICATION and SELECT pg_create_logical_replication_slot() commands. Check if TLS certificates need to be included in the connection URI.

Wrong replication slot name

run SELECT pg_create_logical_replication_slot('molt_slot', 'pgoutput'); in source database

Resolution: Create the replication slot or verify the correct slot name:

SELECT pg_create_logical_replication_slot('molt_slot', 'pgoutput');

Repeated binlog syncing restarts

If Replicator repeatedly restarts binlog syncing, this indicates an invalid or purged GTID.

Resolution: Verify the GTID set is valid and not purged:

-- Check if GTID is in executed set
SELECT GTID_SUBSET('your-gtid-set', @@GLOBAL.gtid_executed) AS in_executed;

-- Check if GTID is purged
SELECT GTID_SUBSET('your-gtid-set', @@GLOBAL.gtid_purged) AS in_purged;

Interpret the results as follows:

• If in_executed returns 1 and in_purged returns 0, the GTID is valid for replication.
• If in_purged returns 1, the GTID has been purged and you must find a newer consistent point.
• If both return 0, the GTID doesn't exist in the records and is invalid.

If the GTID is purged or invalid, follow these steps:

1. Increase binlog retention by configuring binlog_expire_logs_seconds in MySQL or through your cloud provider:

   -- Increase binlog retention (example: 7 days = 604800 seconds)
   SET GLOBAL binlog_expire_logs_seconds = 604800;
   

2. Get a current GTID set to restart replication:

   -- For MySQL < 8.0:
   SHOW MASTER STATUS;
   -- For MySQL 8.0+:
   SHOW BINARY LOG STATUS;
   

Invalid GTID format

Invalid GTIDs can occur when GTIDs are purged due to insufficient binlog retention, when connecting to a replica instead of the primary host, or when passing a GTID that has valid format but doesn't exist in the binlog history.

Resolution: Use a valid GTID from SHOW MASTER STATUS (MySQL < 8.0) or SHOW BINARY LOG STATUS (MySQL 8.0+) and ensure you're connecting to the primary host. If GTIDs are being purged, increase binlog retention.

Table/column names exceed 30 characters

Oracle LogMiner excludes tables and columns with names longer than 30 characters from redo logs.

Resolution: Rename tables and columns to 30 characters or fewer before migration.

Unsupported data types

LogMiner and replication do not support: - Long BLOB/CLOBs (4000+ characters) - User-defined types (UDTs) - Nested tables - Varrays

Resolution: Convert unsupported data types or exclude affected tables from replication.

LOB column UPDATE statements

UPDATE statements that only modify LOB columns are not supported by Oracle LogMiner.

Resolution: Avoid LOB-only updates during replication, or use Binary Reader for Oracle 12c.

JSONB null handling

SQL NULL and JSON null values are not distinguishable in JSON payloads during replication.

Resolution: Avoid using nullable JSONB columns where the distinction between SQL NULL and JSON null is important.