Migrating an Oracle database to OCI involves more than moving data — it requires selecting the correct migration toolchain, designing for cutover, and managing risk. Oracle provides multiple migration paths, and choosing incorrectly leads to extended downtime or data integrity issues. This article compares the major approaches and provides decision criteria.

Migration Approach Decision Tree

Is downtime during migration acceptable?
├── YES (hours or more acceptable)
│   ├── Database size < 1 TB → Data Pump (expdp/impdp) over network or pre-stage to Object Storage
│   └── Database size > 1 TB → RMAN duplicate or backup/restore to OCI Object Storage
└── NO (near-zero or zero downtime required)
    ├── Source is Oracle → Zero Downtime Migration (ZDM) with GoldenGate
    └── Source is non-Oracle → OCI Database Migration Service (DMS)

Zero Downtime Migration (ZDM)

Oracle ZDM is the recommended tool for migrating Oracle databases to OCI with minimal or zero downtime. It orchestrates a combination of RMAN (initial bulk copy) + GoldenGate (ongoing replication during migration) in an automated workflow.

ZDM Architecture

On-Premises Source DB
   │
   ├─[RMAN Backup]──────────────────► OCI Object Storage
   │                                        │
   │                                   [OCI DB System]
   │                                   [RMAN Restore]
   │                                        │
   └─[GoldenGate Replication]──────────────► Target DB (catching up to source)
                                             │
                              [Application Cutover: redirect connection strings]

ZDM Workflow

ZDM runs on a dedicated ZDM service host — a small compute instance that orchestrates the migration. The ZDM service host requires connectivity to both the source and target.

# On ZDM service host: run a logical migration (Data Pump based)
zdmcli migrate database \
  -sourcesid ORCL \
  -sourcenode source-host.domain.com \
  -srcauth zdmauth \
  -srcarg1 user:oracle \
  -srcarg2 identity_file:/home/oracle/.ssh/id_rsa \
  -targetdatabaseid <target_db_ocid> \
  -targethome /u01/app/oracle/product/19.0.0/dbhome_1 \
  -backupuser <object_storage_user> \
  -rsp /u01/zdm/zdm_migration.rsp \
  -eval   # evaluation mode: validate without migrating

# Response file: key parameters
TGT_DB_UNIQUE_NAME=TARGET_DB
MIGRATION_METHOD=ONLINE_LOGICAL   # uses Data Pump + GoldenGate
DATA_TRANSFER_MEDIUM=OSS          # OCI Object Storage
OSS_BUCKET_NAME=zdm-migration-bucket
PLATFORM_TYPE=ORACLE
USE_DG_BROKER=FALSE

ZDM Migration Phases

ZDM breaks migration into phases visible in zdmcli query job:

  1. ZDM_VALIDATE_SRC — validates source database.
  2. ZDM_SETUP_SRC — installs GoldenGate and configures Extract on source.
  3. ZDM_BACKUP_FULL_SRC — RMAN backup to Object Storage.
  4. ZDM_RESTORE_TGT — RMAN restore on OCI target.
  5. ZDM_MONITOR_GG_LAG — waits for GoldenGate lag to fall below threshold.
  6. ZDM_SWITCHOVER — performs the cutover when lag is minimal.
# Monitor ZDM job progress
zdmcli query job -jobid 5

OCI Database Migration Service (DMS)

OCI DMS is a fully managed cloud service for database migrations — Oracle-to-Oracle, and heterogeneous (MySQL, PostgreSQL to Oracle or ADB). It provides a wizard-driven experience via the OCI Console.

When to Use DMS over ZDM

  • Migrating from non-Oracle sources (MySQL, PostgreSQL) to Oracle or ADB.
  • You prefer a managed service approach without maintaining a ZDM host.
  • Target is Autonomous Database (ZDM supports ADB, DMS provides a simpler path for ADB-S).

DMS Migration Steps

# OCI CLI: create a DMS migration
oci database-migration migration create \
  --compartment-id <compartment_ocid> \
  --display-name "prod-migration" \
  --source-database-connection-id <source_conn_ocid> \
  --target-database-connection-id <target_conn_ocid> \
  --type "ONLINE"   # ONLINE = with GoldenGate replication; OFFLINE = bulk only

