Examkin

Question

Difficulty: Very hardDatabase Migration and Schema Conversion using DMS and SCT

An organization is planning to migrate a critical, highly transactional Oracle database to an Amazon Aurora PostgreSQL-Compatible Edition DB cluster. The database contains several tables with large amounts of data, some of which contain Large Objects (LOBs) and do not have primary keys defined. The migration must have near-zero downtime, so the team is using the AWS Schema Conversion Tool (SCT) and AWS Database Migration Service (DMS) with Change Data Capture (CDC) enabled. Minimal supplemental logging is enabled at the database level on the source Oracle database. During testing, the DMS task completes the full load phase successfully. However, during the CDC phase, the task shows a status of 'Running with errors'. An analysis of the target database shows that while INSERT operations are being replicated, UPDATE and DELETE operations are missing for tables that do not have primary keys. Furthermore, tables containing LOB columns are experiencing severe performance degradation during replication. Which combination of actions will resolve these issues while maintaining replication performance?

  1. Enable table-level supplemental logging on the source Oracle database for all columns of tables without primary keys, and primary key columns for tables with primary keys. Configure the AWS DMS replication task to use Limited LOB mode with a specified maximum LOB size to optimize replication speed.Answer
  2. B
    Enable database-level supplemental logging for all columns on the source Oracle database. Configure the AWS DMS task to use Full LOB mode, and increase the LOB chunk size parameter to improve replication performance.
  3. C
    Change the AWS DMS source endpoint settings to use Oracle Binary Reader instead of Oracle LogMiner to bypass the requirement for table-level supplemental logging. Configure the AWS DMS task to exclude LOB columns during CDC, and migrate LOB columns using a separate AWS SCT agent task.
  4. D
    Install the AWS SCT extension pack on the target Aurora PostgreSQL database to automatically generate primary keys using UUIDs. Configure the AWS DMS task to use Inline LOB mode with a custom replication task setting to sync tables without primary keys.

Answer

Enable table-level supplemental logging on the source Oracle database for all columns of tables without primary keys, and primary key columns for tables with primary keys. Configure the AWS DMS replication task to use Limited LOB mode with a specified maximum LOB size to optimize replication speed.
The correct action is to enable table-level supplemental logging on the source Oracle database for all columns of tables without primary keys, and primary key columns for tables with primary keys. This allows AWS DMS to parse the redo logs and correctly identify which rows need to be updated or deleted on the target. Additionally, using Limited LOB mode with a specified maximum LOB size optimizes replication performance because it avoids the need to query the source database for each LOB row individually, which is a major performance bottleneck in Full LOB mode.

Step-by-Step Solution

1
Configure table-level supplemental logging on the source database.
Tables without primary keys now have supplemental logging enabled for all columns. Tables with primary keys have supplemental logging for their primary keys.
For AWS DMS to replicate UPDATE and DELETE operations during CDC, it must be able to identify the unique rows being changed in the redo logs. For tables without primary keys, this requires logging all columns.
2
Configure LOB settings on the AWS DMS task.
The replication task is configured to use Limited LOB mode with a specified maximum LOB size.
Limited LOB mode is significantly faster than Full LOB mode because DMS allocates a buffer of the specified maximum LOB size and transfers the data inline, avoiding additional round-trips to the source database.

Key Concept

AWS DMS requires appropriate supplemental logging on Oracle source databases to replicate update and delete changes during CDC, and performance is optimized by selecting Limited LOB mode instead of Full LOB mode.
Rate this question

Topics

Database Migration and Schema Conversion using DMS and SCT

More questions from this topic