dump 出logfile 解读

chinadm123

今天在oracle 11.2.0.1上模拟了一下dump redo logfile，是想分析一下DML操作产生的redo日志。
实际的操作是
SQL> update t_1 set id=9 where id=8;

1 row updated.

SQL> commit;

Commit complete.
我想请教一下，在这dump出来的文件里，哪里证实了update t_1 set id=9 where id=8;这个操作？

*** 2014-05-28 13:53:35.945
Oradebug command 'tracefile_name' console output:
/test/oracle/diag/rdbms/yongyi/yongyi/trace/yongyi_ora_10629.trc


*** 2014-05-28 13:57:00.242
DUMP OF REDO FROM FILE '+DATA/yongyi/onlinelog/group_3.273.843558283'
Opcodes *.*
RBAs: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff
SCNs: scn: 0x0000.00137908 (1276168) thru scn: 0x0000.00137920 (1276192)
Times: creation thru eternity
FILE HEADER:
        Compatibility Vsn = 186646528=0xb200000
        Db ID=3258653378=0xc23b1ac2, Db Name='YONGYI'
        Activation ID=3258631357=0xc23ac4bd
        Control Seq=2003=0x7d3, File size=102400=0x19000
        File Number=3, Blksiz=512, File Type=2 LOG
descrip:"Thread 0001, Seq# 0000000030, SCN 0x000000137798-0xffffffffffff"
thread: 1 nab: 0xffffffff seq: 0x0000001e hws: 0x1 eot: 1 dis: 0
resetlogs count: 0x3247ad85 scn: 0x0000.000e6c20 (945184)
prev resetlogs count: 0x296b946b scn: 0x0000.00000001 (1)
Low  scn: 0x0000.00137798 (1275800) 05/28/2014 13:39:27
Next scn: 0xffff.ffffffff 01/01/1988 00:00:00
Enabled scn: 0x0000.000e6c20 (945184) 03/30/2014 09:44:37
Thread closed scn: 0x0000.00137798 (1275800) 05/28/2014 13:39:27
Disk cksum: 0x75e2 Calc cksum: 0x75e2
Terminal recovery stop scn: 0x0000.00000000
Terminal recovery  01/01/1988 00:00:00
Most recent redo scn: 0x0000.00000000
Largest LWN: 0 blocks
End-of-redo stream : No
Unprotected mode
Miscellaneous flags: 0x800000
Thread internal enable indicator: thr: 0, seq: 0 scn: 0x0000.00000000

REDO RECORD - Thread:1 RBA: 0x00001e.000000ad.0010 LEN: 0x0070 VLD: 0x06
SCN: 0x0000.0013790e SUBSCN:  1 05/28/2014 13:51:45
CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:0 OP:23.1 ENC:0
Block Written - afn: 1 rdba: 0x004007d9 BFT:(1024,4196313) non-BFT:(1,2009)
                   scn: 0x0000.001378a5 seq: 0x02 flg:0x06

REDO RECORD - Thread:1 RBA: 0x00001e.000000ad.0080 LEN: 0x0044 VLD: 0x02
SCN: 0x0000.0013790e SUBSCN:  1 05/28/2014 13:51:45
CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:0 OP:23.1 ENC:0
Block Written - afn: 3 rdba: 0x00c000b8 BFT:(1024,12583096) non-BFT:(3,184)
                   scn: 0x0000.001378a5 seq: 0x03 flg:0x04

REDO RECORD - Thread:1 RBA: 0x00001e.000000ad.00c4 LEN: 0x0044 VLD: 0x02
SCN: 0x0000.0013790e SUBSCN:  1 05/28/2014 13:51:45
CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:0 OP:23.1 ENC:0
Block Written - afn: 1 rdba: 0x00405569 BFT:(1024,4216169) non-BFT:(1,21865)
                   scn: 0x0000.001378a3 seq: 0x03 flg:0x06

REDO RECORD - Thread:1 RBA: 0x00001e.000000ad.0108 LEN: 0x0044 VLD: 0x02
SCN: 0x0000.0013790e SUBSCN:  1 05/28/2014 13:51:45
CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:0 OP:23.1 ENC:0
Block Written - afn: 3 rdba: 0x00c00118 BFT:(1024,12583192) non-BFT:(3,280)
                   scn: 0x0000.001378a3 seq: 0x02 flg:0x04

