Understanding AWS DMS: How to Show the Full Row Each Time in Updates

Learn how to configure AWS Database Migration Service (DMS) to capture the full current state of a row in your Oracle database during ongoing replication. Follow our guide to ensure all column values are included in your data migrations!
---
This video is based on the question https://stackoverflow.com/q/72187898/ asked by the user 'Sixsmith' ( https://stackoverflow.com/u/7487653/ ) and on the answer https://stackoverflow.com/a/72576669/ provided by the user 'Sixsmith' ( https://stackoverflow.com/u/7487653/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: AWS Oracle DMS show full row each time

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/licensing
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/by-sa/4.0/ ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/by-sa/4.0/ ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
AWS DMS: How to Show the Full Row on Updates

When using AWS Database Migration Service (DMS) with an Oracle RDS instance, many users encounter a common issue regarding data replication, specifically when updating existing rows.

The problem arises when an update occurs and only the columns that were modified display in the output file, rather than showing the complete row with the latest values of all columns. In this post, we will guide you through understanding this issue and how to solve it effectively.

The Problem Explained

When you perform ongoing replication after a full load, you expect to see the current state of the entire row after an update. For example, if you update John Smith’s age from 46 to 47, the expectation is to see the entire record reflected in the output:

[[See Video to Reveal this Text or Code Snippet]]

However, if the DMS output looks like this instead:

[[See Video to Reveal this Text or Code Snippet]]

You’re left wondering why the columns not part of the primary key are missing, despite values being present in the database.

The Solution

The issue you're facing arises from a missing configuration in your Oracle database, specifically regarding supplemental logging. To capture the entire row during DMS replication, you need to ensure that supplemental logging is enabled for all columns in your tables.

Step-by-Step Guide to Enable Supplemental Logging

Alter the Table Configuration
You must modify the table schema by adding supplemental log data for all columns. Use the following SQL command:

[[See Video to Reveal this Text or Code Snippet]]

Replace table_name with your actual table name.

Applying Settings to Multiple Tables
If you need to apply this change across several tables within a schema, you can create a PL/SQL block to automate the process. Below is an example of how to do this:

[[See Video to Reveal this Text or Code Snippet]]

Make sure to replace YOUR_SCHEMA_OWNER with the correct schema name for your database.

Important Points to Remember

Enable Supplemental Logging: This is key to ensuring that all row data is replicated during updates.

Run the PL/SQL Block: Executing the above block will allow you to alter all tables within the specified schema without doing it one by one.

Monitor Changes: Utilize DBMS_OUTPUT.PUT_LINE to track the alterations being made, which can aid in troubleshooting.

Conclusion

By following the steps outlined above, you can resolve the issue of missing columns during AWS DMS updates. Ensuring supplemental logging is enabled for all columns in your Oracle tables will provide you with the complete current state of each row as intended. By keeping your data migration accurate and thorough, you can improve data integrity and management across your applications.

For continued success in using AWS DMS, always refer to the official documentation and community resources. Happy migrating!

Видео Understanding AWS DMS: How to Show the Full Row Each Time in Updates канала vlogize