Examkin

Question

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

An enterprise is migrating a legacy on-premises Oracle 19c database to an Amazon Aurora PostgreSQL-Compatible Edition DB cluster. The migration strategy utilizes the AWS Schema Conversion Tool (SCT) for schema conversion and AWS Database Migration Service (DMS) for full-load and Ongoing Replication (Change Data Capture - CDC).

The source database contains several tables that do not have primary keys. To prepare the source database for CDC, the database administrator puts the Oracle database in ARCHIVELOG mode and enables database-level minimal supplemental logging by running:
`ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;`

During the migration, the AWS DMS task completes the Full Load phase successfully. However, when the task transitions to the CDC phase, updates to the tables without primary keys are not replicated to the target Aurora DB cluster, and the DMS task log displays errors indicating that target rows cannot be located.

Which of the following describes the root cause of this issue and the correct resolution?

  1. A
    AWS DMS requires the Oracle LogMiner engine to capture changes for tables without primary keys. The replication task must be reconfigured to use the Oracle Binary Reader instead of LogMiner by setting the source endpoint connection attribute `useLogminerReader=false` and granting read access to the Oracle redo logs.
  2. Oracle writes only the modified columns to the redo logs by default, which prevents AWS DMS from identifying target rows for tables without primary keys. To resolve this, supplemental logging must be configured for all columns of these tables on the source database by executing `ALTER TABLE ... ADD SUPPLEMENTAL LOG GROUP ... ALWAYS` for each table.Answer
  3. C
    The AWS DMS source database user lacks the `SELECT ANY TRANSACTION` privilege required to read supplemental log data for non-keyed tables. The database administrator must grant the `SELECT ANY TRANSACTION` privilege to the DMS user and restart the task with the `TargetTablePrepMode` set to `TRUNCATE_BEFORE_LOAD`.
  4. D
    AWS DMS does not support CDC replication of tables without primary keys to PostgreSQL targets. The schema must be refactored using AWS SCT to add a surrogate primary key column to the source Oracle tables, followed by enabling database-level primary key supplemental logging using `ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS`.

Answer

Oracle writes only the modified columns to the redo logs by default, which prevents AWS DMS from identifying target rows for tables without primary keys. To resolve this, supplemental logging must be configured for all columns of these tables on the source database by executing `ALTER TABLE ... ADD SUPPLEMENTAL LOG GROUP ... ALWAYS` for each table.
The correct option is that Oracle writes only the modified columns to the redo logs by default, which prevents AWS DMS from identifying target rows for tables without primary keys. To resolve this, supplemental logging must be configured for all columns of these tables on the source database by executing `ALTER TABLE ... ADD SUPPLEMENTAL LOG GROUP ... ALWAYS` for each table.

Step-by-Step Solution

1
Analyze the DMS CDC log errors regarding target rows that cannot be located during the replication of tables without primary keys.
Identify that the issue is specific to the CDC phase and tables lacking a primary key.
Since the Full Load phase completed successfully, the issue must lie within the transactional changes (redo logs) captured during CDC.
2
Review the default behavior of Oracle redo logging and AWS DMS CDC requirements.
Determine that Oracle only logs modified columns by default. For tables without primary keys, DMS cannot identify which row on the target corresponds to the incoming update/delete from the redo log unless all columns are logged.
DMS requires unchanged column values to formulate the WHERE clause for target updates and deletes on tables without primary keys.
3
Examine the database-level supplemental logging configuration.
Note that minimal database-level supplemental logging (`ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;`) was enabled, but table-level supplemental logging for tables without primary keys was omitted.
Database-level minimal supplemental logging only provides the minimal information needed to identify transactions, not the full column data needed for non-keyed tables.
4
Formulate the resolution to configure table-level supplemental logging.
Add a supplemental log group that contains all columns (using the ALWAYS clause) for each table without a primary key on the source Oracle database.
This forces Oracle to write the values of all columns to the redo log whenever any column is updated, allowing DMS to match the target rows.

Key Concept

Oracle source CDC supplemental logging requirements for tables without primary keys in AWS DMS
Estimated Time:3m 0s
Rate this question

Topics

Database Migration and Schema Conversion using DMS and SCT

More questions from this topic