Deep parsing of UNDO field header block format

Next I will make a comprehensive and in-depth analysis of the format of the UNDO header block. This helps us understand the nature of transactions. Well, to facilitate the test, I create a very small UNDO tablespace with the following operations: gyj@OCMcreateundotablespaceundotbs4datafileu01apporacleoradataocmundotbs04.dbfsize192k; Tablesp

Next I will make a comprehensive and in-depth analysis of the format of the UNDO header block. This helps us understand the nature of transactions. Well, to facilitate the test, create a very small UNDO tablespace, as shown below: gyj @ OCM create undo tablespace undotbs4 datafile/u01/app/oracle/oradata/ocm/undotbs04.dbf size 192 k; Tablesp

Next I will make a comprehensive and in-depth analysis of the format of the UNDO header block. This helps us understand the nature of transactions.

Well, to facilitate the test, create a very small UNDO tablespace, as shown below:

gyj@OCM> create undo tablespace undotbs4 datafile '/u01/app/oracle/oradata/ocm/undotbs04.dbf' size 192k;Tablespace created.gyj@OCM> alter system set undo_tablespace=undotbs4;System altered. 

gyj@OCM> select * from v$rollname;       USN NAME---------- ------------------------------         0 SYSTEM        17 _SYSSMU17_3012809736$ 

A transaction occurs:

Gyj @ OCM> update gyj_test set name = 'guoyjob' where id = 1;

1 row updated.

Dump the UNDO header block:

Gyj @ OCM> alter system dump undo header "_ SYSSMU17_3012809736 $ ";

System altered.

Find the trace log of the dump undo field header block:

Gyj @ OCM> select * from v $ diag_info wherename = 'default Trace file ';

INST_ID NAME VALUE

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

1 Default Trace File/u01/app/oracle/diag/rdbms/ocm/trace/ocm_ora_6151.trc

Analyze the log of the UDNO header Block

[Root @ mydb ~] # More/u01/app/oracle/diag/rdbms/ocm/trace/ocm_ora_6151.trc

********************************************************************************Undo Segment:  _SYSSMU17_3012809736$ (17)********************************************************************************  Extent Control Header  -----------------------------------------------------------------  Extent Header:: spare1: 0      spare2: 0      #extents: 2      #blocks: 15                      last map  0x00000000  #maps: 0      offset: 4080        Highwater::  0x0280000a  ext#: 0      blk#: 1      ext size: 7       #blocks in seg. hdr's freelists: 0       #blocks below: 0       mapblk  0x00000000  offset: 0                        Unlocked     Map Header:: next  0x00000000  #extents: 2    obj#: 0      flag: 0x40000000

# Extents: 2 indicates that the 17 UNDO segment has two zones

# Blocks: 15 indicates that 15 UNDO blocks are available in the two zones of the UNDO rollback segment on the 17th. (Why not 16 undo block blocks? Remove one undo block header)

Ext #: 0 indicates that the transaction occurs in 1st partitions (starting from 0)

Blk #: 1 indicates that the transaction occurs on the 1st blocks in the 1st partition.

Ext size: 7 indicates that seven UNDO blocks are available in one partition.

The v $ rollname view shows the UNDO rollback segment on the 17 th.

Gyj @ OCM> select * from v $ rollname;

USN NAME

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

0 SYSTEM

17 _ SYSSMU17_3012809736 $

The dba_extents view shows a total of two zones with 16 blocks.

Gyj @ OCM> select extent_id, file_id, block_id, blocks, bytes fromdba_extents where segment_name = '_ SYSSMU17_3012809736 $ ';

EXTENT_ID FILE_ID BLOCK_ID BLOCKS BYTES

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

0 10 8 65536

1 10 16 8 65536

