Monday Jul 01, 2013

Oracle GoldenGate 11g Certified Implementation Beta Exam Available

We have great news for Oracle Data Integration partners:  Oracle GoldenGate 11g Certified Implementation Beta Exam is now available. 

The Oracle GoldenGate 11g Certified Implementation Exam Essentials (1Z1-481) exam is designed for individuals who possess a strong foundation and expertise in selling and implementing Oracle Data Integration 11g solutions.

This certification covers topics such as:

  • Oracle GoldenGate 11g Overview
  • Architecture Overview, 
  • Configuring Oracle GoldenGate
  • Parameters, Mapping and Transformation Overview, 
  • Configuration Options, 
  • Managing and Monitoring Oracle GoldenGate 11g. 
This certification helps OPN members differentiate themselves in the marketplace through proven in-depth expertise and helps their partner company qualify for the Oracle Data Integration 11g Specialization Criteria. We recommend up-to-date training and field experience. OPN members earning this certification will be recognized as OPN Certified Specialists.

Request a discounted beta voucher today using the OPN Beta Certified Specialist Exam  Voucher Request Form
You can take the exam now at a near-by Pearson VUE testing center.

Monday Apr 29, 2013

Monitoring Oracle GoldenGate 11g Release 2

One of the key improvements in Oracle GoldenGate 11g Release 2 is in the monitoring area. This release brought  integration with Oracle Enterprise Manager 12c, as well as a new product dedicated for monitoring Oracle GoldenGate, called Oracle GoldenGate Monitor.  This new product is included in the Management Pack for Oracle GoldenGate and offers GUI-based solution for end-to-end, real-time visibility into Oracle GoldenGate implementations across the enterprise. GoldenGate Monitor increases IT productivity by offering consolidated information for multiple instances of Oracle GoldenGate and via its comprehensive e-mail, SNMP and CLI alert capabilities, among many other features.

We have a new screencast that presents Oracle GoldenGate  Monitor's features and architecture, along with a product demonstration. If you have a GoldenGate solution or plan to deploy one, I highly recommend watching this screencast here or via the embedded video below to learn about monitoring GoldenGate processes via this simple but powerful GUI-based product.

For more information on Oracle GoldenGate 11g Release 2, please check out our free resources.

Monday Dec 03, 2012

ODI 11g - Scripting a Reverse Engineer

A common question is related to how to script the reverse engineer using the ODI SDK. This follows on from some of my posts on scripting in general and accelerated model and topology setup.

Check out this viewlet here to see how to define a reverse engineering process using ODI's package.

Using the ODI SDK, you can script this up using the OdiPackage and StepOdiCommand classes as follows;

  1.  OdiPackage pkg = new OdiPackage(folder, "Pkg_Rev"+modName);
  2.   StepOdiCommand step1 = new StepOdiCommand(pkg,"step1_cmd_reset");
  3.   step1.setCommandExpression(new Expression("OdiReverseResetTable \"-MODEL="+mod.getModelId()+"\"",null, Expression.SqlGroupType.NONE));
  4.   StepOdiCommand step2 = new StepOdiCommand(pkg,"step2_cmd_reset");
  5.   step2.setCommandExpression(new Expression("OdiReverseGetMetaData \"-MODEL="+mod.getModelId()+"\"",null, Expression.SqlGroupType.NONE));
  6.   StepOdiCommand step3 = new StepOdiCommand(pkg,"step3_cmd_reset");
  7.   step3.setCommandExpression(new Expression("OdiReverseSetMetaData \"-MODEL="+mod.getModelId()+"\"",null, Expression.SqlGroupType.NONE));
  8.   pkg.setFirstStep(step1);
  9.   step1.setNextStepAfterSuccess(step2);
  10.   step2.setNextStepAfterSuccess(step3);

The biggest leap of faith for users is getting to know which SDK classes have to be used to build the objects in the design, using StepOdiCommand isn't necessarily obvious, once you see it in action though it is very simple to use. The above snippet uses an OdiModel variable named mod, its a snippet I added to the accelerated model creation script in the post linked above.

Thursday Sep 13, 2012

Thank You for a Great Welcome for Oracle GoldenGate 11g Release 2

