oracle数据块转储说明

  • selectshen

    2016-02-19 12:19:33
  • Oracle
  • 原创

#环境:
os:centos 6.6 x64
db version:11.2.0.4.0

#建测试表
[oracle@ct6605 ~]$ ORACLE_SID=ct66
[oracle@ct6605 ~]$ sqlplus / as sysdba
SQL> create table scott.tb_block
as
select 'selectshen' a,123 b from dual
union all select 'shengjie' a,234 b from dual;

#查看测试表的数据,rowid,object_id,relative_fno,block_number,row_number
SQL> select t.*,
       rowid,
       dbms_rowid.rowid_object(rowid) object_id,
       dbms_rowid.rowid_relative_fno(rowid) relative_fno,
       dbms_rowid.rowid_block_number(rowid) block_number,
       dbms_rowid.rowid_row_number(rowid) row_number
from scott.tb_block t;
/*
A            B    ROWID                OBJECT_ID    RELATIVE_FNO    BLOCK_NUMBER    ROW_NUMBER
selectshen    123    AAAVxaAAEAAABVDAAA    89178        4                5443            0
shengjie    234    AAAVxaAAEAAABVDAAB    89178        4                5443            1
*/

#查看测试表的数据,rowid,dump值
SQL> select t.*,
       rowid,
       dump(rowid),
       dump(rowid,16)
from scott.tb_block t;
/*
A            B    ROWID                DUMP(ROWID)                                DUMP(ROWID,16)
selectshen    123    AAAVxaAAEAAABVDAAA    Typ=69 Len=10: 0,1,92,90,1,0,21,67,0,0    Typ=69 Len=10: 0,1,5c,5a,1,0,15,43,0,0
shengjie    234    AAAVxaAAEAAABVDAAB    Typ=69 Len=10: 0,1,92,90,1,0,21,67,0,1    Typ=69 Len=10: 0,1,5c,5a,1,0,15,43,0,1
*/

#转储数据块
SQL> alter system dump datafile 4 block 5443;

#查看数据块的转储内容
[oracle@ct6605 ~]$ cd /u01/app/oracle/diag/rdbms/ct66/ct66/trace
[oracle@ct6605 trace]$ vi ct66_ora_6238.trc
#以下是转储出来数据块的内容
Start dump data blocks tsn: 4 file#:4 minblk 5443 maxblk 5443
Block dump from cache:
Dump of buffer cache at level 4 for tsn=4 rdba=16782659
BH (0x72fe04a0) file#: 4 rdba: 0x01001543 (4/5443) class: 1 ba: 0x72d10000
  set: 3 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,25
  dbwrid: 0 obj: 89178 objn: 89178 tsn: 4 afn: 4 hint: f
  hash: [0x76fe2778,0x8a241350] lru: [0x73bd8328,0x77bed4c0]
 dbwrid: 0 obj: 89178 objn: 89178 tsn: 4 afn: 4 hint: f
  hash: [0x76fe2778,0x8a241350] lru: [0x73bd8328,0x77bed4c0]
  ckptq: [NULL] fileq: [NULL] objq: [0x773daa50,0x86aaa498] objaq: [0x773e1a80,0x86aaa488]
  st: XCURRENT md: NULL fpin: 'kdswh11: kdst_fetch' tch: 1
  flags: only_sequential_access
  LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [65535]  
Block dump from disk:
buffer tsn: 4 rdba: 0x01001543 (4/5443)
scn: 0x0000.001ce6b1 seq: 0x02 flg: 0x04 tail: 0xe6b10602
frmt: 0x02 chkval: 0x2079 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F38E710FA00 to 0x00007F38E7111A00
7F38E710FA00 0000A206 01001543 001CE6B1 04020000  [....C...........]
7F38E710FA10 00002079 00000001 00015C5A 001CE6AF  [y ......Z\......]
7F38E710FA20 00000000 00320003 01001540 0000FFFF  [......2.@.......]
7F38E710FA30 00000000 00000000 00000000 00008000  [................]
7F38E710FA40 001CE6AF 00000000 00000000 00000000  [................]
7F38E710FA50 00000000 00000000 00000000 00000000  [................]
        Repeat 1 times
7F38E710FA70 00000000 00000000 00000000 00020100  [................]
7F38E710FA80 0016FFFF 1F481F5E 00001F48 1F6E0002  [....^.H.H.....n.]
7F38E710FA90 00001F5E 00000000 00000000 00000000  [^...............]
7F38E710FAA0 00000000 00000000 00000000 00000000  [................]
        Repeat 498 times
