Implement log based change data capture in ODI

Hello everyone!

Change data capture is mainly used to capture the data inserted, deleted or updated at the source side. The changes thus captured are then replicated to the target side. There are many ways to implement Change Data Capture in Oracle Data Integrator. Two commonly used methods are:

1. Log based change data capture

2. Trigger based change data capture

I had implemented trigger based change data capture in one of my earliertutorials. In this post, I will implement the log based change data capture method!

Pre-requisites: Oracle database 11gR2 Enterprise edition with *SQLPlus, Oracle data integrator 11g (build version 11.1.1.7.0)

Must read: Previous version of Oracle data integrator namely ODI 10gincluded the JKM Oracle 11g Consistent LOGMINER (REALTIME)module for change data capture. However, this knowledge module is not present from ODI 11g onwards. As Oracle is slowly moving towards streams, this KM was then replaced by JKM Oracle 11g Consistent Streams. Hence in order to implement log based CDC we either need to download the older LOGMINER KM or use the current Streams KM. For using streams in Oracle it is mandatory to have the Oracle database Enterprise Edition (EE) to be downloaded and installed. Other versions of Oracle database such as XE (Express edition), SE (Standard edition) do not support streams.

Note: I assume you have the data models, physical and logical schemas already set up for the connection to your Oracle database.

Step 1: Create source and target tables

source table for CDC

create table cdc_src(empid int primary key, empname varchar(20),empcity varchar(20));

target table for CDC

create table cdc_target(empid int, empname varchar(20), empcity varchar(20));

Insert a few dummy records in the source table only.

Next, reverse engineer both source and target tables in a new Modelnamed cdc_model. Add the source data store cdc_src to CDC by right clicking–> Change data capture–>Add to CDC

Step 2: Apply log based method

Open the cdc_model and select the Journalizing option. You now need to select the Journalizing mode. Simple is used for CDC implementation on a single table and Consistent on multiple tables or models. In this tutorial I will be implementing the Consistent Set option even though I have a single source table. You can always try it using multiple source tables described over here

Once you have selected the Consistent option, you need to choose the corresponding Knowledge module. I have selected JKM Oracle 11g Consistent Streams for my Project cdc_test_project.

Make sure you follow the Basic Configuration steps that appear in the description below before you start implementing your Interface!

Basic Configuration Steps

1. Connect SYS as SYSDBA with SQL*Plus and run the following statements     -- Replace "SAS" with the name of your user     -- This user is specified in the Data Server definition     grant dba to SAS;     BEGIN     DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE     (     grantee => 'SAS',     grant_privileges=> true);     END;     /           SHUTDOWN IMMEDIATE;     STARTUP MOUNT;     ALTER DATABASE ARCHIVELOG;     ALTER DATABASE OPEN;   2. Exit from SQL*Plus 3. Select the JKM on the model you want to journalize 4. Add the Datastores to the CDC Set (with this JKM, it is not mandatory to define an order for the datastores in the CDC Set) 5. Make sure that the AUTO_CONFIGURATION and VALIDATE options are set to Yes 6. Start the Journal on the Model 7. Create a Subscriber on the Model

Note: If you are logged in as SUPERVISOR the default subscriber chosen would be SUNOPSIS.

Once again, right click the source data store cdc_src and select Start Journal. You should see a green color icon on the data store. This indicates that the data store is now in journalizing mode ready to capture changes. Check the status of the operation in the Operator tab.

Step 3: Make changes to source table

After you have started the journal successfully, you now need to update or insert any row in the source table cdc_src. In my case, I have added a new row as follows!

SQL> insert into cdc_src values(108,'salman','mumbai'); 1 row created. SQL> commit; Commit complete.

Step 4: Create an interface

Create a new Interface named cdc_test. Drag and drop the journalized source table and the target table in Mapping. In Mapping, select source data store and tick the option Journalized data only in the Source properties window. Finally, select IKM as Oracle Incremental Update and run the interface.

Ensure that no errors occur. Check the status of the operation. If the operation was successful, right click the source data store –> Change data capture and select Journal data. You should now see the changed data getting reflected!