Oracle Data Integrator 12c----Simple CDC

Simple CDC, the simplest CDC. Here, each CDC table change is captured independently, and there is no need to consider the consistency of data between tables that have primary foreign key reference relationships.

1 Environment Preparation

SOURCE table: Odi_src. DEPT

Target table: Odi_tag. DEPT

Configuration topology See the creation and configuration of the ODI studio topology (Oracle)

Model Reverse engineering See: Oracle Data Integrator 12c-model and data storage (DataStore)

2 Importing and configuring JKM

First import JKM JKM Oracle simple in your project

JKM is a knowledge module that generates and captures logs for a table and needs to be configured on the model or table.

Edit the journal record for model ODI_SRC, select simple journaling mode, and select JKM Oracle simplicity:

3 Enable CDC

ODI provides a simple interface to enable CDC, including the following steps: Add to CDC, subscribe to CDC, start journaling.

First right-click Model ODI_SRC (or right-click Data store emp) → changed data capture → Add to CDC

Click the Refresh button to see that the chart for the table in model ODI_SRC has changed and a small yellow clock has appeared:

If there is no yellow hour clock, you can display CDC content in the tree with the tools → preferences check box

Next, select Start Journal from the pop-up menu of the model ODI_SRC (you can also start a journal for a table):

For simple CDC, you can enter the user name of the subscription when you start the journal. In the popup window, enter the user name of the subscription, such as simple, click Add Subscriber:

Click OK to finish:

This will start a session,

See if the session completed successfully in Operator. This session creates some objects in the database: The CDC data dictionary table (the SNP start), the journal table and the view (usually j$jv$), trigger (usually beginning with t$).

can find the EMP table, the Dept table more triggers, the contents are as follows

Trigger T$emp (If you start the log only on the table dept instead of on the model, this is not the case)

Create or Replace TriggerOdi_src_work. T$empafterInsert or Update or Delete  onodi_src. EMP forEach rowDeclareV_flagVARCHAR(1); V_empno Number(4);beginifInserting ThenV_empno:=: New. EMPNO; V_flag:= 'I';End if;ifUpdating ThenV_empno:=: New. EMPNO; V_flag:= 'I';End if;ifdeleting ThenV_empno:=: Old. EMPNO; V_flag:= 'D';End if;Insert  intoOdi_src_work. J$emp (jrn_subscriber,jrn_consumed,jrn_flag,jrn_date,empno)SelectJrn_subscriber,'0', V_flag,sysdate,v_empno fromOdi_src_work. Snp_subscriberswhereJrn_tname= 'odi_src. EMP'/*The following line can is uncommented for symetric replication*//*and Upper (USER) <> Upper (' odi_src ')*/;End;

View Code

Trigger T$dept

Create or Replace TriggerOdi_src_work. T$deptafterInsert or Update or Delete  onodi_src. DEPT forEach rowDeclareV_flagVARCHAR(1); V_deptno Number(2);beginifInserting ThenV_deptno:=: New. DEPTNO; V_flag:= 'I';End if;ifUpdating ThenV_deptno:=: New. DEPTNO; V_flag:= 'I';End if;ifdeleting ThenV_deptno:=: Old. DEPTNO; V_flag:= 'D';End if;Insert  intoOdi_src_work. J$dept (Jrn_subscriber,jrn_consumed,jrn_flag,jrn_date,deptno)SelectJrn_subscriber,'0', V_flag,sysdate,v_deptno fromOdi_src_work. Snp_subscriberswhereJrn_tname= 'odi_src. DEPT'/*The following line can is uncommented for symetric replication*//*and Upper (USER) <> Upper (' odi_src ')*/;End;

View Code4 testing to see if changes can be captured

Inserts a row of data into the DEPT.

Insert  into Values ('assitant' 'Boston');

Then query the journal table:

Select *  from Odi_src_work.j$dept;

We can see that the changes have been captured in the diary table.

In this case we can also view the diary data in the ODI:

5 Create interface to synchronize change data to target

Create an interface in Project Odi_exercise simple_cdc_dept

In the diagram, add the EMP from ODI_SRC to the source, add the EMP in the ODI_TRG to the target datastore, and map it automatically.

Then select the source data store, select "Data for journaling only",

Specify the log filter, the content of the filter is for subscribers, the following example indicates that the current interface will consume ' simple Subscriber ' data:

Other and general mappings are the same

Then configure LKM (using LKM SQL to Oracle), IKM (using Oracle Increamental Update), and CKM.

6 Executing the CDC interface

Executing simple_cdc_dept and viewing the results in Operator, we see a record inserted.

View the data for the Odi_tag Dept table in the model:

We can see that the changed data is synchronized:

To view the journal data for the source table dept:

We see that there is no change in the data:

This means that once the ODI interface consumes a table change data, the corresponding subscriber's journaling data will be emptied. This is a very simple approach, but data synchronization for multiple tables with primary foreign key reference relationships can result in inconsistent data. Consistency JKM is required for this complex situation.

7 Creating package polling change data

Change data can be generated at any time, so new changes need to be kept in sync. There are two ways to accomplish this task.

The first approach can be achieved through planning. For example, create a schedule that performs a synchronization interface every half hour. This can synchronize the change data to the target every half hour.

The second approach is to create a package that uses the CDC-related tools provided by ODI to poll the journal and invoke the synchronization interface once a change has been made.

The first approach is to pay attention to the scheduled repetition interval, to ensure that the last call has been completed before repeated calls, and can generally be used for periodic synchronization requirements.

The second method can meet the demand of higher real-time.

First create a package: simple_cdc_dept_pkg

Add a tool for CDC in the diagram: Odiwaitforlogdata. Configuration properties:

The Dept journal table will be polled every 1 seconds as configured above until 1 changes are found.

Drag the map simple_cdc_dept to the package

Add Odisleep

After the interface is finished, call Odisleep to wait 1 seconds before continuing polling.

Execute the package, we find that this package will be executed and be in Odiwaiteforlogdata this step. Insert the data using the following SQL:

Inserts a row of data into the DEPT.

Insert  into Values ('assitant', 'Boston');

From the operater we find that this session will complete a loop,

Querying the DEPT table in Odi_tag, we can also find that the data has been synchronized.

We can continue to delete the data from the source table and we can see that the data will be synchronized in the past.

8 Clearing logs

To delete a running package

Delete a journal

This operation starts a session, check the results of the session execution in Operator. If successful, check the database and you should see CDC-related objects: triggers and journal tables and views are removed.

Remove from CDC

After the refresh, you should see the table icon above the small clock disappears. The table on the model that has journal records is empty

Oracle Data Integrator 12c----Simple CDC