Oracle to AWS Migration Lab

I led the Oracle 12c to 19c migration at Kainga Ora. Before that I did on-prem to AWS migrations at Eka Software. This post documents the architecture, the tools, and the parts that will catch you if you are not careful.

This is not a tutorial written from documentation. These are the actual steps.

The architecture

The migration path looks like this:

On-prem Oracle (12c/19c, RAC)
  └── GoldenGate EXTRACT (reads redo logs)
       └── Trail files (local)
            └── PUMP process (sends over network)
                 └── Trail files (remote, AWS side)
                      └── AWS DMS or GoldenGate REPLICAT
                           └── RDS for Oracle or EC2 Oracle (target)
                                └── CloudWatch (monitoring lag and errors)

You have two main options for the transfer layer: AWS DMS with Oracle as source, or GoldenGate end-to-end. I have used both. Here is when each makes sense.

Use AWS DMS when:

• You are doing a straightforward Oracle-to-Oracle lift and shift
• The source schema does not use complex Oracle-specific types (XMLTYPE, spatial, nested tables)
• You want to minimise licensing cost (DMS does not need Oracle GoldenGate licenses)
• The database is under 500GB and the team is not already GoldenGate-trained

Use GoldenGate end-to-end when:

• You have complex Oracle types that DMS cannot handle
• You need bidirectional replication during the transition period
• You already have GoldenGate infrastructure (as we did at Kainga Ora)
• The migration involves Oracle RAC as source (DMS struggles with RAC)

Prerequisites on the source Oracle DB

Before you touch DMS or GoldenGate, the source database needs to be configured correctly. Supplemental logging is the most common thing people miss.

-- Check current supplemental logging status
SELECT SUPPLEMENTAL_LOG_DATA_MIN,
       SUPPLEMENTAL_LOG_DATA_PK,
       SUPPLEMENTAL_LOG_DATA_UI,
       SUPPLEMENTAL_LOG_DATA_FK,
       SUPPLEMENTAL_LOG_DATA_ALL
FROM V$DATABASE;

-- Enable minimum supplemental logging (required for both DMS and GoldenGate)
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

-- For GoldenGate: also enable at table level for the tables you are replicating
ALTER TABLE schema_name.table_name ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

-- Verify redo log mode (must be ARCHIVELOG for CDC)
SELECT LOG_MODE FROM V$DATABASE;

-- If not in ARCHIVELOG mode:
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

For DMS specifically you also need a user with the right privileges:

CREATE USER dms_user IDENTIFIED BY your_password;
GRANT CREATE SESSION TO dms_user;
GRANT SELECT ANY TABLE TO dms_user;
GRANT SELECT ON V_$DATABASE TO dms_user;
GRANT SELECT ON V_$LOGFILE TO dms_user;
GRANT SELECT ON V_$ARCHIVED_LOG TO dms_user;
GRANT SELECT ON V_$LOG TO dms_user;
GRANT SELECT ON V_$LOGMNR_LOGS TO dms_user;
GRANT LOGMINING TO dms_user;  -- Oracle 12c+
GRANT EXECUTE ON DBMS_LOGMNR TO dms_user;
GRANT SELECT ON DBA_OBJECTS TO dms_user;
GRANT SELECT ON DBA_SEGMENTS TO dms_user;
GRANT SELECT ON DBA_SEQUENCES TO dms_user;
GRANT SELECT ON DBA_TABLES TO dms_user;

Terraform for the AWS side

Here is the Terraform to provision the DMS infrastructure. This is what you actually need, not a tutorial skeleton.

# variables.tf
variable "source_oracle_endpoint" {
  description = "On-prem Oracle connection string"
  type        = string
}
variable "db_password" {
  description = "Database password"
  type        = string
  sensitive   = true
}
variable "aws_region" {
  default = "ap-southeast-2"  # Sydney — closest to Auckland
}

# main.tf
terraform {
  required_providers {
    aws = { source = "hashicorp/aws", version = "~> 5.0" }
  }
  backend "s3" {
    bucket = "your-terraform-state-bucket"
    key    = "oracle-migration/terraform.tfstate"
    region = "ap-southeast-2"
  }
}

