We are using AWS DMS for on-going replication of specific tables from one Oracle RDS database instance to another Oracle RDS database (both 11g).
Intermittently, the replication seems to fall behind or get out of sync. There are no errors in the log and everything is reported as successful, but data is missing.
We can kick off a full refresh and the data will show up, but this isn't a viable option on a regular basis. This is a production system and a full refresh takes upwards of 14 hours
We would like to monitor whether the destination database is [at least mostly] up to date. Meaning, no more than 2-3 hours behind.
I've found that you can get the current SCN from the source database using "SELECT current_scn FROM V$DATABASE" and from the target in the "awsdms_txn_state" table.
However, that table doesn't exist and I don't see any option to enable TaskRecoveryTableEnabled when creating or modifying a task.
Is there an existing feature that will automatically monitor these values? Can it be done through Lambda?
If DMS is reporting success, then we have no way of knowing that our data is hours or days behind until someone calls us complaining.
I do see an option in the DMS task to "Enable validation", but intuition tells me that's going to create a significant amount of unwanted overhead.
Thanks in advance.