以修改emp表中一条记录中某个字段值为例,来说明Redo信息内容
强制切换日志文件,以保证使用新的日志文件
SQL> alter system switch logfile;
System altered
update操作并提交
SQL> update emp set sal=3000 where empno=7788;
1 row updated
SQL> commit;
Commit complete
确认当前日志文件是哪个
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- -----------
1 1 118 52428800 1 NO CURRENT 63527376 2014/3/26 8
2 1 116 52428800 1 YES ACTIVE 63501547 2014/3/25 2
3 1 117 52428800 1 YES ACTIVE 63523908 2014/3/26 8
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE
---------- ------- ------- -------------------------------------------------------------------------------- ---------------------
3 ONLINE /home/oracle/app/oradata/ora11g/redo03.log NO
2 ONLINE /home/oracle/app/oradata/ora11g/redo02.log NO
1 ONLINE /home/oracle/app/oradata/ora11g/redo01.log NO
转储日志文件
SQL> alter system dump logfile '/home/oracle/app/oradata/ora11g/redo01.log';
System altered
找到对应trace文件并分析
SQL> select * from v$diag_info where name ='Default Trace File';
INST_ID NAME VALUE
---------- ---------------------------------------------------------------- --------------------------------------------------------------------------------
1 Default Trace File /home/oracle/app/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_27538.trc
找出文件对应ID
SQL> select file#,name from v$datafile;
FILE# NAME
---------- --------------------------------------------------------------------------------
1 /home/oracle/app/oradata/ora11g/system01.dbf
2 /home/oracle/app/oradata/ora11g/sysaux01.dbf
3 /home/oracle/app/oradata/ora11g/undotbs01.dbf
4 /home/oracle/app/oradata/ora11g/users01.dbf
5 /opt/oracle/ora11g/MSGAGENT.ora
6 /opt/oracle/ora11g/MSGAGENTIDX.ora
7 /home/oracle/oradata/ap_table
8 /home/oracle/oradata/ap_index
9 /home/oracle/oradata/group_table
10 /home/oracle/oradata/group_clob
11 /home/oracle/oradata/group_index
12 /home/oracle/oradata/ps_table
13 /home/oracle/oradata/ps_clob
14 /home/oracle/oradata/ps_index
15 /home/oracle/oradata/ome_mc_4K
16 /home/oracle/oradata/ome_mc_4k_index
17 /home/oracle/oradata/ome_mc_4k_temp
18 /home/oracle/oradata/mlee_data.dbf
19 /home/oracle/oradata/mlee_index.dbf
20 /home/oracle/app/oradata/ora11g/ebms0528.ora
21 /home/oracle/app/oradata/ora11g/ebms0528IDX.ora
22 /home/oracle/app/oradata/ora11g/report.ora
23 /home/oracle/app/oradata/ora11g/reportIDX.ora
24 /home/oracle/app/oradata/ora11g/egroup0528index01
25 /home/oracle/app/oradata/ora11g/egroup0528data01
26 /home/oracle/app/oradata/ora11g/econsole0528.ora
27 /home/oracle/app/oradata/ora11g/econsole0528IDX.ora
28 /home/oracle/app/oradata/ora11g/jforum.ora
29 /home/oracle/oradata/temp/eygle.f
30 /home/oracle/oradata/temp/eygle1.f
31 /home/oracle/oradata/temp/eygle2.f
32 /home/oracle/oradata/temp/eygle3.f
33 /home/oracle/oradata/temp/eygle4.f
ora11g_ora_27538.trc文件中内容如下:
REDO RECORD - Thread:1 RBA: 0x000076.00000030.0170 LEN: 0x01a8 VLD: 0x01
SCN: 0x0000.03c95a0b SUBSCN: 1 03/26/2014 08:25:14
CHANGE #1 TYP:0 CLS:35 AFN:3 DBA:0x00c00110OBJ:4294967295 SCN:0x0000.03c95a01 SEQ: 1 OP:5.2 ENC:0 //改变向量1,对回滚段头的修改操作(OP得出),分配事务表,文件ID为3(AFN)得出是UNDO表空间
ktudh redo: slt: 0x000d sqn: 0x00010578 flg: 0x0412 siz: 176 fbi: 1
uba: 0x00c00c52.2067.45 pxid: 0x0000.000.00000000
CHANGE #2 TYP:0 CLS:36 AFN:3 DBA:0x00c00c52OBJ:4294967295 SCN:0x0000.03c9594e SEQ: 54 OP:5.1 ENC:0 //改变向量2,记录前镜像信息(即sal为4000的原记录),对undo块或undo header操作(OP得出),仍是UNDO表空间(AFN)
ktudb redo: siz: 176 spc: 2538 flg: 0x0012 seq: 0x2067 rec: 0x45
xid: 0x000a.00d.00010578
ktubl redo: slt: 13 rci: 0 opc: 11.1 [objn: 443 objd: 443 tsn: 0]
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
0x00000000 prev ctl uba: 0x00c00c56.2067.3d
prev ctl max cmt scn: 0x0000.03c955bd prev tx cmt scn: 0x0000.03c955f5
txn start scn: 0xffff.ffffffff logon user: 0 prev brb: 12586050 prev bcl: 0 BuExt idx: 0 flg2: 0
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: L itl: xid: 0x0009.00c.000103bc uba: 0x00c00d3f.1e4e.2c
flg: C--- lkc: 0 scn: 0x0000.03c9272f
KDO Op code: URP row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x00400bc1 hdba: 0x00400ba8
itli: 1 ispac: 0 maxfr: 4863
tabn: 0 slot: 85(0x55) flag: 0x2c lock: 0 ckix: 119
ncol: 9 nnew: 7 size: 0
Vector content:
col 2: [ 2] c1 03 //对于col 2的修改,修改前的值为4000
col 3: [ 1] 80
col 4: [ 1] 80
col 5: [ 1] 80
col 6: [ 1] 80
col 7: [ 1] 80
col 8: [ 7] 78 72 03 12 10 1a 27
CHANGE #3 TYP:2 CLS: 1 AFN:1 DBA:0x00400bc1 OBJ:443 SCN:0x0000.03c946c5 SEQ: 1OP:11.5 ENC:0 //改变向量3,数据块的修改操作(OP),SYSTEM表空间
KTB Redo
op: 0x01 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: F xid: 0x000a.00d.00010578 uba: 0x00c00c52.2067.45
KDO Op code: URP row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x00400bc1 hdba: 0x00400ba8
itli: 1 ispac: 0 maxfr: 4863
tabn: 0 slot: 85(0x55) flag: 0x2c lock: 1 ckix: 119
ncol: 9 nnew: 7 size: 0
Vector content:
col 2: [ 2] c1 04 //col 2的修改,修改后的值为3000
col 3: [ 1] 80
col 4: [ 1] 80
col 5: [ 1] 80
col 6: [ 1] 80
col 7: [ 1] 80
col 8: [ 7] 78 72 03 1a 09 1a 0f
CHANGE #4 TYP:0 CLS:21 AFN:3 DBA:0x00c000a0 OBJ:4294967295 SCN:0x0000.03c959d9 SEQ: 1OP:5.4 ENC:0 //改变向量4,事务提交(OP)
ktucm redo: slt: 0x0018 sqn: 0x000103d3 srt: 0 sta: 9 flg: 0x2 ktucf redo: uba: 0x00c005b7.2158.06 ext: 2 spc: 7482 fbi: 0