Yesterday morning we had two launch webcasts for Oracle GoldenGate 11g Release 2. I had the pleasure to present, as well as moderate the Q&A panels in both of these webcasts. Both events had hundreds of live attendees, sending us over 150 questions. Even though we left 30 minutes for Q&A, it was not nearly enough time to address for all the insightful questions our audience sent. Our product management team and I really appreciate the interaction we had yesterday and we are starting to respond back with outstanding questions today.

Oracle GoldenGate’s new release launch also had great welcome from the media. You can find the links for various articles on the new release below:

ITBusinessEdge Oracle Embraces Cross-Platform Data Integration

Information Week: Oracle Real-Time Advance Taps Compressed Data

Integration Developer News, Oracle GoldenGate Adds Deeper Oracle Integration, Extends Real-Time Performance

CIO, Oracle GoldenGate Buddies Up with Sibling Software

DBTA, Real-Time Data Integration: Oracle GoldenGate 11g Release 2 Now Available

CBR Oracle unveils GoldenGate 11g Release 2 real-time data integration application

In this blog, I want to address some of the frequently asked questions that came up during the webcasts. You can find the top questions and their answers along with related resources below. We will continue to address frequently asked questions via future blogs.

Q: Will the new Integrated Capture for Oracle Database replace the Classic Capture? If not, which one do I use when?

A: No, Classic Capture will be around for long time. Core platform specific features, bug fixes, and patches will be available for both Capture processes.Oracle Database specific features will be only available in the Integrated Capture. The Integrated Capture for Oracle Database is an option for users that need to capture data from compressed tables or need support for XML data types, XA on RAC. Users who don’t leverage these features should continue to use our Classic Capture.

For more information on Oracle GoldenGate 11g Release 2 I recommend to check out the White paper: Oracle GoldenGate 11gR2 New Features as well as other technical white papers we have on OTN.                                                        

For those of you coming to OpenWorld, please attend the related session:

Extracting Data in Oracle GoldenGate Integrated Capture Mode, Monday Oct 1st 1:45pm Moscone South – 102

to learn more about this new feature.

Q: What is new in Conflict Detection and Resolution? And how does it work?

A: There are now pre-built functions to identify the conditions under which an error occurs and how to handle the record when the condition occurs. Error conditions handled include inserts into a target table where the row already exists, updates or deletes to target table rows that exist, but the original source data (before columns) do not match the existing data in the target row, and updates or deletes where the row does not exist in the target database table.For
each of these conditions a method to handle the error is specified. 

Please check out our recent blog on this topic and the White paper: Oracle GoldenGate 11gR2 New Features white paper.

 Also, for those attending OpenWorld please attend the session:

Best Practices for Conflict Detection and Resolution in Oracle GoldenGate for Active/Active-  Wednesday Oct 3rd  3:30pm Mascone 3000

Q: Does Oracle GoldenGate Veridata and the Management Pack require additional licenses, or is it incorporated with the GoldenGate license?

A: Oracle GoldenGate Veridata and Oracle Management Pack for Oracle GoldenGate are additional products and require separate licenses. Please check out Oracle's price list here.

Q: Does GoldenGate - Oracle Enterprise Manager Plug-in require additional license?

A: Oracle Enterprise Manager Plug-in is included in the Oracle Management Pack for Oracle GoldenGate license, which is separate from Oracle GoldenGate license. There is no separate license for the Enterprise Manager Plug-in by itself. Oracle GoldenGate Monitor, Oracle GoldenGate Director, and Enterprise Manager Plug-in are included in the Management Pack for Oracle GoldenGate license. Please check out Management Pack for Oracle GoldenGate data sheet for more info on this product bundle.

Q: Is Oracle GoldenGate replacing Oracle Streams product?

A: Oracle GoldenGate is the strategic data replication product. Therefore, Oracle Streams will continue to be supported, but will not be actively enhanced. Rather, the best elements of Oracle Streams will be added to Oracle GoldenGate. Conflict management is one of them and with the latest release Oracle GoldenGate has a more advanced conflict management offering. Current customers depending on Oracle Streams will continue to be fully supported.

Q: How is Oracle GoldenGate different than Oracle Data Integrator?

