Shulou(Shulou.com)06/02 Report--

Detailed physical structure diagram of Oracle block:

This paper mainly describes the physical structure of oracle block, which is the smallest storage unit of oracle and consists of multiple os data blocks. It is mainly composed of three logical layers (the structure described by c language, as shown in figure 1 below): the cache layer,the transaction layer,data layer. If it is further refined, data layer will be divided into many structures, such as table directory,row directory,free space,row data, etc.

The block of Oracle is mapped to the corresponding block of SGA kcbh (kernel cache block header).

The cache layer: it is the first part of block header and takes up 20 bytes. Used to check the correctness of the data, that is, whether the block being read is broken or damaged. It contains the following structure

1. The data block address (DBA)

2. The block type (e.g. Table/Index, Rollback Segment, Temporary)

3. The block format (all 8i~9i are 0x02 10.1.02k: 0x62 4k:0x82 8k:0xa2 16k:0xc2 (logfile 0x22 512 bytes))

4. A system change number (SCN) used for ordering purposes during recovery

The transaction layer: where a user stores transaction information in a data block, which contains two parts of information

1. One is a fixed component, KTBBH (TRANSACTION FIXED HEADER), which contains information about the type of data block, the latest cleanout time of the data block, the number of ITL (Interested Transcation List), links to free lists, and free space lock.

two。 The other is a variable portion,KTBIT (TRANSACTION VARIABLE HEADER), which contains the ITLs that a process needs to edit a line in a block. By default, the number of only one ITL,ITL in a data block containing a table is set by storing the parameter INITRANS. Setting a larger value will reduce the free space of the row data. This parameter can be dynamically modified, but only affects the new block, but has no effect on the existing block (you can use imp/exp,move and other methods to make it work on the existing block).

The data layer: contains the data header structure, KDBH (kernel data block header, is DATA HEADER, occupancy 14bytes), and row data. Where data header contains the number of tables (in the table index, that is, table directory), the number of data rows, the entry of the first free row (in the row index, that is, row directory), the offset to the start and end of the free area, and the available free space. The data rows are insert from the bottom of the block, and along with insert and delete operations, the row data is stored randomly.

As shown in the following figure:

Suppose you initialize 5 rows of data, each row of 10bytes size, sorted by offsets is 5pm 4pm 3pm 2pm 1. Now delete 2 20bytes 4 lines, and then insert a row of 20bytes data. Slot2 is used in row directory, but the actual row data is stored on top of row5. At this time, it is sorted by offsets, that is, 2, 5, 5, 3, and 1. The more frequently you DML on the data block, the more random the row will be.

Here is the design of data block, as shown in the following figure

The following is the dump file of oracle block, combined with the image above to verify the storage of oracle block.

Dump file e:/oracle/product/10.2.0/admin/test/udump/test_ora_4820.trc

Thu Aug 19 13:01:36 2010

ORACLE V10.2.0.4.0-Production vsnsta=0

Vsnsql=14 vsnxtr=3

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0-Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Windows XP Version V5.1 Service Pack 3, v.3300

CPU: 2-type 586,2 Physical Cores

Process Affinity: 0x00000000

Memory (Avail/Total): Ph:313M/1918M, Ph+PgF:2399M/3812M, VA:1289M/2047M

Instance name: test

Redo thread mounted by this instance: 1

Oracle process number: 21

Windows thread id: 4820, image: ORACLE.EXE (SHAD)

* * 2010-08-19 13 Fringe 01purl 36.593

* ACTION NAME: () 2010-08-19 1315 01purl 36.578

* MODULE NAME: (SQL*Plus) 2010-08-19 1314 01purl 36.578

* SERVICE NAME: (test) 2010-08-19 1314 01purl 36.578

* SESSION ID: (201.21830) 2010-08-19 1314 01purl 36.578

Error: alter system dump datafile/tempfile: invalid input file # 0

* * 2010-08-19 1314 02purl 41.375

Error: alter system dump datafile/tempfile: invalid input file # 0

* 2010-08-19 13 Fringe 03purl 17.296

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

Buffer tsn: 4 rdba: 0x010072a3 (4 Accord 29347)

Scn: 0x0000.009b876f seq: 0x01 flg: 0x04 tail: 0x876f2301

Frmt: 0x02 chkval: 0x4671 type: 0x23=PAGETABLE SEGMENT HEADER

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

Dump of memory from 0x0A2A8400 to 0x0A2AA400