Use the dba_segments view to find the UNDO field header block. That is, the block 8 of file 10 is the UNDO field header block (so # blocks: 15)

Gyj @ OCM> select header_file, header_block from dba_segments wheresegment_name = '_ SYSSMU17_3012809736 $ ';

HEADER_FILE HEADER_BLOCK

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

10 8

 Extent Map  -----------------------------------------------------------------   0x02800009  length: 7        0x02800010  length: 8  

The UNDO rollback segment on the 17th has two zones:

The first zone corresponds to No. 1, No. 2, No. 3, No. 4, No. 5, No. 6, and No. 7 of file no. 10, A total of 7 UNDO Blocks

The first zone corresponds to No. 9, No. 10, No. 11, no. 12, No. 13, No. 14, and No. 15 of file no. 10, BLOCK 16, a total of 8 UNDO Blocks

Retention Table   ----------------------------------------------------------- Extent Number:0  Commit Time: 1389838948 Extent Number:1  Commit Time: 1389838948

The submission timestamp of the partition, which starts from, January 1, January 1, 1970 (recorded in seconds)

TRN CTL:: seq: 0x000d chd: 0x000a ctl: 0x000b inc: 0x00000000 nfb: 0x0000            mgc: 0xb000 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)            uba: 0x0280000a.000d.2e scn: 0x0000.0028a2af

Transaction Control:

Seq: 0x000d indicates that the UNDOBLOCK block where the value before the transaction is modified is overwritten 13 times, which corresponds to 000d in uba: 0x02820.a. 000d. 2e.

In the following TRNTBL: :( transaction table), index = 0x000a records the new transaction information, that is, the chain header or portal of the transaction table.

Ctl: 0x000b indicates the end of the chain of the transaction table (in fact, you can go to trn tbl: to see index = 0x000b, and its corresponding SCN = 0x0000. 0028a4d5 is the largest SCN in the transaction table, that is, the transaction slot will be overwritten at last)

Nfb: 0x0000 indicates the number of idle UNDO blocks in the idle pool, and 0x0000 indicates that there are no idle UNDO blocks in the pool, that is, free blockpool: no idle blocks.

Flg: 0x0001 indicates the purpose of the BLOCK. 1 = ktuundo header (2 = ktu undo block, etc)

Uba: 0x02820.a. 000d. 2e indicates the First UNDO record of the new transaction (It consists of three parts: the address of the undo block, the number of times the UNDO block is reused, and the number of records in the UNDO block)

Undo block address: 0x02820.a, that is, block 10 of file 10

Number of times the UNDO block is reused: 000d, that is, the UNDO block is overwritten 13 times.

The number of records in the UNDO block 2e is the 46th records in the UNDO Block

Scn: 0x0000. 0028a2af indicates the minimum submitted SCN in the header block of the UNDO field on the 17 th. In fact, this SCN is the SCN on the transaction slot corresponding to the smallest SCN in the transaction table.

Note: The following transaction control is performed before a transaction occurs (that is, update gyj_test set name = 'gggggggg 'where id = 1; The transaction control at the previous dumping)

Trn ctl: seq: 0x000d chd: 0x0017 ctl: 0x000b inc: 0x00000000 nfb: 0x0001

Mgc: 0xb000 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)

Uba: 0x02820.a. 000d. 2b scn: 0x0000. 0028a26a

OK. We can find the INDEX = 0x0017 in the transaction table from the perspective of chd: 0x0017.

Trn tbl ::

Index state cflags wrap # uel scn dba parent-xid nub stmt_num cmt

Bytes ------------------------------------------------------------------------------------------------

0x17 9 0x00 0x001c 0x000a 0x0000. 0028a2af 0x0280000a 0x0000. 000.00000000 0x00000001 0x00000000 1389839441

Have you found out that the above scn = 0x0000. 0028a2af is the SCN recorded in our transaction control? I understand it, but I really don't understand it in the ORACLE DSI group discussion (group number 127149411)

FREE BLOCK POOL::    uba: 0x00000000.000d.2d ext: 0x0  spc: 0x8b8       uba: 0x00000000.000d.0d ext: 0x0  spc: 0x19e8      uba: 0x00000000.0009.08 ext: 0x0  spc: 0x932       uba: 0x00000000.0000.00 ext: 0x0  spc: 0x0         uba: 0x00000000.0000.00 ext: 0x0  spc: 0x0  

The idle pool of the UNDO block. when the transaction is committed, the UNDO block in the transaction will be added to the idle pool.

Uba: contains the address of the undo block, the number of times the UNDO block is reused, and the number of records in the UNDO block, when the address of the undo block is 0, it indicates that the UNDO block is not idle, that is, 0x00000000.

