梦想有多远

 

BBED> show

        FILE#           4

        BLOCK#          29

        OFFSET          0

        DBA             0×0100001d (16777245 4,29)

        FILENAME        /u02/boson/users01.dbf

        BIFILE          bifile.bbd

        LISTFILE        /home/oracle/file_nls.log

        BLOCKSIZE       8192

        MODE            Edit

        EDIT            Unrecoverable

        IBASE           Dec

        OBASE           Dec

        WIDTH           80

        COUNT           512

        LOGFILE         log.bbd

        SPOOL           No

 

 

 

BBED> p kcbh;(块头，总共20字节）

struct kcbh, 20 bytes                       @0      

   ub1 type_kcbh                            @0        0×06 trans data

   ub1 frmt_kcbh                            @1        0xa2 (8K的块，REDO是0×22)

   ub1 spare1_kcbh                          @2        0×00

   ub1 spare2_kcbh                          @3        0×00   (以上两个已经不在使用）

   ub4 rdba_kcbh                            @4        0×0100001d (0×0100001d (4/29),文件4块29）

   ub4 bas_kcbh                             @8        0×000457fe （BASE OF SCN)

   ub2 wrp_kcbh                             @12       0×0000      (WRAP OF SCN)

   ub1 seq_kcbh                             @14       0×01        （sequence # of changes at same scn */

   ub1 flg_kcbh                             @15       0×04 (KCBHFCKV)

   ub2 chkval_kcbh                          @16       0×0d7d

   ub2 spare3_kcbh                          @18       0×0000

flg_kcbh:

define KCBHFNEW 0×01 /* new block - zeroed data area */

#define KCBHFDLC 0×02 /* Delayed Logging Change advance SCN/seq */

#define KCBHFCKV 0×04 /* ChecK Value saved-block xor’s to zero */

#define KCBHFTMP 0×08 /* Temporary block */

这是一个可以组合的值 也就是说有为 6 的时候是 2,4 两种情况的组合

 

 

这个是逻辑DUMP的结果，可以和上面对下

 

Start dump data blocks tsn: 4 file#: 4 minblk 29 maxblk 29

buffer tsn: 4 rdba: 0×0100001d (4/29)

scn: 0×0000.000457fe seq: 0×01 flg: 0×00 tail: 0×57fe0601

frmt:

 

0×02 chkval: 0×0000 type: 0×06=trans data

Hex dump of block: st=0, typ_found=1

 

 

BBED> p  ktbbh （事务头）

struct ktbbh, 72 bytes                      @20     

   ub1 ktbbhtyp                             @20       0×01 (KDDBTDATA) 数据类型（1为DATA 2为索引）

   union ktbbhsid, 4 bytes                  @24        （段或者对象的ID，也就是下边的0×00002de6转换为10进制就是

      ub4 ktbbhsg1                          @24       0×00002de6

      ub4 ktbbhod1                          @24       0×00002de6 

（SQL> c/dua/dual

  1* select to_number(’00002de6′,’xxxxxxxxxx’) from dual

SQL> /

 

TO_NUMBER(’00002DE6′,’XXXXXXXXXX’)

———————————-

                             11750

 

SQL> select object_name,object_id from dba_objects where object_name=’HAOCP’;

 

OBJECT_NAME

——————————————————————————–

 OBJECT_ID

———-

HAOCP

     11750

 

 

  

struct ktbbhcsc, 8 bytes                 @28    （最后一次块清除的SCN） 

      ub4 kscnbas                           @28       0×0002bdb1 （也就是对应下边的csc)

      ub2 kscnwrp                           @32       0×0000

   b2 ktbbhict                              @36       2      (事务槽的个数）和下边的ITC对应

   ub1 ktbbhflg                             @38       0×32 (NONE)（

   ub1 ktbbhfsl                             @39       0×00     事务TX freelist

   ub4 ktbbhfnx                             @40       0×01000019 (freelist上下一个块的DBA地址）和ddba对应

  

下面是两个事务槽的结构

struct ktbbhitl[0], 24 bytes             @44     

      struct ktbitxid, 8 bytes              @44     

         ub2 kxidusn                        @44       0×0002 这是USN

         ub2 kxidslt                        @46       0×002f 

         ub4 kxidsqn                        @48       0×00000071

以上三部分分别对应下面xid 0×0002.02f.00000071

 

 

SQL> select XIDUSN, XIDSLOT,XIDSQN from v$transaction;

 

    XIDUSN    XIDSLOT     XIDSQN

———- ———- ———-

        10         21        117

 

      struct ktbituba, 8 bytes              @52     

         ub4 kubadba                        @52       0×00800f73

         ub2 kubaseq                        @56       0×002c

         ub1 kubarec                        @58       0×33

      ub2 ktbitflg                          @60       0×2001 (KTBFUPB)

      union _ktbitun, 2 bytes               @62     

         b2 _ktbitfsc                       @62       0

         ub2 _ktbitwrp                      @62       0×0000

      ub4 ktbitbas                          @64       0×0002bdb5

第二个事务槽 通上和下面的对应下就看的出来了

 

 struct ktbbhitl[1], 24 bytes             @68     

      struct ktbitxid, 8 bytes              @68     

         ub2 kxidusn                        @68       0×000a

         ub2 kxidslt                        @70       0×0015

         ub4 kxidsqn                        @72       0×00000075

      struct ktbituba, 8 bytes              @76     

         ub4 kubadba                        @76       0×008011f9

         ub2 kubaseq                        @80       0×002c

         ub1 kubarec                        @82       0×32

      ub2 ktbitflg                          @84       0×0001 (NONE)

      union _ktbitun, 2 bytes               @86     

         b2 _ktbitfsc                       @86       0

         ub2 _ktbitwrp                      @86       0×0000

      ub4 ktbitbas                          @88       0×00000000

 

这个是逻辑DUMP，可以和上面对下

 

 Object id on Block? Y

 seg/obj: 0×2de6  csc: 0×00.2bdb1  itc: 2  flg: E  typ: 1 - DATA

     brn: 0  bdba: 0×1000019 ver: 0×01 opc: 0

     inc: 0  exflg: 0

 

 

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

 

0×01   0×0002.02f.00000071  0×00800f73.002c.33  –U-    1  fsc 0×0000.0002bdb5

 

0×02   0×000a.015.00000075  0×008011f9.002c.32  —-    1  fsc 0×0000.00000000

 

flag

C = Committed;

U = Commit Upper Bound;

T = Active at CSC;

B = Rollback of this UBA gives before image of the ITL. —- = transaction is active, or committed pending cleanout 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 3 nibbles The number of row-level locks held in the block by this transaction.

 

 

 

数据头部分

 

BBED> p kdbh

struct kdbh, 14 bytes                       @100    

   ub1 kdbhflag                             @100      0×00 (NONE)

   b1 kdbhntab                              @101      1 （次块表的个数 1

   b2 kdbhnrow                              @102      2  （2 行）

   sb2 kdbhfrre                             @104     -1   （

   sb2 kdbhfsbo                             @106      22 （开始的OFFSET 

   sb2 kdbhfseo                             @108      8078(最后的OFFSET)

   b2 kdbhavsp                              @110      8048(块中公共可以用的空间，也就是8048字节）

   b2 kdbhtosp                              @112      8048

表的目录entry

 

BBED> p kdbt  

struct kdbt[0], 4 bytes                     @114    

   b2 kdbtoffs                              @114      0

   b2 kdbtnrow                              @116      2

行的目录entry

 

BBED> p kdbr

sb2 kdbr[0]                                 @118      8083

sb2 kdbr[1]                                 @120      8078

 

 

 

 

 

综上所述

 

以下部分属于图中的common and variable header

kcbh

ktdbh

ktbbhcsc

 

 

kdbh

这个是数据和空间空间部分

 

 

 

表目录

kdbt

行目录

kdbr

 

BBED> x/2rcnn

kdbr[0]                                     @118    

——-

flag@118:  0×93 (KDRHFN, KDRHFP, KDRHFD, KDRHFK)

lock@119:  0×1f

cols@120:     0

 

 

kdbr[1]                                     @120    

——-

flag@120:  0×8e (KDRHFP, KDRHFL, KDRHFF, KDRHFK)

lock@121:  0×1f

cols@122:     0

http://www.sosdb.com/article/datafile_block.htm

Offset	0	1	2	3	4	5	6	7	8	9	a	b	c	d	e	f
00014000	06	A2	00	00	0A	00	40	01	0E	89	43	00	00	00	05	02
	type	frmt	spare1/2_kcbh		rdba				scn						seq	flg
1 : 20 bytes type: 0×06=trans data defined in kcb.h frmt: 8i~9i 都是0×02 10.1.0 2k: 0×62 4k:0×82 8k:0xa2 16k:0xc2 (logfile 0×22 512 bytes) spare1/2_kcbh: ub1 spare1_kcbh this field is no longer used (old inc#, now always 0) ub1 spare2_kcbh this field is no longer used (old ts#, now always 0) rdba: 0×0140000a 转换成2进制后它的前10 bit 表示file id 后22 bit 表示的block id 可以看出一个tablespace 可以有1023 个datafile ,每个datafile可以有4M 的block 10G 出现的 big datafile 这里表示的就是block id了 没有file id 9.2.0试验过一个tablespace可以有1023个datafile 一个object可以存放在1023个datafile中 scn: scn: 0×0000.0043890e seq: A sequence number incremented for each change to a block at the same SCN A new SCN is allocated if the sequence number wraps. 同一个SCN影响这个block中的行数大于 254 行就会为这个事务分配一个新的SCN 如下面的操作就可能引起同一个SCN但影响的同一个block 中的行超过254行 “delete from table_name” 影响的行数(最大254) 是用从 0×01 到 0xfe 表示的 当这个byte 的数据为 0xff 的时候标志这个 block 坏调了—> ora-01578 Sequence number: SEQ -> 0 /* non-logged changes - do not advance seq# */ SEQ -> (UB1MAXVAL-1)/* maximum possible sequence number */ SEQ -> (UB1MAXVAL) /* seq# to indicate a block is corrupt,equal to FF. soft corrupt*/ 0xff : When present it indicates that the block has been marked as corrupt by Oracle. either by the db_block_checking functionality or the equivalent events (10210 for data blocks, 10211 for index blocks, and 10212 for cluster blocks) when making a database change, or by the DBMS_REPAIR.FIX_CORRUPT_BLOCKS procedure, or by PMON after an unsuccessful online block recovery attempt while recovering a failed process, or by RMAN during a BACKUP, COPY or VALIDATE command with the CHECK LOGICAL option. Logical corruptions are normally due to either recovery through a NOLOGGING operation, or an Oracle software bug. flg: as defined in kcbh.h #define KCBHFNEW 0×01 /* new block - zeroed data area */ #define KCBHFDLC 0×02 /* Delayed Logging Change advance SCN/seq */ #define KCBHFCKV 0×04 /* ChecK Value saved-block xor’s to zero */ #define KCBHFTMP 0×08 /* Temporary block */ 这是一个可以组合的值 也就是说有为 6 的时候是 2,4 两种情况的组合 Block structure as defined in kcbh.h: struct kcbh {ub1 type_kcbh; /* Block type* / ub1 frmt_kcbh; /* #define KCBH_FRMT8 2 */ ub1 spare1_kcbh; ub1 spare2_kcbh; krdba rdba_kcbh; /* relative DBA / ub4 bas_kcbh; /* base of SCN */ ub2 wrp_kcbh; /* wrap of SCN */ ub1 seq_kcbh; /* sequence # of changes at same scn */ ub1 flg_kcbh; ub2 chkval_kcbh; };
00014010	00	00	00	00	01	00	17	00	54	D2	00	00	0A	89	43	00
	chkval		spare3_kcbh		typ		?		seg/obj				csc
spare3_kcbh : ub2 spare3_kcbh 2 : 24 bytes (总计44bytes) typ : 1 - DATA 2 index 改成3了在10.1.0 上引起了ora-600[2032]然后ORA-27101: shared memory realm does not exist oracle进行查询的时候是根据 obj$表中的情况来判断对象的类型的,不是根据这个typ 也就是说如果有一个表但改变表中block的这个标志位，一样可以查询出数据来, 但dump block 时会出错,ORA-00600: 内部错误代码，自变量: [4555], [0], [], [], [], [], [], [] 错误中的 [0] 就是typ对应的数据 在10G中改变它后update这个block的数据commit可以但rollback的报错 ? 见过有其他值 但用编辑器改这个值 在 dump 文件中显示不出来变化 seg/obj: 0xd254 csc : 0×00.43890a The SCN at which the last full cleanout was performed on the block
00014020	00	00	E8	1F	02	00	03	00	00	00	00	00	04	00	0C	00
	csc		?		itc	?	flg	fsl	fnx				xid
3 : 24 bytes * itl (2个itl总计92bytes) ? 见过有其他值 但用编辑器改这个值 在 dump 文件中显示不出来变化 itc ITL 条目的个数 max 255超过会报ORA-02207 ORA-00060 ORA-00054 可能是没空间分配itl条目了或它的争用引起的 在8i中 INITRANS default为1 , 9.2.0中 INITRANS default为2 flg indicates that the block is on a freelist. Otherwise the flag is - 9i 的ASSM 的情况下这个值为 E ixora 上说他占用 2 bytes 但我下面的试验和他的结果有一定的出入 我观察到的情况是 : Object id on Block? Y flg: O ver: 0×01 上面的3项是用同一个 byte 来表示的 flg: O ver: 0×01 Object id on Block? Y 从我的观察中 dump 出来的文件中 flg ver Object id on Block 他们共同占用的这个一个字节 他的规律可以从下面的情况看出 2进制数据 flg ver Object id on Block? 0×00 - 0×00 N 0×01 0 0×00 N 0×02 - 0×01 Y 0×03 0 0×01 Y 0×04 - 0×02 Y 0×05 0 0×02 Y 0×06 - 0×03 Y 0×07 0 0×03 Y 0×08 - 0×04 N 0×09 0 0×04 N 0×0a - 0×05 Y 0×0b 0 0×05 Y 0×0c - 0×06 Y 0×0d 0 0×06 Y 0×0e - 0×07 Y 0×0f 0 0×07 Y 0×10 … 类似上面的循环了 这种情况在9i上已经改变因为ASSM的出现 fsl : Index to the first slot on the ITL freelist. ITL TX freelist slot fnx : 自由列表中下一块的地址 Null if this block is not on a freelist 有数据例如: fnx: 0×1000029
00014030	50	18	00	00	96	14	80	00	B9	07	01	00	01	20	00	00
	xid				uba								Lck Flag		Scn/Fsc
xid : Transaction ID (UndoSeg.Slot.Wrap) 值可以用select XIDUSN, XIDSLOT,XIDSQN from v$transaction;查到 This is comprised of the rollback segment number (2 bytes), the slot number in the transaction table of that rollback segment (2 bytes), and the number of times use of that transaction table has wrapped (4 bytes). uba : Undo address (UndoDBA.SeqNo.RecordNo) The location of the undo for the most recent change to this block by this transaction. This is comprised of the DBA of the rollback segment block (4 bytes), the sequence number (2 bytes), and the record number for the change in that undo block (1 byte), plus 1 unused byte. Lck Flag: Lck 锁定的row数 这里还用到了下一个 byte 的数据 2 对应的二进制表示为 0010 正好和dump文件中的 –U- 吻合 flag 1 nibble C = Committed; U = Commit Upper Bound; T = Active at CSC; B = Rollback of this UBA gives before image of the ITL. —- = transaction is active, or committed pending cleanout 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 3 nibbles The number of row-level locks held in the block by this transaction. Scn/Fsc : If the transaction has been cleaned out, this is the commit SCN or an upper bound thereof. Otherwise the leading two bytes contain the free space credit for the transaction - that is, the number of bytes freed in the block by the transaction Scn = SCN of commited TX; Fsc = Free space credit (bytes)
00014040	0E	89	43	00	00	00	00	00	00	00	00	00	00	00	00	00
	Scn/Fsc				第2条itl 这里没使用
00014050	00	00	00	00	00	00	00	00	00	00	00	00	00	01	01	00
	第2条itl 这里没使用												flag	ntab	nrow
4 : 14 bytes 从这个flag位置开始是data区 也是下面的行的offset的起始地址 flag : N=pctfree hit(clusters), F=don’t put on free list K=flushable cluster keys. 当然还有别的标记: A … ntab : 这block中有几个table的数据 cluster这个就可能大于1 nrow : block 有多少行数据
00014060	FF	FF	14	00	9B	1F	83	1F	83	1F	00	00	01	00	9B	1F
	frre		fsbo		fseo		avsp		tosp		offs		nrow		row offs
frre : First free row index entry. -1=you have to add one. fsbo : Free Space Begin offset 出去row dict 后面的可以放数据的空间的起始位置 也可以看成是从这个区域的开始”flag”到最后一个 “row offs”占用的空间 fseo : Free Space End offset ( 9.2.0 )参与db_block_checking的计算剩余空间 select 的时候oracle不是简单的根据offset定位row.这个值也是参与了定位row的 avsp : Available space in the block (pctfree and pctused) ORA-01578 tosp : Total available space when all TXs commit ( 9.2.0 )参与db_block_checking offs : 偏移量 用 cluster 的时候可以看出值 nrow : 这个table有多少行数据 row offs : 这行数据相对的起始位置 after delete & commit is 0xffff
00015FF0	00	00	00	00	00	00	00	2C	01	01	01	61	05	06	0E	89
								fb	lb	cc	length	data	block tail
5 : 用户数据 6 : 4 bytes block tail fb : K = Cluster Key (Flags may change meaning if this is set to show HASH cluster) C = Cluster table member H = Head piece of row D = Deleted row F = First data piece L = Last data piece P = First column continues from previous piece N = Last column continues in next piece lb : 和上面的 ITL 的lck相对应 表示这行是否被 lock 了 cc : 有几列数据 这里只能表示255列 超过了就会有链接行 length : 这列的数据的长度是多少 0xfa ( 250 bytes ) 其实0xfb,0xfc,0xfd 也同样是250bytes 0xfe fb 00 ( 0xfb 00 表示的251 bytes 0xfe表示row的长度超过了250 bytes) 0xff 表示number 的 null 这也是oracle中null的表现形式排序的时候null最大了 字段的数据超过250字节是就用3bytes来表示字段的长度,因为如果是long类型它的字段再长 它在这个block中的数据的长度不会超过64K 所以最长用3bytes来表示行的长度已经够了.再长就链接行了 data : ‘a’ block tail : 改这 block 最后的4 bytes 数据中的任意肯定ora-1578 第 1 byte : 对应开始的 seq 第 2 byte : 对应开始的 type 第3,4byte : 对应开始的scn的末2为 control file 这里是control seq

This entry was posted on 星期天, 一月 4th, 2009 at 12:28 and is filed under oracle. You can follow any responses to this entry through the RSS 2.0 feed. You can skip to the end and leave a response. Pinging is currently not allowed.