Data Pump Migration: Still Valid at Scale

For databases where a maintenance window is acceptable, Data Pump via OCI Object Storage is reliable and well-understood.

Full Export to OCI Object Storage

-- Create a directory pointing to OCI Object Storage via DBMS_CLOUD (ADB only)
-- For non-ADB: export locally, then upload via OCI CLI

-- Export with Data Pump (source side)
expdp userid=system/password \
  full=y \
  dumpfile=full_export_%U.dmp \
  logfile=expdp_full.log \
  parallel=8 \
  compression=all \
  directory=DATA_PUMP_DIR \
  filesize=5G

# Upload dump files to OCI Object Storage
oci os object bulk-upload \
  --bucket-name migration-bucket \
  --src-dir /u01/datapump/ \
  --include "full_export_*.dmp"

# On target OCI instance: download and import
oci os object bulk-download \
  --bucket-name migration-bucket \
  --dest-dir /u01/datapump/

impdp userid=system/password \
  full=y \
  dumpfile=full_export_%U.dmp \
  logfile=impdp_full.log \
  parallel=8 \
  directory=DATA_PUMP_DIR \
  transform=disable_archive_logging:y  # speeds up import

Estimating Data Pump Duration

-- Estimate export size before running
expdp userid=system/password full=y estimate_only=statistics parallel=8

Rule of thumb: parallel=N where N ≈ number of CPUs / 2. Data Pump scales well up to about 16 parallel streams; beyond that, bottlenecks shift to I/O.

RMAN-Based Physical Migration

For large databases (multi-TB) where Data Pump would be too slow, RMAN duplicate over the network or backup/restore to Object Storage is faster.

# RMAN: backup to OCI Object Storage using DBMS_BACKUP_RESTORE
rman target sys/password@source_db

RMAN> CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE'
PARMS 'SBT_LIBRARY=libopc.so,ENV=(OPC_PFILE=/etc/opc.pfile)';

RMAN> BACKUP DATABASE PLUS ARCHIVELOG
  FORMAT '%d_%T_%U' TAG 'OCI_MIGRATION';

For the lowest-latency physical migration, provision the OCI DB System in the same region and use DUPLICATE TARGET DATABASE over the network with active duplication:

# RMAN active duplicate (requires network between source and target)
rman target sys/password@source auxiliary sys/password@target

RMAN> DUPLICATE TARGET DATABASE TO TARGET_UNIQUE_NAME
  FROM ACTIVE DATABASE
  USING BACKUPSET
  SECTION SIZE 10G;

Post-Migration Validation

After any migration, validate thoroughly before cutover:

-- Row count comparison (source vs target) for critical tables
SELECT table_name, num_rows
FROM dba_tables
WHERE owner = 'SCHEMA1'
ORDER BY table_name;

-- Object count comparison
SELECT object_type, COUNT(*)
FROM dba_objects
WHERE owner = 'SCHEMA1'
GROUP BY object_type
ORDER BY object_type;

-- Invalid objects
SELECT object_name, object_type, status
FROM dba_objects
WHERE owner = 'SCHEMA1'
  AND status = 'INVALID';

Recompile invalid objects after migration:

EXEC DBMS_UTILITY.COMPILE_SCHEMA(schema => 'SCHEMA1', compile_all => FALSE);

Common Pitfalls

  • Time zone mismatch: Source and target TIME_ZONE database settings must match, or TIMESTAMP WITH TIME ZONE data will shift. Verify with SELECT DBTIMEZONE FROM DUAL.
  • Character set issues: Export with NLS_CHARACTERSET matching target, or use Data Pump’s character set conversion options.
  • Missing privileges: Data Pump full exports require EXP_FULL_DATABASE role. Confirm on source before migration day.
  • Network throughput: Pre-test throughput between source host and OCI Object Storage. Migration timelines often depend on this bottleneck more than CPU or database I/O.
  • Supplemental logging for ZDM: GoldenGate requires supplemental logging enabled on the source before Extract starts. Enable at least one day before migration to avoid log gaps.