Phased Bulk Load Migration from Oracle

• Data is migrated to the target in phases.
• This approach does not utilize continuous replication.
• Rollback is manual. If you wish to roll back before the target has received any writes that are not present on the source database, nothing needs to be done. If you wish to roll back after the target has received writes that are not present on the source database, you must manually replicate these new rows on the source.

​

Example scenario

​

Before the migration

• Install the MOLT (Migrate Off Legacy Technology) tools.
• Review the MOLT Fetch documentation.
• Develop a migration plan and prepare for the migration.
• 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.
• Announce the maintenance window to your users on a per-region basis.
• Understand the prerequisites and limitations of the MOLT tools:

​

Prerequisites

​

Oracle Instant Client

• 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 primary key types of INT, FLOAT, or UUID can be sharded with --export-concurrency.
• GEOMETRY and GEOGRAPHY types are not 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.

​

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 flag.
• MOLT Verify checks for collation mismatches on primary key columns. This may cause validation to fail when a STRING is used as a primary key and the source and target databases are using different collations.
• MOLT Verify might give an error in case of schema changes on either the source or target database.
• Geospatial types cannot yet be compared.

​

Step 1: Prepare the source database

• Create a dedicated migration user on your source database.

​

Create migration user on source database

CREATE USER MIGRATION_USER IDENTIFIED BY 'password';

​

Oracle Multitenant (PDB/CDB) user privileges

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

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

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

​

Step 2: Prepare the target database

• Provision and run a new CockroachDB cluster.
• Define the tables on the target cluster to match those on the source.
• Create a SQL user on the target cluster with the necessary write permissions.

​

Provision a CockroachDB cluster

​

Option 1: Create a secure cluster locally

​

Option 2: Create a CockroachDB Self-Hosted cluster on AWS

​

Option 3: Create a CockroachDB Cloud cluster

​

Define the target tables

• The source and target table definitions must match. Review Type mapping 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 drop-on-target-and-recreate option.
  • If you define the target tables manually, review how MOLT Fetch handles type mismatches. 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 --case-sensitive 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 guidance on indexing with sequential keys. If a sequential key is necessary in your CockroachDB table, you must create it manually, after using MOLT Fetch to load and replicate the data.
• Review Transformations 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 Considerations for 64-bit signed integers.

​

Schema Conversion Tool

1. Upload a source .sql file to convert the syntax and identify unimplemented features and syntax incompatibilities in the table definitions.
2. Import the converted table definitions to a CockroachDB cluster:
   • When migrating to CockroachDB Cloud, the Schema Conversion Tool automatically applies the converted table definitions to a new Cloud database.
   • When migrating to a self-hosted CockroachDB cluster, export a converted DDL file and pipe the data definition language (DDL) directly into cockroach sql.

​

Drop constraints and indexes

• FOREIGN KEY
• UNIQUE
• Secondary indexes
• CHECK
• DEFAULT
• NOT NULL (you do not need to drop this constraint when using drop-on-target-and-recreate for table handling)

​

Create the SQL user

CREATE USER crdb_user WITH PASSWORD 'password';

GRANT ALL ON DATABASE defaultdb TO crdb_user;

GRANT SYSTEM MODIFYCLUSTERSETTING TO crdb_user;

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

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

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

ALTER TABLE migration_schema.table1 OWNER TO crdb_user;

​

IMPORT INTO privileges

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

GRANT SYSTEM EXTERNALIOIMPLICITACCESS TO crdb_user;

​

COPY FROM privileges

GRANT admin TO crdb_user;

​

Set session variable

ALTER ROLE crdb_user SET statement_timeout = '0s';

​

Migrate each phase

​

Step 3: Stop application traffic

kubectl scale deploy/app-eu --replicas=0

apiVersion: networking.k8s.io/v1
kind: Ingress
metadata:
  name: app
  annotations:
    nginx.ingress.kubernetes.io/server-snippet: |
      if ($http_x_region = "eu") {
        return 503;
      }
spec:
  ingressClassName: nginx
  rules:
  - host: api.example.com
    http:
      paths:
      - path: /
        pathType: Prefix
        backend:
          service:
            name: app
            port:
              number: 80

​

Step 4: Load data into CockroachDB

• 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

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

molt fetch \
--source $SOURCE \
--target $TARGET \
--table-filter '.*user.*' \
--bucket-path 's3://bucket/path' \
--ignore-replication-check

​

Run MOLT Fetch

1. Run the MOLT Fetch command to move the source data into 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 limits the migration to a single schema and filters for three specific tables. The data load mode defaults to IMPORT INTO. Include the --ignore-replication-check flag to skip replication checkpoint queries, which eliminates the need to configure the source database for logical replication. The command assumes an Oracle Multitenant (CDB/PDB) source. --source-cdb 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
   
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"}
   

​

Continue MOLT Fetch after an interruption

{"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)"}

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

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 5: Verify the data

​

Run MOLT Verify

1. Run the MOLT Verify 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 MOLT Verify 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 6: Finalize the target schema

​

Add constraints and indexes

​

Step 7: Cut over application traffic

​

Modify application code

env:
  - name: DATABASE_URL_US_EAST
    value: postgres://root@cockroachdb.us-east:26257/defaultdb?sslmode=verify-full
  - name: DATABASE_URL_US_WEST
    value: postgres://legacy-db.us-west:5432/defaultdb  # Still on source

def get_db_connection(user_region):
    if user_region == "us-east":
        return os.getenv("DATABASE_URL_US_EAST")  # CockroachDB
    else:
        return os.getenv("DATABASE_URL_US_WEST")  # Source database

​

Resume application traffic

kubectl scale deploy/app-eu --replicas=3

apiVersion: networking.k8s.io/v1
kind: Ingress
metadata:
  name: app
#   annotations:
#     nginx.ingress.kubernetes.io/server-snippet: |
#       if ($http_x_region = "eu") {
#         return 503;
#       }
spec:
  ingressClassName: nginx
  rules:
  - host: api.example.com
    http:
      paths:
      - path: /
        pathType: Prefix
        backend:
          service:
            name: app
            port:
              number: 80

​

Repeat for each phase

​

See also

• Migration Overview
• Migration Considerations
• Classic Bulk Load Migration
• MOLT Fetch
• MOLT Verify
• MOLT Fetch Troubleshooting for Oracle
• MOLT Replicator Troubleshooting for Oracle