AWS DMS for Data Migration and more

 🧠 DBA Tip: AWS DMS Isn’t Just for Migrations — It’s a Hidden Gem for Continuous Replication

If you’ve ever had to move a live production database with near-zero downtime, AWS Database Migration Service (DMS) can save your weekend.

Here’s a quick example setup I recently used to replicate an on-prem Oracle DB to Amazon Aurora PostgreSQL — while keeping both in sync until cutover 👇

---

⚙️ Step 1: Create a Replication Instance

aws dms create-replication-instance \
  --replication-instance-identifier dms-repl-prod \
  --replication-instance-class dms.r6i.large \
  --allocated-storage 100 \
  --multi-az

---

⚙️ Step 2: Define Source and Target Endpoints

aws dms create-endpoint \
  --endpoint-identifier oracle-src \
  --endpoint-type source \
  --engine-name oracle \
  --username admin \
  --password '****' \
  --server-name oradb01.example.com \
  --port 1521 \
  --database-name PRODDB

aws dms create-endpoint \
  --endpoint-identifier aurora-target \
  --endpoint-type target \
  --engine-name aurora-postgresql \
  --username dbauser \
  --password '****' \
  --server-name aurora-cluster.cluster-xyz.us-east-1.rds.amazonaws.com \
  --port 5432 \
  --database-name prod_migrated

---

⚙️ Step 3: Create and Start the Migration Task

aws dms create-replication-task \
  --replication-task-identifier oracle-to-aurora-task \
  --source-endpoint-arn arn:aws:dms:...:endpoint/oracle-src \
  --target-endpoint-arn arn:aws:dms:...:endpoint/aurora-target \
  --migration-type full-load-and-cdc \
  --table-mappings file://table-mappings.json \
  --replication-task-settings file://task-settings.json

Then start it:

aws dms start-replication-task \
  --replication-task-arn arn:aws:dms:...:task/oracle-to-aurora-task \
  --start-replication-task-type start-replication

---

🔍 Pro Tips for DBAs:

• Use --migration-type full-load-and-cdc to keep the target updated while users are still writing to the source.

• Check task status & latency:

  aws dms describe-replication-tasks \
    --filters "Name=replication-task-id,Values=oracle-to-aurora-task" \
    --query "ReplicationTasks[*].{Status:Status,CDCStartTime:ReplicationStats.FullLoadProgressPercent}"
  

• Always validate with:

  aws dms describe-table-statistics --replication-task-arn <task-arn>
  

• Combine with AWS SCT (Schema Conversion Tool) for object conversion before DMS handles data replication.

---

💡 Bonus use case: You can repurpose DMS for ongoing replication — e.g., feeding an analytics cluster or keeping a DR instance up to date.

Curious — how many of you have used DMS for continuous sync rather than one-time migrations?

#AWS #DMS #DBA #CloudMigration #PostgreSQL #Oracle #AWSCLI #DataEngineering #DevOps

Useful AWS DMS Postgres & Oracle SQL Queries

 When working with AWS Database Migration Service (DMS) and migrating between PostgreSQL and Oracle, there are key SQL queries you’ll want to use for monitoring, validating, and troubleshooting. Below is a categorized list of useful SQL queries for both PostgreSQL and Oracle, especially tailored to DMS contexts.

---

🔍 General Categories

1. Schema Comparison

2. Row Count Validation

3. Data Type Check

4. Change Data Capture (CDC) Monitoring

5. Latency and Lag Analysis

6. DMS-specific Tables & Metadata

7. Performance Troubleshooting

---

📘 PostgreSQL Useful Queries

1. List All Tables

SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
  AND table_schema NOT IN ('pg_catalog', 'information_schema');

2. Row Count per Table

SELECT schemaname, relname AS table_name, n_live_tup AS row_count
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC;

3. Check Table Size

SELECT relname AS table_name,
       pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

4. Check Replication/CDC Lag

If logical replication is enabled:

SELECT pid, state, sent_lsn, write_lsn, flush_lsn, replay_lsn,
       pg_wal_lsn_diff(sent_lsn, replay_lsn) AS replication_lag_bytes
FROM pg_stat_replication;

5. Get Indexes for a Table

SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'your_table';

---

📕 Oracle Useful Queries

1. List All User Tables

SELECT owner, table_name
FROM all_tables
WHERE owner NOT IN ('SYS', 'SYSTEM');

2. Row Count per Table

SELECT owner, table_name, num_rows
FROM all_tables
WHERE owner NOT IN ('SYS', 'SYSTEM');

num_rows is based on stats — use ANALYZE TABLE or DBMS_STATS.GATHER_TABLE_STATS for current counts.

3. Actual Row Count

SELECT COUNT(*) FROM schema_name.table_name;

4. Table Size in MB

SELECT segment_name, segment_type, bytes / (1024*1024) AS size_mb
FROM dba_segments
WHERE segment_type='TABLE' AND owner='YOUR_SCHEMA';

5. Indexes for a Table

SELECT index_name, column_name
FROM all_ind_columns
WHERE table_name = 'YOUR_TABLE';

---

🛠️ DMS Metadata Tables (Query from Target Database)

If DMS is replicating these:

SELECT * FROM awsdms_apply_exceptions;
SELECT * FROM awsdms_heartbeat;

Use these to detect:

• Apply lag

• Last replicated change

• Failed rows or skipped rows

---

✅ Validation Queries

Compare Row Counts (PostgreSQL vs Oracle)

On PostgreSQL:

SELECT COUNT(*) FROM public.my_table;

On Oracle:

SELECT COUNT(*) FROM my_schema.my_table;

You can automate validation using EXCEPT logic or checksum approaches.

---

📈 Monitoring Query Example (DMS Task Monitoring)

Use AWS DMS CloudWatch Logs and ReplicationInstanceMetrics to monitor:

• CDCLatencySource

• CDCLatencyTarget

• FullLoadThroughputRowsSource

• FullLoadThroughputRowsTarget