ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > Redo internals update single row

Redo internals update single row

原创 Oracle 作者：desert_xu 时间：2015-09-11 20:12:27 0 删除 编辑

  看了travel dba的blog 关于 redo internal 的干货，自己也学习研究了一下，俗话说好记忆不如烂笔头，我就写下来，记之。
   

点击(此处)折叠或打开

  1. -- +----------------------------------------------------------------------------+
  2. -- | Travel Liu |
  3. -- | travel.liu@outlook.com |
  4. -- | www.traveldba.com |
  5. -- |----------------------------------------------------------------------------|
  6. -- | |
  7. -- |----------------------------------------------------------------------------|
  8. -- | DATABASE : Oracle |
  9. -- | FILE : redo_internals_update.sql |
  10. -- | CLASS : Redo_Internal |
  11. -- | PURPOSE : This script will Single row update statement |
  12. -- | and dump the log file |
  13. -- +----------------------------------------------------------------------------+
  14. -- | MODIFIED (YYYY/MM/DD) |
  15. -- | Travel.liu 2013/09/14 - First Edition |
  16. -- +----------------------------------------------------------------------------+


  19. -- |----------------------------------------------------------------------------|
  20. set serveroutput on size 1000000
  21. prompt Test case : Redo internals : Single row update on heap table with no index

  23. drop table redo_internals;
  24. create table redo_internals
  25. (
  26. col1 char(2),
  27. col2 varchar2(20)
  28. )
  29. ;
  30. -- insert into table two record
  31. --
  32. insert into redo_internals values ('A1','FIRST ROW');
  33. insert into redo_internals values ('A2','SECOND ROW');


  36. commit;

  38. alter system switch logfile;

  40. def scn_1="NA"
  41. col CURRENT_scn new_value scn_1
  42. select CURRENT_scn from v$DATABASE;

  44. exec dbms_output.put_line ('scn_1 :'||&scn_1);

  46. select getmystat('redo size') from dual;

  48. update redo_internals set col2='FIRST ROW UPD' where col1 ='A1';

  50. select getmystat('redo size') from dual;

  52. def scn_2="NA"
  53. col CURRENT_scn new_value scn_2
  54. select CURRENT_scn from v$DATABASE;
  55. exec dbms_output.put_line ('scn_2 :'||&scn_2);


  58. def scn_3="NA"
  59. col CURRENT_scn new_value scn_3
  60. select CURRENT_scn from v$DATABASE;
  61. exec dbms_output.put_line ('scn_3 :'||&scn_3);

  63. commit;

  65. def scn_4="NA"
  66. col CURRENT_scn new_value scn_4
  67. select CURRENT_scn from v$DATABASE;
  68. exec dbms_output.put_line ('scn_4 :'||&scn_4);

  70. -- |----------------------------------------------------------------------------|
  71. -- | dump redo last log
  72. -- |----------------------------------------------------------------------------|

  74. def log_file="NA"
  75. col member for a50 new_value log_file;
  76. select member from
  77. v$log lg, v$logfile lgfile
  78. where lg.group# = lgfile.group# and lg.status='CURRENT';

  80. alter system dump logfile '&log_file' scn min &scn_1 scn max &scn_2;

  82. exec dbms_output.put_line ('dumo sql :'||'alter system dump logfile '||chr(39)||'&log_file'||chr(39)||' scn min '||&scn_1 ||' scn max '|| &scn_2);

  84. alter system dump logfile '&log_file' scn min &scn_2 scn max &scn_3;

  86. exec dbms_output.put_line ('dumo sql :'||'alter system dump logfile '||chr(39)||'&log_file'||chr(39)||' scn min '||&scn_2 ||' scn max '|| &scn_3);

  88. alter system dump logfile '&log_file' scn min &scn_3 scn max &scn_4;

  90. exec dbms_output.put_line ('dumo sql :'||'alter system dump logfile '||chr(39)||'&log_file'||chr(39)||' scn min '||&scn_3 ||' scn max '|| &scn_4);
  91. -- |----------------------------------------------------------------------------|

  93. col trace_file_name for a100

  95. SELECT d.VALUE || '/' || UPPER(RTRIM(i.INSTANCE, CHR(0))) || '_ora_' ||      --modify by desert_xu
  96.        p.spid || '.trc' trace_file_name
  97.   FROM (SELECT p.spid
  98.           FROM v$mystat m, v$session s, v$process p
  99.          WHERE m.statistic# = 1
  100.            AND s.SID = m.SID
  101.            AND p.addr = s.paddr) p,
  102.        (SELECT t.INSTANCE
  103.           FROM v$thread t, v$parameter v
  104.          WHERE v.NAME = 'thread'
  105.            AND (v.VALUE = 0 OR t.thread# = TO_NUMBER(v.VALUE))) i,
  106.        (SELECT VALUE FROM v$parameter WHERE NAME = 'user_dump_dest') d;
  107. -- |----------------------------------------------------------------------------|

  109. undef log_file
  110. undef scn_1
  111. undef scn_2
  112. undef scn_3
  113. undef scn_4

 执行如上脚本 
sys@PROD@148> @redo_internals_update.sql
Test case : Redo internals : Single row update on heap table with no index
drop table redo_internals
        *
ERROR at line 1:
ORA-00942: table or view does not exist

Table created.
1 row created.

1 row created.

Commit complete.

System altered.

CURRENT_SCN
-----------
   13228213

scn_1 :13228213

PL/SQL procedure successfully completed.


GETMYSTAT('REDOSIZE')
---------------------
                12124

1 row updated.

GETMYSTAT('REDOSIZE')
---------------------
                12680
CURRENT_SCN
-----------
   13228215
scn_2 :13228215

PL/SQL procedure successfully completed.

CURRENT_SCN
-----------
   13228216

scn_3 :13228216

PL/SQL procedure successfully completed.

Commit complete.

CURRENT_SCN
-----------
   13228219

scn_4 :13228219

PL/SQL procedure successfully completed.

MEMBER
--------------------------------------------------
+DATA/prod/redo01.log

old   1: alter system dump logfile '&log_file' scn min &scn_1 scn max &scn_2
new   1: alter system dump logfile '+DATA/prod/redo01.log' scn min   13228213 scn max  13228215

System altered.

dumo sql  :alter system dump logfile '+DATA/prod/redo01.log' scn min 13228213 scn max 13228215

PL/SQL procedure successfully completed.

old   1: alter system dump logfile '&log_file' scn min &scn_2 scn max &scn_3
new   1: alter system dump logfile '+DATA/prod/redo01.log' scn min   13228215 scn max  13228216

System altered.

dumo sql  :alter system dump logfile '+DATA/prod/redo01.log' scn min 13228215 scn max 13228216

PL/SQL procedure successfully completed.

old   1: alter system dump logfile '&log_file' scn min &scn_3 scn max &scn_4
new   1: alter system dump logfile '+DATA/prod/redo01.log' scn min   13228216 scn max  13228219

System altered.

dumo sql  :alter system dump logfile '+DATA/prod/redo01.log' scn min 13228216 scn max 13228219

PL/SQL procedure successfully completed.

TRACE_FILE_NAME
----------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/prod/PROD/trace/prod_ora_7071.trc



点击(此处)折叠或打开

  1. [root@ocp02 /]# more /u01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_ora_7071.trc
  2. Trace file /u01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_ora_7071.trc
  3. Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
  4. With the Partitioning, Automatic Storage Management, OLAP, Data Mining
  5. and Real Application Testing options
  6. ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1
  7. System name: Linux
  8. Node name: ocp02
  9. Release: 3.8.13-16.2.1.el6uek.x86_64
  10. Version: #1 SMP Thu Nov 7 17:01:44 PST 2013
  11. Machine: x86_64
  12. Instance name: PROD
  13. Redo thread mounted by this instance: 1
  14. Oracle process number: 23
  15. Unix process pid: 7071, image: oracle@ocp02 (TNS V1-V3)


  18. *** 2015-09-11 17:20:34.861
  19. *** SESSION ID:(148.21) 2015-09-11 17:20:34.861
  20. *** CLIENT ID:() 2015-09-11 17:20:34.861
  21. *** SERVICE NAME:(SYS$USERS) 2015-09-11 17:20:34.861
  22. *** MODULE NAME:(sqlplus@ocp02 (TNS V1-V3)) 2015-09-11 17:20:34.861
  23. *** ACTION NAME:() 2015-09-11 17:20:34.861
  24.  
  25. Initial buffer sizes: read 1024K, overflow 832K, change 805K
  26.  
  27. DUMP OF REDO FROM FILE '+DATA/prod/redo01.log'
  28.  Opcodes *.*
  29.  RBAs: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff
  30.  SCNs: scn: 0x0000.00c9d8b5 (13228213) thru scn: 0x0000.00c9d8b7 (13228215)
  31.  Times: creation thru eternity
  32.  FILE HEADER:
  33.         Compatibility Vsn = 186647552=0xb200400
  34.         Db ID=256082616=0xf4382b8, Db Name='PROD'
  35.         Activation ID=280829487=0x10bd1e2f
  36.         Control Seq=15559=0x3cc7, File size=102400=0x19000
  37.         File Number=1, Blksiz=512, File Type=2 LOG
  38.  descrip:"Thread 0001, Seq# 0000000157, SCN 0x000000c9d8b4-0xffffffffffff"
  39.  thread: 1 nab: 0xffffffff seq: 0x0000009d hws: 0x1 eot: 1 dis: 0
  40.  resetlogs count: 0x33cb6ac8 scn: 0x0000.0081cdf7 (8506871)
  41.  prev resetlogs count: 0x33ae8481 scn: 0x0000.007a5b94 (8018836)
  42.  Low scn: 0x0000.00c9d8b4 (13228212) 09/11/2015 17:20:34
  43.  Next scn: 0xffff.ffffffff 01/01/1988 00:00:00
  44.  Enabled scn: 0x0000.0081cdf7 (8506871) 01/14/2015 12:19:20
  45.  Thread closed scn: 0x0000.00c9d8b4 (13228212) 09/11/2015 17:20:34
  46.  Disk cksum: 0x92cd Calc cksum: 0x92cd
  47.  Terminal recovery stop scn: 0x0000.00000000
  48.  Terminal recovery 01/01/1988 00:00:00
  49.  Most recent redo scn: 0x0000.00000000
  50.  Largest LWN: 0 blocks
  51.  End-of-redo stream : No
  52.  Unprotected mode
  53.  Miscellaneous flags: 0x800000
  54.  Thread internal enable indicator: thr: 0, seq: 0 scn: 0x0000.00000000
  55.  Zero blocks: 0
  56.  Format ID is 2
  57.  redo log key is 17e3d82ad2e75c99be94764bae96f58
  58.  redo log key flag is 5
  59.  Enabled redo threads: 1
  60.  
  61. REDO RECORD - Thread:1 RBA: 0x00009d.00000002.0010 LEN: 0x022c VLD: 0x05
  62. SCN: 0x0000.00c9d8b6 SUBSCN: 1 09/11/2015 17:20:34
  63. (LWN RBA: 0x00009d.00000002.0010 LEN: 0002 NST: 0001 SCN: 0x0000.00c9d8b4)
  64. CHANGE #1 TYP:0 CLS:19 AFN:3 DBA:0x00c00090 OBJ:4294967295 SCN:0x0000.00c9d894 SEQ:1 OP: undo header transaction status 更新为10 
  65. 5.2 ENC:0 RBL:0 
  66. ktudh redo: slt: 0x0003 sqn: 0x00001562 flg: 0x0012 siz: 168 fbi: 0
  67.             uba: 0x00c00dba.0187.0e pxid: 0x0000.000.00000000
  68. CHANGE #2 TYP:0 CLS:20 AFN:3 DBA:0x00c00dba OBJ:4294967295 SCN:0x0000.00c9d893 SEQ:3 OP:undo block， 回滚值 写入 undo block  
  69. 5.1 ENC:0 RBL:0
  70. ktudb redo: siz: 168 spc: 6100 flg: 0x0012 seq: 0x0187 rec: 0x0e
  71.             xid: 0x0002.003.00001562
  72. ktubl redo: slt: 3 rci: 0 opc: 11.1 [objn: 21291 objd: 21291 tsn: 0]
  73. Undo type: Regular undo Begin trans Last buffer split: No
  74. Temp Object: No
  75. Tablespace Undo: No
  76.              0x00000000 prev ctl uba: 0x00c00dba.0187.0b
  77. prev ctl max cmt scn: 0x0000.00c9d528 prev tx cmt scn: 0x0000.00c9d53e
  78. txn start scn: 0xffff.ffffffff logon user: 0 prev brb: 12586386 prev bcl: 0 BuExt id
  79. x: 0 flg2: 0
  80. KDO undo record:
  81. KTB Redo
  82. op: 0x03 ver: 0x01
  83. compat bit: 4 (post-11) padding: 1
  84. op: Z
  85. KDO Op code: URP row dependencies Disabled
  86.   xtype: XA flags: 0x00000000 bdba: 0x0040fc09 hdba: 0x0040fc08
  87. itli: 2 ispac: 0 maxfr: 4863
  88. tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0
  89. ncol: 2 nnew: 1 size: -4
  90. col 1: [ 9] 46 49 52 53 54 20 52 4f 57  UPDATE 之前的原值
  91. CHANGE #3 TYP:2 CLS:1 AFN:1 DBA:0x0040fc09 OBJ:21291 SCN:0x0000.00c9d8b0 SEQ:1 OP:11.5 E   redo 记录 行更新操作 11.5 记录更新后的值如下
  92. NC:0 RBL:0
  93. KTB Redo
  94. op: 0x11 ver: 0x01
  95. compat bit: 4 (post-11) padding: 1
  96. op: F xid: 0x0002.003.00001562 uba: 0x00c00dba.0187.0e
  97. Block cleanout record, scn: 0x0000.00c9d8b6 ver: 0x01 opt: 0x02, entries follow...
  98.   itli: 1 flg: 2 scn: 0x0000.00c9d8b0
  99. KDO Op code: URP row dependencies Disabled
  100.   xtype: XA flags: 0x00000000 bdba: 0x0040fc09 hdba: 0x0040fc08
  101. itli: 2 ispac: 0 maxfr: 4863
  102. tabn: 0 slot: 0(0x0) flag: 0x2c lock: 2 ckix: 0
  103. ncol: 2 nnew: 1 size: 4
  104. col 1: [13] 46 49 52 53 54 20 52 4f 57 20 55 50 44    update 后的新值
  105. CHANGE #4 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:0 OP:5.20 ENC:0  事物审计记录
  106. session number = 148
  107. serial number = 21
  108. transaction name =
  109. version 186647552
  110. audit sessionid 4294967295
  111. Client Id =
  112. login username = SYS
  113. END OF REDO DUMP
  114. ----- Redo read statistics for thread 1 -----
  115. Read rate (ASYNC): 1Kb in 0.26s => 0.00 Mb/sec
  116. Total redo bytes: 1023Kb Longest record: 0Kb, moves: 0/1 moved: 0Mb (0%)
  117. Longest LWN: 1Kb, reads: 1
  118. Last redo scn: 0x0000.00c9d8b6 (13228214)
  119. Change vector header moves = 1/4 (25%)
  120. ----------------------------------------------
  121. 第二次 redo dump
  122. *** 2015-09-11 17:20:35.577
  123. Initial buffer sizes: read 1024K, overflow 832K, change 805K
  124. redo文件头 
  125. DUMP OF REDO FROM FILE '+DATA/prod/redo01.log'
  126.  Opcodes *.*
  127.  RBAs: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff
  128.  SCNs: scn: 0x0000.00c9d8b7 (13228215) thru scn: 0x0000.00c9d8b8 (13228216)
  129.  Times: creation thru eternity
  130.  FILE HEADER:
  131.         Compatibility Vsn = 186647552=0xb200400
  132.         Db ID=256082616=0xf4382b8, Db Name='PROD'
  133.         Activation ID=280829487=0x10bd1e2f
  134.         Control Seq=15559=0x3cc7, File size=102400=0x19000
  135.         File Number=1, Blksiz=512, File Type=2 LOG
  136.  descrip:"Thread 0001, Seq# 0000000157, SCN 0x000000c9d8b4-0xffffffffffff"
  137.  thread: 1 nab: 0xffffffff seq: 0x0000009d hws: 0x1 eot: 1 dis: 0
  138.  resetlogs count: 0x33cb6ac8 scn: 0x0000.0081cdf7 (8506871)
  139.  prev resetlogs count: 0x33ae8481 scn: 0x0000.007a5b94 (8018836)
  140.  Low scn: 0x0000.00c9d8b4 (13228212) 09/11/2015 17:20:34
  141.  Next scn: 0xffff.ffffffff 01/01/1988 00:00:00
  142.  Enabled scn: 0x0000.0081cdf7 (8506871) 01/14/2015 12:19:20
  143.  Thread closed scn: 0x0000.00c9d8b4 (13228212) 09/11/2015 17:20:34
  144.  Disk cksum: 0x92cd Calc cksum: 0x92cd
  145.  Terminal recovery stop scn: 0x0000.00000000
  146.  Terminal recovery 01/01/1988 00:00:00
  147.  Most recent redo scn: 0x0000.00000000
  148.  Largest LWN: 0 blocks
  149.  End-of-redo stream : No
  150.  Unprotected mode
  151.  Miscellaneous flags: 0x800000
  152.  Thread internal enable indicator: thr: 0, seq: 0 scn: 0x0000.00000000
  153.  Zero blocks: 0
  154.  Format ID is 2
  155.  redo log key is 17e3d82ad2e75c99be94764bae96f58
  156.  redo log key flag is 5
  157.  Enabled redo threads: 1
  158. END OF REDO DUMP
  159. ----- Redo read statistics for thread 1 -----
  160. Read rate (ASYNC): 1Kb in 0.07s => 0.01 Mb/sec
  161. Total redo bytes: 1023Kb Longest record: 0Kb, moves: 0/1 moved: 0Mb (0%)
  162. Longest LWN: 1Kb, reads: 1
  163. Last redo scn: 0x0000.00c9d8b6 (13228214)
  164. Change vector header moves = 1/4 (25%)
  165. ----------------------------------------------
  166. Initial buffer sizes: read 1024K, overflow 832K, change 805K
  167. 第3次 dump redo 
  168. DUMP OF REDO FROM FILE '+DATA/prod/redo01.log'
  169.  Opcodes *.*
  170.  RBAs: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff
  171.  SCNs: scn: 0x0000.00c9d8b8 (13228216) thru scn: 0x0000.00c9d8bb (13228219)
  172.  Times: creation thru eternity
  173.  FILE HEADER:
  174.         Compatibility Vsn = 186647552=0xb200400
  175.         Db ID=256082616=0xf4382b8, Db Name='PROD'
  176.         Activation ID=280829487=0x10bd1e2f
  177.         Control Seq=15559=0x3cc7, File size=102400=0x19000
  178.         File Number=1, Blksiz=512, File Type=2 LOG
  179.  descrip:"Thread 0001, Seq# 0000000157, SCN 0x000000c9d8b4-0xffffffffffff"
  180.  thread: 1 nab: 0xffffffff seq: 0x0000009d hws: 0x1 eot: 1 dis: 0
  181.  resetlogs count: 0x33cb6ac8 scn: 0x0000.0081cdf7 (8506871)
  182.  prev resetlogs count: 0x33ae8481 scn: 0x0000.007a5b94 (8018836)
  183.  Low scn: 0x0000.00c9d8b4 (13228212) 09/11/2015 17:20:34
  184.  Next scn: 0xffff.ffffffff 01/01/1988 00:00:00
  185.  Enabled scn: 0x0000.0081cdf7 (8506871) 01/14/2015 12:19:20
  186.  Thread closed scn: 0x0000.00c9d8b4 (13228212) 09/11/2015 17:20:34
  187.  Disk cksum: 0x92cd Calc cksum: 0x92cd
  188.  Terminal recovery stop scn: 0x0000.00000000
  189.  Terminal recovery 01/01/1988 00:00:00
  190.  Most recent redo scn: 0x0000.00000000
  191.  Largest LWN: 0 blocks
  192.  End-of-redo stream : No
  193.  Unprotected mode
  194.  Miscellaneous flags: 0x800000
  195.  Thread internal enable indicator: thr: 0, seq: 0 scn: 0x0000.00000000
  196.  Zero blocks: 0
  197.  Format ID is 2
  198.  redo log key is 17e3d82ad2e75c99be94764bae96f58
  199.  redo log key flag is 5
  200.  Enabled redo threads: 1
  201.  
  202. REDO RECORD - Thread:1 RBA: 0x00009d.00000003.004c LEN: 0x00a4 VLD: 0x01
  203. SCN: 0x0000.00c9d8b9 SUBSCN: 1 09/11/2015 17:20:34
  204. (LWN RBA: 0x00009d.00000002.0010 LEN: 0002 NST: 0001 SCN: 0x0000.00c9d8b4)
  205. CHANGE #1 TYP:0 CLS:19 AFN:3 DBA:0x00c00090 OBJ:4294967295 SCN:0x0000.00c9d8b6 SEQ:1 OP:
  206. 5.4 ENC:0 RBL:0  事物提交操作
  207. ktucm redo: slt: 0x0003 sqn: 0x00001562 srt: 0 sta: 9 flg: 0x2 ktucf redo: uba: 0x00c00d
  208. ba.0187.0e ext: 2 spc: 5930 fbi: 0
  209. CHANGE #2 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:0 OP:24.4 ENC:0
  210. END OF REDO DUMP
  211. ----- Redo read statistics for thread 1 -----
  212. Read rate (ASYNC): 1Kb in 0.06s => 0.02 Mb/sec
  213. Total redo bytes: 1023Kb Longest record: 0Kb, moves: 0/2 moved: 0Mb (0%)
  214. Longest LWN: 1Kb, reads: 1
  215. Last redo scn: 0x0000.00c9d8b9 (13228217)
  216. Change vector header moves = 1/6 (16%)
  217. ----------------------------------------------

IN_MEMORY_UNDO 没有起作用，可能虚拟机环境有限


来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/21980353/viewspace-1797547/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： 10.2.0.5 rac crs 起不来
下一篇： Redo log header 研究
请登录后发表评论 登录
全部评论
desert_xu

注册时间：2013-10-23

  • 博文量
    79
  • 访问量
    281029

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593