Get the redo record's START_SCN and COMMIT_SCN

[bale-lin]

Is your feature request related to a problem? Please describe.
The OpenLogReplicator's json output has scn, c_scn field, like:

{"scn":358135,"tm":1712602205000000000,"c_scn":358141,"xid":"0x0005.00d.00000191","payload":[{"op":"begin"}]}
{"c_scn":358141,"xid":"0x0005.00d.00000191","payload":[{"op":"c","schema":{"owner":"TESTUSR1","table":"T2","obj":20060,"columns":[{"name":"ID","type":"number","precision":-1,"scale":0,"nullable":false,"key_type":2,"key_name":"SYS_C007005","order_by":"ASC"},{"name":"DATA","type":"varchar2","length":128,"nullable":true,"key_type":0,"key_name":"","order_by":"ASC"}]},"after":{"ID":3,"DATA":"##########STR##########STR##########STR##########STR##########STR##########STR##########STR#########3"}}]}
{"c_scn":358141,"xid":"0x0005.00d.00000191","payload":[{"op":"commit"}]}

while dumping redo from Logminer by SQL:

SELECT (XIDUSN || '.' || XIDSLT || '.' || XIDSQN) AS XID, SCN,START_SCN,COMMIT_SCN, TABLE_NAME, SQL_REDO FROM V\$LOGMNR_CONTENTS WHERE REGEXP_LIKE(TABLE_NAME, '(T1|T2|T3|T4|T5)') AND SCN >=${scn}

result is like:

SCN:358141 START_SCN:358135 COMMIT_SCN:358142 XID:5.13.401 SQL:insert into "TESTUSR1"."T2"("ID","DATA") values ('3','##########STR##########STR##########STR##########STR##########STR##########STR##########STR#########3');

after a little big digging, we can find that:

1. the transaction id (xid) is exactly the same
2. the data is exactly the same
3. openlogreplicator's start scn and c_scn is the not same as logminer's start_scn;

Describe the solution you'd like
Know the matching start_scn and commit_scn in openlogreplicator's output or how to calculate the start_scn and commit_scn by simple math op from current JSON output.

BTW, if the meaning of scn in begin output (in above example, is 358135) the meaning of c_scn in commit output? (in above example, is 358141)? Can find any matching record in Logminer's output

Describe alternatives you've considered
not found yet, but maybe can use XID to identify the transaction but it is not monotonically increasing

Additional context
primary usage of start_scn and commit_scn is for smooth connection of full sync and increment sync and validation

[bale-lin]

Thanks for amazing high performance and free Oracle redo parser, Bersler !

[bersler]

@vgrails , Can you provide some redo log file which contains the invalid SCN value?

[bersler]

For me it is a problem to set up everything, logminer, config, etc. I need either a script to run to get the results or some redo log data to reproduce the issue.

[bersler]

After thinking about it I would say this it not a but rather an enhancement. By default OLR puts the commit timestamp to all DML operations from the transaction. This is more logical. Actually if you look at the database and would like to restore the content up to some SCN - the particular DML did not change at that moment. Thus it is misleading that it changed at that SCN. It actually changed at the moment when the transaction has been successfully committed.
Thus, the actual SCN of the DML operation should be the COMMIT SCN.