Hi all,

Oracle 9i

Can anyone explain why the following would generate two identical records when mined by logminer?

SQL> select count(*) from centers where center like 'DG%';

COUNT(*)
----------
0

SQL> INSERT INTO "SONICA"."CENTERS" ("CENTER" ,"WORK_MODE" ,"DIRECTED"
2 ,"CYCLE_QTY" ,"CYCLE_FREQ" ,"DESCR" ,"PICK_METHOD" ,
3 "PICK_ZONING" ,"PICK_DEV" ,"LABEL_NO" ,"PRINT_TIME" ,
4 "SUPER_CNT" ,"GROUP_NAME" ,"STG_LOC" ,"SYS" ,"WHO_CREATE" ,
5 "CREATE_DT" ,"UPDATE_DT" ,"WHO_UPDATE" )
6 VALUES ('DG1' ,'M' ,'Y' , NULL , NULL ,'Test' ,'' ,'' ,'' ,
7 '' ,'' , NULL ,'' ,'' ,'N' ,'Me' ,TO_DATE('08-Mar-2010
8 04:05:19 PM', 'dd-Mon-yyyy HH:MI:SS AM') ,
9 TO_DATE('08-Mar-2010 04:05:19 PM', 'dd-Mon-yyyy HH:MI:SS AM')
10 ,'Me' );

1 row created.

SQL> commit;
Commit complete.

SQL> select count(*) from centers where center like 'DG%';

COUNT(*)
----------
1


begin DBMS_LOGMNR.ADD_LOGFILE(LogFileName => 'TURTEST_T1_S1197.ARC', Options =>dbms_logmnr.NEW); end;
begin DBMS_LOGMNR.START_LOGMNR(DictFileName =>'TURTEST_DICTIONARY.ORA', Options => DBMS_LOGMNR.COMMITTED_DATA_ONLY); end;
insert into lm_contents (select * from v$logmnr_contents where operation in ('INSERT','UPDATE','DELETE') );
begin DBMS_LOGMNR.END_LOGMNR; end;

Now if I look in the table lm_contents there are two identical rows which relate to the same INSERT transaction.

Specifically I need the date from sql_redo & sql_undo, both are identical.

Anyone got any thoughts on this?

Thanks
Dave

Hi Dave,

>> Now if I look in the table lm_contents there are two identical rows which relate to the same INSERT transaction.

Is it the exact same row values?

That would not make sense, because if you replayed the redo log, two rows would be inserted . . .

If it's valid, it may have to do with:

- The internal page cloning algorithm, part of the read consistency mechanism?

- A fragmented insert that chained onto multiple data blocks?

- Oracle 11g compression does versioning:

http://www.dba-oracle.com/oracle11g/sf_Ora...or_the_DBA.html

I'm just guessing . . .

Hi,
The two rows are identical. However if I query the view v$logmnr_contents then all I get is a single row.
So the culplrit must be the INSERT statement although I can't see anything wrong with it...

insert into lm_contents (select * from v$logmnr_contents where operation in ('INSERT','UPDATE','DELETE') );

I only needed this data in a static table for test so as long as the data in the view is ok then I'm happy.

Thanks
Dave