Oracle LogMiner Results Inconsistent

Question

I'm working on a LogMiner-based solution for capturing changes and I've uncovered what appears to either an unusual set of expectations when attempting to mine redo events that pertain to CLOB or BLOB operations.

In my use case, I've inserted record into a table that contains 3 CLOB fields where one of the CLOB fields value is small while the other two CLOB fields must be set using LOB_WRITE operations.

When I set a starting LogMiner SCN range that starts before and ends after the transaction commit, I get the full expected rows in V$LOGMNR_CONTENTS, which are:

0a00070084220000 37717288 START
0a00070084220000 37717288 INSERT
0a00070084220000 37717312 SEL_LOB_LOCATOR
0a00070084220000 37717312 LOB_WRITE (several of these as expected)
0a00070084220000 37717331 SEL_LOB_LOCATOR
0a00070084220000 37717331 LOB_WRITE (several of these as expected)
0a00070084220000 37717332 INSERT (sets the smaller clob data values)
0a00070084220000 37717334 COMMIT

The unusual bit occurs when starting the mining session with certain start/end SCN ranges.

For example, when I mine from 37717239 to 37717289, I expected LogMiner to provide both the START and the INSERT in the table; however only the START operation was present.

Additionally, when I mine from 37717290 to 37717340, I expected LogMiner to provide all the SEL_LOB_LOCATOR, LOB_WRITE, and subsequent INSERT and the COMMIT; however only the subsequent INSERT and COMMIT were present.

The only assertion I can make from this is that LogMiner seems to have trouble when you split a transction where certain redo events represent various synthetic operations as they relate to LOB operations and therefore the only way I've been able to actually always reconstruct the series of events has been to mine from 37717288 forward to force LogMiner to have the full scope of the transaction available when it materializes the rows in the contents view.

Why does LogMiner behave like this? Why does it not correctly materialize when splitting the transaction with the SCN ranges I presented above?

Answer 1

For Logminer any single command is atomic by definition. In this case it starts at 37717288 and ends at 37717332. It cannot be split. If you will ask any range that splits it - Logminer will not fetch it on purpose (so you won't have partial results of a single command).

This is also right for large non-LOB commands, like DDL that generate many internal commands (e.g. alter table modify column default value)

Besides that, pay attention that fetching the values of LOB from Logminer is not reliable. just play around with the values and you will see that it is highly inconsistent. (I have somewhere tests to prove it, so if you are interested I can provide them).

Here is the test: define a table with 2 lobs, create 2 rows - first with single lob and second with two lobs.

drop table sample1.clobs2;
create table sample1.clobs2 (id number not null, clob1 clob not null, clob2 clob);

--start
select current_scn from v$database;

insert into sample1.clobs2 (id, clob1) values (3, 'abc');
insert into sample1.clobs2 (id, clob1, clob2) values (4, 'abc', '2abc');
commit;
update sample1.clobs2 set clob1='def' where id=3;
update sample1.clobs2 set clob1='def', clob2='2def' where id=4;
commit;
update sample1.clobs2 set clob1=rpad('ghj',30000,'Z') where id=3;
update sample1.clobs2 set clob1=rpad('ghj',30000,'Z'), clob2=rpad('ghj',30000,'Z') where id=4;
commit;


--end
select current_scn from v$database;

Start the logminer:

exec DBMS_LOGMNR.end_LOGMNR;
exec DBMS_LOGMNR.ADD_LOGFILE('put here any logfile(select MEMBER from v$logfile), logminer will do the rest');
begin DBMS_LOGMNR.START_LOGMNR(
    STARTSCN => put here the scn from the above test,
    ENDSCN   => put here the scn from the above test,
    OPTIONS  =>  -- I leave all the possible parameters here just for you to play
                --DBMS_LOGMNR.DICT_FROM_REDO_LOGS +
                --DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + 
                DBMS_LOGMNR.CONTINUOUS_MINE +
                --DBMS_LOGMNR.COMMITTED_DATA_ONLY+
                --DBMS_LOGMNR.DDL_DICT_TRACKING+ 
                DBMS_LOGMNR.NO_ROWID_IN_STMT+
                DBMS_LOGMNR.NO_SQL_DELIMITER 
                );
end;
/      

Check the results:

select scn, (XIDUSN || '.' || XIDSLT || '.' || XIDSQN) AS transaction_id, operation, seg_name, ROW_ID, rollback, csf,SQL_REDO,  c.*
from v$logmnr_contents c
where 1=1 
    (seg_name='OBJ# put here the object id of the table sample1.clobs2' or operation='COMMIT');

You will see:

1. large lobs behave differently from small ones
2. in case when only one lob is updated - it is impossible to understand which one was it (the first or the second)

In addition, this behaviour changes between different Oracle versions.