ORACLE - Streams / Change Data Capture

> Database > Oracle Database

1 - Description

For the basic of change data capture, see this article: Data Integration - Change Data Capture (Incremental Load, ...)

In Oracle database since version 9i.

The first version of Change Data Capture placed a set of triggers on the tables in the source application, and these triggers propagated inserts, updates and deletes to your data warehouse’s staging area. The only problem with this was the “invasive” nature of the approach - the tables in your source application had to be modified to put the triggers on, and the source database transactions couldn’t commit until they’d propagated the changes over - hence it’s title of “Synchronous” Change Data Capture.

Change Data Capture was enhanced though in Oracle Database 10g in that it became “Asynchronous”. Instead of using triggers to pick up and propagate changes, a process on the source database picked up the transactions from the redo log, and used the Streams mechanism in Oracle Database 10g to transmit the changes, asynchronously, to the target environment. By using the redo log and streams, the process was non-invasive and only had minimal impact on the source application.

When you get to Oracle Database 10g Release 2, there are in fact several variations on Asynchronous Change Data Capture. Asynchronous Autolog Change Data Capture uses the database’s redo log transport mechanism to propagate changes to the target database, whilst Asynchronous Hotlog Change Data Capture reads from the redo log and uses Oracle Streams to move the data from one schema to another within the same database.


3 - Article, Reference

db/oracle/cdc.txt · Last modified: 2017/09/13 20:17 by gerardnico