Shulou(Shulou.com)05/31 Report--

What this article shares with you is about how to analyze the oracle data block format. The editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

Oracle data blocks can be divided into three layers

More refined

Cache layer--20 bytes, including DBA, block type, block format, SCN; block integrity check when read to ensure that there is no corruption or fracture, that is, only part of the block update information is written to disk

Transaction layer

Fixed transaction layer-- block type, last block cleanup time csc,ITL quantity itc

Variable transaction layer-- contains ITL, 24 bytes each

Data layer

Data header (KDBH): 14 bytes, number of table entries, number of row entries, offset to the start and end of free space, current block remaining space

Table catalog-cluster table records more than 1, records the number of rows associated with this table, and the starting position

Line directory-record the starting position of each line, that is, the offset to the head of the line, 2 bytes each; innodb uses entry?

Take 10205 as an example

Create table t (id number (2))

Insert into t values (1)

Insert into t values (2)

Commit

SQL > select FILE_ID,BLOCK_ID,EXTENT_ID,BLOCKS from dba_extents where owner='SYS' and SEGMENT_NAME='T'

FILE_ID BLOCK_ID EXTENT_ID BLOCKS

--

68 2260129 0 8

SQL > select dbms_rowid.rowid_block_number (rowid), id from t

DBMS_ROWID.ROWID_BLOCK_NUMBER (ROWID) ID

2260132 1

2260132 2

2260132 1

Alter system dump datafile 68 block 2260132

Select spid from v$process where addr= (select paddr from v$session where sid= (select sid from v$mystat where rownum=1))

The output format of Dump block may not be consistent with the actual order.

* cache layer**

Buffer tsn: 115 rdba: 0x11227ca4 (68max 2260132)

Scn: 0x0860.07f11dad seq: 0x01 flg: 0x02 tail: 0x1dad0601

Frmt: 0x02 chkval: 0x0000 type: 0x06=trans data

SCN=2 byte base + 4 byte wrap

Seq:sequence number

Flag:0x01-new block;0x02-delayed logging change advance SCN/seq;0x04-check value saved-block xor's to zero;0x08-temporary block

Frmt: block format, 0x02 from 8i to 10205

Chkval: optional, enabled when db_block_checksum=true

Tail: stored at the end of the block footer,SCN base low 2 bytes + block type + SCN seq = 12ca + 06 + 01

Kcbh data structure

Typedef struct kcbh_ {

Ub1 type_kcbh

Ub1 frmt_kcbh

Ub1 spare1_kcbh

Ub1 spare2_kcbh

Krdba rdba_kcbh

Ub4 bas_kcbh; / * base of SCN * /

Ub2 wrp_kcbh; / * wrap of SCN * /

Ub1 seq_kcbh; / * seq# of changes at same scn, KCBH_NLCSEQ * /

Ub1 flg_kcbh; / * see KCBHFNEW etc below * /

Ub2 chkval_kcbh

Ub2 spare3_kcbh

} kcbh

* transaction layer****

-- fixed part

Block header dump: 0x11227ca4

Object id on Block? Y

Seg/obj: 0x5454c csc: 0x860.7f11dac itc: 2 flg: e typ: 1-DATA

Brn: 0 bdba: 0x11227ca1 ver: 0x01 opc: 0

Inc: 0 exflg: 0

Csc: last block cleanup SCN

Typ:1=DATA; 2=INDEX

Typedef struct ktbbh_ {/ * 10201 struct ktbbh block header * /

Ub1 ktbbhtyp; / * block type * /

Ub4 ktbbhsid

Kscn ktbbhcsc; / * effective time of last cleanout * /

B2 ktbbhict; / * number of itl entries mask 0x00ff*/

Ub1 ktbbhflg; / * flags * /

Ub1 ktbbhfsl; / * free space lock * /

Krdba ktbbhfnx; / * next block in free list * /

}

-- variable length, 24 bytes per ITL slot

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0x000a.01d.00017bc3 0x00800330.7d5f.1e-- U3 fsc 0x0000.07f11dad

0x02 0x0000.000.00000000 0x00000000.0000.00-0 fsc 0x0000.00000000

Xid: transaction ID undoseg + slot + wrap

Uba:undodba + seqno + recordNo

Flags:C=Commited; U=Commited Upper Bound; T=Active at CSC

Lck: the number of rows involved in this transaction

Scn: the SCN when submitting the TX

