Extract writing rollback DML in trail file due to DB logminer issue. (Doc ID 3035579.1)

Last updated on NOVEMBER 12, 2024

Applies to:

Symptoms

 Integrated Extract having issues of writing the DML in trail file ,Which was rollback in database.

We have found below Insert statement in logminer report with rollback column value 0.

execute dbms_logmnr.start_logmnr( options => dbms_logmnr.dict_from_redo_logs);

select l.XID, l.OPERATION, l.ROLLBACK, l.SEG_OWNER, l.TABLE_NAME, l.SQL_REDO from V$LOGMNR_CONTENTS l where XID=hextoraw('<XID>') and l.TABLE_NAME='<Table_name>';

XID OPERATION ROLLBACK SEG_OWNER TABLE_NAME SQL_REDO
---------------- ---------- ---------- ------------------------------ ------------------------------ ----------------------------------------------------------------------------------------------------
<XID> INSERT 0 <Schema_name> <Table_name> insert into "<Schema_name>"."<Table_name>"("**","*******".....
<XID> DELETE 1 <Schema_name> <Table_name> delete from "<Schema_name>"."<Table_name>" where ROWID = '<ROWID>';

But when we crosschecked COMMITTED_DATA_ONLY option we are getting rollback insert statement which is due to BUG in Database logminer side.

execute dbms_logmnr.start_logmnr( options => dbms_logmnr.dict_from_redo_logs + DBMS_LOGMNR.COMMITTED_DATA_ONLY);

select l.XID, l.OPERATION, l.ROLLBACK, l.SEG_OWNER, l.TABLE_NAME, l.SQL_REDO from V$LOGMNR_CONTENTS l where XID=hextoraw('<XID>') and l.TABLE_NAME='<Table_name>';

XID OPERATION ROLLBACK SEG_OWNER TABLE_NAME SQL_REDO
---------------- ---------- ---------- ------------------------------ ------------------------------ ----------------------------------------------------------------------------------------------------
<XID> INSERT 0 <Schema_name> <Table_name> insert into "<Schema_name>"."<Table_name>"("**","******",...... <<==== ERROR !!!!!!!!!!!!!!!!!!!

Changes

N/A

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.