Validation

• ODI Version 12.2.1.4.200721.1540
• Oracle GoldenGate 12c (12.3.0.1)

Introduction

Thanks to the ODI JKMs for GoldenGate, we do not need to have much experience with GoldenGate to configure a replication environment: ODI generates all the necessary parameters for GoldenGate; it can communicate in real-time with your GoldenGate instance and make sure that changes propagated by GoldenGate will be processed by ODI with no additional setup or configuration.

But when we already have a fine tuned GoldenGate installation, the last thing we want is for this end-to-end automation to alter or overwrite the existing configuration.  This is when we need to decorrelate both processes. This is actually a very simple operation. The following steps by step instructions will allow to safely take advantage of the integration between both products without damaging your existing environment.

1. Define Goldengate in the ODI Topology

All the details to configure the ODI topology to support GoldenGate are available here.

We do have to define a GoldenGate Data Server whether we will have an online connection of not, so that ODI can receive the changes propagated by Goldengate. In our case, because we want to leverage an existing installation of GoldenGate, we have to pay attention to the following elements:

• in the Physical Architecture, the name of the Capture and Delivery process MUST match the names used by GoldenGate for its own Capture and Replicat processes. It is particularly important as these name will be used in the data used in the GoldenGate Checkpoint Tables. This will become apparent in the ODI Packages that process changed data, when we use the ‘Extend Window’ command as shown here.

• If there is more than one GoldenGate Replicat configured to apply changes to the GoldenGate target database, ODI will add incremental numbers to the physical name of the replicats defined in the ODI Topology. If the ‘Physical name’ is set to REPSALE, ODI will expect replicats named REPSALE1, REPSALE2, etc. This is why there is a limit in how many characters can be used in that field in the ODI Topology. We have to make sure that the existing GoldenGate installation adheres to this standard.  

• If you only have one single replicat, the number ‘1’ will still be added to the name of that replicat (so REPSALE1 in our previous example). We have to make sure that the existing GoldenGate installation adheres to this standard. 

2. Associate the GG JKM to the ODI Model that you are replicating from

Details on how to associate the JKM to the ODI model are available here. We select the JKM in the ‘Journalizing’ tab of the model. There are three options in the JKM that are of particular interest to us:

• The ‘ONLINE option must be set to false (this is the default anyways) to make sure that ODI will not try to overwrite existing GoldenGate configuration files;

• The ‘LOCAL_TEMP_DIRECTORY’ option must be set to a valid path on the system that will execute the code of the JKM (usually the system where we installed the agent that will be used to execute the command ‘Start Journal…’).

• The ‘CHECKPOINT_TABLE_NAME’ option must match the name of the GoldenGate Checkpoint table. ODI will expect this table in the ‘work schema’ of the default ‘physical schema’ of the database data server (the server that is the target for the GoldenGate replication, and the source for ODI jobs). This default schema is where ODI will store all the tables related to its CDC infrastructure, as explained here.

• If there is more than one replicat process, the option ‘NB_APPLY_PROCESS’ (default is 1) must be set to match that count. This is what ODI uses to rename the replicat processes as described earlier.

3. Generate the GG Files

There is a very good step-by-step description on how to setup the ODI journalization here (a little dated, but still very accurate): when you use the command ‘Start the Journal’, ODI will create its own infrastructure table and generate the necessary prm and oby files for a new installation of GoldenGate. The only element we need to retrieve from these files is in the APPLY prm file. ODI generates new MAPS that must be added to the existing GG replication: we have to make sure that GoldenGate updates the ODI J$ tables with the appropriate primary keys and window IDs when changed data are replicated. For instance this simple MAP:

map ODI_DEMO.AGG_SALES, TARGET ODI_DEMO.AGG_SALES, KEYCOLS (SALES_ID);

Would become (the bold section is the part we need to retrieve from the generated APPLY prm file):

map ODI_DEMO.AGG_SALES, TARGET ODI_DEMO.AGG_SALES, KEYCOLS (SALES_ID);
map ODI_DEMO.AGG_SALES, target ODI_STAGING.J$AGG_SALES, KEYCOLS (SALES_ID, WINDOW_ID),  INSERTALLRECORDS, OVERRIDEDUPS, WINDOW_ID = @STRCAT(@GETENV('RECORD', 'FILESEQNO'), @STRNUM(@GETENV('RECORD', 'FILERBA'), RIGHTZERO, 10)) ;

Once our GoldenGate prm files are up to date, we  have to stop and restart the GoldenGate replication manually so that GoldenGate updates the ODI J$ tables as well as the usual GoldenGate target tables.

At this point, we are ready to consume changed data with ODI as we would have otherwise.

4. Additional comments on using a fully integrated solution

Automating everything with ODI means that ODI will start and stop GoldenGate each time the users use the commands ‘Start Journal…’ and ‘Stop Journal…’. This behavior is extremely convenient when we get started with GoldenGate: it guarantees that ODI will continuously update the oby and prm files with our changes, and restart GoldenGate. But this can be counterproductive when our GoldenGate replication is already in place and only needs to be adapted to feed the proper ODI infrastructure tables.

Conclusion