Question: I'm trying to implement change data capture (CDC) on the Oracle Source Database to achieve the real-time staging database and loading this changed data into a specific "data layer" through the extract, transform and load (ETL) tool for the intra-day reporting. I found two types of methods for CDC: synchronous and asynchronous. Asynchronous further classified into four methods: hotlog, distributed hotlog, autolog archived and autolog online.
I've implemented all the methods on small source databases, but Im not able to judge the performance of the individual methods.
Now, my query is: Which method is the best to achieve my goal (staging database with changed data) taking into consideration the issues of performance, latency, impact on source database etc.?
Chuck Kelleys Answer: I think that it depends on your requirements and how long before it has to be in the data warehouse. If it must be immediate and always in sync with the source system, then use synchronous. The negative is that it will slow down your source systems, since you are in effect applying a two phase commit between source and staging. I have found that, in most cases, distributed hotlog is probably the best of the four asynchronous methods.