Change data capture implementation in Oracle Data warehouses – Part 3

Earlier
in this series, we discussed Oracle streams framework and a simple Streams
configuration. While Oracle Streams (new feature in Oracle 9.2.0) provides for
multitude of functions such as messaging, event management, DML and DDL change
capture from disparate databases etc., Change data capture (from Oracle 9.0)
can be used only for DML operations on the source tables in an Oracle system

Essentially, in change data
capture system, DML operations on the source tables are captured synchronously
or continuously and in real time.

The change data capture model

The components in the model can be described as:

1. The
   source system is typically a low volume Oracle based OLTP production
   database. Because the change data capture captures changes to the source table
   continuously and in real time, significant overheads are incurred during
   capture time.

2. The
   change source (SYNC_SOURCE is the system generated change source),
   represents the source system and contains change set (collection of change
   tables.)

3. The
   change set (SYNC_SET is the system generated change set) is a collection
   of change tables.

4. The
   change table is table that contains all the source table data changes
   and also system metadata necessary to maintain the change table such as
   “username$”, “timestamp$” etc.

5. The
   publisher, usually a DBA who is responsible for setting up the change
   data capture data system and maintaining it. The publisher identifies the
   source tables from which the changed data has to be captured and published to
   the change tables.( as in the case of extraction and generating a flat file).
   The publisher also controls access to this published data by using GRANT and
   REVOKE privileges on the change tables.

6. The
   subscriber is typically a datawarehouse application that consumes
   the changed data. The subscriber subscribes to one or more sets of columns in
   the source tables (Subscriber views) and can receive a set of change
   data in specific time window (sets of rows or subscriber window).The
   subscriber should SELECT priveleges on the changes tables in order to subscribe
   to the published data.

Unlike Oracle Streams that can be configured in a
number of ways to capture different kind of changes and transformation, the
change data capture is a very straightforward process and captures any DML
operations on the columns of the source tables as indicated in the definition
of change tables.