Change data capture implementation in Oracle Data warehouses - Part 3
May 29, 2003
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:
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
change source (SYNC_SOURCE is the system generated change source),
represents the source system and contains change set (collection of change
change set (SYNC_SET is the system generated change set) is a collection
of change tables.
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.
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.
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.