provider "aws" { region = var.aws_region }

# VPC and subnet group for RDS
resource "aws_db_subnet_group" "oracle_migration" {
  name       = "oracle-migration-subnet-group"
  subnet_ids = ["subnet-xxx", "subnet-yyy"]  # your private subnets

  tags = { Name = "oracle-migration", Project = "db-migration" }
}

# RDS for Oracle (target)
resource "aws_db_instance" "oracle_target" {
  identifier           = "oracle-migration-target"
  engine               = "oracle-ee"
  engine_version       = "19.0.0.0.ru-2024-01.rur-2024-01.r1"
  instance_class       = "db.r6i.2xlarge"
  allocated_storage    = 500
  storage_type         = "gp3"
  storage_encrypted    = true
  license_model        = "bring-your-own-license"

  username = "admin"
  password = var.db_password

  db_subnet_group_name   = aws_db_subnet_group.oracle_migration.name
  vpc_security_group_ids = [aws_security_group.rds_oracle.id]

  backup_retention_period = 7
  deletion_protection     = true
  skip_final_snapshot     = false
  final_snapshot_identifier = "oracle-migration-final-snap"

  parameter_group_name = aws_db_parameter_group.oracle19c.name

  tags = { Name = "oracle-target", Environment = "migration" }
}

# Parameter group for Oracle 19c
resource "aws_db_parameter_group" "oracle19c" {
  family = "oracle-ee-19"
  name   = "oracle-19c-migration"

  parameter {
    name  = "enable_goldengate_replication"
    value = "TRUE"
    apply_method = "pending-reboot"
  }
}

# DMS replication instance
resource "aws_dms_replication_instance" "oracle_migration" {
  replication_instance_id    = "oracle-migration-dms"
  replication_instance_class = "dms.r6i.2xlarge"
  allocated_storage          = 100
  engine_version             = "3.5.2"

  vpc_security_group_ids = [aws_security_group.dms_instance.id]
  replication_subnet_group_id = aws_dms_replication_subnet_group.migration.id

  multi_az            = false  # set true for production HA
  publicly_accessible = false

  tags = { Name = "oracle-dms-instance" }
}

# DMS source endpoint (on-prem Oracle)
resource "aws_dms_endpoint" "oracle_source" {
  endpoint_id   = "oracle-source"
  endpoint_type = "source"
  engine_name   = "oracle"

  server_name = var.source_oracle_endpoint
  port        = 1521
  username    = "dms_user"
  password    = var.db_password
  database_name = "ORCL"

  oracle_settings {
    use_logminer_reader    = true
    security_db_encryption = "AES256"
  }
}

# DMS target endpoint (RDS Oracle)
resource "aws_dms_endpoint" "oracle_target" {
  endpoint_id   = "oracle-target-rds"
  endpoint_type = "target"
  engine_name   = "oracle"

  server_name   = aws_db_instance.oracle_target.address
  port          = 1521
  username      = "admin"
  password      = var.db_password
  database_name = "ORCL"
}

# DMS replication task — full load then CDC
resource "aws_dms_replication_task" "full_load_cdc" {
  replication_task_id      = "oracle-full-load-cdc"
  migration_type           = "full-load-and-cdc"
  replication_instance_arn = aws_dms_replication_instance.oracle_migration.replication_instance_arn
  source_endpoint_arn      = aws_dms_endpoint.oracle_source.endpoint_arn
  target_endpoint_arn      = aws_dms_endpoint.oracle_target.endpoint_arn

  table_mappings = jsonencode({
    rules = [{
      rule-type = "selection"
      rule-id   = "1"
      rule-name = "include-all-tables"
      object-locator = {
        schema-name = "YOUR_SCHEMA"
        table-name  = "%"
      }
      rule-action = "include"
    }]
  })

  replication_task_settings = jsonencode({
    TargetMetadata = {
      TargetSchema = ""
      SupportLobs  = true
      FullLobMode  = false
      LobChunkSize = 64
    }
    FullLoadSettings = {
      TargetTablePrepMode       = "DROP_AND_CREATE"
      CreatePkAfterFullLoad     = false
      StopTaskCachedChangesApplied = false
    }
    Logging = {
      EnableLogging = true
      LogComponents = [
        { Id = "SOURCE_UNLOAD", Severity = "LOGGER_SEVERITY_DEFAULT" },
        { Id = "TARGET_LOAD",   Severity = "LOGGER_SEVERITY_DEFAULT" },
        { Id = "TASK_MANAGER",  Severity = "LOGGER_SEVERITY_DEFAULT" }
      ]
    }
  })
}

