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) RemediationThe flow:
- CloudWatch detects a DMS metric breach (e.g., CDC latency > threshold)
- EventBridge captures the alarm state change
- Lambda calls the DevOps Agent webhook
- 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
- Create Agent Space — Name it
dms-oracle-aurora-pgsql. Add AWS accounts containing DMS, RDS Oracle, and Aurora PostgreSQL resources.
- Generate webhook — In Capabilities -> Webhooks -> Add webhook. Store
WEBHOOK_URLandWEBHOOK_SECRETin AWS Secrets Manager.
- Create Lambda function — Receives EventBridge events, calls DevOps Agent webhook. Reference: Invoking DevOps Agent through Webhook .
- 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:
-
CDCLatencySourcepeaked at 340s (threshold: 120s)
- Oracle CPU spiked from 3% to 68%, WriteIOPS from 5 to 4,800
- LogMiner processing capped at ~3,800 rows/sec vs 180K incoming changes/min
- DMS instance and Aurora target are healthy — bottleneck is source-side
- 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:
- Task stopped with
Error— 14/22 tables loaded, 3 in error state
- TARGET_LOAD log shows three error patterns
- Aurora PostgreSQL metrics healthy (CPU 12%, connections stable) — data-level errors only
- 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 mappingRoot 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_metadataWHERE 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:
- Deploy source Oracle + target Aurora PostgreSQL + DMS task (use this CloudFormation template )
- Add CloudWatch alarms from Step 1
- Wire EventBridge + Lambda from Step 2
- 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;/- 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);- 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.
Enjoyed reading this content? Let the author know!
Your likes, comments, shares, and saves help creators reach more builders.
Loading recommendations
Loading article