ODI11g: Combining Oracle Data Integrator and Oracle GoldenGate

 

<Do not delete this text because it is a placeholder for the generated list of "main" topics when run in a browser>

Purpose

This tutorial walks you through the steps that are needed to to add Oracle Golden Gate Changed Data Capture (CDC) to an existing ODI project

Time to Complete

Approximately 1 hour

Overview

Oracle Data Integrator can be combined with Oracle Golden Gate (OGG) , that provides a cross-platform data replication and changed data capture. Oracle Golden Gate worked in a similar way to Oracle’s asynchronous change data capture but handles greater volumes and works across multiple database platforms. Golden Gate uses its own data logging (or “trails”) to record DML and DDL activity on a source database, which it then replays on staging and target platforms to replicate changes in real-time. This makes OGG a valuable technology, especially if you are have replication performance limits. In this OBE, participants use ODI’s Journalizing Knowledge Module for OGG to generate OGG scripts based on ODI models and interfaces.

For simplicity, in this OBE the entire setup is on one machine using a single database. Real-live use cases would most likely use source and target on different servers, but the fundamental setup of OGG and ODI are very similar. In this OBE, you suppose to have two separate locations of OGG, one in C:\gg_src and one in C:\gg_stg folders.These two locations simulate OGG installations on two separate machines as illustrated below:

Scenario

Linda works as a database administrator for Global Enterprise. In Global Enterprise, Linda is responsible for performing database management and integration tasks on various resources within the organization. In particular, Linda is responsible for data loading, transformation, and validation. To begin working on her projects, Linda created the new Master repository and Work repository. Linda also created number of interfaces for data transformation and changed data capture. Now, Linda decided to begin using Oracle Data Integrator (ODI) and Oracle GoldenGate
(OGG) together to perform real-time ETL.

Software and Hardware Requirements

The following is a list of software requirements:

Prerequisites

Before you start the tasks, make sure that your system environment meets the following requirements:

.

Have installed Oracle Database 11g. If not done before, start the services and components for Oracle Database 11g

.

Have installed Oracle Data Integrator 11gR1

.

Have Installed Golden Gate 10.4.0 for Oracle 11g on Windows 2000, XP and 2003

.

Download demo.zip file from HERE, and extract it in c:\ directory on your machine.

.

Before attempting this OBE, you should have successfully configured ODI 11g to use Oracle Golden Gate for CDC. For configuration steps, click HERE.

 

Enable Staging Model for Changed Data Capture


You review your ODI models and projects and enable the staging model for ODI CDC.

.

Make sure you are disconnected from ODI Studio. Open DOS Command prompt window and execute reset_ODI.bat script located in C:\demo\odi11g\Installs as shown below. This will recreate the ODI objects which will be used in the subsequent steps.
Warning: If you have custom ODI projects in the repository on your machine, they will be deleted.




.

Open the Oracle Data Integrator Studio using the Start Menu > Oracle > Oracle Data Integrator > ODI Studio menu. Choose the Designer tab and click on Connect To Repository... In the Login dialog, choose WORKREP1 from the Login Name list. For password, enter SUNOPSIS. Click OK.





.

Click on the Models tab to expand the models pane. Review the models present in the respository: Expand each one of the 4 models
Oracle_Source, Oracle_Staging, Oracle_Target, and Flat File Sources. The Oracle_Source model contains the tables in the original source location. Oracle_Staging contains copies of tables from the source that need to be replicated by Oracle GoldenGate in real-time. The Oracle_Taget model contains transformed tables in the final target location. The Flat File Sources model contains flat file datastores that will participate in the transformation to the target table.





.

Enable the staging model for ODI CDC: The JKM for Oracle GoldenGate performs a 2- stage CDC; OGG detects changes in the source and replicates them to the staging tables; ODI is informed of changes arriving in the staging table through its standard CDC framework. Therefore, the staging tables are enabled for CDC and generate OGG scripts to perform replication from source to staging. Right-click the model Oracle_Staging, open the submenu Changed Data Capture, and select Add to CDC. When asked “Are you sure you want to add all the model’s tables to CDC?", click Yes.




.

Double-click on the model Oracle_Staging to open the model editor. Select Journalizing to open the Journalizing tab. In Journalizing tab, for Journalizing Mode select Consistent Set, Click Yes in the Alert dialog box, and then in Journalizing KM select “JKM Oracle to Oracle Consistent (OGG)”.





.

Enter the following options to the JKM. Leave options starting with <Default> unchanged, as shown below. Click Save icon to save your changes in the model. Close model editor.

Parameter

Value

LOCAL_TEMP_DIR

c:/temp

SRC_LSCHEMA

ORCL_SOURCE

SRC_DB_USER system

SRC_DB_PASSWORD

Oracle1

SRC_OGG_PATH c:/gg_src
STG_MANAGER_PORT 7909

STG_OGG_PATH

c:/gg_stg

COMPATIBLE

10

Port

1521



.

Right-click on model Oracle_Staging, go into the submenu Changed Data Capture > Subscriber, and select Subscribe…. Enter ODI as the name of a new subscriber and press the red Arrow Down button to add to the list. Click OK to close the dialog. Press OK on the Execution dialog box, and then Information dialogs that appear.







.

