Configure CDC ODI 12C with Reusable Mappings

1.- Identify the source and target locations
2.- Create your source model, and target model
3- In my case I created the CDC_Test model connected to a Database 11g XE Edition and my target is a Database 12c EE

Goldengate is a oracle product to syncronize legacy systems or databases using the log files, but is quite expensive, in this case we are going to see, how to do a sync between two systems using just ODI 12C,

Using CDC Simple  Mode:

1.- Create a Project and import JKM Oracle Simple

2.- Change de Model of CDC_Test to Simple

3.- Do the Reverse-Engineering for the tables to add to CDC, in this case the table I chose was SRC_CITY.

4.- Once you have the datastore in your data model, Now configure the CDC, right clic over the datastore SRC_CITY and Add to CDC.

5.- Add a subscriber, right clic over datastore and navigate to subscriber, Add subscriber

6.- Add the subscriber SUNOPSIS

7.- Start Journal, select the subscriber, then session is prompted to start, clic ok.
Note:
The steps to undo this are (Unsubscribe, Drop  Journal, Remove from  CDC)

8.- So far, we have configured the basic CDC Simple, let's remember this is intrusive, because it creates triggers over the source tables and may affect the performance, so check carefully.

9- Test

10.- Now let's do this automatically using a package, mapping (ODI Interface in ODI 11G) and reusable mapping

11.- Create a Reusable Mapping, just drag the source table (The one you just added to CDC, see above), and also drag an output signature and do the mapping.

12.- Create a mapping in ODI and drag the reusable mapping created in the last step.

13.- Drag the target table and do the mapping.

14.- Choose the Optimization context to Development
15.- Configure the Journalize Data (select CheckBox)
16.- In the Logical Section change the target to  Incremental Update
17.- In the Physical, select the target TRG_CITY, and in the section Integrated Knowledge Module change to IKM Oracle Incremental Update

Look like this:

18.- Create a package to automatize this.
19.- Drag a OdiWaitForLogData component

OdiWaitForLogData "-CONTEXT=DEVELOPMENT" "-GLOBAL_ROWCOUNT=1" "-LSCHEMA=GG_SOURCE" "-OPTIMIZED_WAIT=AUTO" "-POLLINT=1000" "-SUBSCRIBER_NAME=SUNOPSIS" "-TIMEOUT=0" "-TIMEOUT_WITH_ROWS_OK=YES" "-TABLE_NAME=SRC_CITY"

20.- Drag the interface into the package and make a connection to the OdiWaitForLogData and viceversa

21.- Test the package.

SELECT * FROM SALES_DATA.SRC_city;

SELECT count(*) FROM SALES_DATA.SRC_city;

SELECT * FROM SALES_DATA.SRC_city;

delete from SALES_DATA.SRC_city where city_id = 100;

commit;

insert into SALES_DATA.SRC_city values (100,'Acapulco',20,123456);

commit;