In-depth exploration of Oracle rollback Mechanism

The undo mechanism is a clever feature of Oracle and one of the difficulties in learning oracle.

Next we will learn more about it internally.

Session 1: Scott user, initiates a transaction

hr@ORCL> conn scott/oracleConnected.scott@ORCL> UPDATE emp SET sal=4000 WHERE empno=7788;1 row updated.scott@ORCL> SELECT empno,ename,job,mgr,sal FROM emp WHERE empno=7788;     EMPNO ENAME      JOB              MGR        SAL---------- ---------- --------- ---------- ----------      7788 SCOTT      ANALYST         7566       4000

Do not submit this transaction first. Session 2: SYS user
Obtain transaction information

From v $ transaction, you can get: the transaction is in the rollback segment on the 9th, located in the transaction slot on the 26th

sys@ORCL> SELECT xidusn,xidslot FROM v$transaction;    XIDUSN    XIDSLOT---------- ----------         9         26

From v $ rollstat, it can also be seen that the rollback segment of the transaction is 9

sys@ORCL> SELECT usn,xacts FROM v$rollstat;       USN      XACTS---------- ----------         0          0         1          0         2          0         3          0         4          0         5          0         6          0         7          0         8          0         9          1        10          0

Dump rollback segment header information

sys@ORCL> SELECT * FROM v$rollname a WHERE a.usn=9;       USN NAME---------- ------------------------------         9 _SYSSMU9$sys@ORCL> alter system dump undo header '_SYSSMU9$';System altered.sys@ORCL> select spid from v$process where addr in (select paddr from v$session where sid in (select sid   2  from v$mystat where rownum=1));SPID------------10086

Some content is extracted as follows:

********************************************************************************Undo Segment:  _SYSSMU9$ (9)********************************************************************************  Extent Control Header  -----------------------------------------------------------------  Extent Header:: spare1: 0      spare2: 0      #extents: 4      #blocks: 31                  last map  0x00000000  #maps: 0      offset: 4080      Highwater::  0x00800615  ext#: 2      blk#: 4      ext size: 8  #blocks in seg. hdr's freelists: 0  #blocks below: 0  mapblk  0x00000000  offset: 2                   Unlocked     Map Header:: next  0x00000000  #extents: 4    obj#: 0      flag: 0x40000000  Extent Map  -----------------------------------------------------------------   0x0080008a  length: 7   0x008000d9  length: 8   0x00800611  length: 8   0x00800669  length: 8 Retention Table  ----------------------------------------------------------- Extent Number:0  Commit Time: 1344269736 Extent Number:1  Commit Time: 1344276385 Extent Number:2  Commit Time: 1344264118 Extent Number:3  Commit Time: 1344264252  TRN CTL:: seq: 0x0105 chd: 0x0019 ctl: 0x0014 inc: 0x00000000 nfb: 0x0000            mgc: 0x8201 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)            uba: 0x00800615.0105.11 scn: 0x0000.000d2615Version: 0x01  FREE BLOCK POOL::    uba: 0x00000000.0105.10 ext: 0x2  spc: 0x16f6    uba: 0x00000000.0105.03 ext: 0x2  spc: 0x1eac    uba: 0x00000000.0101.0b ext: 0xb  spc: 0x198c    uba: 0x00000000.0000.00 ext: 0x0  spc: 0x0    uba: 0x00000000.0000.00 ext: 0x0  spc: 0x0  TRN TBL::  index  state cflags  wrap#    uel         scn            dba            parent-xid    nub     stmt_num    cmt  ------------------------------------------------------------------------------------------------   0x00    9    0x00  0x0162  0x0029  0x0000.000d2ab9  0x00800613  0x0000.000.00000000  0x00000001   0x00000000  1344280244             ............................................   0x1a   10    0x80  0x0162  0x0002  0x0000.00000000  0x00800615  0x0000.000.00000000  0x00000001   0x00000000  0             ............................................

This is the mysterious rollback segment header information, including the transaction table information. 0x1a is a hexadecimal number and is converted to a hexadecimal value:

scott@ORCL> select to_number('1a','xx') from dual;TO_NUMBER('1A','XX')--------------------                  26

From this, we can see that 0x1a is 26. Looking back at the previous transaction information, this transaction occupies exactly the No. 26th transaction slot, and the status (State) is 10, which indicates the active transaction.

Dump old value information
Column 7 of the field header information: DBA (data block address), which refers to the address 0x00800615 of the rollback Block
Translate the DBA

sys@ORCL> select to_number('00800615','xxxxxxxx') from dual;TO_NUMBER('00800615','XXXXXXXX')--------------------------------                         8390165sys@ORCL> select dbms_utility.data_block_address_file(8390165) from dual;DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(8390165)---------------------------------------------                                            2sys@ORCL> select dbms_utility.data_block_address_block(8390165) from dual;DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(8390165)----------------------------------------------                                          1557

From this, we can see that the old value is on the 1,557th rollback blocks of file 2.
This is exactly the same as the data obtained from the V $ transaction query:

sys@ORCL> select ubafil,ubablk from v$transaction;    UBAFIL     UBABLK---------- ----------         2       1557

Return to session 1 and continue DML transaction: Scott user

scott@ORCL> update emp set sal=4000 where empno=7788;1 row updated.scott@ORCL> update emp set sal=4000 where empno=7782;1 row updated.scott@ORCL> update emp set sal=40000 where empno=7698;1 row updated.

Session 2: SYS user
Dumped the rollback block 1557dump.

sys@ORCL> select spid from v$process where addr in (select paddr from v$session where sid in (select sid from v$mystat  where rownum=1));SPID------------11146sys@ORCL> alter system dump datafile 2 block 1557;System altered.

