Logo
AWS AWS DMS CDC database migration database

AWS DMS: rising CDC latency and replication task failure

Fix rising CDC latency and AWS DMS task failures: diagnose CDCLatencySource vs CDCLatencyTarget, tables without a primary key, LOB truncation, and replication instance sizing.

Jerzy Kopaczewski ·
Your AWS DMS task was meant to catch up on changes before the production cutover, but CDC latency is rising instead of falling - the cutover never converges to zero. Or the task fails during full load on a table without a primary key, or silently truncates data in LOB columns. This runbook shows how to diagnose the cause and get replication back on track.

This runbook covers troubleshooting an AWS DMS task during a database migration. For the full guide to the service itself, see AWS DMS - Database Migration Service Guide. For migration planning and delivery, book a consulting session.

Symptoms

The DMS task is in the “Running” state but never approaches completion, or it moves to “Failed”/”Error”. Typical signals:

# CloudWatch metrics show rising CDC latency (in seconds)
aws cloudwatch get-metric-statistics \
  --namespace AWS/DMS \
  --metric-name CDCLatencyTarget \
  --dimensions Name=ReplicationInstanceIdentifier,Value=dms-prod \
               Name=ReplicationTaskIdentifier,Value=oracle-to-aurora \
  --start-time $(date -u -d '1 hour ago' +%Y-%m-%dT%H:%M:%S) \
  --end-time $(date -u +%Y-%m-%dT%H:%M:%S) \
  --period 60 --statistics Maximum \
  --query 'Datapoints | sort_by(@, &Timestamp) | [-10:].[Timestamp,Maximum]'

# Per-table task statistics (which tables are in an error state)
aws dms describe-table-statistics \
  --replication-task-arn arn:aws:dms:eu-west-1:123456789012:task:ORACLE2AURORA \
  --query 'TableStatistics[?TableState!=`Table completed`].{schema:SchemaName, table:TableName, state:TableState, fullLoadRows:FullLoadRows}' \
  --output table

# Common messages in task logs (CloudWatch Logs, group dms-tasks-*):
# "Some changes from the source database were not applied due to missing primary key"
# "LOB column data was truncated" / "Limited LOB mode"
# "Last Error ... Task error notification received from subtask"

The CDCLatencyTarget value climbs over time (never stabilises), or the table statistics show a Table error state.

Cause

Rising CDC latency and task errors usually come down to one of four causes:

  • Replication instance too small: under a high transaction volume on the source, the instance can’t keep up with reading and applying changes. Rule of thumb: ~1 vCPU per 50 GB of loaded data, or per 100 tables under CDC. Too small a class (e.g. dms.t3) under production load is the most common cause of rising latency.
  • Tables without a primary key: CDC identifies rows by key. A table with no primary key or unique index means UPDATE/DELETE operations have no way to target a specific row - the task reports an error, or in the worst case applies the change to every matching row.
  • LOB truncation (Limited LOB mode): the default DMS mode truncates BLOB/CLOB/BYTEA columns above a set LobMaxSize. This is silent data loss - the task doesn’t fail, but the target data is incomplete.
  • CDCLatencySource vs CDCLatencyTarget: high CDCLatencySource points to a problem on the source side (e.g. missing supplemental logging in Oracle, slow log reads, LogMiner under load). High CDCLatencyTarget with low source points to a bottleneck on the apply side of the target (instance size, indexes, triggers).

Solution

A) Determine which side the bottleneck is on (source vs target):

# Compare both latency metrics over the same window
for M in CDCLatencySource CDCLatencyTarget; do
  echo "=== $M ==="
  aws cloudwatch get-metric-statistics \
    --namespace AWS/DMS --metric-name $M \
    --dimensions Name=ReplicationInstanceIdentifier,Value=dms-prod \
                 Name=ReplicationTaskIdentifier,Value=oracle-to-aurora \
    --start-time $(date -u -d '30 minutes ago' +%Y-%m-%dT%H:%M:%S) \
    --end-time $(date -u +%Y-%m-%dT%H:%M:%S) \
    --period 60 --statistics Average \
    --query 'Datapoints | sort_by(@, &Timestamp) | [-5:].[Timestamp,Average]'
done
# High source -> problem on the source (change logging).
# High target with low source -> problem on the target (sizing/indexes).

B) High CDCLatencySource - fix change logging on the source (Oracle):

-- Check supplemental logging (required for CDC from Oracle)
SELECT supplemental_log_data_min, supplemental_log_data_pk, supplemental_log_data_ui
FROM v$database;

