Monthly Archives: January 2015

Integrating Oracle GoldenGate (OGG) with Oracle Data Integrator (ODI)

ODI CDC Overview
ODI CDC Infrastrucuture
CDC Implementations
Using CDC Data
ODI CDC using Oracle GoldenGate (OGG) CDC Overview?

CDC feature is to detect changes occurring on the data and only process these changes in the integration flows CDC Introduction:
The purpose of Changed Data Capture is to reduce the amount of data to process by allowing applications to use changed data only
Loads will only process changes since the last load which dramatically reduces the volume of data to be processed.
CDC is extemely useful for near real time implementations, synchronization, Master Data Management

comment parler a un homme sur un site de rencontre CDC in ODI:
CDC in ODI is implemented through a family of Journalization KMs.
Developer can choose from the interface whether he will use the full data set or only the changed data

web link Journalizing Components: These components are implemented in the journalizing infrastructure
Journals: Contains references to the changed records and type of changes e.g Insert, update or delete (I or D). Journal tables and prefix is J$ and are created in work schema
Capture processes: Capture the changes in the source data stores either by creating triggers on the data tables or by using database-specific programs to retrieve log data from data server log files. The default value for prefix journalizing  trigger names is T$ and are created in the application data schema
Subscribers: (applications, integration processes) Use the changes tracked on a data store or on a consistent set. These changes are discarded from journals once all the subscribers have consumed these changes. The common infrastructure is installed which contains subscriber information, consistent sets. The prefix is SNP_CDC_ and are created in work schema

Journalizing Views: Provide access to the changes and the changed data captured.They are used by the user to view the changes captured and by integration processes to view the changed data. Journalizing views prefix is JV$ and are created in work schema

my latest blog post CDC Infrastructure in ODI?
CDC in ODI relies on a journal table. This table is created by the JKM and loaded by specific steps implemented by the JKM
This table has a very simple structure (Primary Key, Timestamp, A Flag to allow for a logical lock of the records). A series of views are created to join this journal table with the actual data. Other KMS will know to use the views instead of the tables.

advice CDC Implementations?
CDC Implementation Strategies:
1. Oracle GoldenGate (OGG) can be used with ODI that provides the least intrusive and most efficient CDC solution
2. Log-based CDC, which loads the journal table when the changed data are loaded into the target system
3. Triggers will directly update the journal table with the changes

stud 5000 spray uk Simple and Consistent Set Journalizing?
Simple Journalizing enables to journalize one or more data stores. Each journalized data store is treated separately when capturing the changes (no consistency)
Consistent Set Journalizing provides the guarantee of the consistency of the captured changes (Consistency Window)

Simple CDC?
Consistent CDC?
The mechanism put in place by consistent CDC will solve the issues faced with simple CDC. It guarantees the consistency of the captured changes. The set of available changes for which consistency is guaranteed is called the consistent window.
The difference here will be to lock children records before processing the parent records.

Setting up Journalizing?

Setting up CDC in ODI:

1. Set the CDC parameters in the data model – Consists of setting the journalizing mode – Consistent Set or Simple

If the model is already being journalized, it is recommended that you stop journalizing with the existing configuration before modifying the data model journalizing parameters.

2. Add the data stores to the CDC – Select Changed Data Capture –> Add to CDC or Remove from CDC

Changes to the order of datastores are taken into account the next time the journals are (re)started.

If existing scenarios consume changes from this CDC set, you should regenerate them to take into account the new organization of the CDC set.

3. For consistent set journalizing,  set the data stores order – Journalzed Tables tab -> Reorganize button

4. Add Subscribers – Changed Data Capture -> Subscriber -> Subscribe or Unsubscribe

You can also add subscribers after starting the journals. Subscribers added after journal startup will only retrieve changes captured since they were added to the subscribers list.

  • Inserts Subscribers data into table SNP_SUBSCRIBERS
  • Alters J$ table with JRN_SUBSCRIBER column
  • Runs Gather Schema Stats on table SNP_SUBSCRIBERS

5. Start the Journals.

  • Creates JV$ View e.g. JV$TRG_SALES_PERSON
  • Creates trigger e.g. T$TRG_SALES_PERSON
  • Creates data view e.g. JV$DTRG_SALES_PERSON

Using CDC?

Model -> Changed Data Capture -> Journal Data

The journalizing colums are available for the data store

JRN_FLAG: Flag indicates the type of change. I – Inserted/updated, D – Deleted record

JRN_SUBSCRIBER: Name of the subscriber

JRN_DATE: Timestamp of the change
KM Option SYNC_JRN_DELETE? Yes – Invokes the deletion from the target data store of the records marked as Delete (D) in the journals and that are not excluded by the  journalizing filter.

No – Integration will only process inserts and deletes

Journalizing Tools?

ODI provides a set of tools that can be used for journalizing to refresh information on the captured changes or trigger other processes

OdiWaitForData, OdiWaitForLogData, OdiWaitForTable

OdiRetrieveJournalData, OdiRefreshJournalCount

ODI CDC with Oracle GoldenGate (OGG)?
OGG complements ODI by providing real time solutions

How Oracle GoldenGate Works?
Capture – Committed transactions are captured and can be filtered as they occur by reading the transaction logs
Trail – Stages and Queues data for routing
Pump – Distributes data for routing to targets
Route – Data is compressed, encrypted for routing to targets
Delivery – Applies data with transactional integrity, transforming the data as required

Oracle Real Time Data Integration
1) Oracle GoldenGate
2) Oracle Data Integrator EE

ODI Journalization Knowledge Modules for Oracle GoldenGate?

Oracle Enterprise Data Quality (EDQ) Installation

Download the software from the below location:

Two instances of PostgreSQL are installed on the system

1. Director Database

2. Results Database

JDBC connection strings are exposed in the file, located in oedq_local_home directory

3. If you are using a 64-bit system, do the following

Stop the Oracle EDQ Application Server Service and EDQ Database Service

Windows: services.msc and search above and stop

4. Search for executable dnConfig, click on Application Server, check use 64-bit JVM and apply JVM change. Goto Java tab and use 50% of RAM, click OK

5. Start the EDQ database services and Application server service

6. Change the default password

click on enterprise data quality launchpad

Navigates to http://server name:port number/edq


Click on Change password, enter dnadmin on username and password. Enter new password and click submit


Now all set to use EDQ. Enjoy 😀