The information starting with rollback block 1557 is as follows:

*** 2012-08-07 16:31:42.449*** SERVICE NAME:(SYS$USERS) 2012-08-07 16:31:42.355*** SESSION ID:(135.102) 2012-08-07 16:31:42.355Start dump data blocks tsn: 1 file#: 2 minblk 1557 maxblk 1557buffer tsn: 1 rdba: 0x00800615 (2/1557)scn: 0x0000.000d36d2 seq: 0x01 flg: 0x04 tail: 0x36d20201frmt: 0x02 chkval: 0xbeca type: 0x02=KTU UNDO BLOCKHex dump of block: st=0, typ_found=1Dump of memory from 0x0E7E4400 to 0x0E7E6400......................********************************************************************************UNDO BLK:xid: 0x0009.01a.00000162  seq: 0x105 cnt: 0x14  irb: 0x14  icl: 0x0   flg: 0x0000 Rec Offset      Rec Offset      Rec Offset      Rec Offset      Rec Offset---------------------------------------------------------------------------0x01 0x1f08     0x02 0x1e58     0x03 0x1df4     0x04 0x1da0     0x05 0x1d1c0x06 0x1ca0     0x07 0x1c54     0x08 0x1b80     0x09 0x1afc     0x0a 0x1a700x0b 0x198c     0x0c 0x1924     0x0d 0x18cc     0x0e 0x17e8     0x0f 0x17800x10 0x1728     0x11 0x16a8     0x12 0x1650     0x13 0x15f4     0x14 0x1598*-----------------------------

Note that this part of information has a parameter IRB: 0x14. IRB refers to the start point of all recent uncommitted transactions recorded in the rollback segment, that is, the last modification. If you want to roll back, this is the starting point of rollback.
From the above, we can see that: (IRB: 0x14) the offset address is 0x1598
Find the IRB: 0x14 information, as shown below:

*-----------------------------* Rec #0x14  slt: 0x1a  objn: 51148(0x0000c7cc)  objd: 51148  tblspc: 4(0x00000004)*       Layer:  11 (Row)   opc: 1   rci 0x13Undo type:  Regular undo   Last buffer split:  NoTemp Object:  NoTablespace Undo:  Nordba: 0x00000000*-----------------------------KDO undo record:KTB Redoop: 0x02  ver: 0x01op: C  uba: 0x00800615.0105.13KDO Op code: URP row dependencies Disabled  xtype: XA flags: 0x00000000  bdba: 0x01000020  hdba: 0x0100001bitli: 2  ispac: 0  maxfr: 4858tabn: 0 slot: 5(0x5) flag: 0x2c lock: 0 ckix: 191ncol: 8 nnew: 1 size: 1col  5: [ 3]  c2 1d 33

C2 1D 33 is converted to decimal 2850. This is the old value of the last updated record. That is, update EMP set sal = 4000 where empno = 7698.

Note: The following parameters need to be noted:

1) The rci 0x13 parameter represents the Undo chain (multiple modifications to the same transaction are associated with the chain link ), rci 0x13 points to the second update EMP set sal = 4000 where empno = 7782. if other processes query Scott. for EMP tables, Oracle needs to construct consistent reads to present data to users.
2) parameter bdba: 0x01000020, which represents the address of the data block corresponding to the old value. The parameter is translated below:

scott@ORCL> select to_number('01000020','xxxxxxxx') from dual;TO_NUMBER('01000020','XXXXXXXX')--------------------------------                        16777248scott@ORCL> select dbms_utility.data_block_address_file(16777248) from dual;DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(16777248)----------------------------------------------                                             4scott@ORCL> select dbms_utility.data_block_address_block(16777248) from dual;DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(16777248)-----------------------------------------------                                             32

Then dump the 32nd blocks of file 4:

sys@ORCL> select spid from v$process where addr in (select paddr from v$session  2  where sid in (select sid from v$mystat where rownum=1));SPID------------13929sys@ORCL> alter system dump datafile 4 block 32;System altered.

Some content is extracted as follows:

Start dump data blocks tsn: 4 file#: 4 minblk 32 maxblk 32buffer tsn: 4 rdba: 0x01000020 (4/32)scn: 0x0000.000d36d2 seq: 0x01 flg: 0x04 tail: 0x36d20601frmt: 0x02 chkval: 0xa9ea type: 0x06=trans dataBlock header dump:  0x01000020 Object id on Block? Y seg/obj: 0xc7cc  csc: 0x00.b9cf3  itc: 2  flg: E  typ: 1 - DATA     brn: 0  bdba: 0x1000019 ver: 0x01 opc: 0     inc: 0  exflg: 0 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc0x01   0x0003.011.000000f2  0x00805794.00c8.49  C---    0  scn 0x0000.0006bfdb0x02   0x0009.01a.00000162  0x00800615.0105.14  ----    3  fsc 0x0002.00000000

The ITL transaction slot information exists here, and the transaction must obtain an ITL transaction slot for data modification. The ITL content mainly includes Xid (ITL pointing to the rollback segment header), UBA (pointing to the rollback block), and LCK (number of row head locks, which are modified three times here, so there are three locks ). In the above output, we can see that there are active transactions on itl2 (0x02.

Set Xid (0x0009. 01a. 00000162) decomposition: 0009 indicates that the transaction points to the rollback segment No. 9, and 01a indicates that the transaction points to the transaction slot No. 26, 00000162 indicates that the transaction is overwritten for 354th times (because the rollback segment is used cyclically ).
Break down UBA (0x00800615. 0105.14): 00800615 indicates 1,557th rollback blocks, and 14 indicates IRB information (the starting point of rollback ).

At this point, the transaction information in the Undo segment and data block is completely parsed, and the two are completely consistent!