Ext: The partition in which the UNDO block is located (extent)

Spc: the number of idle space in the UNDO block, in bytes.

From the above UNDO idle pool, there is no idle UNDO block.

 TRN TBL::   index  state cflags  wrap#    uel         scn            dba            parent-xid    nub     stmt_num    cmt  ------------------------------------------------------------------------------------------------   0x00    9    0x00  0x001d  0x001f  0x0000.0028a444  0x02800009  0x0000.000.00000000  0x00000001   0x00000000  1389840449   0x01    9    0x00  0x001d  0x000e  0x0000.0028a454  0x02800009  0x0000.000.00000000  0x00000001   0x00000000  1389840449   0x02    9    0x00  0x001d  0x0003  0x0000.0028a448  0x02800009  0x0000.000.00000000  0x00000001   0x00000000  1389840449   0x03    9    0x00  0x001d  0x0005  0x0000.0028a44a  0x02800009  0x0000.000.00000000  0x00000001   0x00000000  1389840449   0x04    9    0x00  0x001d  0x000b  0x0000.0028a4d3  0x0280000a  0x0000.000.00000000  0x00000001   0x00000000  1389840762   0x05    9    0x00  0x001d  0x001d  0x0000.0028a44c  0x02800009  0x0000.000.00000000  0x00000001   0x00000000  1389840449   0x06    9    0x00  0x001d  0x000d  0x0000.0028a493  0x0280000a  0x0000.000.00000000  0x00000001   0x00000000  1389840612   0x07    9    0x00  0x001d  0x0008  0x0000.0028a452  0x02800009  0x0000.000.00000000  0x00000001   0x00000000  1389840449   0x08    9    0x00  0x001d  0x0001  0x0000.0028a453  0x02800009  0x0000.000.00000000  0x00000001   0x00000000  1389840449   0x09    9    0x00  0x001d  0x0016  0x0000.0028a457  0x02800009  0x0000.000.00000000  0x00000001   0x00000000  1389840449   0x0a    9    0x00  0x001c  0x0020  0x0000.0028a2e3  0x0280000a  0x0000.000.00000000  0x00000001   0x00000000  1389839562   0x0b    9    0x00  0x001d  0xffff  0x0000.0028a4d5  0x0280000a  0x0000.000.00000000  0x00000001   0x00000000  1389840762   0x0c    9    0x00  0x001d  0x0006  0x0000.0028a459  0x0280000a  0x0000.000.00000000  0x00000001   0x00000000  1389840449   0x0d    9    0x00  0x001d  0x0012  0x0000.0028a495  0x0280000a  0x0000.000.00000000  0x00000001   0x00000000  1389840612   0x0e    9    0x00  0x001c  0x0009  0x0000.0028a455  0x02800009  0x0000.000.00000000  0x00000001   0x00000000  1389840449   0x0f    9    0x00  0x001d  0x0011  0x0000.0028a498  0x0280000a  0x0000.000.00000000  0x00000001   0x00000000  1389840612   0x10    9    0x00  0x001d  0x0014  0x0000.0028a4c3  0x0280000a  0x0000.000.00000000  0x00000001   0x00000000  1389840737   0x11    9    0x00  0x001d  0x0010  0x0000.0028a499  0x0280000a  0x0000.000.00000000  0x00000001   0x00000000  1389840612   0x12    9    0x00  0x001d  0x000f  0x0000.0028a497  0x0280000a  0x0000.000.00000000  0x00000001   0x00000000  1389840612   0x13    9    0x00  0x001d  0x0004  0x0000.0028a4d1  0x0280000a  0x0000.000.00000000  0x00000001   0x00000000  1389840762   0x14    9    0x00  0x001d  0x0013  0x0000.0028a4c8  0x0280000a  0x0000.000.00000000  0x00000001   0x00000000  1389840740   0x15    9    0x00  0x001c  0x0019  0x0000.0028a2f9  0x0280000a  0x0000.000.00000000  0x00000001   0x00000000  1389839562   0x16    9    0x00  0x001a  0x000c  0x0000.0028a458  0x02800009  0x0000.000.00000000  0x00000001   0x00000000  1389840449   0x17   10    0x80  0x001d  0x0000  0x0000.0028a4f7  0x0280000a  0x0000.000.00000000  0x00000001   0x00000000  0   0x18    9    0x00  0x001c  0x0021  0x0000.0028a3de  0x02800009  0x0000.000.00000000  0x00000001   0x00000000  1389840161   0x19    9    0x00  0x001c  0x001c  0x0000.0028a31e  0x02800009  0x0000.000.00000000  0x00000001   0x00000000  1389839663   0x1a    9    0x00  0x001c  0x001e  0x0000.0028a35f  0x0280000a  0x0000.000.00000000  0x00000001   0x00000000  1389839848   0x1b    9    0x00  0x001c  0x0007  0x0000.0028a450  0x02800009  0x0000.000.00000000  0x00000001   0x00000000  1389840449   0x1c    9    0x00  0x001c  0x001a  0x0000.0028a35e  0x02800009  0x0000.000.00000000  0x00000001   0x00000000  1389839848   0x1d    9    0x00  0x001b  0x001b  0x0000.0028a44e  0x02800009  0x0000.000.00000000  0x00000001   0x00000000  1389840449   0x1e    9    0x00  0x001c  0x0018  0x0000.0028a3dc  0x02800009  0x0000.000.00000000  0x00000001   0x00000000  1389840161   0x1f    9    0x00  0x001c  0x0002  0x0000.0028a446  0x02800009  0x0000.000.00000000  0x00000001   0x00000000  1389840449   0x20    9    0x00  0x001b  0x0015  0x0000.0028a2ee  0x0280000a  0x0000.000.00000000  0x00000001   0x00000000  1389839562   0x21    9    0x00  0x001c  0x0000  0x0000.0028a3e0  0x02800009  0x0000.000.00000000  0x00000001   0x00000000  1389840161

