[20170412]分析重做日志.txt
--//自己很少做重做日志转储，测试看看。
1.环境:
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SCOTT@book> create table empx as select * from emp;
Table created.
SCOTT@book> @ &r/qq empx 1
ROWID OBJECT FILE BLOCK ROW ROWID_DBA EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------------------ ------------ ------------ ------------ ------------ -------------------- ------------ ---------- --------- ------------ ------------------- ------------ ------------ ------------
AAAWGtAAEAAAAIrAAA 90541 4 555 0 0x100022B 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
SCOTT@book> select * from empx where empno=7788;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------------ ---------- --------- ------------ ------------------- ------------ ------------ ------------
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
$ cat ax.sql
column member new_value v_member
column member noprint
set numw 12
--//alter system archive log current;
SELECT member FROM v$log a, v$logfile b WHERE a.group#(+) = b.group# and a.STATUS='CURRENT' and rownum=1;
column curr1 new_value v_curr1
select current_scn curr1 from v$database;
update scott.empx set sal=4000 where empno=7788;
commit;
column curr2 new_value v_curr2
select current_scn curr2 from v$database;
prompt alter system dump logfile '&&v_member' scn min &&v_curr1 scn max &&v_curr2;
alter system dump logfile '&&v_member' scn min &&v_curr1 scn max &&v_curr2;
2.执行脚本：
--//检查转储内容：
REDO RECORD - Thread:1 RBA: 0x00033a.00001aae.0010 LEN: 0x021c VLD: 0x0d
SCN: 0x0003.17659fa9 SUBSCN: 1 04/12/2017 11:13:30
(LWN RBA: 0x00033a.00001aae.0010 LEN: 0002 NST: 0001 SCN: 0x0003.17659fa6)
CHANGE #1 TYP:0 CLS:1 AFN:4 DBA:0x0100022b OBJ:90541 SCN:0x0003.17659f96 SEQ:2 OP:11.19 ENC:0 RBL:0
KTB Redo
op: 0x01 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: F xid: 0x0008.004.000006fe uba: 0x00c05eb3.0370.32
Array Update of 1 rows:
tabn: 0 slot: 7(0x7) flag: 0x2c lock: 2 ckix: 12
ncol: 8 nnew: 1 size: 0
KDO Op code: 21 row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x0100022b hdba: 0x0100022a
itli: 2 ispac: 0 maxfr: 4858
vect = 30
col 5: [ 2] c2 29
CHANGE #2 TYP:0 CLS:31 AFN:3 DBA:0x00c000f0 OBJ:4294967295 SCN:0x0003.17659ec4 SEQ:1 OP:5.2 ENC:0 RBL:0
ktudh redo: slt: 0x0004 sqn: 0x000006fe flg: 0x0012 siz: 164 fbi: 0
uba: 0x00c05eb3.0370.32 pxid: 0x0000.000.00000000
CHANGE #3 TYP:0 CLS:31 AFN:3 DBA:0x00c000f0 OBJ:4294967295 SCN:0x0003.17659fa9 SEQ:1 OP:5.4 ENC:0 RBL:0
ktucm redo: slt: 0x0004 sqn: 0x000006fe srt: 0 sta: 9 flg: 0x2 ktucf redo: uba: 0x00c05eb3.0370.32 ext: 2 spc: 2046 fbi: 0
CHANGE #4 TYP:0 CLS:32 AFN:3 DBA:0x00c05eb3 OBJ:4294967295 SCN:0x0003.17659ec3 SEQ:3 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 164 spc: 2212 flg: 0x0012 seq: 0x0370 rec: 0x32
xid: 0x0008.004.000006fe
ktubl redo: slt: 4 rci: 0 opc: 11.1 [objn: 90541 objd: 90541 tsn: 4]
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
0x00000000 prev ctl uba: 0x00c05eb3.0370.2f
prev ctl max cmt scn: 0x0003.17658385 prev tx cmt scn: 0x0003.176584b8
txn start scn: 0x0003.17659f9c logon user: 83 prev brb: 12607153 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: 7(0x7) flag: 0x2c lock: 0 ckix: 12
ncol: 8 nnew: 1 size: 0
KDO Op code: 21 row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x0100022b hdba: 0x0100022a
itli: 2 ispac: 0 maxfr: 4858
vect = 30
col 5: [ 2] c2 1f
END OF REDO DUMP
$ grep OP: /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_57112.trc
CHANGE #1 TYP:0 CLS:1 AFN:4 DBA:0x0100022b OBJ:90541 SCN:0x0003.17659f96 SEQ:2 OP:11.19 ENC:0 RBL:0
CHANGE #2 TYP:0 CLS:31 AFN:3 DBA:0x00c000f0 OBJ:4294967295 SCN:0x0003.17659ec4 SEQ:1 OP:5.2 ENC:0 RBL:0
CHANGE #3 TYP:0 CLS:31 AFN:3 DBA:0x00c000f0 OBJ:4294967295 SCN:0x0003.17659fa9 SEQ:1 OP:5.4 ENC:0 RBL:0
CHANGE #4 TYP:0 CLS:32 AFN:3 DBA:0x00c05eb3 OBJ:4294967295 SCN:0x0003.17659ec3 SEQ:3 OP:5.1 ENC:0 RBL:0
--//redo中change vector里面常见的op的含义,参考：http://blog.itpub.net/19602/viewspace-1060164/
11.19 没有找到，估计更新操作。11.5更新记录
5.2 事务开始
5.4 commit
5.1 修改undo header中的事务信息
3.分析：
CHANGE #1 TYP:0 CLS:1 AFN:4 DBA:0x0100022b OBJ:90541 SCN:0x0003.17659f96 SEQ:2 OP:11.19 ENC:0 RBL:0
--//修改的数据块。
SCOTT@book> select rowid from empx where empno=7788;
ROWID
------------------
AAAWGtAAEAAAAIrAAH
SCOTT@book> @ &r/rowid AAAWGtAAEAAAAIrAAH
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
------------ ------------ ------------ ------------ -------------------- -------------------- ----------------------------------------
90541 4 555 7 0x100022B 4,555 alter system dump datafile 4 block 555 ;
tabn: 0 slot: 7(0x7) flag: 0x2c lock: 2 ckix: 12
ncol: 8 nnew: 1 size: 0
--//行＝7,字段总数8,修改1个字段，长度没有变化。
col 5: [ 2] c2 29
SCOTT@book> select dump(4000,16) from dual ;
DUMP(4000,16)
------------------
Typ=2 Len=2: c2,29
--//第5个字段，这里竟然记录的是修改后的值4000(IMU特性）。bbed观察数据块。
BBED> p ktbbh.ktbbhitl[1]
struct ktbbhitl[1], 24 bytes @68
struct ktbitxid, 8 bytes @68
ub2 kxidusn @68 0x0008
ub2 kxidslt @70 0x0004
ub4 kxidsqn @72 0x000006fe
struct ktbituba, 8 bytes @76
ub4 kubadba @76 0x00c05eb3
ub2 kubaseq @80 0x0370
ub1 kubarec @82 0x32
ub2 ktbitflg @84 0x2001 (KTBFUPB)
union _ktbitun, 2 bytes @86
sb2 _ktbitfsc @86 0
ub2 _ktbitwrp @86 0x0000
ub4 ktbitbas @88 0x17659fa9
--//op: F xid: 0x0008.004.000006fe uba: 0x00c05eb3.0370.32 都可以对上。
CHANGE #2 TYP:0 CLS:31 AFN:3 DBA:0x00c000f0 OBJ:4294967295 SCN:0x0003.17659ec4 SEQ:1 OP:5.2 ENC:0 RBL:0
ktudh redo: slt: 0x0004 sqn: 0x000006fe flg: 0x0012 siz: 164 fbi: 0
uba: 0x00c05eb3.0370.32 pxid: 0x0000.000.00000000
--//修改是回滚段。实际上是事务表。也就是事务开始。
SCOTT@book> @ &r/dfb16 0x00c000f0
RFILE# BLOCK# TEXT
------------ ------------ ------------------------------------------------------------
3 240 alter system dump datafile 3 block 240 ;
--//usn=8
SCOTT@book> select * from V$ROLLname where usn=8;
USN NAME
------------ --------------------------------------------------
8 _SYSSMU8_517538920$
SCOTT@book> select owner,segment_name,segment_type,header_file,header_block from dba_segments where segment_name='_SYSSMU8_517538920$';
OWNER SEGMENT_NAME SEGMENT_TYPE HEADER_FILE HEADER_BLOCK
------ -------------------- ------------------ ------------ ------------
SYS _SYSSMU8_517538920$ TYPE2 UNDO 3 240
CHANGE #3 TYP:0 CLS:31 AFN:3 DBA:0x00c000f0 OBJ:4294967295 SCN:0x0003.17659fa9 SEQ:1 OP:5.4 ENC:0 RBL:0
ktucm redo: slt: 0x0004 sqn: 0x000006fe srt: 0 sta: 9 flg: 0x2 ktucf redo: uba: 0x00c05eb3.0370.32 ext: 2 spc: 2046 fbi: 0
--//修改是回滚段。实际上是事务表。
--//实际上这里是提交修改标识。
CHANGE #4 TYP:0 CLS:32 AFN:3 DBA:0x00c05eb3 OBJ:4294967295 SCN:0x0003.17659ec3 SEQ:3 OP:5.1 ENC:0 RBL:0
--//相当于在undo段保存前映像。
col 5: [ 2] c2 1f
SCOTT@book> select dump(3000,16) from dual ;
DUMP(3000,16)
------------------
Typ=2 Len=2: c2,1f
--//10g开始引入IMU，导致在重做日志中记录顺序发生了改变。
SYS@book> alter database flashback on ;
Database altered.
SYS@book> alter system archive log current ;
System altered.
--//这样相当于禁用IMU。再修改会原值看看。
$ cat ax.sql
column member new_value v_member
column member noprint
set numw 12
--//alter system archive log current;
SELECT member FROM v$log a, v$logfile b WHERE a.group#(+) = b.group# and a.STATUS='CURRENT' and rownum=1;
column curr1 new_value v_curr1
select current_scn curr1 from v$database;
update scott.empx set sal=3000 where empno=7788;
commit;
column curr2 new_value v_curr2
select current_scn curr2 from v$database;
prompt alter system dump logfile '&&v_member' scn min &&v_curr1 scn max &&v_curr2;
alter system dump logfile '&&v_member' scn min &&v_curr1 scn max &&v_curr2;
SCOTT@book> @ ax.sql
...
REDO RECORD - Thread:1 RBA: 0x00033b.00000019.0010 LEN: 0x01fc VLD: 0x05
SCN: 0x0003.1765ae19 SUBSCN: 1 04/12/2017 12:06:16
(LWN RBA: 0x00033b.00000019.0010 LEN: 0002 NST: 0001 SCN: 0x0003.1765ae17)
CHANGE #1 TYP:0 CLS:35 AFN:3 DBA:0x00c00110 OBJ:4294967295 SCN:0x0003.1765ae13 SEQ:1 OP:5.2 ENC:0 RBL:0
ktudh redo: slt: 0x000a sqn: 0x00004fc8 flg: 0x0012 siz: 164 fbi: 0
uba: 0x00c0028c.0fd5.39 pxid: 0x0000.000.00000000
CHANGE #2 TYP:0 CLS:36 AFN:3 DBA:0x00c0028c OBJ:4294967295 SCN:0x0003.1765ae12 SEQ:1 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 164 spc: 1094 flg: 0x0012 seq: 0x0fd5 rec: 0x39
xid: 0x000a.00a.00004fc8
ktubl redo: slt: 10 rci: 0 opc: 11.1 [objn: 90541 objd: 90541 tsn: 4]
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
0x00000000 prev ctl uba: 0x00c0028c.0fd5.38
prev ctl max cmt scn: 0x0003.1765ac40 prev tx cmt scn: 0x0003.1765ac47
txn start scn: 0xffff.ffffffff logon user: 83 prev brb: 12583553 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: 7(0x7) flag: 0x2c lock: 0 ckix: 12
ncol: 8 nnew: 1 size: 0
KDO Op code: 21 row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x0100022b hdba: 0x0100022a
itli: 3 ispac: 0 maxfr: 4858
vect = 30
col 5: [ 2] c2 29
CHANGE #3 TYP:2 CLS:1 AFN:4 DBA:0x0100022b OBJ:90541 SCN:0x0003.17659fa9 SEQ:2 OP:11.19 ENC:0 RBL:0
KTB Redo
op: 0x11 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: F xid: 0x000a.00a.00004fc8 uba: 0x00c0028c.0fd5.39
Block cleanout record, scn: 0x0003.1765ae19 ver: 0x01 opt: 0x02, entries follow...
itli: 2 flg: 2 scn: 0x0003.17659fa9
Array Update of 1 rows:
tabn: 0 slot: 7(0x7) flag: 0x2c lock: 3 ckix: 12
ncol: 8 nnew: 1 size: 0
KDO Op code: 21 row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x0100022b hdba: 0x0100022a
itli: 3 ispac: 0 maxfr: 4858
vect = 30
col 5: [ 2] c2 1f
REDO RECORD - Thread:1 RBA: 0x00033b.0000001a.001c LEN: 0x0060 VLD: 0x01
SCN: 0x0003.1765ae1a SUBSCN: 1 04/12/2017 12:06:16
CHANGE #1 TYP:0 CLS:35 AFN:3 DBA:0x00c00110 OBJ:4294967295 SCN:0x0003.1765ae19 SEQ:1 OP:5.4 ENC:0 RBL:0
ktucm redo: slt: 0x000a sqn: 0x00004fc8 srt: 0 sta: 9 flg: 0x2 ktucf redo: uba: 0x00c0028c.0fd5.39 ext: 2 spc: 928 fbi: 0
END OF REDO DUMP
$ grep "OP:" /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_57447.trc
CHANGE #1 TYP:0 CLS:35 AFN:3 DBA:0x00c00110 OBJ:4294967295 SCN:0x0003.1765ae13 SEQ:1 OP:5.2 ENC:0 RBL:0
CHANGE #2 TYP:0 CLS:36 AFN:3 DBA:0x00c0028c OBJ:4294967295 SCN:0x0003.1765ae12 SEQ:1 OP:5.1 ENC:0 RBL:0
CHANGE #3 TYP:2 CLS:1 AFN:4 DBA:0x0100022b OBJ:90541 SCN:0x0003.17659fa9 SEQ:2 OP:11.19 ENC:0 RBL:0
CHANGE #1 TYP:0 CLS:35 AFN:3 DBA:0x00c00110 OBJ:4294967295 SCN:0x0003.1765ae19 SEQ:1 OP:5.4 ENC:0 RBL:0
--//redo中change vector里面常见的op的含义,参考：http://blog.itpub.net/19602/viewspace-1060164/
5.2 事务开始
5.1 修改undo header中的事务信息
11.19 没有找到，估计更新操作。11.5更新记录
5.4 commit