A: Oracle Data Integrator is designed for fast bulk data movement and transformation between heterogeneous systems, while GoldenGate is designed for real-time movement of transactions between heterogeneous systems. These two products are completely complementary where GoldenGate provides low-impact real-time change data capture and delivery to a staging area on the target. And Oracle Data Integrator transforms this data and loads the DW tables. In fact, Oracle Data Integrator integrates with GoldenGate to use GoldenGate’s Capture process as one option for its CDC mechanism. We have several customers that deployed GoldenGate and ODI together to feed real-time data to their data warehousing solutions. Please also check out Oracle Data Integrator Changed Data Capture with Oracle GoldenGate Data Sheet (PDF).

Thank you again very much for welcoming Oracle GoldenGate 11g Release 2 and stay in touch with us for more exciting news, updates, and events.

Monday Aug 20, 2012

Book Review Getting Started with Oracle Data Integrator 11g

The Getting Started with Oracle Data Integrator 11g: A hands On Tutorial book is a good introduction to ODI with some common examples that will be useful to many. It illustrates how data can be imported from various sources, transformed and exported to various sources with ODI. Following the step by step guide of the examples and you will have the right knowledge at the end of the book to create your own data integration solutions. The book is easy to read for novices and offers a great introduction to the world of ODI with useful tips from the authors - and there are a few of them ;-)

This book is an ideal book for somebody who wants to learn quickly about ODI, it's not a reference manual but a good introduction. It is filled with an abundance of useful info, I even discovered new information myself! Such as the Column Setup Wizard when importing flat file data. The chapter on Working with XML Files is also a useful one for anyone working in that area clearly spelling out with an example how it works.

The first and second chapters are all about the product overview and installation. Chapter 3 introduces variables which is a useful reference since it details now only how variables are defined but also where they can be used - this is a much asked question which I have seen many ask for mailing lists and forums. So good to see it included here. The fourth chapter introduces the design objects including topology, models and interfaces. There are useful notes scattered throughout the book which are worth checking out - these are little insights into useful hints on using the tool, personally these are my favorite items in the book. Chapters five, six and seven have illustrations of working with databases including Oracle, MySQL and Microsoft SQL Server covering various areas along the way from building interfaces using ODI's declarative designer, designing lookups, writing transformation expressions and KM selection. Chapters eight and nine are all about files and XML files and as I mentioned even surprised me with a few little pieces of info. Chapter 10 covers the workflow oriented objects including load plans, packages and procedures - a lot for a single chapter but an ok introduction. These 3 could easily be expanded into much, much more information. Chapter 11 covers errors in general - nice to see this too, this gives some insight of how the error handling in ODI works, and where to go to check up on errors. Finally there is an introduction to the ODI management components including the integration with Oracle's Enterprise Manager and the ODI console itself. There are some topics covered quickly such as procedures and I couldn't find any information on user functions, but on the whole it's a good start.

All in all, this book is in excellent read for somebody who wants to have a quick start in ODI. Its a useful book to dive into each chapter and have a read up on topics you don;t have to read end to end - my favorite type. Its an introduction, not a cookbook, the cookbook would be another useful companion book for ODI! This book is ideal for somebody who wants to get up and running in a short amount of time. If you interested in this book, you can get further info from Packt Publishing  here plus see Julien's earlier blog post here on discounts.

Thursday Jul 12, 2012

Discount Codes for 'Getting Started with ODI 11g: A Hands-On Tutorial' Book