A2A8400 0000A223 010072A3 009B876F 04010000 [#.... r..o.]

A2A8410 00004671 00000000 00000000 00000000 [qF.]

A2A8420 00000000 00000001 00000008 00000A9C [.]

A2A8430 00000000 00000004 00000008 010072A5 [.r..]

.. r.

.. r.

A2A9850 00000000 00000000 00000000 [.]

Repeat 185 times

A2AA3F0 00000000 00000000 00000000 876F2301 [. # o.]

It is explained here that TAIL is used to verify the integrity of block, which is composed of SCNBase,block type,SCN seq number.

876F2301=876F (last two bytes of SCN Base) + 23 (type) + 01 (seq)

The red word is offset (offset)

First of all, let's take a look at the data of the first 20 bytes, or the cache layer,16, as follows:

0000A223 010072A3 009B876F 04010000 00004671

The first and second bytes are filler, that is, unused

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)

), undefined (somewhat different from the previous figure)

The third byte is frmt, usually 0x02, and here is 0xa2. You can use the mask 0x0f and operation to retrieve the 0x02 (the mask is to protect sensitive information)

The fourth byte is type, and here is 23, which stands for PAGETABLE SEGMENT HEADER

The fifth to eighth byte is rdba, and here is 0x010072a3

The ninth to twelfth byte is SCNBase, and here is 0x009B876F.

The thirteenth byte is flg, and here is 0x04

(

As defined in kcbh.h

# define KCBHFNEW 0x01 / * new block-zeroed data area * /

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

# define KCBHFCKV 0x04 / * ChecK Value saved-block xor's to zero * /

# define KCBHFTMP 0x08 / * Temporary block * /

This is a value that can be combined, that is to say, when there is a promising 6, it is a combination of 2 and 4.

)

The fourteenth byte seq, here is 0x01.

(A sequence number incremented for each change to a block at the same SCN

A new SCN is allocated if the sequence number wraps.

If the same SCN affects the number of rows in this block, more than 254rows, a new SCN will be assigned to the transaction.

Actions such as the following may cause the same SCN to affect more than 254rows in the same block

"delete from table_name"

The number of rows affected (up to 254) is expressed from 0x01 to 0xfe

When the data of the byte is 0xff, it indicates that the block is out of tune-> 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.)

The fifteenth and sixteenth bytes are SCNWrap, and here is 0x0000

The seventeenth and eighteenth bytes are spare3_kcbh and are not used here

The nineteenth and twenty bytes are checksum, and this is 0x4671.

It is exactly the same as the content of dump

Buffer tsn: 4 rdba: 0x010072a3 (4 Accord 29347)

Scn: 0x0000.009b876f seq: 0x01 flg: 0x04 tail: 0x876f2301

Frmt: 0x02 chkval: 0x4671 type: 0x23=PAGETABLE SEGMENT HEADER

Related instructions:

Relative address of Rdba:block (DBA)

Scn: SCN number

Seq:sequence number incremented for each change made to the block at the same SCN

Flg:flag

Tail: verify the integrity of block by checking whether the beginning and end of block are the same version

Frmt:block format is usually 0x02

Chkval: if db_block_checksum=true, the check value of block

Types of Type:block, such as data,index, etc.

At this point, the pre-20bytes of block has been interpreted, and then let's take a look at the 24bytes content interpretation of kttbh that follows.

BBED > p ktbbh

Struct ktbbh, 48 bytes @ 20

Ub1 ktbbhtyp @ 20 0x01 (KDDBTDATA)

Union ktbbhsid, 4 bytes @ 24

Ub4 ktbbhsg1 @ 24 0x0000001c

Ub4 ktbbhod1 @ 24 0x0000001c

Struct ktbbhcsc, 8 bytes @ 28

Ub4 kscnbas @ 28 0x805c12df

Ub2 kscnwrp @ 32 0x0000

B2 ktbbhict @ 36 1

Ub1 ktbbhflg @ 38 0x02 (NONE)

Ub1 ktbbhfsl @ 39 0x00

Ub4 ktbbhfnx @ 40 0x00000000

Struct ktbbhitl [0], 24 bytes @ 44

Struct ktbitxid, 8 bytes @ 44

Ub2 kxidusn @ 44 0x0002

Ub2 kxidslt @ 46 0x0025

Ub4 kxidsqn @ 48 0x0006e714

Struct ktbituba, 8 bytes @ 52

Ub4 kubadba @ 52 0x00801ba0

Ub2 kubaseq @ 56 0xaa14

Ub1 kubarec @ 58 0x10

Ub2 ktbitflg @ 60 0x2001 (KTBFUPB)

Union _ ktbitun, 2 bytes @ 62

B2 _ ktbitfsc @ 62 0

Ub2 _ ktbitwrp @ 62 0x0000

Ub4 ktbitbas @ 64 0x805c12e0

Here are the contents of the hexadecimal file:

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

Buffer tsn: 4 rdba: 0x010072a4 (4 Accord 29348)

Scn: 0x0000.00e66a1e seq: 0x02 flg: 0x06 tail: 0x6a1e0602

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

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

Dump of memory from 0x061E8400 to 0x061EA400

61E8400 0000A206 010072A4 00E66A1E 06020000 [.r...j.]

