Redo internals update single row

  看了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. -- +----------------------------------------------------------------------------+
    
17. 
    
18. 
    
19. -- |----------------------------------------------------------------------------|
    
20. set serveroutput on size 1000000
    
21. prompt Test case : Redo internals : Single row update on heap table with no index
    
22. 
    
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');
    
34. 
    
35. 
    
36. commit;
    
37. 
    
38. alter system switch logfile;
    
39. 
    
40. def scn_1="NA"
    
41. col CURRENT_scn new_value scn_1
    
42. select CURRENT_scn from v$DATABASE;
    
43. 
    
44. exec dbms_output.put_line ('scn_1 :'||&scn_1);
    
45. 
    
46. select getmystat('redo size') from dual;
    
47. 
    
48. update redo_internals set col2='FIRST ROW UPD' where col1 ='A1';
    
49. 
    
50. select getmystat('redo size') from dual;
    
51. 
    
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);
    
56. 
    
57. 
    
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);
    
62. 
    
63. commit;
    
64. 
    
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);
    
69. 
    
70. -- |----------------------------------------------------------------------------|
    
71. -- | dump redo last log
    
72. -- |----------------------------------------------------------------------------|
    
73. 
    
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';
    
79. 
    
80. alter system dump logfile '&log_file' scn min &scn_1 scn max &scn_2;
    
81. 
    
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);
    
83. 
    
84. alter system dump logfile '&log_file' scn min &scn_2 scn max &scn_3;
    
85. 
    
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);
    
87. 
    
88. alter system dump logfile '&log_file' scn min &scn_3 scn max &scn_4;
    
89. 
    
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. -- |----------------------------------------------------------------------------|
    
92. 
    
93. col trace_file_name for a100
    
94. 
    
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. -- |----------------------------------------------------------------------------|
     
108. 
     
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)
    
16. 
    
17. 
    
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博客 ” ，链接：http://blog.itpub.net/21980353/viewspace-1797547/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21980353/viewspace-1797547/