An ODI Journalizing Knowledge Module for GoldenGate Integrated Replicat

June 4, 2015 | 5 minute read
Christophe Dupupet
Sr Director, A-Team - Cloud Solutions Architects
Text Size 100%:

Validation

Content validated on 5/6/2021 with
  • ODI Version 12.2.1.4.200721.1540
  • Oracle GoldenGate 12c (12.3.0.1)

Introduction

One of the new features in GoldenGate 12c is the Integrated Replicat apply mode.

All out-of-the-box versions of the ODI JKM for GoldenGate to this date were designed for the Classic Replicat apply mode and they rely on the Checkpoint table maintained by GoldenGate. This table is used to figure out which changed records can reliably be processed by ODI. However, if you choose to use the Integrated Replicat apply mode of GoldenGate, there is no Checkpoint table anymore.

This post proposes a solution to modify the out-of-the-box JKM for GoldenGate to support Integrated Replicat apply mode.

Update: As of February 2nd, 2016, the techniques described in this article are implemented in out-of-the-box KMs in ODI patch #17017980. Upcoming releases of ODI will continue to support Integrated Replicat as well as Classic Replicat.

Out-of-the-box JKMs for Oracle GoldenGate

In a nutshell, ODI maintains window_ids to keep track of the primary keys (PKs) of new, updated to deleted the records, and uses the GoldenGate Checkpoint table to seed these window-ids: this seeding is called the Extend Window operation. If you want more details on the inner workings of the out-of the box JKMs and how they leverage the GoldenGate checkpoint table with Classic Replicat, the post Understanding the ODI JKMs and how they work with Oracle GoldenGate will provide all the necessary background.

Understanding Oracle SCN and GoldenGate CSN

The Oracle database provides a very good description of what a SCN is: documentation: “A system change number (SCN) is a logical, internal time stamp used by Oracle Database. SCNs order events that occur within the database, which is necessary to satisfy the ACID properties of a transaction. Oracle Database uses SCNs to mark the SCN before which all changes are known to be on disk so that recovery avoids applying unnecessary redo. (…) Every transaction has an SCN.” You can find the complete description here: System Change Numbers (SCNs).

The Oracle GoldenGate documentation describes a CSN in the section About the Commit Sequence Number as follows: “A CSN is a monotonically increasing identifier generated by Oracle GoldenGate that uniquely identifies a point in time when a transaction commits to the database.“

On an Oracle database, GoldenGate will use the database SCN for its CSN.

Description of Integrated Replicat

With the Integrated Replicat mode, GoldenGate constructs logical change records (LCR) that represent source database DML transactions instead of constructing SQL statements. This approach greatly improves performance and reliability of the write process, and as such it does not require a Checkpoint Table.

The Integrated Replicat stores details of its processing in a system table: SYS.DBA_GG_INBOUND_PROGRESS.

This table stores the source SCN (System Change Number) of the last record processed by each Replicat (or GoldenGate CSN for non-Oracle sources). All records up to the APPLIED_LOW_POSITION SCN are guaranteed to be committed. Records between APPLIED_LOW_POSITION and APPLIED_HIGH_POSITION are being processed (i.e. they could be committed or not).

Figure 1 shows the complete structure of the table:

Table SYS.DBA_GG_INBOUND_PROGRESS(

SERVER_NAME                                          VARCHAR2(128)
PROCESSED_LOW_POSITION                  VARCHAR2(4000)
APPLIED_LOW_POSITION                         VARCHAR2(4000)
APPLIED_HIGH_POSITION                         VARCHAR2(4000)
SPILL_POSITION                                        VARCHAR2(4000)
OLDEST_POSITION                                   VARCHAR2(4000)
APPLIED_LOW_SCN                                  NUMBER
APPLIED_TIME                                           DATE
APPLIED_MESSAGE_CREATE_TIME         DATE
SOURCE_DATABASE                                 VARCHAR2(128)
SOURCE_ROOT_NAME                            VARCHAR2(128))

Figure 1: Structure of the SYS.DBA_GG_INBOUND_PROGRESS view.

If you want more details on Integrated Replicat for Oracle GoldenGate, the Oracle documentation provides an excellent description of the technology and its benefits here: Choosing Capture and Apply Modes.

The challenge for ODI is that there is no way to relate the source SDN with anything that can be stored in the J$ table, hence the need for a new approach.

Description of the new approach

Instead of having GoldenGate provide a WINDOW_ID when PKs are written to the J$ table, we remove the WINDOW_ID column altogether and we replace it with the Oracle database ORA_ROWSCN Pseudocolumn. The SCN is assigned by the database when the transaction completes: this provides us with a reliable value that we can use as a WINDOW_ID at no additional cost.

To have row level detail in that pseudo column, we have to create the J$ table with the option ROWDEPENDENCIES (for more details on this option, see this post from Tom Kyte: Using the Oracle ORA_ROWSCN). From then on, all we need is to retrieve the current SCN from the database when we do the ‘Extend Window’ operation: all records committed at this point in the J$ table are available for CDC processing. We can retrieve this value with the command:

select CURRENT_SCN from v$database

Note that the ODI user needs the ‘Select’ privilege on the ‘v$database’ view to be able to run this query.

Steps to modify in the JKM

A modified version of the out-of-the-box JKM is available here: JKM Oracle to Oracle Consistent (OGG Online) Integrated Replicat. In this implementation, all KM tasks that were modified from the original JKM have their name prefixed with a * sign.

Table 1 below shows the alterations to the original JKM for the modified tasks (all changes are done in the Target Command of the tasks):

Table 1: Code changes to the original JKM

You can also delete the KM option called CHECKPOINT_TABLE_NAME option since it is not used by the JKM anymore.

Beyond Integrated Replicat

This approach can be expanded beyond the use-case described here: SCNs can be used in the J$ tables independently of the capture mechanism and, in the case of GoldenGate, can be used whether Integrated Replicat is used or not to deliver the data.

When similar mechanisms are available for non-Oracle databases, they can be used as well. For instance Microsoft SQL server allows the creation of a column of type rowversion that can be used for the same purpose.

Conclusion

With relatively simple modifications to the original JKM, ODI can now support the Oracle GoldenGate Integrated Replicat apply mode. This will allow you to take advantage of all the benefits provided by this new mode and allows further integration with ODI.

References

The following references were used to aggregate the necessary information for this post:

For more ODI best practices, tips, tricks, and guidance that the A-Team members gain from real-world experiences working with customers and partners, visit Oracle A-Team Chronicles for ODI. For Oracle GoldenGate, visit “Oracle A-Team Chronicles for GoldenGate

Acknowledgements

Special thanks to Tim Garrod for pointing out the possible use of SCNs, Valarie Bedard, Nick Wagner and Mony Subramony for reviewing and validating the successive approaches and attempts, and Pat McElroy for sharing her expertise on Integrated Replicat.

 

Christophe Dupupet

Sr Director, A-Team - Cloud Solutions Architects


Previous Post

Java Flight Recorder

Kiran Thakkar | 9 min read

Next Post


Invoke Fusion Cloud Secured RESTFul Web Services

Jack Desai | 6 min read