Oracle Consistent Streams Based CDC - The Window ID Is Out of Range (Doc ID 1576058.1)

Last updated on OCTOBER 13, 2023

Applies to:

Symptoms

Using Oracle Data Integrator (ODI) "JKM Oracle 11g Consistent (Streams)" for incremental changes with Oracle Database 11g.

The Subscriber window is falling out of range for a couple of records, meaning that, when the subsequent *consumer* Integration Interfaces are being executed, certain rows are not being processed.

Following are the implemented steps:

1. Add the Model to CDC, create Subscriber, and start the Journal.
2. Make changes to table A, lets say 6 records of it. Verify that all the 6 records are getting captured in J$ journal tables.
3. Extend the window, and lock the Subscriber.
4. Run the Integration Interface, and observe that some of the records in the J$ journal tables are not *consumed*.

Following SQL command has been used to finger-point the records that are falling out of range:

select distinct decode(TARG.ROWID, null, 'D', 'I') JRN_FLAG,
       sysdate            JRN_DATE,
       SUB.CDC_SUBSCRIBER JRN_SUBSCRIBER, 
       JRN.TEST_ID        TEST_ID,
       TARG.TEST_NAME     TEST_NAME 
from   MYSCHEMA.J$TEST       JRN, 
       MYSCHEMA.SNP_CDC_SUBS SUB, 
       MYSCHEMA.TEST         TARG
where  JRN.TEST_ID      =  TARG.TEST_ID (+) 
and    SUB.CDC_SET_NAME =  'MYSCHEMA.MYTABLE' 
and    JRN.CSCN$        >  SUB.MIN_WINDOW_ID
and    JRN.CSCN$       <=  SUB.MAX_WINDOW_ID;

For the rows that are not being picked up by the Integration Interface, the last condition in the above query doesn't return any record.

Cause