7F38E71119D0 00000000 00000000 002C0000 68730802  [..........,...sh]
7F38E71119E0 6A676E65 C2036569 002C2303 65730A02  [engjie...#,...se]
7F38E71119F0 7463656C 6E656873 1802C203 E6B10602  [lectshen........]

Block header dump:  0x01001543
 Object id on Block? Y
 seg/obj: 0x15c5a  csc: 0x00.1ce6af  itc: 3  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x1001540 ver: 0x01 opc: 0
     inc: 0  exflg: 0

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0xffff.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.001ce6af
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x03   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
bdba: 0x01001543
data_block_dump,data header at 0x7f38e710fa7c
===============
tsiz: 0x1f80
hsiz: 0x16
pbl: 0x7f38e710fa7c
     76543210
flag=--------
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1f5e
avsp=0x1f48
tosp=0x1f48
0xe:pti[0]      nrow=2  offs=0
0x12:pri[0]     offs=0x1f6e
0x14:pri[1]     offs=0x1f5e
block_row_dump:
tab 0, row 0, @0x1f6e
tl: 18 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [10]  73 65 6c 65 63 74 73 68 65 6e
col  1: [ 3]  c2 02 18
tab 0, row 1, @0x1f5e
tl: 16 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 8]  73 68 65 6e 67 6a 69 65
col  1: [ 3]  c2 03 23
end_of_block_dump
End dump data blocks tsn: 4 file#: 4 minblk 5443 maxblk 5443

#解释:
Start dump data blocks tsn: 4 file#:4 minblk 5443 maxblk 5443
dump表空间4,数据文件号4,数据块号5443

#数据块头
Block dump from cache:
Dump of buffer cache at level 4 for tsn=4 rdba=16782659
BH (0x72fe04a0) file#: 4 rdba: 0x01001543 (4/5443) class: 1 ba: 0x72d10000
  set: 3 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,25
  dbwrid: 0 obj: 89178 objn: 89178 tsn: 4 afn: 4 hint: f
  hash: [0x76fe2778,0x8a241350] lru: [0x73bd8328,0x77bed4c0]
 dbwrid: 0 obj: 89178 objn: 89178 tsn: 4 afn: 4 hint: f
  hash: [0x76fe2778,0x8a241350] lru: [0x73bd8328,0x77bed4c0]
  ckptq: [NULL] fileq: [NULL] objq: [0x773daa50,0x86aaa498] objaq: [0x773e1a80,0x86aaa488]
  st: XCURRENT md: NULL fpin: 'kdswh11: kdst_fetch' tch: 1
  flags: only_sequential_access
  LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [65535]
Block dump from disk:
buffer tsn: 4 rdba: 0x01001543 (4/5443)
scn: 0x0000.001ce6b1 seq: 0x02 flg: 0x04 tail: 0xe6b10602
frmt: 0x02 chkval: 0x2079 type: 0x06=trans data
tsn:4是表空间号
rdba:0x01001543是数据文件号块号,它是rowid的第7到第15个64进制字符AAEAAABVD的16进制形式,在dump(rowid,16)中的第5位到第8位中也可以看出,可参考:http://blog.itpub.net/28539951/viewspace-1986647/
scn: 0x0000.001ce6b1是数据块头的scn,16进制,前4位占2个字节,是scn wrap,后8位占4个字节,是scp base.
seq: 0x02是顺序号,incremented for every change made to the block at the same SCN
flg: 0x04
    0x01 New block
    0x02 Delayed Logging Change advanced SCN/seq
    0x04 Check value/saved - block XOR‘s to zero
    0x08 Temporary block
tail: 0xe6b10602是用于校验数据块的一致性,存放在最据块的最后4个字节,tail=SCN Base的低2个字节+type+seq=e6b1+06+02
frmt: 0x02 是块格式
    0x01:oracle 7
    0x02:oracle 8+
chkval: 0x2079是块的检查值,用于对比检查数据块是否为坏块
type: 0x06=trans data
    0x01  Undo segment header
    0x02  Undo data block
    0x03  Save undo header
    0x04  Save undo data block
    0x05  Data segment header (temp, index, data and so on)
    0x06  KTB managed data block (with ITL)
    0x07  Temp table data block (no ITL)
    0x08  Sort Key
    0x09  Sort Run
    0x10  Segment free list block
    0x11  Data file header  
    

#数据区
Dump of memory from 0x00007F38E710FA00 to 0x00007F38E7111A00
7F38E710FA00 0000A206 01001543 001CE6B1 04020000  [....C...........]
7F38E710FA10 00002079 00000001 00015C5A 001CE6AF  [y ......Z\......]
7F38E710FA20 00000000 00320003 01001540 0000FFFF  [......2.@.......]
7F38E710FA30 00000000 00000000 00000000 00008000  [................]
7F38E710FA40 001CE6AF 00000000 00000000 00000000  [................]
7F38E710FA50 00000000 00000000 00000000 00000000  [................]
        Repeat 1 times
7F38E710FA70 00000000 00000000 00000000 00020100  [................]
7F38E710FA80 0016FFFF 1F481F5E 00001F48 1F6E0002  [....^.H.H.....n.]
7F38E710FA90 00001F5E 00000000 00000000 00000000  [^...............]
7F38E710FAA0 00000000 00000000 00000000 00000000  [................]
        Repeat 498 times
