We are in the process of migrating our Oracle 19c DB to another server. The DB is a source DB for AWS DMS replication. After the migration of our DB to another server ( using Datapump Export/Import), can we resume the AWS DMS CDC job using SCN of the new DB. The SCN is when the new DB was opened for transaction processing to all the users.
Before migration we log out all the users from the source DB and lock all the user accounts. As such, n there are no transactions happening on the source DB. After that we log out the the Oracle user account used by DMS.
The AWS DMS job resumes replicating data from the new migrated DB. Bur we do see some gaps in the records on the target DB.
You should resume CDC from the time you started your datapump export.(may feel counter intutive). but here is how DMS will handle. In DMS when you tell CDC to start from when full load (in your case data pump export /import) , any changes that happened during the time of datapump export will be ignored (like duplicate etc) you can see that in cloudwatch logs with filtering with "W]". This setup of CDC only works if your table(s) has primary key defined.
Also updates and deletes are also handled correcltly if you use the starttime same as start of datapump export