Right-click the Oracle_Staging model, open the submenu Changed Data Capture, and select Start Journal. Click OK on the Execution and then Information dialogs that appear.










Configure Oracle Golden Gate

Now, you review existing models and projects and perform OGG configuration.

.

Open a Windows File Explorer window by opening My Computer on the desktop and navigate to directory C:\TEMP. A new directory called ODIS_to_ODIT1 should be visible. This directory contains the OGG configuration files that were generated during the Start Journal step earlier. Open the ODIS_to_ODIT1 directory. You can see two directories containing the configuration files for the source and staging OGG installations, as well as a Readme.txt file explaining the configuration of OGG using these files.


.

Double-click the Readme.txt file to review the configuration steps. We will perform the 6 actions listed in the Readme in the coming steps.
Note: You can alternatively execute the copy_gg_files.bat script from the Desktop. In this case you can skip steps 3-8 below.









.

UPLOAD FILES TO SOURCE MACHINE:

Navigate to directory C:\TEMP\ ODIS_to_ODIT1\src. It contains 3 subdirectories dirdat, diroby, and dirprm which mirror the common OGG directory structure. Open a second file explorer to the directory C:\gg_src and copy those 3 subdirectories into it.


 


.

UPLOAD FILES TO STAGING MACHINE:

Open the directory C:\TEMP\ODIS_to_ODIT1\stg. It contains 3 subdirectories dirdat, diroby, and dirprm which mirror thecommon OGG directory structure. Open a second file explorer to the directory C:\gg_stg and copy those 3 subdirectories into it.



.

RUN THE SOURCE OBEY FILE:

Open a DOS Command Prompt and enter the following command (Same as in the Readme.txt file):
c:/gg_src/ggsci paramfile c:/gg_src/diroby/ODISS.oby






.

GENERATE THE DEFINITION FILE:

Enter the following command in a DOS command prompt:
c:/gg_src/defgen paramfile c:/gg_src/dirprm/ODISD.prm





.

COPY DEFINITION FILE:

Copy the definition file c:/gg_src/dirdef/SRCC.def to C:/gg_stg/dirdef/SRCC.def:





.

RUN THE STAGING OBEY FILE:

Enter the following command in a DOS command prompt:
c:/gg_stg/ggsci paramfile c:/gg_stg/diroby/STG1T.oby



Initialize Load to Staging and Target Tables

In this section you will initalize both staging and target areas with the pre-existing source data.

.

Start the ETL Demo Client to view all tables. To start ETL Demo Client, open the command pprompt, change directory to c:\demo\odi11g, and then execute file client.bat. This client is a custom Java program to display changes in the 3 tables as they occur. At this point only the source table has data.
Keep this client running for later.



 

.

Open the ODI Studio and go to Designer > Projects. Review the folders. The relevant folders for this GG_HOL project are:

1. Initial Load Staging: Performs a 1-to-1 copy from the source tables to the staging tables;
2. Initial Load Target: Does an bulk load & transformation from staging to target tables;
3. Synchronize Target: Performs a CDC-triggered load and transformation from staging to target.

Expand the folder 1. Initial Load Staging and its Interfaces subfolder. Right -click the interface 1. Initial Load Staging and select Execute. Click OK. Click OK again. Open the ETL Demo Client to review the loading of the staging data:










.

In ODI Studio, expand the folder Initial Load Target and its Interfaces subfolder. Right-click the interface Full Load Customer to Target and select Execute. Click OK. Click OK again. On Operator, verify that the session executed successfully.








.

In the ETL Demo Client view the loading of the target data:

Note: Loading data into the Target table may take some time.



Initialize CDC Process and Perform Change Operations

 

.

Open the ODI Studio, select Designer > Projects. Expand: 3. Synchronize Target > Packages. Double-Click the the Sync Target package to open it. Click the Diagram tab.The interface consists of a perpetual loop that is triggered by a new journal log entry and processes new changes with the CDC Load Customer interface.



 

.

Click Execute button in the toolbar to start the package . Click OK., and then click OK again. Open the Operator from the toolbar to view the process of Sync Target execution. Click refresh button . Open the Session List > All Executions > Sync Target in the Operator. The package is currently running and waiting for a new journal entry at OdiWaitForLogData 1 as shown below.


.

In the ETL Demo Client, double-click the FIRST_NAME of an entry (for CUSTID =101) in the Source table as shown below. The entry is editable.



.

Edit the First Name and press Enter. Watch the Staging and Target tables. After a few seconds the FIRST_NAME in the Staging table flashes yellow and then, the updated concatenated CUST_NAME in the Target table appears.

Continue editing the Source table and viewing how changed data is captured and passed to the Target.

Note: If your OGG-ODI integrated environment is not functioning properly, you may need to reset your OGG environment. In this case, execute the script ogg_reset.bat located in c:\demo\odi11g\Installs. Close connection to ODI Studio, and then execute reset_ODI.bat script as described in step1. To set up OGG-ODI integrated environment again, repeat other steps from this OBE.




Summary

 

In this tutorial, you have learned how to:

Resources

 

Hardware and Software Engineered to Work Together About Oracle |Oracle and Sun | Oracle RSS Feeds | Careers | Contact Us | Site Maps | Legal Notices | Terms of Use | Your Privacy Rights