If you would like to get your hands on a copy of 'Getting Started with ODI 11g: A Hands-On Tutorial', Packt Publishing provided us with discount codes you can use to buy the book from their website (

The codes are as follows :

  • odi5pbk for 15% off on print books.
  • rfk6ebk for 25% off on eBooks.

You will need to enter the respective discount codes in the “Promotional code” field and click “Update” in your shopping cart. The discount will be applied to the purchase.

Friday Jul 06, 2012

ODI 11g - Oracle Data Integrator 11g – A Hands-On Tutorial

I've have been asked by Packt publishing to review a brand new book on Oracle Data Integrator: Getting Started with Oracle Data Integrator 11g – A Hands-On Tutorial. Waiting on this book to arrive and see what goodies are inside, I'll blog a review later. The book can be found at Oracle Data Integrator 11g – A Hands-On Tutorial

Looking at the table of contents, it looks like it gives a good broad introduction (including various data formats) to the product;

  • Chapter 1: Product Overview
  • Chapter 2: Product Installation
  • Chapter 3: Using Variables
  • Chapter 4: ODI Sources, Targets, and Knowledge Modules
  • Chapter 5: Working with Databases
  • Chapter 6: Working with MySQL
  • Chapter 7: Working with Microsoft SQL Server
  • Chapter 8: Integrating File Data
  • Chapter 9: Working with XML Files
  • Chapter 10: Creating Workflows—Packages and Load Plans
  • Chapter 11: Error Management
  • Chapter 12: Managing and Monitoring ODI Components
  • Chapter 13: Concluding Remarks

Looking forward to it.

Tuesday Jun 19, 2012

ODI 11g – Insight to the SDK

This post is a useful index into the ODI SDK that cross references the type names from the user interface with the SDK class and also the finder for how to get a handle on the object or objects. The volume of content in the SDK might seem a little ominous, there is a lot there, but there is a general pattern to the SDK that I will describe here.

Also I will illustrate some basic CRUD operations so you can see how the SDK usage pattern works. The examples are written in groovy, you can simply run from the groovy console in ODI

Entry to the Platform


Object Finder SDK
odiInstance odiInstance (groovy variable for console) OdiInstance

Topology Objects

Object Finder SDK
Technology IOdiTechnologyFinder OdiTechnology
Context IOdiContextFinder OdiContext
Logical Schema IOdiLogicalSchemaFinder OdiLogicalSchema
Data Server IOdiDataServerFinder OdiDataServer
Physical Schema IOdiPhysicalSchemaFinder OdiPhysicalSchema
Logical Schema to Physical Mapping IOdiContextualSchemaMappingFinder OdiContextualSchemaMapping
Logical Agent IOdiLogicalAgentFinder OdiLogicalAgent
Physical Agent IOdiPhysicalAgentFinder OdiPhysicalAgent
Logical Agent to Physical Mapping IOdiContextualAgentMappingFinder OdiContextualAgentMapping
Master Repository IOdiMasterRepositoryInfoFinder OdiMasterRepositoryInfo
Work Repository IOdiWorkRepositoryInfoFinder OdiWorkRepositoryInfo

Project Objects

Object Finder SDK
Project IOdiProjectFinder OdiProject
Folder IOdiFolderFinder OdiFolder
Interface IOdiInterfaceFinder OdiInterface
Package IOdiPackageFinder OdiPackage
Procedure IOdiUserProcedureFinder OdiUserProcedure
User Function IOdiUserFunctionFinder OdiUserFunction
Variable IOdiVariableFinder OdiVariable
Sequence IOdiSequenceFinder OdiSequence
KM IOdiKMFinder OdiKM

Load Plans and Scenarios


Object Finder SDK
Load Plan IOdiLoadPlanFinder OdiLoadPlan
Load Plan and Scenario Folder IOdiScenarioFolderFinder OdiScenarioFolder

Model Objects

Object Finder SDK
Model IOdiModelFinder OdiModel
Sub Model IOdiSubModel OdiSubModel
DataStore IOdiDataStoreFinder OdiDataStore
Column IOdiColumnFinder OdiColumn
Key IOdiKeyFinder OdiKey
Condition IOdiConditionFinder OdiCondition

Operator Objects


Object Finder SDK
Session Folder IOdiSessionFolderFinder OdiSessionFolder
Session IOdiSessionFinder OdiSession
Schedule OdiSchedule

How to Create an Object?

Here is a simple example to create a project, it uses IOdiEntityManager.persist to persist the object.

import oracle.odi.domain.project.OdiProject;

txnDef = new DefaultTransactionDefinition();
tm = odiInstance.getTransactionManager()
txnStatus = tm.getTransaction(txnDef)

project = new OdiProject("Project For Demo", "PROJECT_DEMO")

How to Update an Object?

This update example uses the methods on the OdiProject object to change the project’s name that was created above, it is then persisted.

import oracle.odi.domain.project.OdiProject;
import oracle.odi.domain.project.finder.IOdiProjectFinder;

txnDef = new DefaultTransactionDefinition();
tm = odiInstance.getTransactionManager()
txnStatus = tm.getTransaction(txnDef)

prjFinder = (IOdiProjectFinder)odiInstance.getTransactionalEntityManager().getFinder(OdiProject.class);
project = prjFinder.findByCode("PROJECT_DEMO");

project.setName("A Demo Project");


How to Delete an Object?

Here is a simple example to delete all of the sessions, it uses IOdiEntityManager.remove to delete the object.

import oracle.odi.domain.runtime.session.finder.IOdiSessionFinder;
import oracle.odi.domain.runtime.session.OdiSession;

txnDef = new DefaultTransactionDefinition();
tm = odiInstance.getTransactionManager()
txnStatus = tm.getTransaction(txnDef)

sessFinder = (IOdiSessionFinder)odiInstance.getTransactionalEntityManager().getFinder(OdiSession.class);
sessc = sessFinder.findAll();
sessItr = sessc.iterator()
while (sessItr.hasNext()) {
  sess = (OdiSession)

This isn't an all encompassing summary of the SDK, but covers a lot of the content to give you a good handle on the objects and how they work. For details of how specific complex objects are created via the SDK, its best to look at postings such as the interface builder posting here. Have fun, happy coding!

ODI 11g – Expert Accelerator for Model Creation

Following on from my post earlier this morning on scripting model and topology creation tonight I thought I’d add a little UI to make those groovy functions a little more palatable. In OWB we have experts for capturing user input, with the groovy console we open up opportunities to build UI around the scripts in a very easy way – even I can do it;-)

After a little googling around I found some useful posts on SwingBuilder, the most useful one that I used for the dialog below was this one here. This dialog captures user input for the technology and context for the model and logical schema etc to be created. You can see there are a variety of interesting controls, and its really easy to do.

The dialog captures the users input, then when OK is pressed I call the functions from the earlier post to create the logical schema (plus all the other objects) and model. The image below shows what was created, you can see the model (with typo in name), the model is Oracle technology and references the logical schema ORACLE_SCOTT (that I named in dialog above), the logical schema is mapped via the GLOBAL context to the data server ORACLE_SCOTT_DEV (that I named in dialog above), and the physical schema used was just the user name that I connected with – so if you wanted a different user the schema name could be added to the dialog.

In a nutshell, one dialog that encapsulates a simpler mechanism for creating a model. You can create your own scripts that use dialogs like this, capture input and process.

You can find the groovy script for this is here odi_create_model.groovy, again I wrapped the user capture code in a groovy function and return the result in a variable and then simply call the createLogicalSchema and createModel functions from the previous posting. The script I supplied above has everything you will need. To execute use Tools->Groovy->Open Script and then execute the green play button on the toolbar.

Have fun.

ODI 11g - Faster Files

Deep in the trenches of ODI development I raised my head above the parapet to read a few odds and ends and then think why don’t they know this? Such as this article here – in the past customers (see forum) were told to use a staging route which has a big overhead for large files. This KM is an example of the great extensibility capabilities of ODI, its quite simple, just a new KM that;

  1. improves the out of the box experience – just build the mapping and the appropriate KM is used
  2. improves out of the box performance for file to file data movement.

This improvement for out of the box handling for File to File data integration cases (from the companion CD and on) dramatically speeds up the file integration handling. In the past I had seem some consultants write perl versions of the file to file integration case, now Oracle ships this KM to fill the gap. You can find the documentation for the IKM here. The KM uses pure java to perform the integration, using classes to read and write the file in a pipe – it uses java threading in order to super-charge the file processing, and can process several source files at once when the datastore's resource name contains a wildcard. This is a big step for regular file processing on the way to super-charging big data files using Hadoop – the KM works with the lightweight agent and regular filesystems.

So in my design below transforming a bunch of files, by default the IKM File to File (Java) knowledge module was assigned. I pointed the KM at my JDK (since the KM generates and compiles java), and I also increased the thread count to 2, to take advantage of my 2 processors.

For my illustration I transformed (can also filter if desired) and moved about 1.3Gb with 2 threads in 140 seconds (with a single thread it took 220 seconds) - by no means was this on any super computer by the way. The great thing here is that it worked well out of the box from the design to the execution without any funky configuration, plus, and a big plus it was much faster than before,

So if you are doing any file to file transformations, check it out!

Friday Jul 08, 2011

ODI SDK: Leveraging ODI Tools with Groovy

How to leverage the ODI SDK to dynamically create a scenario that uses an ODI tool and execute the scenario.[Read More]

Thursday Jul 07, 2011

ODI SDK: How to Automate Changes to Interfaces

Use the ODI SDK to list interfaces in the repository and perform changes in the interfaces

[Read More]

ODI SDK: Browsing The Repository to List All Interfaces

How to leverage the ODI SDK to list all projects, folders, subfolders and interfaces.[Read More]

Tuesday Jul 05, 2011

ODI 11g - Pipelines

Carrying on from the simple, flexible, powerful posting on the code generation capabilities of ODI 11g which illustrated the SQL as a Source option we can see how additional new code generation components can be defined (which let us capture information about specific transformations) and assemble in a pipeline. This is the kind of info I covered in my ODTUG KScope11 'Pushing the Envelope with ODIEE' talk at Long Beach this year - had a great time meeting everyone there!

The example we will illustrate here is using table functions (could be other transformations such as SQL unpivot) in the Oracle database – table functions are functions that can be queried like a table and support parallelization capabilities along with piping of data which are critical elements for improving performance and leveraging capabilities of the server (the same approach can be used for DB2 table functions or Microsoft SQLServer CROSS APPLY style capabilities). The flow can be constructed by piping SQL and transformations (like table functions) in an assembly like manner by constructing interfaces in a modular manner.

Below is an illustration of a number of interfaces assembled in a pipeline which can leverage the code generation capabilities in ODI 11g for assembling components.

We have seen from other posts how the interfaces can be organized into flows which are resolved into a single SQL (for example) with sub-queries. In this post you will see how transformations such as table functions can be incorporated into a design and the components assembled. The ODI 11g capability to define the SQL subquery generator within a KM opens the door to this flexibility. Table functions themselves are useful for many things;

  • parallelizing arbitrarily complex (PLSQL, SQL PL) code in a SQL manner
  • eliminating intermediate storage
  • SQL-izing the stored procedure

In the illustration below I have a pipelined table function FX which is a temporary target data store process data from datastores T1 and T2. The output of the table function is then joined with T3 and further consumed in target FX_TAB. The temporary datastore FX (representing the table function) has user defined properties set which indicate the inputs and outputs for FX (A and B are inputs and have expressions, X and Y are outputs).
The image has the two interfaces side by side, on the left we have a temporary interface for T1 and T2 joined and providing data for table function FX. On the right, the result of the table function is joined with T3 and written to FX_TAB.

The above illustrates how table functions can be used in pipelined manner with query inputs. The meaning of the columns in the FX temporary target datastore is inferred from the user defined property settings – this lets us distinguish the inputs from the outputs. Below in the KM definition (SQL_TableFunction) we can see how the SQL for the transformation type is generated.

Things to note are that the KM supports both cursor and scalar definitions (so a query can be the input to the table function or alternatively you can provide scalar values such as ODI variables, the option CURSOR_INPUT provides the switch between cursor and scalar). So, importantly table functions can be used as a data source, as transformation pipes and and targets (since there is a step in the KM for this). A good use case for a target table function is when the results of the SQL are being written in parallel to file for example.

In my case I used the cursor approach, so the CURSOR_INPUT option for the SQL_TableFunction KM assignment is set to true (the default);

The cursor option in the KM lets us generate either the following style for the cursor;

…from TABLE ( <tablefunction> ( CURSOR(SELECT <columns> FROM <tables> where <conditions>

or for scalar inputs;

…from TABLE ( <tablefunction> ( <variables> ) )

Simple and flexible. As you can see we can assemble a flow of ODI interfaces into a series of pipes that interconnect leveraging ODIs declarative description for the capabilities that are supported well out of the box and extend to encompass table functions for incorporating pipelining or other transformation capabilities that you can think of. A topical area of assembly style problem solving is the divide and conquer Map-Reduce style problem, check out Jean-Pierre’s example from the Oracle Data Warehousing blog MapReduce + Oracle = Table Functions, we can see how this maps to the components we have described above;

each mapper and reducer is an ODI interface.

Where is the sample KM? It's on the Oracle Code Sample site here;

The example above was illustrated using SQL and table functions to chain pipes together, there are other avenues for creating named pipes between interfaces to do much, much more.


Learn the latest trends, use cases, product updates, and customer success examples for Oracle's data integration products-- including Oracle Data Integrator, Oracle GoldenGate and Oracle Enterprise Data Quality


« June 2016