Trn tbl: (transaction table) is the most important part of the UNDO field header block. Let's explain the meaning of each field one by one:

Index indicates the slot number in the transaction table. It is just a sequence. From 0x00 to 0x21, the 11g version has 34 slots.

State indicates the transaction state: 9 indicates that the transaction is not active, and 10 indicates that the transaction is active. From here, we can see that the transaction in the hex 0x17 slot is active. Have you ever found that before a transaction occurs, Oracle will find the transaction control list in the "chd = 0x0017". To put it bluntly, it will re-store the latest transaction from the "index = 0x17" slot:

Note: The following transaction control is performed before a transaction occurs (that is, update gyj_test set name = 'gggggggg 'where id = 1; The transaction control at the previous dumping)

Trn ctl: seq: 0x000d chd: 0x0017 ctl: 0x000b inc: 0x00000000 nfb: 0x0001

Mgc: 0xb000 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)

Uba: 0x02820.a. 000d. 2b scn: 0x0000. 0028a26a

Cflags indicates the status of the transaction that is using the transaction slot: 0x00 indicates the transaction is not active, 0x80 indicates the active transaction, 0x10 indicates the dead transaction, and 0x90 indicates the dead transaction that is rolled back.

At most, we can see that 0x00 indicates a non-active transaction, 0x80 indicates an active transaction, and rarely occurs later.

Wrap # indicates the number of times the transaction slot in the transaction table is reused. It is part of the XID. 0x001d indicates that the transaction slot has been reused for 29 times.

Uel indicates the pointer of the current active transaction to the next transaction slot in the transaction slot (that is, if another transaction occurs, the index on the transaction slot pointed by UEL will be used ).

Scn indicates the SCN used to start, submit, and roll back a transaction.

Dba indicates uba: The undo block address in the first part. This DBA is the starting point of rollback, that is, the address of the UNDO block where the last record of transaction modification is located.

Nub indicates the number of UNDO blocks used by the current firm.

Cmt indicates the closest to the current submission timestamp, starting from, January 1, January 1, 1970 (recorded in seconds ). 0 indicates that the transaction is active.

First, the UNDO block format and REDO block format will be further analyzed. Finally, a transaction example is used to concatenate the REDO block, UNDO segment header block, UNDO block, and DATA block for analysis.