Database Migration Service(DMS) 및 Schema Conversion Tool

# Create a DMS replication instance
aws dms create-replication-instance \
  --replication-instance-identifier my-dms-instance \
  --replication-instance-class dms.t3.medium \
  --allocated-storage 50 \
  --vpc-security-group-ids sg-abc123 \
  --replication-subnet-group-identifier my-dms-subnet-group \
  --multi-az false \
  --publicly-accessible false

# Create a DMS replication task with Full Load + CDC
aws dms create-replication-task \
  --replication-task-identifier sales-migration \
  --source-endpoint-arn arn:aws:dms:us-east-1:123:endpoint:SOURCE \
  --target-endpoint-arn arn:aws:dms:us-east-1:123:endpoint:TARGET \
  --replication-instance-arn arn:aws:dms:us-east-1:123:rep:my-dms-instance \
  --migration-type full-load-and-cdc \
  --table-mappings '{"rules": [{"rule-type": "selection", "rule-id": "1", "rule-name": "all", "object-locator": {"schema-name": "%", "table-name": "%"}, "rule-action": "include"}]}'

# SCT is a GUI desktop tool, but here is the workflow:
# 1. Connect SCT to source Oracle database
# 2. Connect SCT to target Aurora PostgreSQL database
# 3. Run schema assessment: SCT rates conversion complexity per object
# 4. Convert schema automatically (SCT generates PostgreSQL DDL)
# 5. Apply converted DDL to target (SCT executes or exports SQL script)
# 6. Review and manually fix flagged items (e.g., PL/SQL procedures with Oracle-specific syntax)

# Example heterogeneous migration workflow:
# On-premises Oracle -> Amazon Aurora PostgreSQL
#
# Step 1: SCT converts Oracle DDL to PostgreSQL DDL
# Step 2: Apply PostgreSQL DDL to Aurora target
# Step 3: DMS Full Load + CDC copies Oracle table data to Aurora
# Step 4: Validate row counts and checksums
# Step 5: Cut over application connections to Aurora endpoint
# Step 6: Terminate DMS task and replication instance

# Table mapping: include only the 'orders' table, rename schema
{
  'rules': [
    {
      'rule-type': 'selection',
      'rule-id': '1',
      'rule-name': 'select-orders',
      'object-locator': {'schema-name': 'prod_db', 'table-name': 'orders'},
      'rule-action': 'include'
    },
    {
      'rule-type': 'transformation',
      'rule-id': '2',
      'rule-name': 'rename-schema',
      'rule-action': 'convert-uppercase',
      'rule-target': 'schema',
      'object-locator': {'schema-name': 'prod_db'}
    }
  ]
}

# Monitor CDC latency via CloudWatch CLI
aws cloudwatch get-metric-statistics \
  --namespace AWS/DMS \
  --metric-name CDCLatencyTarget \
  --dimensions Name=ReplicationInstanceIdentifier,Value=my-dms-instance \
  --start-time 2024-01-01T00:00:00Z \
  --end-time 2024-01-01T01:00:00Z \
  --period 60 \
  --statistics Average

# Create a DMS Serverless replication
aws dms create-replication \
  --replication-config-identifier my-serverless-migration \
  --replication-type full-load-and-cdc \
  --source-endpoint-arn arn:aws:dms:us-east-1:123:endpoint:SOURCE \
  --target-endpoint-arn arn:aws:dms:us-east-1:123:endpoint:TARGET \
  --compute-config '{
    "MinCapacityUnits": 2,
    "MaxCapacityUnits": 64,
    "MultiAZ": false,
    "ReplicationSubnetGroupId": "my-subnet-group",
    "VpcSecurityGroupIds": ["sg-abc123"]
  }'

# Enable validation in DMS task settings (JSON)
{
  'TargetMetadata': {'SupportLobs': true, 'FullLobMode': false},
  'ValidationSettings': {
    'EnableValidation': true,
    'ValidationMode': 'ROW_LEVEL',
    'ValidationOnly': false,
    'FailureMaxCount': 10000
  }
}