Oracle CDC to S3: Redo Log Gaps, Duplicate Rows, and Change-File Flood

DMS reads archived redo logs to apply changes. When the task falls behind further than the archive retention window, or the archive is purged by backup jobs, DMS loses its position and cannot resume; it requires a reload. At 4,000 records/s sustained for three hours the task's apply side becomes the limiter and lag grows into that window.

Changes:

• Extend archived log retention on the source and exclude those logs from purge during the close window; confirm with the DBA team that retention comfortably exceeds the longest expected lag, not the average.
• Tune CDC apply: enable batch apply, set the memory limit and keep-time for change processing, and adjust commit timeout so changes apply in larger units.
• Size the replication instance for month-end, not for baseline, or pre-scale before the close; monitor CDCLatencySource and CDCLatencyTarget with an alarm well below the retention window.
• Document the recovery path explicitly: when logs are gone, the only correct answer is a resync of the affected tables, and it must be a rehearsed procedure with validation, not an improvised one.

Because end-of-day parity is an audit requirement, enable DMS validation on the replicated tables and treat validation failure as a release blocker.

CDC files are append-only streams of I/U/D events. If the task resumes from an earlier position or the merge job reprocesses a prefix because a bookmark was lost or reset, the same events are applied twice; appending Parquet can never recover from that.

Design:

• Make the Iceberg table keyed on the source primary key, with columns for op, commit timestamp, and source file sequence.
• Each run does a MERGE that first deduplicates the input window with row_number() over (partition by pk order by commit_ts desc, file_seq desc) = 1, then applies the surviving event as insert, update, or delete.
• Drive the window by source commit time rather than file arrival time, and make the processing unit a closed time range so re-running the same range produces the same result.
• Keep a processed-window registry so an accidental replay is a no-op rather than a duplicate.
• Use Glue job bookmarks, but do not rely on them alone; the dedupe above is what makes a lost bookmark survivable.

For the 1.9x data already present, rebuild the affected partitions from the deduplicated source rather than deleting rows by hand.

The CLOB truncation is a DMS LOB mode setting, not a target format issue: limited LOB mode truncates at the configured maximum size, which is clearly set near 4 KB here. Set the LOB max size above the largest real CLOB, or switch to full LOB mode for the few tables that need it, and accept the throughput cost on those tables only. Re-validate a sample of rows afterwards; truncated values are already in the target and will not fix themselves.

For the file flood, 340,000 files per day means commits are far too granular. Raise the S3 target endpoint file size and commit timeout so change batches land as 128 MB objects, which alone can cut file count by an order of magnitude. Where the task cannot be retuned, route CDC through a streaming layer and land with a writer that commits every few minutes.

Then decouple merge cost from file count: run an hourly compaction over recent partitions and a weekly one over older data, expire Iceberg snapshots, and process only closed commit-time windows. Also check whether the 5-hour runtime is metadata planning rather than data volume; if the job opens hundreds of thousands of files to plan, compaction fixes latency even though data size is unchanged.