REDO RECORD - Thread:1 RBA: 0x00001e.000000ad.014c LEN: 0x0044 VLD: 0x02
SCN: 0x0000.0013790e SUBSCN:  1 05/28/2014 13:51:45
CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:0 OP:23.1 ENC:0
Block Written - afn: 1 rdba: 0x004007e9 BFT:(1024,4196329) non-BFT:(1,2025)
                   scn: 0x0000.001378a5 seq: 0x03 flg:0x06

REDO RECORD - Thread:1 RBA: 0x00001e.000000ae.0010 LEN: 0x0244 VLD: 0x0d
SCN: 0x0000.0013791f SUBSCN:  1 05/28/2014 13:52:30
CHANGE #1 TYP:2 CLS:1 AFN:5 DBA:0x01400d73 OBJ:73644 SCN:0x0000.001377a9 SEQ:1 OP:11.19 ENC:0 RBL:0
KTB Redo
op: 0x11  ver: 0x01  
compat bit: 4 (post-11) padding: 1
op: F  xid:  0x0006.016.0000036a    uba: 0x00c00642.00d8.05
Block cleanout record, scn:  0x0000.0013791d ver: 0x01 opt: 0x02, entries follow...
  itli: 1  flg: 2  scn: 0x0000.001377a9
Array Update of 1 rows:
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 2 ckix: 0
ncol: 1 nnew: 1 size: 0
KDO Op code:  21 row dependencies Disabled
  xtype: XAxtype KDO_KDOM2 flags: 0x00000080  bdba: 0x01400d73  hdba: 0x01400d72
itli: 2  ispac: 0  maxfr: 4858
vect = 0
col  0: [ 2]  c1 0a
CHANGE #2 TYP:0 CLS:27 AFN:3 DBA:0x00c000d0 OBJ:4294967295 SCN:0x0000.0013785d SEQ:1 OP:5.2 ENC:0 RBL:0
ktudh redo: slt: 0x0016 sqn: 0x0000036a flg: 0x0012 siz: 164 fbi: 0
            uba: 0x00c00642.00d8.05    pxid:  0x0000.000.00000000
CHANGE #3 TYP:0 CLS:27 AFN:3 DBA:0x00c000d0 OBJ:4294967295 SCN:0x0000.0013791f SEQ:1 OP:5.4 ENC:0 RBL:0
ktucm redo: slt: 0x0016 sqn: 0x0000036a srt: 0 sta: 9 flg: 0x2 ktucf redo: uba: 0x00c00642.00d8.05 ext: 2 spc: 7220 fbi: 0
CHANGE #4 TYP:0 CLS:28 AFN:3 DBA:0x00c00642 OBJ:4294967295 SCN:0x0000.0013785c SEQ:1 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 164 spc: 7386 flg: 0x0012 seq: 0x00d8 rec: 0x05
            xid:  0x0006.016.0000036a  
ktubl redo: slt: 22 rci: 0 opc: 11.1 [objn: 73644 objd: 73644 tsn: 6]
Undo type:  Regular undo        Begin trans    Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
             0x00000000  prev ctl uba: 0x00c00642.00d8.04
prev ctl max cmt scn:  0x0000.00136dd6  prev tx cmt scn:  0x0000.00136dea
txn start scn:  0x0000.001377a9  logon user: 85  prev brb: 12584510  prev bcl: 0 BuExt idx: 0 flg2: 0
KDO undo record:
KTB Redo
op: 0x03  ver: 0x01  
compat bit: 4 (post-11) padding: 1
op: Z
Array Update of 1 rows:
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0
ncol: 1 nnew: 1 size: 0
KDO Op code:  21 row dependencies Disabled
  xtype: XAxtype KDO_KDOM2 flags: 0x00000080  bdba: 0x01400d73  hdba: 0x01400d72
itli: 2  ispac: 0  maxfr: 4858
vect = 0
col  0: [ 2]  c1 09
END OF REDO DUMP

ps：请大家介绍一点详尽点的文档介绍如何解读dump文件。

chinadm123

     FILE#        TS# NAME
---------- ---------- ------------------------------
         1          0 SYSTEM
         2          1 SYSAUX
         3          2 UNDOTBS1
         4          4 USERS
         5          6 SQLTEST
修改的数据是修改file#=5的数据。

Maclean Liu(刘相兵

SQL> select dump(9,16) from dual;

DUMP(9,16)
-----------------
Typ=2 Len=2: c1,a

SQL> select dump(8,16) from dual;

DUMP(8,16)
-----------------
Typ=2 Len=2: c1,9


AFN 代表absolute file number

chinadm123

Maclean Liu(刘相兵 发表于 2014-5-28 19:42
SQL> select dump(9,16) from dual;

DUMP(9,16)

多谢刘大，完全解决了我的疑惑。