Monitoring the migration

Once DMS is running, you need to watch three things:

1. CDC lag — how far behind the target is from the source. Should be under 60 seconds during the steady-state replication phase. Under 5 seconds before you cut over.

# Check DMS task lag via AWS CLI
aws dms describe-replication-tasks \
  --filters Name=replication-task-id,Values=oracle-full-load-cdc \
  --query 'ReplicationTasks[0].ReplicationTaskStats' \
  --output json

2. DMS task status — watch for errors in CloudWatch Logs. The log group is /aws/dms/tasks/oracle-full-load-cdc.

3. Row counts — compare source and target table counts regularly during the full load phase:

-- Run on source
SELECT table_name, num_rows
FROM all_tables
WHERE owner = 'YOUR_SCHEMA'
ORDER BY num_rows DESC;

-- Run on target after full load
SELECT table_name, num_rows
FROM all_tables
WHERE owner = 'YOUR_SCHEMA'
ORDER BY num_rows DESC;

The cutover sequence

This is where most migrations fail. Do this in order:

1. Freeze application writes — put the application into maintenance mode or direct connection away from the source DB.
2. Wait for CDC lag to hit zero — watch the DMS task stats until CDCLatencyTarget and CDCLatencySource both show 0 or near-0 for at least 2 minutes.
3. Stop the DMS task — do not delete it. Stop it so you have a rollback path.
4. Final row count comparison — compare source and target counts for your most important tables.
5. Run validation queries — spot-check business-critical data. For us this was account balances and transaction histories.
6. Update connection strings — update your application config to point at the RDS endpoint.
7. Test application — run smoke tests against the new target.
8. Monitor for 30 minutes — watch CloudWatch metrics and application error rates.

Lessons learned from the Kainga Ora migration

Supplemental logging on RAC — with Oracle RAC you need to enable supplemental logging at the database level AND verify it has replicated across all instances. It is easy to enable on one node and miss the others.

LOB columns slow everything down — if your schema has CLOB or BLOB columns, DMS will be significantly slower. Set LobChunkSize to match your average LOB size, and consider running LOB columns in a separate task.

Archive log retention — during the full load phase (which can take days for large databases), you need archive logs from the start of the task to still be available when CDC begins. Set archive log retention to at least 24 hours longer than you expect the full load to take. We got caught by this once.

Sequences do not migrate automatically — DMS migrates tables, not sequences. You need to separately script and migrate your sequences, then bump them on the target to be higher than the current source value.

The rollback plan — because DMS keeps a task in stopped state, if something goes wrong post-cutover you can restart the task with the source as authoritative and revert the connection string. This is your safety net. Do not delete the task until you are confident the migration is stable.

AWS cost estimate

For a 500GB Oracle database migrated over 2 weeks:

| Resource | Spec | Est. cost | |---|---|---| | DMS replication instance | dms.r6i.2xlarge, 2 weeks | ~$180 | | RDS for Oracle (BYOL) | db.r6i.2xlarge, 500GB gp3 | ~$620/month | | Data transfer | 500GB cross-region or on-prem | ~$45 | | CloudWatch logs | 10GB logs | ~$5 |

Total migration phase: approximately $250 one-time. Ongoing RDS cost depends on your instance class.

---

This is part of my AWS Golden Jacket journey. Next post covers GoldenGate replication setup in detail.

References

• AWS DMS Oracle source documentation
• RDS for Oracle user guide
• Oracle supplemental logging reference