What is the difference between Oracle Streams and Change Data Capture?

Question

There are several similar Oracle technologies - Oracle Streams, Oracle Change Data Capture and Database Change Notification.

What are the differences between these?

Are they related to Oracle Advanced Queueing?

Bret

Somewhat related: dba.stackexchange.com/questions/283350/… – Voiceless

Answer 1

Oracle CDC is all about capturing changes in DB tables and the changes are stored in special Oracle tables. There are two modes of CDC operation: asynchronous (based on Java) or synchronous (based on DB triggers, more performance overhead).

Oracle Streams sits on top of Oracle CDC and it's a full transport mechanism (over e.g. HTTP) for data synchronization between 2 servers. It's based on Oracle Advanced Queues technology and it's designed for high performance and reliability.

Both Oracle CDC and Streams are generally used for data synchronization between Oracle DB servers... With Oracle CDC, you don't have to use Oracle Streams for, e.g. you could write your own data export routines which create flat files for the purpose of synchronization between 2 DB servers, whereas with Streams you must have a network link between the 2 servers.

Database Change Notification is something else again, it's not used for server-to-server synch but instead more for server notification of resultset changes to clients, mostly in the context of data caches on the client side.

Coelho

Be careful of the term "resultset", everyone believe, since you use a query to indicate which tables you want DCN active, on that only results of the query cause notification. All DCN cares about is the FROM clause. Any changes to any table in the FROM cause a notification. – Soniferous

Hi Andrew, Do you have any example of CDC binded with java in the asynrhonous mode? Kind of a subscriber implemented in java or so. – Ancheta

According to the docs.oracle.com/cd/E11882_01/server.112/e25554/cdc.htm, "Asynchronous Change Data Capture is built on, and provides a relational interface to, Oracle Streams." I don't know how/if this affects the rest of your answer, which seems correct. – Yoohoo

Answer 2

I would add that for synchronizing between systems, you can achieve asynchronous mode by combining Streams and the CDC publishing mechanism. If you choose not to use Streams for this purpose, you would end up using a synchronous mode (I think it's via triggers) putting a little extra overhead on each transaction.

Endothermic