谈谈Oracle undo表空间(三)
442 www.2cto.com
KCRA: start recovery claims for 9 data blocks
*** 2012-09-25 13:19:01.499
KCRA: blocks processed = 9/9, claimed = 9, eliminated = 0
*** 2012-09-25 13:19:01.499
Recovery of Online Redo Log: Thread 1 Group 6 Seq 613 Reading mem 0
----- Recovery Hash Table Statistics ---------
Hash table buckets = 32768
Longest hash chain = 1
Average hash chain = 9/9 = 1.0
Max compares per lookup = 1
Avg compares per lookup = 21/21 = 1.0
----------------------------------------------
kwqmnich: current time:: 5: 19: 6
kwqmnich: instance no 0 check_only flag 1
kwqmnich: initialized job cache structure
*** 2012-09-25 13:21:45.194
Start dump data blocks tsn: 10 file#: 8 minblk 16 maxblk 16
buffer tsn: 10 rdba: 0x02000010 (8/16)
scn: 0x0000.01175499 seq: 0x01 flg: 0x00 tail: 0x54990601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0DCC2400 to 0x0DCC4400
... www.2cto.com
Block header dump: 0x02000010
Object id on Block Y
seg/obj: 0x1116e csc: 0x00.1175499 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x2000009 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0004.005.00000c41 0x00800c4a.0692.13 ---- 1 fsc 0x0001.00000000
0x02 0x0003.01b.00000cfe 0x008006d1.072a.04 C--- 0 scn 0x0000.01151109
data_block_dump,data header at 0xdcc2464
=============== www.2cto.com
tsiz: 0x1f98
hsiz: 0x18
pbl: 0x0dcc2464
bdba: 0x02000010
76543210
flag=--------
ntab=1
nrow=3
frre=-1
fsbo=0x18
f
seo=0x1f5c
avsp=0x1f64
tosp=0x1f65
0xe:pti[0] nrow=3 offs=0
0x12:pri[0] offs=0x1f5c
0x14:pri[1] offs=0x1f88
block_row_dump: www.2cto.com
tab 0, row 0, @0x1f5c
tl: 8 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 02
col 1: [ 1] 61
tab 0, row 1, @0x1f88
tl: 8 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 03
col 1: [ 1] 62
tab 0, row 2, @0x1f80
tl: 8 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 04
col 1: [ 1] 63
end_of_block_dump
End dump data blocks tsn: 10 file#: 8 minblk 16 maxblk 16
这个dump文件开头对数据库的环境做了一些描述,中间是一些16进制的内容,最后面是事务和行的一些信息,任何一个事务想修改数据块,都必需要获取一个Itl:
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0004.005.00000c41 0x00800c4a.0692.13 ---- 1 fsc 0x0001.00000000
0x02 0x0003.01b.00000cfe 0x008006d1.072a.04 C--- 0 scn 0x0000.01151109
看上面的事务的信息,查看Flag,4个'-'代表有一个事务正在修改数据块,Lck代表当前锁定了一条数据,Itl=0x01,其实对应的就是下面的:
tab 0, row 0, @0x1f5c
tl: 8 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 02
col 1: [ 1] 61 www.2cto.com
当为0x1状态时,表明该条数据已经被锁定,加了TX锁,其他事务想访问它的时候会被阻塞..
(7)从这个时候Oracle数据块的强大开始体现出来,如果是其他数据块(如sqlserver)的话,那么就会等待,而Oracle的一致性读很牛逼的解决了这个问题,它不让阻塞,而是让其他session去undo段里读,具体的undo地址就是Uba(undo block address)所指示的地址:0x00800c4a.0692.13
我们对这个地址进行转换,查询它具体是哪个文件的哪个块,首先将16进制转换为10进制,再用相应的工具包进行转换查询:
SQL> select to_number('00800c4a','xxxxxxxx') from dual;
TO_NUMBER('0080B673','XXXXXXXX')
--------------------------------
8391754
SQL> select dbms_utility.data_block_address_file(8391754) as file#,
2 dbms_utility.data_block_address_block(8391754) as block#
3 from dual;
FILE# BLOCK#
--------