Comparison between LOGMNR mining logs and DUMP logs

Comparison between LOGMNR mining logs and DUMP logs many people know that LOGMNR is used to analyze logs, but few people use DUMP to analyze logs. This is because the information analyzed by LOGMNR is easy to read and understand. but sometimes we still need to DUMP to analyze the log file, because it records more details and is more real. (In fact, the logs analyzed by LOGMNR are not quite comprehensive.) After LOGMNR log analysis, I found that the information mined was very strange. I queried the records analyzed by LOGMNR Based on the ROWID, we found that a ROWID has INSERT and DELETE operations, but there is no UPDATE operation. In fact, there is indeed an UPDATE operation based on business analysis (note that I have also thought about changing the ROWID here, for example, row migration occurs, but the detection of SCN and ROWID shows that the ROWID has not changed at all.) So I began to doubt the correctness of the LOGMNR log analysis, I started a quiz to verify my thoughts. The quiz is as follows: first find the currently used log file select. status, B. member from v $ log a, v $ logfile B where. group # = B. group #; the log file of the CURRENT activity is: G: \ APP \ ADMINISTRATOR \ ORADATA \ ORCL \ REDO03.LOG [s Ql] -- create a test table 00:41:20 scott @ orcl> create table dump_a (id number, tt varchar2 (20); the table has been created. Used time: 00: 00: 00.40 -- current SCN number 00:41:22 scott @ orcl> select dbms_flashback.get_system_change_number () a from dual; A ------------- 1257368 select 1 line. -- Insert a data entry at 00:42:47 scott @ orcl> insert into dump_a values (1, 'w'); 1 row has been created. Used time: 00: 00: 00.11 -- update a piece of data 00:43:44 scott @ orcl> update dump_a set id = 2 where id = 1; 1 row updated. Used time: 00: 00: 00.14 -- the current SCN number is 00:43:52 scott @ orcl> select dbms_flashback.get_system_change_number () a from dual; A ------------- 1267898. One row has been selected. -- The above SCN number is used to DUMP logs. * use LOGMNR to analyze logs *** * **********************/begin dbms_logmnr.add_logfile ('G: \ APP \ ADMINISTRATOR \ ORADATA \ ORCL \ REDO03.LOG ', dbms_logmnr.new); end;/begin dbms_logmnr.start_logmnr (options => logs); end;/-- query log information select timestamp, scn, SQL _redo from v $ logmnr_contents where lower (SQL _redo) like '% dump_a %' order by scn; -- the query result is as follows: TIM Estamp scn SQL _REDO2013-4-26 23:30:14 1260172 insert into "SYS ". "WRH $ _ SEG_STAT_OBJ" ("SNAP_ID", "DBID", "TS #", "OBJ #", "DATAOBJ #", "OWNER", "OBJECT_NAME ", "SUBOBJECT_NAME", "PARTITION_TYPE", "OBJECT_TYPE", "TABLESPACE_NAME", "INDEX_TYPE", "BASE_OBJ #", "BASE_OBJECT_NAME", "BASE_OBJECT_OWNER") values ('54 ', '123', '4', '123', '123', 'Scott ', 'dump _ a', NULL, 'none', 'table', 'users ', NULL, NULL); is there only one record?? Yes, there is only one record after LOGMNR, that is, the INSERT and UPDATE operations are not recorded. I forgot to say that my test database is not archived. After archiving, the redo log is written a little more, and the LOGMNR may get more information. Now let's analyze whether the INSERT and UPDATE records REDO logs? Let's use DUMP to see [SQL] -- first stop LOGMNR log analysis BEGIN dbms_logmnr.end_logmnr (); END;/-- query the object_Id of the table (used later in log analysis) 00:45:52 sys @ orclselect object_id a from dba_objects where object_name = 'dump _ a'; A ------------- 70974 1 row selected. -- Use the previous SCN to DUMP a specified range of log files. 00:54:18 SYS @ orcl> alter system dump logfile 'G: \ APP \ ADMINISTRATOR \ ORADATA \ ORCL \ REDO03.LOG 'scn min 1257368 scn max 1267898; the system has changed. Time used: 00: 00: 00.10 enter the trace directory, find the most recent trc file (I am here orcl_ora_172.trc), and then directly search for the previously queried object_id content as follows: CHANGE #1 TYP: 0 CLS: 4 AFN: 4 DBA: 0x0100018b OBJ: 70974 SCN: 0x0000. 00132a36 SEQ: 1 OP: 13.28 High HWM Highwater: 0x01000191 ext #: 0 blk #: 8 ext size: 8 # blocks in seg. hdr's freelists: 0 # blocks below: 5 mapblk 0x00000000 offset: 0 lfdba: 0x01000189 CHANGE #2 TYP: 0 CLS: 8 AFN: 4 DBA: 0x01000189 OBJ: 70974 SCN: 0x0000. 00132a36 SEQ: 1 OP: 13.22 Redo on Level1 Bitmap BlockRedo to set hwmOpcode: 32 Highwater: 0x01000191 ext #: 0 blk #: 8 ext size: 8 # blocks in seg. hdr's freelists: 0 # blocks below: 5 mapblk 0x00000000 offset: 0 CHANGE #3 TYP: 1 CLS: 1 AFN: 4 DBA: 0x0100018c OBJ: 70974 SCN: 0x0000. 00133d95 SEQ: 1 OP: 13.21 ktspbfredo-Format Pagetable Datablock Parent (l1) DBA: 0x01000189 typ: 1 objd: 70974 itls: 2 f Mt_flag: 0 poff: 0 CHANGE #4 TYP: 1 CLS: 1 AFN: 4 DBA: 0x0100018d OBJ: 70974 SCN: 0x0000. 00133d95 SEQ: 1 OP: 13.21 ktspbfredo-Format Pagetable Datablock Parent (l1) DBA: 0x01000189 typ: 1 objd: 70974 itls: 2 fmt_flag: 0 poff: 0 CHANGE #5 TYP: 1 CLS: 1 AFN: 4 DBA: 0x0100018e OBJ: 70974 SCN: 0x0000. 00133d95 SEQ: 1 OP: 13.21 ktspbfredo-Format Pagetable Datablock Parent (l1) DBA: 0x01000189 typ: 1 objd: 70974 itls: 2 fmt_flag: 0 poff: 0 CHANGE #6 TYP: 1 CLS: 1 AFN: 4 DBA: 0x0100018f OBJ: 70974 SCN: 0x0000. 00133d95 SEQ: 1 OP: 13.21 ktspbfredo-Format Pagetable Datablock Parent (l1) DBA: 0x01000189 typ: 1 objd: 70974 itls: 2 fmt_flag: 0 poff: 0 CHANGE #7 TYP: 1 CLS: 1 AFN: 4 DBA: 0x01000190 OBJ: 70974 SCN: 0x0000. 00133d95 SEQ: 1 OP: 13.21 ktspbfredo-Format Pagetable Datablock Parent (l1) DBA: 0x01000189 typ: 1 objd: 70974 I Tls: 2 fmt_flag: 0 poff: 0 CHANGE #8 TYP: 0 CLS: 8 AFN: 4 DBA: 0x01000189 OBJ: 70974 SCN: 0x0000. 00133d9e SEQ: 1 OP: 13.22 Redo on Level1 Bitmap BlockRedo for state changeLen: 5 Offset: 3 newstate: 5 CHANGE #9 TYP: 0 CLS: 4 AFN: 4 DBA: 0x0100018b OBJ: 70974 SCN: 0x0000. 00133d9e SEQ: 1 OP: 13.28Low HWM Highwater: 0x01000191 ext #: 0 blk #: 8 ext size: 8 # blocks in seg. hdr's freelists: 0 # blocks below: 5 mapblk 0x 00000000 offset: 0 lfdba: 0x01000189 CHANGE #10 TYP: 0 CLS: 1 AFN: 4 DBA: 0x01000190 OBJ: 70974 SCN: 0x0000. 00133d9e SEQ: 1 OP: 11.2KTB Redo op: 0x01 ver: 0x01 compat bit: 4 (post-11) padding: 0op: F xid: 0x000a. 00d. 000002d4 uba: 0x00c03f3c. 018b. 22 kdo Op code: IRP row dependencies Disabled xtype: XA flags: 0x00000000 bdba: 0x01000190 hdba: 0x0100018bitli: 1 ispac: 0 maxfr: 4858 tabn: 0 slot: 0 (0x0) size/ Delt: 8fb: -- H-FL -- lb: 0x1 cc: 2 null: -- col 0: [2] c1 02col 1: [1] 77. You can see the block size, HWM, and other information of the table to be created. Note that red is the inserted content. col 0 -- first column, 2 represents the length of 2 bytes, c1 02 -- represents the number 1 col 1 -- second column, 1 represents the length of 1 byte, 77 -- represents the character w. In this way, the hexadecimal 77 is the 10th 119. then [SQL] 00:54:27 SYS @ orcl> select chr (119) from dual; C-w has selected 1 line. You can get .. the UPDATE operation log is immediately displayed at the end, as shown in the following figure: redo record-Thread: 1 RBA: 0x00001b. 20179dc3. 00e4 LEN: 0x01c0 VLD: 0x09SCN: 0x0000.00134807 SUBSCN: 154 04/26/2013 23: 31: 58 CHANGE #1 TYP: 2 CLS: 1 AFN: 4 DBA: 0x01000190 OBJ: 70974 SCN: 0x0000. 00133df4 SEQ: 1 OP: 11.19KTB Redo op: 0x11 ver: 0x01 compat bit: 4 (post-11) padding: 0op: F xid: 0x0008. 01b. 000002e0 uba: 0x00c06f20. 01c6. 0 fBlock cleanout record, scn: 0x0000.00134801 ver: 0x01 opt: 0x02, entries follow... itli: 1 flg: 2 scn: 0x0000. 00133df4Array Update of 1 rows: tabn: 0 slot: 0 (0x0) flag: 0x2c lock: 2 ckix: 0 ncol: 2 nnew: 1 size: 0KDO Op code: 21 row dependencies Disabled xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x01000190 hdba: 0x0100018bitli: 2 ispac: 0 maxfr: 4858 vect = 0col 0: [2] c1 03 CHANGE #2 TYP: 0 CLS: 31 AFN: 3 DBA: 0x00c00079 OBJ: 4294967295 SCN: 0x0000. 0013419a SEQ: 1 OP: 5.2 ktudh redo: slt: 0x001b sqn: 0x000002e0 flg: 0x0012 siz: 156 fbi: 0 uba: 0x00c06f20. 01c6. 0f pxid: 0x0000. 000.00000000 CHANGE #3 TYP: 0 CLS: 32 AFN: 3 DBA: 0x00c06f20 OBJ: 4294967295 SCN: 0x0000.00134199 SEQ: 1 OP: 5.1 ktudb redo: siz: 156 spc: 6202 flg: 0x0012 seq: 0x01c6 rec: 0x0f xid: 0x0008. 01b. 000002e0 ktubl redo: slt: 27 rci: 0 opc: 11.1 objn: 70974 objd: 70974 tsn: 4 Undo type: Regular undo Begin trans Last buffer split: No Temp Object: no Tablespace Undo: No 0x00000000 prev ctl uba: 0x00c06f20. 01c6. 0d prev ctl max cmt scn: 0x0000. 001315c1 prev tx cmt scn: 0x0000. 001315ce txn start scn: 0x0000. 00133d9e logon user: 81 prev brb: 12611358 prev bcl: 0 BuExt idx: 0 flg2: 0KDO undo record: KTB Redo op: 0x03 ver: 0x01 compat bit: 4 (post-11) padding: 0op: ZArray Update of 1 rows: tabn: 0 slot: 0 (0x0) flag: 0x2c lock: 0 ckix: 0 ncol: 2 nnew: 1 size: 0KDO Op code: 21 row dependencies Disabled xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x01000190 hdba: 0x0100018bitli: 2 ispac: 0 maxfr: 4858 vect = 0col 0: [2] c1 02 pay attention to the red color. It turns from c1 03 to c1 02, which is the update operation. update dump_a set id = 2 where id = 1; in this way, we find that all operations have been written into logs, but LOGMNR mining is incomplete. (no additional log is enabled) --- the information after DUMP is indeed a bit difficult to understand, and the character matching is good to find (after conversion, check the CHR), but the number is not very good to query. I did not find the conversion rules, but I hope you can give me some advice. c1 03 corresponds to number 2; c1 02 corresponds to number 1; 80 corresponds to number 0; 3e 64 66 corresponds to number-1