Chinaunix首页 | 论坛 | 博客

吕途的ChinaUnix博客

首页　| 　博文目录　| 　关于我

lvchangxin1987

  • 博客访问： 96770
  • 博文数量： 20
  • 博客积分： 0
  • 博客等级： 民兵
  • 技术积分： 202
  • 用 户 组： 普通用户
  • 注册时间： 2013-09-07 01:56
个人简介

数据库技术爱好者

文章分类

全部博文（20）

文章存档

2016年（11）

2015年（9）

我的朋友
最近访客
推荐博文
相关博文
REDO与UNDO几个实验

分类： Oracle

2016-11-06 22:18:30


点击(此处)折叠或打开

  1. 1.IMU模式与非IMU模式下REDO的区别
  2. 1.1.IMU模式
  3. REDO RECORD - Thread:1 RBA: 0x000004.0000000b.0010 LEN: 0x0374 VLD: 0x0d //RBA: 0x000004.0000000b.0010 日志文件号4+块号64+偏移量10
  4. SCN: 0x0000.04fd17af SUBSCN: 1 11/06/2016 04:32:47
  5. CHANGE #1 TYP:2 CLS:1 AFN:4 DBA:0x01000a77 OBJ:91779 SCN:0x0000.04fd177c SEQ:1 OP:11.19 ENC:0 RBL:0
  6. KTB Redo
  7. op: 0x11 ver: 0x01
  8. compat bit: 4 (post-11) padding: 1
  9. op: F xid: 0x000a.00d.00007207 uba: 0x00c1198a.0242.30
  10. Block cleanout record, scn: 0x0000.04fd17a9 ver: 0x01 opt: 0x02, entries follow...
  11. itli: 1 flg: 2 scn: 0x0000.04fd177c
  12. Array Update of 1 rows:
  13. tabn: 0 slot: 0(0x0) flag: 0x2c lock: 2 ckix: 0
  14. ncol: 2 nnew: 1 size: 0
  15. KDO Op code: 21 row dependencies Disabled
  16. xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x01000a77 hdba: 0x01000a72
  17. itli: 2 ispac: 0 maxfr: 4858
  18. vect = 3
  19. col 1: [ 5] 41 41 41 41 41
  20. CHANGE #2 TYP:0 CLS:35 AFN:3 DBA:0x00c00110 OBJ:4294967295 SCN:0x0000.04fd1783 SEQ:2 OP:5.2 ENC:0 RBL:0 //CLS:35，大于16，对UNDO段的操作，OP:5.2 事务对UNDO段的操作，存放修改前值
  21. ktudh redo: slt: 0x000d sqn: 0x00007207 flg: 0x0012 siz: 168 fbi: 0
  22. uba: 0x00c1198a.0242.30 pxid: 0x0000.000.00000000
  23. CHANGE #3 TYP:0 CLS:1 AFN:4 DBA:0x01000a77 OBJ:91779 SCN:0x0000.04fd17af SEQ:1 OP:11.19 ENC:0 RBL:0
  24. KTB Redo
  25. op: 0x02 ver: 0x01
  26. compat bit: 4 (post-11) padding: 1
  27. op: C uba: 0x00c1198a.0242.31
  28. Array Update of 1 rows:
  29. tabn: 0 slot: 1(0x1) flag: 0x2c lock: 2 ckix: 0
  30. ncol: 2 nnew: 1 size: 0
  31. KDO Op code: 21 row dependencies Disabled
  32. xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x01000a77 hdba: 0x01000a72
  33. itli: 2 ispac: 0 maxfr: 4858
  34. vect = 3
  35. col 1: [ 5] 42 42 42 42 42
  36. CHANGE #4 TYP:0 CLS:35 AFN:3 DBA:0x00c00110 OBJ:4294967295 SCN:0x0000.04fd17af SEQ:1 OP:5.4 ENC:0 RBL:0
  37. ktucm redo: slt: 0x000d sqn: 0x00007207 srt: 0 sta: 9 flg: 0x2 ktucf redo: uba: 0x00c1198a.0242.31 ext: 2 spc: 1276 fbi: 0
  38. CHANGE #5 TYP:0 CLS:36 AFN:3 DBA:0x00c1198a OBJ:4294967295 SCN:0x0000.04fd1783 SEQ:3 OP:5.1 ENC:0 RBL:0
  39. ktudb redo: siz: 168 spc: 1572 flg: 0x0012 seq: 0x0242 rec: 0x30
  40. xid: 0x000a.00d.00007207
  41. ktubl redo: slt: 13 rci: 0 opc: 11.1 [objn: 91779 objd: 91779 tsn: 4]
  42. Undo type: Regular undo Begin trans Last buffer split: No
  43. Temp Object: No
  44. Tablespace Undo: No
  45. 0x00000000 prev ctl uba: 0x00c1198a.0242.2d
  46. prev ctl max cmt scn: 0x0000.04fd1109 prev tx cmt scn: 0x0000.04fd1133
  47. txn start scn: 0x0000.04fd178c logon user: 84 prev brb: 12654980 prev bcl: 0 BuExt idx: 0 flg2: 0
  48. KDO undo record:
  49. KTB Redo
  50. op: 0x03 ver: 0x01
  51. compat bit: 4 (post-11) padding: 1
  52. op: Z
  53. Array Update of 1 rows:
  54. tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0
  55. ncol: 2 nnew: 1 size: 0
  56. KDO Op code: 21 row dependencies Disabled
  57. xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x01000a77 hdba: 0x01000a72
  58. itli: 2 ispac: 0 maxfr: 4858
  59. vect = 3
  60. col 1: [ 5] 61 61 61 61 61
  61. CHANGE #6 TYP:0 CLS:36 AFN:3 DBA:0x00c1198a OBJ:4294967295 SCN:0x0000.04fd17af SEQ:1 OP:5.1 ENC:0 RBL:0
  62. ktudb redo: siz: 124 spc: 1402 flg: 0x0022 seq: 0x0242 rec: 0x31
  63. xid: 0x000a.00d.00007207
  64. ktubu redo: slt: 13 rci: 48 opc: 11.1 objn: 91779 objd: 91779 tsn: 4
  65. Undo type: Regular undo Undo type: Last buffer split: No
  66. Tablespace Undo: No
  67. 0x00000000
  68. KDO undo record:
  69. KTB Redo
  70. op: 0x02 ver: 0x01
  71. compat bit: 4 (post-11) padding: 1
  72. op: C uba: 0x00c1198a.0242.30
  73. Array Update of 1 rows:
  74. tabn: 0 slot: 1(0x1) flag: 0x2c lock: 0 ckix: 0
  75. ncol: 2 nnew: 1 size: 0
  76. KDO Op code: 21 row dependencies Disabled
  77. xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x01000a77 hdba: 0x01000a72
  78. itli: 2 ispac: 0 maxfr: 4858
  79. vect = 3
  80. Array Update of 1 rows:
  81. tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0
  82. ncol: 2 nnew: 1 size: 0
  83. KDO Op code: 21 row dependencies Disabled
  84. xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x01000a77 hdba: 0x01000a72
  85. itli: 2 ispac: 0 maxfr: 4858
  86. vect = 3
  87. col 1: [ 5] 61 61 61 61 61
  88. CHANGE #6 TYP:0 CLS:36 AFN:3 DBA:0x00c1198a OBJ:4294967295 SCN:0x0000.04fd17af SEQ:1 OP:5.1 ENC:0 RBL:0
  89. ktudb redo: siz: 124 spc: 1402 flg: 0x0022 seq: 0x0242 rec: 0x31
  90. xid: 0x000a.00d.00007207
  91. ktubu redo: slt: 13 rci: 48 opc: 11.1 objn: 91779 objd: 91779 tsn: 4
  92. Undo type: Regular undo Undo type: Last buffer split: No
  93. Tablespace Undo: No
  94. 0x00000000
  95. KDO undo record:
  96. KTB Redo
  97. op: 0x02 ver: 0x01
  98. compat bit: 4 (post-11) padding: 1
  99. op: C uba: 0x00c1198a.0242.30
  100. Array Update of 1 rows:
  101. tabn: 0 slot: 1(0x1) flag: 0x2c lock: 0 ckix: 0
  102. ncol: 2 nnew: 1 size: 0
  103. KDO Op code: 21 row dependencies Disabled
  104. xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x01000a77 hdba: 0x01000a72
  105. itli: 2 ispac: 0 maxfr: 4858
  106. vect = 3
  107. col 1: [ 5] 62 62 62 62 62
  111. 1.2 非IMU模式
  112. REDO RECORD - Thread:1 RBA: 0x000006.00000002.0010 LEN: 0x0204 VLD: 0x05
  113. SCN: 0x0000.04fd31be SUBSCN: 1 11/06/2016 05:06:27
  114. CHANGE #1 TYP:0 CLS:23 AFN:3 DBA:0x00c000b0 OBJ:4294967295 SCN:0x0000.04fd3151 SEQ:1 OP:5.2 ENC:0 RBL:0
  115. ktudh redo: slt: 0x0014 sqn: 0x0000708a flg: 0x0012 siz: 168 fbi: 0
  116. uba: 0x00c1a662.02f9.27 pxid: 0x0000.000.00000000
  117. CHANGE #2 TYP:0 CLS:24 AFN:3 DBA:0x00c1a662 OBJ:4294967295 SCN:0x0000.04fd3150 SEQ:9 OP:5.1 ENC:0 RBL:0
  118. ktudb redo: siz: 168 spc: 2698 flg: 0x0012 seq: 0x02f9 rec: 0x27
  119. xid: 0x0004.014.0000708a
  120. ktubl redo: slt: 20 rci: 0 opc: 11.1 [objn: 91780 objd: 91780 tsn: 4]
  121. Undo type: Regular undo Begin trans Last buffer split: No
  122. Temp Object: No
  123. Tablespace Undo: No
  124. 0x00000000 prev ctl uba: 0x00c1a662.02f9.1e
  125. prev ctl max cmt scn: 0x0000.04fd205c prev tx cmt scn: 0x0000.04fd29f4
  126. txn start scn: 0xffff.ffffffff logon user: 84 prev brb: 12691023 prev bcl: 0 BuExt idx: 0 flg2: 0
  127. KDO undo record:
  128. KTB Redo
  129. op: 0x03 ver: 0x01
  130. compat bit: 4 (post-11) padding: 1
  131. op: Z
  132. Array Update of 1 rows:
  133. tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0
  134. ncol: 2 nnew: 1 size: 0
  135. KDO Op code: 21 row dependencies Disabled
  136. xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x01000a7f hdba: 0x01000a7a
  137. itli: 2 ispac: 0 maxfr: 4858
  138. vect = 3
  139. col 1: [ 5] 61 61 61 61 61
  140. CHANGE #3 TYP:2 CLS:1 AFN:4 DBA:0x01000a7f OBJ:91780 SCN:0x0000.04fd1dca SEQ:1 OP:11.19 ENC:0 RBL:0
  141. KTB Redo
  142. op: 0x11 ver: 0x01
  143. compat bit: 4 (post-11) padding: 1
  144. op: F xid: 0x0004.014.0000708a uba: 0x00c1a662.02f9.27
  145. Block cleanout record, scn: 0x0000.04fd31be ver: 0x01 opt: 0x02, entries follow...
  146. itli: 1 flg: 2 scn: 0x0000.04fd1dca
  147. Array Update of 1 rows:
  148. tabn: 0 slot: 0(0x0) flag: 0x2c lock: 2 ckix: 0
  149. ncol: 2 nnew: 1 size: 0
  150. KDO Op code: 21 row dependencies Disabled
  151. xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x01000a7f hdba: 0x01000a7a
  152. itli: 2 ispac: 0 maxfr: 4858
  153. vect = 3
  154. col 1: [ 5] 41 41 41 41 41
  156. REDO RECORD - Thread:1 RBA: 0x000006.0000000c.0010 LEN: 0x016c VLD: 0x05
  157. SCN: 0x0000.04fd31c9 SUBSCN: 1 11/06/2016 05:06:42
  158. CHANGE #1 TYP:0 CLS:24 AFN:3 DBA:0x00c1a662 OBJ:4294967295 SCN:0x0000.04fd31be SEQ:1 OP:5.1 ENC:0 RBL:0
  159. ktudb redo: siz: 124 spc: 2528 flg: 0x0022 seq: 0x02f9 rec: 0x28
  160. xid: 0x0004.014.0000708a
  161. ktubu redo: slt: 20 rci: 39 opc: 11.1 objn: 91780 objd: 91780 tsn: 4
  162. Undo type: Regular undo Undo type: Last buffer split: No
  163. Tablespace Undo: No
  164. 0x00000000
  165. KDO undo record:
  166. KTB Redo
  167. op: 0x02 ver: 0x01
  168. compat bit: 4 (post-11) padding: 1
  169. op: C uba: 0x00c1a662.02f9.27
  170. Array Update of 1 rows:
  171. tabn: 0 slot: 1(0x1) flag: 0x2c lock: 0 ckix: 0
  172. ncol: 2 nnew: 1 size: 0
  173. KDO Op code: 21 row dependencies Disabled
  174. xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x01000a7f hdba: 0x01000a7a
  175. itli: 2 ispac: 0 maxfr: 4858
  176. vect = 3
  177. col 1: [ 5] 62 62 62 62 62
  178. CHANGE #2 TYP:0 CLS:1 AFN:4 DBA:0x01000a7f OBJ:91780 SCN:0x0000.04fd31be SEQ:1 OP:11.19 ENC:0 RBL:0 //OP:11.19 更新数据块
  179. KTB Redo
  180. op: 0x02 ver: 0x01
  181. compat bit: 4 (post-11) padding: 1
  182. op: C uba: 0x00c1a662.02f9.28
  183. Array Update of 1 rows:
  184. tabn: 0 slot: 1(0x1) flag: 0x2c lock: 2 ckix: 0
  185. ncol: 2 nnew: 1 size: 0
  186. KDO Op code: 21 row dependencies Disabled
  187. xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x01000a7f hdba: 0x01000a7a
  188. itli: 2 ispac: 0 maxfr: 4858
  189. vect = 3
  190. col 1: [ 5] 42 42 42 42 42
  192. REDO RECORD - Thread:1 RBA: 0x000006.0000000c.017c LEN: 0x0060 VLD: 0x01
  193. SCN: 0x0000.04fd31ca SUBSCN: 1 11/06/2016 05:06:42
  194. CHANGE #1 TYP:0 CLS:23 AFN:3 DBA:0x00c000b0 OBJ:4294967295 SCN:0x0000.04fd31be SEQ:1 OP:5.4 ENC:0 RBL:0 //提交事务
  195. ktucm redo: slt: 0x0014 sqn: 0x0000708a srt: 0 sta: 9 flg: 0x2 ktucf redo: uba: 0x00c1a662.02f9.28 ext: 31 spc: 2402 fbi: 0
  196. END OF REDO DUMP
  197. ----- Redo read statistics for thread 1 -----
  198. Read rate (SYNC): 5Kb in 0.01s => 0.54 Mb/sec
  199. Total redo bytes: 5Kb Longest record: 0Kb, moves: 0/14 moved: 0Mb (0%)
  200. Longest LWN: 1Kb, reads: 5
  201. Last redo scn: 0x0000.04fd31ca (83702218)
  202. Change vector header moves = 0/27 (0%)
  203. ----------------------------------------------
  205. 总结：
  206. IMU模式下：日志一次性写入，一个事务对应一条日志记录，先写入事务更改前的所有旧值，后写入更新后的新值
  207. 非IMU模式下：日志多次写，一个事务中有N次DML操作，则有N条日志记录，每条日志对应一次DML操作，存放该DML操作的旧值和更改后值。
  209. 解决问题：减少REDO日志写入次数；快速构造CR块
  211. 二、通过实验重现ORA-01555快照过旧的错误，如何避免错误？
  212. 重现ORA-01555错误：
  213. SQL> create undo tablespace undo1 datafile '/u01/app/oracle/oradata/edus/undo01.dbf' size 1m;
  215. Tablespace created.
  217. SQL> alter system set undo_retention=1;
  219. System altered.
  221. SQL> var x refcursor
  222. SQL> begin
  223. 2 open :x for select * from lvtu;
  224. 3 end;
  225. 4 /
  227. PL/SQL procedure successfully completed.
  229. SQL> begin
  230. 2 for i in 1 .. 20000 loop
  231. 3 update lvtu set id=id+1;
  232. 4 commit;
  233. 5 end loop;
  234. 6 end;
  235. 7 /
  237. PL/SQL procedure successfully completed.
  239. SQL> print :x
  240. ERROR:
  241. ORA-01555: snapshot too old
  242. 如何避免错误：将undo_retention参数设置大点，但会导致UNDO表空间过度扩展，但治标不治本，
  243. 从根本就应该从优化SQL语句和数据库对象设计入手，充分利用索引，减少SQL执行时间

  245. 3.UNDO的一致性读
  246. SQL> select * from lvtu;


            ID NAME
    ---------- ------------------------------------------------------------
             1 DDDDD
             2 BBBBB


    SQL> var x refcursor;
    SQL> exec open:x for select * from lvtu where id=1;


    PL/SQL procedure successfully completed.


    Commit complete.


    SQL> update lvtu set name='AAAAA' where id=1;


    1 row updated.


    SQL> print x


            ID NAME
    ---------- ------------------------------------------------------------
             1 DDDDD
     
     
    SQL> select dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from lvtu where id=1;


    DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
    ------------------------------------ ------------------------------------
                                       4                                 2687
      
      
    另一会话：
    SQL> select addr,xid,xidusn,xidslot,xidsqn,ubafil,ubablk,ubasqn from v$transaction;


    ADDR             XID                  XIDUSN    XIDSLOT     XIDSQN     UBAFIL
    ---------------- ---------------- ---------- ---------- ---------- ----------
        UBABLK     UBASQN
    ---------- ----------
    00000000951B0FF0 0500120027730000          5         18      29479          3
         91676        781


    SQL> alter system checkpoint;


    System altered.


    SQL> alter system dump datafile 4 block 2687;


    System altered.
    SQL> select * from  v$diag_info where name like '%Default%';


       INST_ID
    ----------
    NAME
    --------------------------------------------------------------------------------
    VALUE
    --------------------------------------------------------------------------------
             1
    Default Trace File
    /u01/oracle/diag/rdbms/edus/edus/trace/edus_ora_14202.trc


     Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
    0x01   0x0005.012.00007327  0x00c1661c.030d.1c  ----    1  fsc 0x0000.00000000   //未有明确提交标记，XID,通过事务表发现未提交，可以根据UBA（3号文件91676块第28条记录），往前SCN
    0x02   0x0009.00c.00007259  0x00c08040.028f.11  C---    0  scn 0x0000.04fd42a5
    bdba: 0x01000a7f
    data_block_dump,data header at 0x2ab3be5aea64
    ===============
    tsiz: 0x1f98
    hsiz: 0x16
    pbl: 0x2ab3be5aea64
         76543210
    flag=--------
    ntab=1
    nrow=2
    frre=-1
    fsbo=0x16
    fseo=0x1f80
    avsp=0x1f6a
    tosp=0x1f6a
    0xe:pti[0]      nrow=2  offs=0
    0x12:pri[0]     offs=0x1f8c
    0x14:pri[1]     offs=0x1f80
    block_row_dump:
    tab 0, row 0, @0x1f8c
    tl: 12 fb: --H-FL-- lb: 0x1  cc: 2
    col  0: [ 2]  c1 02
    col  1: [ 5]  41 41 41 41 41    //修改未提交的数据
    tab 0, row 1, @0x1f80
    tl: 12 fb: --H-FL-- lb: 0x0  cc: 2
    col  0: [ 2]  c1 03
    col  1: [ 5]  42 42 42 42 42


    第二次DUMP
    SQL> alter system dump datafile 3 block 91676;


    System altered.


    SQL> select * from  v$diag_info where name like '%Default%';


       INST_ID
    ----------
    NAME
    --------------------------------------------------------------------------------
    VALUE
    --------------------------------------------------------------------------------
             1
    Default Trace File
    /u01/oracle/diag/rdbms/edus/edus/trace/edus_ora_14423.trc


    *-----------------------------
    * Rec #0x1c  slt: 0x12  objn: 91780(0x00016684)  objd: 91780  tblspc: 4(0x00000004)
    *       Layer:  11 (Row)   opc: 1   rci 0x00
    Undo type:  Regular undo    Begin trans    Last buffer split:  No
    Temp Object:  No
    Tablespace Undo:  No
    rdba: 0x00000000Ext idx: 0
    flg2: 0
    *-----------------------------
    uba: 0x00c1661c.030d.19 ctl max scn: 0x0000.04fd3c64 prv tx scn: 0x0000.04fd3c6d
    txn start scn: scn: 0x0000.04fd42b8 logon user: 84
     prev brb: 12674585 prev bcl: 0
    KDO undo record:
    KTB Redo
    op: 0x04  ver: 0x01
    compat bit: 4 (post-11) padding: 1
    op: L  itl: xid:  0x0009.000.00007259 uba: 0x00c08040.028f.10
                          flg: C---    lkc:  0     scn: 0x0000.04fd4272   //该UNDO记录关联的事务槽信息，FLG:已经提交，对比SCN，发会该SCN与SELECT语发出的SCN最相近
    Array Update of 1 rows:
    tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0
    ncol: 2 nnew: 1 size: 0
    KDO Op code:  21 row dependencies Disabled
      xtype: XAxtype KDO_KDOM2 flags: 0x00000080  bdba: 0x01000a7f  hdba: 0x01000a7a
    itli: 1  ispac: 0  maxfr: 4858
    vect = 3
    col  1: [ 5]  44 44 44 44 44  //更新为AAAAA前的旧值，DDDDDD,当前事务的相关旧值

阅读(1934) | 评论(0) | 转发(0) |
0

上一篇：Oracle BBED工具的使用

下一篇：没有了

给主人留下些什么吧！~~
关于我们 | 关于IT168 | 联系方式 | 广告合作 | 法律声明 | 免费注册

Copyright 2001-2010 ChinaUnix.net All Rights Reserved 北京皓辰网域网络信息技术有限公司. 版权所有

感谢所有关心和支持过ChinaUnix的朋友们

16024965号-6