Oracle datafile block format description

1. DUMP DatafileBlock example

There are three types of SCN in a data block of Oracle: The Block header SCN, the CSC (cleanout SCN) and the commit SCN recorded in ITL. If we want to view the content of a block or some blocks, we can dump these blocks to view them.

The dump method is described in the previous blog:

Oracle rdba and dba description
Inclueitl (Interested Transaction List) Description

Query the corresponding block number and datafile number by Rowid:

SQL> SELECT

2dbms_rowid.rowid_relative_fno (rowid) REL_FNO,

3dbms_rowid.rowid_block_number (rowid) BLOCKNO,

4dbms_rowid.rowid_row_number (rowid) ROWNO,

5 empno, ename

6 FROM emp WHERE empno = 7369;

REL_FNO BLOCKNO ROWNO EMPNOENAME

--------------------------------------------------

4 20 0 7369 SMITH

 

Then run the dump command based on the block id:

SQL> alter system dump datafile 4 block 20;

 

To dump multiple blocks, run the following command:

SQL> ALTER SYSTEM dump datafile <file_id> block min <block_id> block max <block_id + blocks-1>;

 

SYS @ anqing2 (rac2)>Oradebug setmypid

Statement processed.

SYS @ anqing2 (rac2)>Alter system dump datafile 1 blocks 292689;

System altered.

SYS @ anqing2 (rac2)>Oradebug tracefile_name

/U01/app/oracle/admin/anqing/udump/anqing2_ora_32276.trc

 

[Oracle @ rac2 ~] $Cat/u01/app/oracle/admin/anqing/udump/anqing2_ora_32276.trc

* ** 17:51:31. 366

Start dump data blocks tsn: 0 file #: 1 minblk 292689 maxblk 292689

Buffer tsn: 0 rdba: 0x00447751 (1/292689)

-- Buffertsn: the number of tablespace corresponding to the data file. This is only the data recorded in the dump file. The block does not record the number of tablespace. 

 

Scn: 0x0000. 005bdee1 seq: 0x01flg: 0x06 tail: 0xdee10601

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

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

Dump of memory from 0x0DC34400to 0x0DC36400

DC34400 running a206 00447751005BDEE1 06010000 [...... QwD ......]

......

DC363F0 C3040203 0da-1c0b65766164 DEE10601 [......]

Block header dump: 0x00447751

Object id on Block? Y

Seg/obj: 0xd5ec csc: 0x00. 5bcbe0 itc: 3 flg:-typ: 1-DATA

Fsl: 0 fnx: 0x0 ver: 0x01

 

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0x000e. 007.00000236 0x00000000. 0000.00 C-U-0 scn 0x0000. 005b1f7f

0x02 0x000c. 005.000003b4 0x01401727. 0144.13 C --- 0 scn 0x0000. 005bbf0b

0x03 0x0011. 007.00000406 0x0140015b. 00c7. 57 -- U-483 fsc 0x0000. 005bdee1

 

Data_block_dump, data header at0xdc34474

-- In fact, this block is not directly dumped from the data buffer, which indicates the starting position of the block's data zone during the real dump, that is, the starting position of the following part.

 

====================

Tsiz: 0x1f88

-- Tsiz: hsiz: pbl: bdba: No data files are stored.

-- Total data area size

-- 8 K block: 8192-20 (blockhead)-24 (Transaction Header)-24*3 (one Transaction)-4 (block tail) = 8072 (0x1f88)

-- Convert hexadecimal to hexadecimal

SYS @ anqing2 (rac2)>Selectto_number ('1f88', 'xxxx') from dual;

TO_NUMBER ('1f88', 'xxxx ')

------------------------

8072

 

Hsiz: 0x3d8

-- Data headersize

 

Pbl: 0x0dc34474

-- Pointer tobuffer holding the block

Bdba: 0x00447751

76543210

Flag = --------

Ntab = 1

Nrow= 483

Frre =-1

Fsbo = 0x3d8

Fsearch = 0x706

Avsp = 0x32e

Tosp = 0x32e

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

0x12: pri [0] offs = 0x1f7b

0x14: pri [1] offs = 0x1f6e

......

0x3d4: pri [481] offs = 0x713

0x3d6: pri [482] offs = 0x706

 

Block_row_dump:

Tab 0, row 0, @ 0x1f7b

Tl: 13 fb: -- H-FL -- lb: 0x3 cc: 2

Col 0: [4] c3 0b 1c 26

Col 1: [4] 64 61 76 65

Tab 0, row 1, @ 0x1f6e

Tl: 13 fb: -- H-FL -- lb: 0x3 cc: 2

Col 0: [4] c3 0b 1c 27

Col 1: [4] 64 61 76 65

Tab 0, row 2, @ 0x1f61

Tl: 13 fb: -- H-FL -- lb: 0x3 cc: 2

Col 0: [4] c3 0b 1c 28

Col 1: [4] 64 61 76 65

......

Tab 0, row 481, @ 0x713

Tl: 13 fb: -- H-FL -- lb: 0x3 cc: 2

Col 0: [4] c3 0b 21 13

Col 1: [4] 64 61 76 65

Tab 0, row 482, @ 0x706

Tl: 13 fb: -- H-FL -- lb: 0x3 cc: 2

Col 0: [4] c3 0b 21 14

Col 1: [4] 64 61 76 65

-- Row482 and so on are specific values in each record. I dumped this block to save the record relatively simple, only two columns of value. This value can be converted to a specific string. The method is as follows:

SYS @ anqing2 (rac2)> setserveroutput on

SYS @ anqing2 (rac2)> declare nnumber;

2 begin

3Dbms_stats.convert_raw_value ('c30b2114 ', n );

4 dbms_output.put_line (n );

5 end;

6/

103219

PL/SQL procedure successfullycompleted.

 

SYS @ anqing2 (rac2)> declarestr varchar2 (100 );

2 begin

3Dbms_stats.convert_raw_value ('20140901', str );

4 dbms_output.put_line (str );

5 end;

6/

Dave -- this is the value corresponding to col 1: [4] 64 61 76 65.

PL/SQL procedure successfullycompleted.

 

End_of_block_dump

End dump data blocks tsn: 0 file #: 1 minblk 292689 maxblk 292689

[Oracle @ rac2 ~] $

1 2 3 Next Page