Examkin

Question

Difficulty: HardDatabase Migration and Schema Conversion using DMS and SCT

A Solutions Architect is designing a heterogeneous migration of a highly transactional, 3 TB on-premises Oracle database to an Amazon Aurora PostgreSQL-Compatible Edition DB cluster. The source database contains complex schemas with deep foreign key hierarchies, triggers, and numerous secondary indexes. The migration must minimize source database performance impact, achieve minimal downtime during cutover, and optimize the initial data load speed while ensuring target data integrity and consistency during replication. Which combination of steps should the Solutions Architect take to meet these requirements?

  1. Enable supplemental logging on the source Oracle database. Use AWS SCT to generate the target DDL, separating table and primary key definitions from secondary indexes, foreign keys, and triggers. Apply only the table and primary key definitions to the target. Create a DMS Full Load task and a separate DMS CDC task. Run the Full Load task with target table preparation mode set to Do Nothing. After completion, apply the remaining indexes, foreign keys, and triggers to the target, then start the CDC task from the Full Load start time.Answer
  2. B
    Use AWS SCT to convert the schema and apply it to the target. Create a single DMS Full Load and CDC task with target table preparation mode set to Do Nothing. Start the task immediately without modifying the logging configuration on the source Oracle database, and allow DMS to automatically handle the initial data load and transition to continuous replication.
  3. C
    Enable supplemental logging on the source Oracle database. Use AWS SCT to convert the schema. Create a single DMS Full Load and CDC task with target table preparation mode set to Drop and Create. Run the task to let DMS automatically handle target table creation, index management, and continuous replication, and then use AWS SCT to re-apply stored procedures and triggers after the task enters the replication phase.
  4. D
    Use Oracle Data Guard to establish continuous replication from the on-premises database directly to the target Aurora PostgreSQL DB cluster, classifying the migration as a replatforming path to avoid schema conversion overhead and minimize cutover downtime.

Answer

Enable supplemental logging on the source Oracle database, use AWS Schema Conversion Tool (SCT) to separate tables/primary keys from constraints/indexes, load data using a separate AWS Database Migration Service (DMS) Full Load task with Do Nothing mode, apply indexes and constraints, and then run a separate DMS Change Data Capture (CDC) task from the Full Load start time.
The correct sequence of actions enables supplemental logging on the source Oracle database to allow Change Data Capture (CDC). Using AWS Schema Conversion Tool (SCT) to separate table/primary key definitions from secondary indexes, foreign keys, and triggers, and applying only the tables/primary keys initially, optimizes the performance of the AWS Database Migration Service (DMS) Full Load task. Running separate Full Load and CDC tasks prevents DMS from automatically moving to replication before constraints and secondary indexes are applied. Applying the remaining indexes, foreign keys, and triggers after the full load completes ensures rapid index creation and data integrity validation. Starting the CDC task from the Full Load start time guarantees that no transactions are missed during the data migration process.

Step-by-Step Solution

1
Configure the source database logging
Supplemental logging is enabled on the source Oracle database.
AWS DMS Change Data Capture (CDC) requires supplemental logging to be enabled on Oracle to capture the full row changes in the redo logs.
2
Convert and separate schema DDL using AWS SCT
Target schema DDL is generated and split into table structures (with primary keys) and secondary structures (indexes, foreign keys, triggers).
Applying only tables and primary keys before the full load prevents the performance overhead of updating indexes and validating constraints for every inserted row.
3
Run DMS Full Load task
Initial dataset is loaded into the target Aurora PostgreSQL database with target table preparation mode set to Do Nothing.
Setting the mode to Do Nothing ensures that the pre-created schema structures from AWS SCT are preserved during the bulk load.
4
Apply secondary schema objects and start CDC
Secondary indexes, foreign keys, and triggers are created on the target, and the DMS CDC task is started from the Full Load start timestamp.
Applying indexes after the bulk load is significantly faster. Starting CDC from the Full Load start time ensures no transactional changes are lost during the load period.

Key Concept

Heterogeneous database migration lifecycle optimization using AWS SCT and AWS DMS
Rate this question

Topics

Database Migration and Schema Conversion using DMS and SCT

More questions from this topic