Skip to main content

Provide Unique identifier to Oracle CDC records with exact same Timestamp and SCN #.

  • January 23, 2022
  • 0 replies
  • 153 views
AkshayJadhav
StreamSets Employee
Forum|alt.badge.img

Question :

How can we differentiate 2 CDC records which has exact same timestamp and SCN #.

 

Solution :

There are a number of unique identifier fields that can be dumped as shown in the screenshots attached to this update.

In my case I chose:

${record:attribute('oracle.cdc.SSN')} and ${record:attribute('SEQ')}


The definition of SSN is provided in Oracle documentation link https://docs.oracle.com/en/database/oracle/oracle-database/12.2/refrn/V-LOGMNR_CONTENTS.html#GUID-B9196942-07BF-4935-B603-FA875064F5C3
 

SSN

NUMBER

SQL sequence number. Used in conjunction with RS_ID, this uniquely identifies a logical row change, shown as one or more rows from the V$LOGMNR_CONTENTS view.


I see we also dump some more Unique transaction identifiers like XID and RS_ID which is again defined in the Oracle documentation https://docs.oracle.com/en/database/oracle/oracle-database/12.2/refrn/V-LOGMNR_CONTENTS.html#GUID-B9196942-07BF-4935-B603-FA875064F5C3
 

RS_ID

VARCHAR2(32)

Record set ID. The tuple (RS_ID, SSN) together uniquely identifies a logical row change. This will usually mean one row from V$LOGMNR_CONTENTS, but could be more than one row if a single SQL statement for either the Redo or Undo would be too large to fit within the respective columns SQL_UNDO or SQL_REDO. RS_ID uniquely identifies the redo record that generated the row.

 

XID

RAW(8)

Raw representation of the transaction identifier


Here is my sample record that has same SCN and Timestamp but the SSN uniquely identifies the SQL Sequence
 

CT_ID":43,"EDITIONABLE":null,"TIMESTAMP":"2019-12-03:22:21:54","TEMPORARY":"N","GENERATED":"N","CREATED_VSNID":null,"DATA_OBJECT_ID":43,"SHARDED":"N","ORACLE_MAINTAINED":"Y","DUPLICATED":"N","MODIFIED_VSNID":null,"MODIFIED_APPID":null,"OBJECT_NAME":"I_FILE1","DEFAULT_COLLATION":null,"CREATED_APPID":null,"SCN":"605777","ORACLECDCTS":"2019-12-04 15:56:33","ROWID":null,"QUERY":null,"OPERATION":"INSERT","SSN":"53","SEQ":"1"}
{"NAMESPACE":4,"SHARING":"NONE","SUBOBJECT_NAME":null,"LAST_DDL_TIME":1575391914000,"APPLICATION":"N","SECONDARY":"N","OBJECT_TYPE":"INDEX","EDITION_NAME":null,"OWNER":"SYS","CREATED":1575391914000,"STATUS":"VALID","OBJECT_ID":47,"EDITIONABLE":null,"TIMESTAMP":"2019-12-03:22:21:54","TEMPORARY":"N","GENERATED":"N","CREATED_VSNID":null,"DATA_OBJECT_ID":47,"SHARDED":"N","ORACLE_MAINTAINED":"Y","DUPLICATED":"N","MODIFIED_VSNID":null,"MODIFIED_APPID":null,"OBJECT_NAME":"I_USER2","DEFAULT_COLLATION":null,"CREATED_APPID":null,"SCN":"605777","ORACLECDCTS":"2019-12-04 15:56:33","ROWID":null,"QUERY":null,"OPERATION":"INSERT","SSN":"54","SEQ":"1"}
{"NAMESPACE":4,"SHARING":"NONE","SUBOBJECT_NAME":null,"LAST_DDL_TIME":1575391914000,"APPLICATION":"N","SECONDARY":"N","OBJECT_TYPE":"INDEX","EDITION_NAME":null,"OWNER":"SYS","CREATED":1575391914000,"STATUS":"VALID","OBJECT_ID":45,"EDITIONABLE":null,"TIMESTAMP":"2019-12-03:22:21:54","TEMPORARY":"N","GENERATED":"N","CREATED_VSNID":null,"DATA_OBJECT_ID":45,"SHARDED":"N","ORACLE_MAINTAINED":"Y","DUPLICATED":"N","MODIFIED_VSNID":null,"MODIFIED_APPID":null,"OBJECT_NAME":"I_TS1","DEFAULT_COLLATION":null,"CREATED_APPID":null,"SCN":"605777","ORACLECDCTS":"2019-12-04 15:56:33","ROWID":null,"QUERY":null,"OPERATION":"INSERT","SSN":"55","SEQ":"1"}


What I have highlighted above is the Record header that I am capturing. In the case of these records, you can note that the times and SCNs are the same but the SSN value is different.

Did this topic help you find an answer to your question?
This topic has been closed for comments
Terms & Conditions