Struct ktbit {

Kxid ktbitxid; / * transaction id * /

Kuba ktbituba; / * undo address for last change * /

B2 ktbitflg; / * num of locks in block * /

Ktbitun_t _ ktbitun

Ub4 ktbitbas; / * sys commit num base * /

}

* data layer**

Tsiz: 0x1f98

Hsiz: 0x18

Pbl: 0x0d0e8664

Bdba: 0x11227ca4

76543210

Flag=-

Ntab=1

Nrow=3

Frre=-1

Fsbo=0x18-the initial offset of free space

Fseo=0x1f86-end offset of free space

Avsp=0x1f65-Total free space

Tosp=0x1f65

Data block structure

Struct kdbh {

Ub1 kdbhflag; / * FLAGs * /

Ktno kdbhntab; / * Number of TABles in the table index * /

Ub2 kdbhnrow; / * Number of ROWs in the row index * /

Sb2 kdbhfrre; / * first FRee Row index Entry * /

Sb2 kdbhfsbo; / * Free Space Beginning Offset * /

Sb2 kdbhfseo; / * Free Space Ending Offset * /

B2 kdbhavsp; / * AVailable SPace in the block * /

B2 kdbhtosp; / * TOtal Space that will be available * /

}

0xe:pti [0] nrow=3 offs=0

Table catalog:

Struct kdbt {

B2 kdbtoffs; / * OFFSet in the block from kdbpri * /

B2 kdbtnrow; / * Number of Rows in the table * /

}

0x12:pri [0] offs=0x1f92

0x14:pri [1] offs=0x1f8c

0x16:pri [2] offs=0x1f86

Line directory: sb2 kdbr [3]

Each row corresponds to a record of 2 bytes. Kdbr is an array of type sb2, pointing to the head of each row.

Block_row_dump:

Tab 0, row 0, @ 0x1f92

Tl: 6 fb:-- HmurFLmuri-lb: 0x1 cc: 1

Col 0: [2] c1 02

Tab 0, row 1, @ 0x1f8c

Tl: 6 fb:-- HmurFLmuri-lb: 0x1 cc: 1

Col 0: [2] c1 03

Tab 0, row 2, @ 0x1f86

Tl: 6 fb:-- HmurFLmuri-lb: 0x1 cc: 1

Col 0: [2] c1 02

The row consists of two parts: the header and the data

Header-line flag + lock flag + number of columns, usually 3 bytes; T1-row size; number of columns in cc- row; lb- lock flag bit, pointing to ITL; fb- flag bit

Data-column length + column data

1 how to locate each line?

The offset of each line is stored by the row directory kdbr, 2 bytes per item

BBED > p kdbr

Sb2 kdbr [0] @ 118 8078

Sb2 kdbr [1] @ 120 8068

BBED > p * kdbr [0]

Rowdata [10]-ub1 rowdata [10]

@ 8178 0x2c

Since the block contains variable-length ITL and row directories, free space is allocated from the end of the block.

2 the value range of row flag

When deleting data, only the row is marked as deleted, and the bitmask of the header row flag is as follows

For ordinary rows (no row link / row migration / deleted / clustered table), the basic flag bit is HFL, that is, 32+8+4=44=0x2c

If the row is deleted, then row flag=44+16=60=0x3c

To restore a deleted line that has not been overwritten, simply change its row flag from 3c to 2c row flag in the first byte of the line

For details, please refer to http://orafaq.com/papers/dissassembling_the_data_block.pdf

3-line lock principle?

Oracle does not provide a data structure for row locks, but is implemented through transaction tables + ITL + lb (row header)

A transaction allocates a TX lock and several TM lock, each of which needs an ITL. The transaction table slot is associated through its XID, and the data row points to the ITL of this block through lb.

4 data block verification?

When a data block is read into memory or written to disk, a consistency check is performed:

1 block version, SCNBase/ block type / seq of the block head is compared with footer

2 comparison of DBA of cache layer with DBA of block buffer

3 block-checksum. Verify if checksum is enabled

Dbv only checks the header/footer of data blocks for logical verification.

Db_block_checking: performs block integrity check instead of 10210 / 10211 / 10212 event, such as free slot list/ line position / number of locks; the block is copied during the check, and the block is marked as soft corruption if there is an error

Db_block_checksum:dbwr and direct loader calculate that checksum coexists in cache layer chkval when writing data blocks, recalculate and compare with existing checksum when reading again

Dbms_repair fixes errors in the cache/transaction layer by marking the block as soft corruption

Event

10231 skip bad blocks during full table scan

10233 Skip index bad block

The above is how to analyze the oracle data block format, and the editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.