7F38E71119D0 00000000 00000000 002C0000 68730802  [..........,...sh]
7F38E71119E0 6A676E65 C2036569 002C2303 65730A02  [engjie...#,...se]
7F38E71119F0 7463656C 6E656873 1802C203 E6B10602  [lectshen........]

#事务区
Block header dump:  0x01001543
 Object id on Block? Y
 seg/obj: 0x15c5a  csc: 0x00.1ce6af  itc: 3  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x1001540 ver: 0x01 opc: 0
     inc: 0  exflg: 0

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0xffff.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.001ce6af
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x03   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
bdba: 0x01001543
Object id on Block? Y是表示是否是对象的块
0x01001543是数据文件号块号
seg/obj: 0x15c5a是对象id 89178,它是rowid的前6个64进制字符AAAVxa的16进制形式,在dump(rowid,16)中的前4位中也可以看出,可参考:http://blog.itpub.net/28539951/viewspace-1986647/
csc: 0x00.1ce6af是The cleanout SCN that is used during read consistency
itc: 3是块中itl slot的数量
flg: E
    E是使用ASSM
    O是使用free list
typ: 1 - DATA 1是数据,2是索引
brn: 0
bdba: 0x1001540是Block relative data block address
ver: 0x01
opc: 0
inc: 0
exflg: 0
Itl是块上相关事务列表interested transaction list
Xid是事务id.Xid=Undo Segment Number XIDUSN+Transaction Table Slot Number XIDSLOT+ Wrap
Uba是该事务对应的回滚段地址.Uba=回滚块地址(undo文件号UBAFIL和数据块号UBABLK)+回滚序列号UBASQN+回滚记录号UBAREC
Flag是事务标志位
    C = transaction has been committed and locks cleaned out
    B = this undo record contains the undo for this ITL entry
    U = transaction committed (maybe long ago); SCN is an upper bound
    T  = transaction was still active at block cleanout SCN
Lck是这个事务影响的行数
Scn/Fsc是scn或者free space credit

#尾区
data_block_dump,data header at 0x7f38e710fa7c
===============
tsiz: 0x1f80
hsiz: 0x16
pbl: 0x7f38e710fa7c
     76543210
flag=--------
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1f5e
avsp=0x1f48
tosp=0x1f48
0xe:pti[0]      nrow=2  offs=0
0x12:pri[0]     offs=0x1f6e
0x14:pri[1]     offs=0x1f5e
tsiz: 0x1f80是数据区的大小Total Data Area Size
hsiz: 0x16是数据块头大小Data Header Size
pbl: 0x7f38e710fa7c是数据块地址
     76543210
flag=--------
ntab=1是The number of table index entries contained in this block(>1 is a cluster)
nrow=2是The number of row index entries in this block
frre=-1是first free row index entry, -1=you have to add one(没有创建索引)
fsbo=0x16是空闲空间起始位置free space begin offset
fseo=0x1f5e是空闲空间结束位置free space end offset
avsp=0x1f48是可用空间available space for new entries
tosp=0x1f48是total available space when all txs commit
0xe:pti[0]      nrow=2  offs=0是Table directory (The offset indicates where the row directory starts;in this case immediately, the offset is “0.”)块中数据记录数
0x12:pri[0]     offs=0x1f6e是Row index (Offset is where the row header for the row begins.)第1条记录在偏移量为0x1f6e的地方
0x14:pri[1]     offs=0x1f5e是Row index 第2条记录在偏移量为0x1f5e的地方

block_row_dump:
tab 0, row 0, @0x1f6e
tl: 18 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [10]  73 65 6c 65 63 74 73 68 65 6e
col  1: [ 3]  c2 02 18
tab 0, row 1, @0x1f5e
tl: 16 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 8]  73 68 65 6e 67 6a 69 65
col  1: [ 3]  c2 03 23
end_of_block_dump
tab 0, row 0, @0x1f6e是第1条记录在偏移量
tl: 18是Row Size(number of bytes plus data)
fb: --H-FL--是Flag Byte
    K- Cluster key
    H- head of row piece
    D- Deleted row
    F- first data piece  
    L- last data piece  
    P- First column cintinues from previous row
    N- Last column cintinues in next piece
lb: 0x0是事物在该数据行上的锁是否清除,0x0是已清除,其它值未清除并对关itl号
col  0: [10]  73 65 6c 65 63 74 73 68 65 6e是列号:[长度] 和值'selectshen'的dump
col  1: [ 3]  c2 02 18是列号:[长度] 和值123的dump,可参考http://blog.itpub.net/28539951/viewspace-1986367/


参考文档:http://wenku.baidu.com/view/ebfcad85d4d8d15abf234e06.html


请使用浏览器的分享功能分享到微信等