-- Enable if disabled
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX) COLUMNS;

-- For MySQL: binlog in ROW format; for PostgreSQL: wal_level = logical

C) High CDCLatencyTarget - scale the replication instance or parallelise:

# Scale up the replication instance class (no task recreation needed)
aws dms modify-replication-instance \
  --replication-instance-arn arn:aws:dms:eu-west-1:123456789012:rep:DMSPROD \
  --replication-instance-class dms.r5.2xlarge \
  --apply-immediately

# In task settings, increase apply parallelism (task settings JSON):
# "TargetMetadata": { "ParallelApplyThreads": 8, "ParallelApplyBufferSize": 1000 }
# and for full load: "FullLoadSettings": { "MaxFullLoadSubTasks": 8 }

D) Tables without a primary key - add a key or migrate as full load only:

-- Identify tables without a primary key on the source (Oracle)
SELECT t.owner, t.table_name
FROM all_tables t
WHERE NOT EXISTS (
  SELECT 1 FROM all_constraints c
  WHERE c.table_name = t.table_name AND c.owner = t.owner
    AND c.constraint_type = 'P')
  AND t.owner = 'APP_SCHEMA';

-- Add a primary key before migration (preferred) OR
-- flag these tables for Full Load only (no CDC) in the table mapping.

E) LOB truncation - switch to Full LOB or size LobMaxSize:

# In task settings (task settings JSON) for tables with large LOBs:
# "TargetMetadata": {
#   "SupportLobs": true,
#   "FullLobMode": true,          # full LOB (slower, but no truncation)
#   "LobChunkSize": 64,
#   "LimitedSizeLobMode": false
# }
# OR Inline/Limited mode with LobMaxSize (KB) sized to the largest LOB.

# After changing settings - the task needs to be stopped and resumed
aws dms stop-replication-task  --replication-task-arn arn:aws:dms:eu-west-1:123456789012:task:ORACLE2AURORA
aws dms start-replication-task --replication-task-arn arn:aws:dms:eu-west-1:123456789012:task:ORACLE2AURORA \
  --start-replication-task-type resume-processing

Prevention: monitor CDCLatencyTarget from day one of the migration, not on cutover day. Enable the DMS Data Validation task to catch data discrepancies (including truncated LOBs). For large databases, size the replication instance with headroom and set a CloudWatch alarm on CDCLatencyTarget above a threshold (e.g. 300s).

Validation

# 1. CDC latency drops and stabilises near zero
aws cloudwatch get-metric-statistics \
  --namespace AWS/DMS --metric-name CDCLatencyTarget \
  --dimensions Name=ReplicationInstanceIdentifier,Value=dms-prod \
               Name=ReplicationTaskIdentifier,Value=oracle-to-aurora \
  --start-time $(date -u -d '15 minutes ago' +%Y-%m-%dT%H:%M:%S) \
  --end-time $(date -u +%Y-%m-%dT%H:%M:%S) \
  --period 60 --statistics Average
# Expected: downward trend, ultimately < 5s

# 2. No table is in an error state
aws dms describe-table-statistics \
  --replication-task-arn arn:aws:dms:eu-west-1:123456789012:task:ORACLE2AURORA \
  --query 'TableStatistics[?TableState==`Table error`].{schema:SchemaName, table:TableName}' \
  --output table
# Expected: empty list

# 3. Data validation (if enabled) reports no discrepancies
aws dms describe-table-statistics \
  --replication-task-arn arn:aws:dms:eu-west-1:123456789012:task:ORACLE2AURORA \
  --query 'TableStatistics[].{table:TableName, validation:ValidationState, pending:ValidationPendingRecords, failed:ValidationFailedRecords}' \
  --output table
# Expected: ValidationState = "Validated", failed = 0

If CDC latency is stably low, no table is in an error state, and data validation passes with no discrepancies, the task is ready for cutover. Keep replication running until you switch over, and monitor latency until you stop writes on the source.

Rising CDC latency means you can't perform a clean production cutover - the cutover window never closes. On a minimal-downtime migration, that's a direct risk of slipping the deadline. LOB truncation is more dangerous: the task doesn't report a failure, so incomplete data reaches production unnoticed until users report it. That's why data validation and latency monitoring go in from the start, not on cutover day.

 

Jerzy Kopaczewski

Is your DMS database migration dragging on?

Book a free 30-minute call. We'll review your DMS configuration, pick the right mode and sizing, and get the migration to a clean cutover.

Book a call