61E8410 00004590 00000001 0000ED65 009B8769 [.E.e...i...]

61E8420 00000000 00320003 010072A1 0000FFFF [.2..r.]

61E8430 00000000 00000000 00000000 00008000 [.]

61E8440 009B8769 00190001 00001B03 0080027B [i. {...]

61E8450 002C0E55 00002001 00E66A1E 00000000 [U.,.. ... j.]

61E8460 00000000 00000000 00000000 [.]

61E8470 00000000 00000000 00000000 00090100 [.]

61E8480 0024FFFF 1C291C4D 00001C29 1F1E0009 [.. $.m.).]

61E8490 1E6C1EC3 1DB71E13 1D021D5B 1C4D1CA7 [..l. [.M.]

61E84A0 00000000 00000000 00000000 [

.

.

.

61EA3E0 38302D35 3A30332D 353A3331 30333A30 [5-08-30 Fran 1313 Rd 50V30]

61EA3F0 4C415605 4E014449 4E014E01 6A1E0602 [.VALID.N.N.N...j]

Buffer tsn: the number of the tablespace corresponding to the data file, which is only the data recorded in the dump file. The number of the tablespace is not recorded in the block.

21-24 bytes, or 0x00000001, denote typ, accounting for 4 bytes

(

1: DATA, 2: index

Changed to 3, causing ora-600 on 10.1.0 [2032] and then ORA-27101: shared memory realm does not exist.

When oracle makes a query, it determines the type of object according to the situation in the obj$ table, not according to this typ

That is to say, if there is a table that can change the flag bit of block in the table, you can also query the data.

But there will be errors in dump block. ORA-00600: internal error code, argument: [4555], [0], []

[0] in the error is the data corresponding to typ.

After changing it in 10G, the data of update this block can be commit, but the error of rollback)

25-28 bytes, that is, 0x0000ED65, represents seg/obj, accounting for 4 bytes

29-36 bytes, or 0x009B8769.00000000, denotes csc, accounting for 6 bytes (The SCN at which the last full cleanout was performed on the block)

The 37th byte, that is, 0x00 represents fsl (Index to the first slot on the ITL freelist. ITL TX freelist slot)

The 38th byte, that is, 0x32 represents flg

(

Indicates that the block is on a freelist. Otherwise the flag is-

In the case of ASSM of 9i, this value is E

Ixora says he takes 2 bytes, but there is a discrepancy between my experiment and his results.

What I have observed is: Object id on Block? Y flg: O ver: 0x01

The above three items are represented by the same byte

Flg: O ver: 0x01 Object id on Block? Y

From my observation, dump came out of the file flg ver Object id on Block.

The byte they jointly occupy can be seen from the following situation.

Binary data flg ver Object id on Block?

0x00-0x00 N

0x01 0 0x00 N

0x02-0x01 Y

0x03 0 0x01 Y

0x04-0x02 Y

0x05 0 0x02 Y

0x06-0x03 Y

0x07 0 0x03 Y

0x08-0x04 N

0x09 0 0x04 N

0x0a-0x05 Y

0x0b 0 0x05 Y

0x0c-0x06 Y

0x0d 0 0x06 Y

0x0e-0x07 Y

0x0f 0 0x07 Y

0x10... Similar to the loop above, this situation has changed on 9i because of the emergence of ASSM.

)

39-40 bytes, that is, 0x0003 stands for itc, occupies 2 bytes. Use the 0x00ff mask to get a value of 3 (the number of ITL entries max 255exceeds the report ORA-02207 ORA-00060 ORA-00054 may be caused by no space to allocate itl entries or its contention, INITRANS default is 1 in 8i and INITRANS default is 2 in 9.2.0)

41-44 bytes, that is, 0x010072A1 (address of the next block in the free list Null if this block is not on a freelist)

After 44 bytes, there is a record of ITL, and each itl occupies 24bytes.

45-52 bytes, or 0x0000.FFFF.00000000, denotes xid

(

Transaction ID (UndoSeg.Slot.Wrap)

The value can be found with select XIDUSN, XIDSLOT,XIDSQN from vested 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).

)

53-60 bytes, or 0x00000000.000000.00, denotes 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.

)

63-64 bytes, or 0x8000, denotes lck flag

(

The number of row locked by Lck. The data of the next byte is also used here.

The corresponding binary representation of 2 is 0010, which coincides with-U-in the dump file.

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

Cmurmuri-= transaction has been committed and locks cleaned out

-Bmuri-= 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.)

61-62 and 65-68 bytes, that is, 0x0000.009B8769 stands for 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)

)

Then analyze it further.

Block header dump: 0x010072a4

Object id on Block? Y

Seg/obj: 0xed65 csc: 0x00.9b8769 itc: 3 flg: e typ: 1-DATA

