AWS Builder Center

Troubleshoot RDS Oracle to Aurora PostgreSQL DMS Migrations with AWS DevOps Agent

Use AWS DevOps Agent to automatically investigate and remediate common AWS DMS issues when migrating from RDS Oracle to Aurora PostgreSQL — CDC latency, data type conversion failures, and more.

Attribute
Detail
AWS Level
300 - Advanced
Time to complete
45 minutes
Services used
AWS DMS, AWS DevOps Agent, Amazon Aurora PostgreSQL, Amazon RDS for Oracle, Amazon CloudWatch, Amazon EventBridge, AWS Lambda

What you'll learn

  • How to configure CloudWatch alarms for key DMS metrics in an Oracle-to-Aurora PostgreSQL migration
  • How to wire EventBridge -> Lambda -> DevOps Agent webhook for automated investigation
  • How DevOps Agent investigates two real-world scenarios: CDC source latency and full load data type conversion failures

The problem

Troubleshooting AWS DMS issues in Oracle-to-Aurora PostgreSQL migrations is time-consuming. You're jumping between DMS task logs, CloudWatch metrics, Oracle system views, and Aurora PostgreSQL pg_stat tables — correlating timestamps manually.
Common pain points:
  • CDC falls behind and you don't know if it's source (Oracle redo), replication instance, or target (Aurora write throughput)
  • Full load fails on specific tables but the error is buried in DMS logs across multiple components
  • Data type mismatches between Oracle and PostgreSQL surface only at runtime
DevOps Agent solves this by automatically correlating telemetry across all three layers (source -> DMS -> target) and delivering root cause analysis with remediation steps in minutes.

Solution architecture

CloudWatch Alarm -> EventBridge Rule -> Lambda Function -> DevOps Agent
(DMS metrics) (ALARM state) (webhook call) (investigation)
| |
AWS DMS Task Root Cause +
(Oracle -> Aurora PG) Remediation
The flow:
  1. CloudWatch detects a DMS metric breach (e.g., CDC latency > threshold)
  1. EventBridge captures the alarm state change
  1. Lambda calls the DevOps Agent webhook
  1. DevOps Agent investigates autonomously and delivers findings

Prerequisites

  • AWS DevOps Agent Space with access to your DMS account
  • Active DMS replication task (RDS Oracle -> Aurora PostgreSQL) with CloudWatch logging enabled
  • Lambda execution role with Secrets Manager read access
  • (Optional) Slack integration for incident coordination

Step 1: Configure CloudWatch alarms

CDC source latency

Fires if DMS falls >2 minutes behind reading Oracle redo logs:
{
"AlarmName": "dms-cdc-source-latency-ora-to-aupg",
"Namespace": "AWS/DMS",
"MetricName": "CDCLatencySource",
"Dimensions": [
{"Name": "ReplicationInstanceIdentifier", "Value": "<your-instance-id>"},
{"Name": "ReplicationTaskIdentifier", "Value": "<your-task-id>"}
],
"Statistic": "Average",
"Period": 30,
"EvaluationPeriods": 2,
"DatapointsToAlarm": 2,
"Threshold": 120,
"ComparisonOperator": "GreaterThanThreshold",
"TreatMissingData": "breaching"
}

CDC target latency

Fires if DMS falls >30 seconds behind applying changes to Aurora PostgreSQL:
{
"AlarmName": "dms-cdc-target-latency-ora-to-aupg",
"Namespace": "AWS/DMS",
"MetricName": "CDCLatencyTarget",
"Dimensions": [
{"Name": "ReplicationInstanceIdentifier", "Value": "<your-instance-id>"},
{"Name": "ReplicationTaskIdentifier", "Value": "<your-task-id>"}
],
"Statistic": "Average",
"Period": 10,
"EvaluationPeriods": 2,
"DatapointsToAlarm": 2,
"Threshold": 30,
"ComparisonOperator": "GreaterThanThreshold",
"TreatMissingData": "breaching"
}
Tip: Disable alarms when you stop or decommission a DMS task to avoid false positives.

Step 2: Set up DevOps Agent

  1. Create Agent Space — Name it  dms-oracle-aurora-pgsql . Add AWS accounts containing DMS, RDS Oracle, and Aurora PostgreSQL resources.
  1. Generate webhook — In Capabilities -> Webhooks -> Add webhook. Store  WEBHOOK_URL  and  WEBHOOK_SECRET  in AWS Secrets Manager.
  1. Create Lambda function — Receives EventBridge events, calls DevOps Agent webhook. Reference:  Invoking DevOps Agent through Webhook .
  1. Create EventBridge rule:
{
"source": ["aws.cloudwatch"],
"detail-type": ["CloudWatch Alarm State Change"],
"detail": {
"state": {"value": ["ALARM"]},
"alarmName": [{"prefix": "dms-"}]
}
}
Target: the Lambda function from step 3.

Scenario 1: CDC source latency spike

Trigger: A batch ETL runs bulk INSERTs on Oracle, generating 12 GB of redo in 20 minutes.  CDCLatencySource  breaches 210 seconds.

What DevOps Agent finds:

  1.  CDCLatencySource  peaked at 340s (threshold: 120s)
  1. Oracle CPU spiked from 3% to 68%, WriteIOPS from 5 to 4,800
  1. LogMiner processing capped at ~3,800 rows/sec vs 180K incoming changes/min
  1. DMS instance and Aurora target are healthy — bottleneck is source-side
  1. Database-level supplemental logging generating unnecessary redo for non-migrated tables

Root cause

