Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

How to understand dump data blocks

2025-01-16 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

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

Today, I will talk to you about how to understand dump data blocks, which may not be well understood by many people. in order to make you understand better, the editor has summarized the following for you. I hope you can get something according to this article.

1. Dump data file

Dump data files need to know the storage information of the table: data file number (file#) and block number (block#).

The table space, file number, section distribution, and block distribution information of the following query table

Col segment_name for a20

Col tablespace_name for a20

Select segment_name, segment_type, tablespace_name, extent_id, file_id, block_id, blocks, bytes from dba_extents where owner = 'SCOTT' and segment_name =' EMP'

SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BLOCKS BYTES

--

EMP TABLE USERS 0 4 144 8 65536

Then look at the block distribution information recorded in the table.

Select dbms_rowid.rowid_block_number (rowid) block#, count (*) block_records from scott.emp group by dbms_rowid.rowid_block_number (rowid) order by block#

BLOCK# BLOCK_RECORDS

--

151 14

It can be seen that the first few blocks at the beginning of the table segment are used to store the segment header information, and the row record data starts from the subsequent block.

Use the following command to dump one or more blocks:

Alter system dump datafile block

Alter system dump datafile block min block max

First take a look at the tracking file corresponding to the current session.

Col trace_file_name for a80

Select a.value | | b.symbol | | c.instance_name | |'_ ora_' | | d.spid | |

'.trc 'trace_file_name

From (select value from v$parameter where name = 'user_dump_dest') a

(select substr (value,-6,1) symbol

From v$parameter

Where name = 'user_dump_dest') b

(select instance_name from v$instance) c

(select spid

From v$session s, v$process p, v$mystat m

Where s.paddr = p.addr

And s.sid = m.sid

And m.statistic# = 0) d

TRACE_FILE_NAME

C:\ oracle\ diag\ rdbms\ mes\ mes\ trace\ mes_ora_3912.trc

Now the dump file block

Alter system dump datafile 4 block 151

View the contents of the trace file

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

Block dump from cache:

Dump of buffer cache at level 4 for tsn=4, rdba=16777367

Block dump from disk:

Buffer tsn: 4 rdba: 0x01000097

Scn: 0x0000.001ef9e3 seq: 0x01 flg: 0x06 tail: 0xf9e30601

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

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

Dump of memory from 0x000000000D186E00 to 0x000000000D188E00

00D186E00 0000A206 01000097 001EF9E3 06010000 [.]

...

00D188DF0 0101110C 09C20201 15C102FF F9E30601 [.]

Block header dump: 0x01000097

Object id on Block? Y

Seg/obj: 0x11dec csc: 0x00.1ef9cc itc: 2 flg: e typ: 1-DATA

Brn: 0 bdba: 0x1000090 ver: 0x01 opc: 0

Inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0x000a.009.0000044c 0x00c00f07.012e.20-- U1 fsc 0x0000.001ef9e3

0x02 0x000a.004.0000044d 0x00c00f07.012e.1c Cmurmuri-0 scn 0x0000.001ef98a

The main information of the block is as follows:

Tsn: tablespace number

File#: file number

Minblk and maxblk: range of export block numbers

Rdba: relative block address information

Here rdba lists the hexadecimal and decimal forms, and this address can be converted by file number and block number using the following query

Select dbms_utility.make_data_block_address (4151) from dual

DBMS_UTILITY.MAKE_DATA_BLOCK_ADDRESS (4151)

16777367

Its hexadecimal form

Select to_char (16777367, 'xxxxxxxx') from dual

TO_CHAR (1

-

1000097

Conversely, the file number and block number can be obtained by block address translation.

Select dbms_utility.data_block_address_file (16777367) file#, dbms_utility.data_block_address_block (16777367) block# from dual

FILE# BLOCK#

--

4 151

Scn: SCN information of the data block

Tail: consists of the last two bytes of SCN, block type type, and sequence number seq

Oracle uses the tail of the data block to judge the consistency of the content of the data block. If the decomposed tail here does not match the last two bytes, block type and sequence number of SCN, Oracle can determine that the block is in an inconsistent state and needs to be restored. Although the tail information here is displayed at the beginning of the block trace file, in fact it is physically stored at the end of the data block, which is the origin of the tail name.

Valid values for block type type are as follows:

0x02:undo block

0x06:trans data

0x0e:undo segment header

0x10:data segment header block

0x17:bitmapped data segment header

0x20:first level bitmap block

0x21:second level bitmap block

0x23:pagetable segment header

Object id on Block? Y: whether the database objects stored on the data block exist in the sys.obj$ data dictionary

Seg/obj: ID information of database objects, which can be verified by the following query

Col object_name for a30

Select owner, object_name from dba_objects where object_id = to_number ('11declines,' xxxxx')

OWNER OBJECT_NAME

SCOTT EMP

Csc: the SCN (cleanout SCN) when the block is cleaned, and watch to see if it matches the SCN of the block.

Itc: the number of transaction slots (ITL slot). The following two lines are the information of ITL, corresponding to two transactions, identified by xid.

Flag: identifies whether the data block exists in the freelist of the segment, "-" indicates that the block is not in freelist, and "o" represents on, indicating that the block exists in freelist and can be used for insert records.

The following is the header section

Data_block_dump,data header at 0xd186e64

=

Tsiz: 0x1f98

Hsiz: 0x2e

Pbl: 0x0d186e64

76543210

Flag=-

Ntab=1

Nrow=14

Frre=-1

Fsbo=0x2e

Fseo=0x1d61

Avsp=0x1d33

Tosp=0x1d33

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

0x12:pri [0] offs=0x1f72

0x14:pri [1] offs=0x1f47

0x16:pri [2] offs=0x1f1c

0x18:pri [3] offs=0x1ef3

0x1a:pri [4] offs=0x1ec6

0x1c:pri [5] offs=0x1e9d

0x1e:pri [6] offs=0x1e74

0x20:pri [7] offs=0x1e4c

0x22:pri [8] offs=0x1e26

0x24:pri [9] offs=0x1dfb

0x26:pri [10] offs=0x1dd5

0x28:pri [11] offs=0x1daf

0x2a:pri [12] offs=0x1d88

0x2c:pri [13] offs=0x1d61

The meaning here is as follows:

Tsiz: the total space used by the data block to store the data portion (total data area size)

Hsiz: head size (header size)

Pbl: the address pointer (pointer to buffer holding the block) of the block in the cache, and the following line is its block address bdba.

Ntab:number of tables, if the value is greater than 1, the data block belongs to the storage block of cluster.

Nrow:number of rows, the number of records stored in the block.

Fsbo, fseo:free space begin offset and free space end offset, both give the starting position of the free space in the block.

Avsp:available space in the block

Tosp:total available space when all transactions commit

Then there is the line record information, and here is the first record.

Block_row_dump:

Tab 0, row 0, @ 0x1f72

Tl: 38 fb:-- Hmurf FLmuri-lb: 0x1 cc: 8

Col 0: [3] c2 4a 46

Col 1: [5] 53 4d 49 54 48

Col 2: [5] 43 4c 45 52 4b

Col 3: [3] c2 50 03

Col 4: [7] 77 b4 0c 11 01 01 01

Col 5: [2] c2 09

Col 6: * NULL*

Col 7: [2] c1 15

The meaning here is as follows:

Tab 0, row 0, @ 0x1f72: the identity, row identity, and row address of the table in cluster.

Tl: indicates the total byte space occupied by the row, expressed in decimal, and contains the other overhead of the row.

Fb: row tag, H for head of row,F and L for row first piece and last piece, respectively, indicating that this row involves the exported data block, there is no row link, and because there is a row header in the block, there is also row migration.

Lb:ITL transaction slot number

Cc: number of columns

Col n: [k]: data of column n + 1, occupying k bytes.

The following verifies that the second column of data col 1: [5] 53 4d 49 54 48 is given in hexadecimal ASCII code and converted to characters by the following query

Select chr (to_number ('53, 'xx')) | | chr (to_number (' 418, 'xx')) | | chr (to_number (' 49, 'xx')) | | chr (to_number (' 54, 'xx')) | | chr (to_number (' 48, 'xx')) from dual

CHR (TO_NUM

-

SMITH

Or use the following package conversion

Set serveroutput on

Declare c varchar2 (30)

Begin

Dbms_stats.convert_raw_value ('534d495448, c)

Dbms_output.put_line (c)

End

/

SMITH

For the first column of data col 0: [3] c24a 46, this column is of type number and can be converted to a decimal number through the above package

Set serveroutput on

Declare n number

Begin

Dbms_stats.convert_raw_value ('c24a46, n)

Dbms_output.put_line (n)

End

/

7369

You can query and verify from the scott.emp table that the employee numbered 7369 is smith.

For the fifth column of data col 4: [7] 77 b4 0c 11 01 01 01, this column is of type date and can also be converted to a displayable date through the above package

Set serveroutput on

Declare dt date

Begin

Dbms_stats.convert_raw_value ('77b40c11010101, dt)

Dbms_output.put_line (to_char (dt, 'yyyy-mm-dd hh34:mi:ss'))

End

/

1980-12-17 00:00:00

II. Dump index file

The storage of index data blocks is significantly different from that of table data blocks. The storage data blocks corresponding to an b-tree index are branch node blocks (branch block) and leaf node blocks (leaf block). To export an b-tree index, you need to provide the object_id of the index. To do this, query the index object and its ID information.

Col table_name for a30

Col tablespace_name for a20

Col index_name for a20

Col index_type for a10

Select table_name, index_name, index_type, status, tablespace_name from dba_indexes where owner='CMES' and table_name='C_MATERIAL_T'

TABLE_NAME INDEX_NAME INDEX_TYPE STATUS TABLESPACE_NAME

C_MATERIAL_T IDX_FK_MATERIAL_NO NORMAL VALID CMES

C_MATERIAL_T IDX_FK_PART_NO NORMAL VALID CMES

C_MATERIAL_T IDX_PK_MATERIAL_ID NORMAL VALID CMES

Select object_id from dba_objects where object_name = 'IDX_FK_PART_NO'

OBJECT_ID

-

77043

Enable treedump event tracking to export, substituting the object_id of the indexed object into the following command

Alter session set events' immediate trace name treedump level 77043'

View the exported file

-begin tree dump

Branch: 0x14003b3 20972467 (0: nrow: 2, level: 1)

Leaf: 0x14003b4 20972468 (- 1: nrow: 322 rrow: 322)

Leaf: 0x14003b5 20972469 (0: nrow: 23 rrow: 23)

-end tree dump

This index tree has only one branch node (branch), that is, the root node of the index (root), the block address is 0x14003b3, its decimal number is 20972467, and the level of the root node is 1, indicating that the height of the b-tree index is 2, because the level of the index tree is calculated from 0. There are no more branches under the root node, that is, the leaf node block, and the level of the leaf node is always 0, so there is no need to mark it in the dump file. The location of the branches and leaves starts at-1 on the far left instead of 0. For leaf node blocks, nrow represents the number of row pointers stored in the node block, and for branch node blocks, nrow represents the number of blocks pointing to the next layer node. In the leaf node block, there is also rrow, which is related to the transaction on the block (rrow is the number of rows after all current transactions have been committed), and rrow and nrow are equal, indicating that there is no transaction in progress on the data involved in the block.

Analyze the first leaf block on the left, leaf: 0x14003b4 20972468 (- 1: nrow: 322 rrow: 322), and convert the block address dba to the corresponding file number and block number.

Select dbms_utility.data_block_address_file (to_number ('14003b4)) file#, dbms_utility.data_block_address_block (to_number (' 14003b4)) block# from dual

FILE# BLOCK#

--

5 948

Dump the data block

Alter system dump datafile 5 block 948

Check the dump file to find the description section of the leaf block

Leaf block dump

=

Header address 532770404=0x1fc16e64

Kdxcolev 0

KDXCOLEV Flags =-

Kdxcolok 0

Kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y

Kdxconco 2

Kdxcosdc 0

Kdxconro 322

Kdxcofbo 680=0x2a8

Kdxcofeo 1502=0x5de

Kdxcoavs 822

Kdxlespl 0

Kdxlende 0

Kdxlenxt 20972469=0x14003b5

Kdxleprv 0=0x0

Kdxledsz 0

Kdxlebksz 8036

Row#0 [8017] flag: -, lock: 0, len=19

Col 0; len 9; (9): 31 36 44 34 30 37 31 38 33

Col 1; len 6; (6): 01 40 03 a5 000a

Row#1 [7997] flag: -, lock: 0, len=20

Col 0; len 10; (10): 31 38 30 35 30 30 30 34 31 45

Col 1; len 6; (6): 01 40 03 9b 000c

Kdxlenxt: the block address of the next leaf node

Kdxleprv: the block address of the previous leaf node

These two pointers are important for the SQL optimizer to perform index range scans operations on the index. Then comes the data content of the leaf block, which shows only two rows of records.

After reading the above, do you have any further understanding of how to understand dump data blocks? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report