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.
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:

Full article...