Batch ETL redo volume exceeded LogMiner capacity. Database-level supplemental logging and undersized redo log groups (200 MB) caused checkpoint contention.

Remediation (provided by DevOps Agent)

Immediate: Throttle or reschedule the ETL during active CDC.
Short-term:
-- Switch to table-level supplemental logging (only migrated tables)
ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;
ALTER TABLE HR.EMPLOYEES ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
ALTER TABLE HR.DEPARTMENTS ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
Long-term: Increase redo log groups to 1 GB. Schedule DMS task pause/resume around known heavy-write windows.

Scenario 2: Full load failure — data type conversion errors

Trigger: Full load starts for a schema with Oracle CLOB (containing JSON), NUMBER without precision, and INTERVAL types. DMS task stops with errors on 3 of 22 tables.

What DevOps Agent finds:

  1. Task stopped with  Error  — 14/22 tables loaded, 3 in error state
  1. TARGET_LOAD log shows three error patterns
  1. Aurora PostgreSQL metrics healthy (CPU 12%, connections stable) — data-level errors only
  1. No column transformation rules configured; LOB settings insufficient
Error patterns identified:
"Invalid input syntax for type json" -> CLOB with malformed JSON -> PostgreSQL jsonb
"Numeric field overflow" -> Oracle NUMBER (no precision) -> numeric overflow
"Cannot cast type bytea to interval" -> Oracle INTERVAL -> no DMS mapping

Root cause

Three data type incompatibilities not addressed in DMS task configuration: invalid JSON rejected by PostgreSQL's strict jsonb validation, unbounded Oracle NUMBER exceeding target precision, and INTERVAL types unsupported by default DMS mapping.

Remediation (provided by DevOps Agent)

Fix 1 — JSON validation: Map CLOB to  TEXT  instead of  jsonb , or validate JSON pre-migration:
SELECT rowid, dbms_lob.substr(order_metadata, 100, 1)
FROM orders.order_metadata
WHERE json_valid(order_metadata) = 0;
Fix 2 — Numeric overflow: Add transformation rules:
{
"rule-type": "transformation",
"rule-action": "change-data-type",
"rule-target": "column",
"object-locator": {"schema-name": "FINANCE", "table-name": "TRANSACTIONS", "column-name": "AMOUNT"},
"data-type": {"type": "numeric", "precision": 38, "scale": 10}
}
Fix 3 — INTERVAL types: Exclude from DMS, populate post-migration:
{
"rule-type": "transformation",
"rule-action": "remove-column",
"rule-target": "column",
"object-locator": {"schema-name": "HR", "table-name": "EMPLOYEE_TENURE", "column-name": "TENURE_INTERVAL"}
}
Fix 4 — LOB settings: Increase  LobMaxSize  to match actual CLOB sizes:
{"LOBSettings": {"mode": "limited", "LobMaxSize": 256}}

Testing this solution

You can validate this architecture using any of these approaches:
Approach
Description
AWS DMS SBS Lab
Follow the
 Oracle-to-PostgreSQL guide 
, add alarms + DevOps Agent
Workshop Studio
Use
 aurora-and-database-migration-labs 
as base
Your own account
Any existing RDS Oracle -> Aurora PG DMS task + alarm/EventBridge setup from this post

Quick test steps:

  1. Deploy source Oracle + target Aurora PostgreSQL + DMS task (use  this CloudFormation template )
  1. Add CloudWatch alarms from Step 1
  1. Wire EventBridge + Lambda from Step 2
  1. Trigger Scenario 1: Generate heavy writes on Oracle:
BEGIN
FOR i IN 1..500000 LOOP
INSERT INTO test_table VALUES (i, 'load_test_' || i, SYSDATE);
END LOOP;
COMMIT;
END;
/
  1. Trigger Scenario 2: Create a table with problematic types and start full load:
CREATE TABLE problem_types (
id NUMBER,
metadata CLOB DEFAULT '{"invalid": json}', -- malformed JSON
amount NUMBER, -- no precision
tenure INTERVAL YEAR TO MONTH -- unsupported
);
  1. Watch DevOps Agent investigate automatically via the Agent Space console or Slack.

Cost considerations

Component
Cost
DevOps Agent
Per-second billing for investigation time
CloudWatch Alarms
~$0.10/alarm/month
EventBridge
$1.00/million events
Lambda
Negligible (few invocations/day)
Delete unused resources after testing to avoid ongoing charges.

Summary

DevOps Agent eliminates the manual correlation work in DMS troubleshooting. For Oracle-to-Aurora PostgreSQL migrations specifically, it handles the added complexity of heterogeneous data type mismatches, LOB migration issues, and CDC semantics unique to this path.
Key takeaways:
  • Set CloudWatch alarms on  CDCLatencySource ,  CDCLatencyTarget , and task state
  • Wire EventBridge -> Lambda -> DevOps Agent for automatic investigation
  • DevOps Agent correlates Oracle, DMS, and Aurora PostgreSQL telemetry in a single pass
  • Remediation steps are actionable — SQL commands, JSON configs, and architecture recommendations

Resources



About the author: Chetan Nandikanti is a Senior Database Specialist Solutions Architect at Amazon Web Services, based in Grapevine, Texas. He works with customers to provide assistance with designing, migrating, and optimizing database solutions on AWS, including integrating operational data with Storage, Analytics, and AI/ML services.
Any opinions in this article are those of the individual author and may not reflect the opinions of AWS.
Enjoyed reading this content? Let the author know!

Your likes, comments, shares, and saves help creators reach more builders.

Loading recommendations

Loading article