Brn: 0 bdba: 0x10072a1 ver: 0x01 opc: 0

Inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0xffff.000.00000000 0x00000000.0000.00 Cmurmuri-0 scn 0x0000.009b8769

0x02 0x0001.019.00001b03 0x0080027b.0e55.2c-- U1 fsc 0x0000.00e66a1e

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

Because there are three itls, all of which occupy 24 × 3=72bytes space, plus 20 × 248 (this may be reserved, what exactly to do is not very clear? ), with a total of 124 bytes

Hexadecimal files are as follows:

61E8470 00000000 00000000 00000000 00090100 [.]

61E8480 0024FFFF 1C291C4D 00001C29 1F1E0009 [.. $.m.).]

61E8490 1E6C1EC3 1DB71E13 1D021D5B 1C4D1CA7 [..l. [.M.]

Byte 125, that is, 0x00 stands for flag

(

N=pctfree hit (clusters), F=don't put on free list

K=flushable cluster keys. Of course, there are other marks: a.

)

The 126th byte, or 0x09, represents nrow (how many rows of data does block have)

The 127th byte, or 0x01, represents ntab (the data cluster of several table in this block may be greater than 1)

128-129bytes, or 0x0000, denote frre (First free row index entry. -1=you have to add one.)

The 130th byte, or 0x24, represents fsbo

(Free Space Begin offset goes out the starting position of the space behind the row dict where you can put the data.

It can also be seen as the space occupied from the beginning of the area "flag" to the last "row offs")

135-136bytes, or 0x1C4D, denotes fseo

(

Free Space End offset (9.2.0) participates in the calculation of remaining space in db_block_checking

When select, oracle is not simply to locate row according to offset. This value is also involved in locating row.

)

139-140bytes, or 0x1C29, indicating that tosp (Total available space when all TXs commit (9.2.0) participates in db_block_checking)

133134 bytes, or 0x1C29, denotes avsp (Available space in the block (pctfree and pctused) ORA-01578)

Oracle's dsi document and bbed look at the structure of block, both show that kdbh occupies 14 bytes, but my test is somewhat different from it. I hope the master will point out

Among them, 141-142bytes, namely 0x1F1E, represent offsets (you can see the value when the offset is cluster)

143-144bytes, or 0x0009, denotes nrow (how many rows of data does this table have)

It corresponds to the dump file of Block below.

Data_block_dump,data header at 0x61e847c

/ / data_block_dump,data header at 0x61e847c / / in fact, this block is not directly dump from the data buffer, which represents the number of block in the real dump / / the starting position of the region / / that is, the starting position of the following part.

= / tsiz: hsiz: pbl: bdba: there is no storage in data files

Tsiz: 0x1f80 / Total data area size-- 8k block: 8192-20 (block head)-24 (Transaction Header)-24room2 (a transaction bar)-8 (8 bytes I don't know)-4 (block tail) = 8072 (0x1f80)

Hsiz: 0x24 / Data header size 20 bytes in block header + 4 bytes at the end of block = 24 bytes (0x14)

Pbl: 0x061e847c / Pointer to buffer holding the block

Bdba: 0x010072a4

76543210

Flag=-

Ntab=1-there are several table data in this block, cluster, which may be greater than 1.

Nrow=9-how many rows of data does block have

Frre=-1

Fsbo=0x24

Fseo=0x1c4d

Avsp=0x1c29

Tosp=0x1c29

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

0x12:pri [0] offs=0x1f1e

0x14:pri [1] offs=0x1ec3

0x16:pri [2] offs=0x1e6c

0x18:pri [3] offs=0x1e13

0x1a:pri [4] offs=0x1db7

0x1c:pri [5] offs=0x1d5b

0x1e:pri [6] offs=0x1d02

0x20:pri [7] offs=0x1ca7

0x22:pri [8] offs=0x1c4d

Block_row_dump:

Tab 0, row 0, @ 0x1f1e

Tl: 98 fb:-- Hmurf FLMI-lb: 0x2 cc: 15

Col 0: [5] c4 02 07 4c 0c

Col 1: [4] 32 30 30 31

Col 2: [3] 53 59 53

Col 3: [16] 53 4e 41 50 5f 4c 4f 41 44 45 52 54 49 4d 45 24

Col 4: * NULL*

Col 5: [3] c2 02 3f

Col 6: [3] c2 02 3e

Col 7: [5] 54 41 42 4c 45

Col 8: [7] 78 69 08 1e 0e 33 1f

Col 9: [7] 78 69 08 1e 0e 33 1f

Col 10: [19] 32 30 30 35 2d 30 38 2d 33 30 3a 31 33 3a 35 30 3a 33 30

Col 11: [5] 56 41 4c 49 44

Col 12: [1] 4e

Col 13: [1] 4e

Col 14: [1] 4e

1.gif

2.gif

3.gif

4.gif

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.