Can we use DMS to perform CDC for the source table which is the target table of other CDC tool?

Yes, it is technically possible to set up AWS DMS to perform CDC on an Oracle table that is itself the target of another CDC tool. However, there are important considerations and potential risks you should be aware of:

Key Considerations:

1. Supplemental Logging Requirements: AWS DMS requires supplemental logging to be enabled on the Oracle source database to capture changes. You'll need to ensure this is properly configured on your intermediate Oracle database.

2. CDC Method Selection: You'll need to choose between Oracle LogMiner or AWS DMS Binary Reader to access the redo logs. If you don't specify a CDC-related connection attribute, AWS DMS will automatically use LogMiner. Binary Reader generally offers better performance and lower impact on the source system, but requires additional configuration including proper permissions and directory access.

3. Transaction Consistency: Since your Oracle table is being updated by another CDC tool, you need to ensure that the CDC start point you choose for AWS DMS captures all relevant transactions. AWS DMS allows you to specify a CDC native start point based on Oracle's system change number (SCN) to identify the earliest open transaction. This is important to avoid missing any changes.

4. Loopback Prevention: While your scenario isn't bidirectional replication, be mindful that changes flowing through multiple CDC tools could potentially create unexpected behaviors if not properly managed.

Potential Risks:

• Performance impact on the intermediate Oracle database from running multiple processes accessing redo logs
• Complexity in troubleshooting data inconsistencies across the chain
• The CDC start point cannot be changed once the AWS DMS task is created, so careful planning is essential

I recommend thoroughly testing this architecture in a non-production environment first to validate data consistency and assess performance impact.
Sources
Using an Oracle database as a source for AWS DMS - AWS Database Migration Service
Creating tasks for ongoing replication using AWS DMS - AWS Database Migration Service
Use AWS DMS to migrate data between Amazon RDS for Oracle and different databases | AWS re:Post