模拟事务的回滚（undo的具体记录）

SQL> select * from v$version；

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

 

SQL> create table test (id int,name varchar2(20));

Table created.

Elapsed: 00:00:00.07
SQL> insert into test select empno,ename from scott.emp;

14 rows created.

Elapsed: 00:00:00.00
SQL> select * from test;

        ID NAME
---------- ---------------------------------------------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS
      7900 JAMES
      7902 FORD
      7934 MILLER

14 rows selected.

Elapsed: 00:00:00.01
SQL> update test set NAME='guangzhou' where id=7369
  2  ;

1 row updated.

Elapsed: 00:00:00.01
SQL> commit;

Commit complete.

Elapsed: 00:00:00.01
SQL>
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
     179253

Elapsed: 00:00:00.00
SQL> update test set NAME='jiangxi' where id=7369
  2  ;

1 row updated.

Elapsed: 00:00:00.01
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
     179263

Elapsed: 00:00:00.01
SQL> update test set NAME='beijing' where id=7369
  2  ;

1 row updated.

Elapsed: 00:00:00.00
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
SQL>
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
     179270

Elapsed: 00:00:00.00
SQL> select * from test;

        ID NAME
---------- ---------------------------------------------
      7369 beijing
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS
      7900 JAMES
      7902 FORD
      7934 MILLER

14 rows selected.

Elapsed: 00:00:00.01
SQL> select * from test as of scn 179253;

        ID NAME
---------- ---------------------------------------------
      7369 guangzhou
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS
      7900 JAMES
      7902 FORD
      7934 MILLER

14 rows selected.

Elapsed: 00:00:00.00
SQL> select * from test as of scn 179263;

        ID NAME
---------- ---------------------------------------------
      7369 jiangxi
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS
      7900 JAMES
      7902 FORD
      7934 MILLER

14 rows selected.

Elapsed: 00:00:00.00
SQL> select * from test as of scn 179270 ;

        ID NAME
---------- ---------------------------------------------
      7369 beijing
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS
      7900 JAMES
      7902 FORD
      7934 MILLER

14 rows selected.

Elapsed: 00:00:00.01
                                     

   模拟undo回滚到 scn 179253     

 

dump 修改后文件对应的undo记录。 file 4，block 342。

Block header dump:  0x01000156
 Object id on Block? Y
 seg/obj: 0x2637  csc: 0x00.2bc43  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x1000151 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 
     Itl                        Xid                  Uba                       Flag  Lck        Scn/Fsc
0x01   0x0001.00e.00000070  0x008015a2.0026.36  --U-    1  fsc 0x0000.0002bc44
0x02   0x0002.020.0000006a  0x00800e5a.0049.0f   C---    0  scn 0x0000.0002bc3d
 
在事务，发现都是提交了的事务，将要回滚的scn和事务的中的scn的对已，回滚的操作scn从大到小依次回滚

data_block_dump,data header at 0xd3ae464
===============
tsiz: 0x1f98
hsiz: 0x2e
pbl: 0x0d3ae464
bdba: 0x01000156
     76543210
flag=--------
ntab=1
nrow=14
frre=-1
fsbo=0x2e
fseo=0x1ec3
avsp=0x1eb3
tosp=0x1eb3
0xe:pti[0]      nrow=14 offs=0
0x12:pri[0]     offs=0x1ec3
0x14:pri[1]     offs=0x1ef0
0x16:pri[2]     offs=0x1efd
0x18:pri[3]     offs=0x1f09
0x1a:pri[4]     offs=0x1f16
0x1c:pri[5]     offs=0x1f24
0x1e:pri[6]     offs=0x1f31
0x20:pri[7]     offs=0x1f3e
0x22:pri[8]     offs=0x1f4b
0x24:pri[9]     offs=0x1f57
0x26:pri[10]    offs=0x1f65
0x28:pri[11]    offs=0x1f72
0x2a:pri[12]    offs=0x1f7e
0x2c:pri[13]    offs=0x1f8a
block_row_dump:
tab 0, row 0, @0x1ec3
tl: 15 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 3]  c2 4a 46
col  1: [ 7]  62 65 69 6a 69 6e 67  

   通过scn的对比，发现  scn 0x0000.0002bc44为最大的（该事务槽中），通过scn对应的uba，找出前镜像。

 计算：  

   0x008015a2.0026.36   

file 2 block 5538

alter system dump datafile 2 block 5538

********************************************************************************
UNDO BLK: 
xid: 0x0001.026.00000070  seq: 0x26  cnt: 0x41  irb: 0x41  icl: 0x0   flg: 0x0000
 
 Rec Offset      Rec Offset      Rec Offset      Rec Offset      Rec Offset
---------------------------------------------------------------------------
0x01 0x1f9c     0x02 0x1f68     0x03 0x1f2c     0x04 0x1eec     0x05 0x1ea4    
0x06 0x1e44     0x07 0x1d64     0x08 0x1ce0     0x09 0x1c80     0x0a 0x1c18    
0x0b 0x1bdc     0x0c 0x1b7c     0x0d 0x1b38     0x0e 0x1ae8     0x0f 0x1a90    
0x10 0x1a4c     0x11 0x19fc     0x12 0x1970     0x13 0x18f0     0x14 0x1870    
0x15 0x181c     0x16 0x17d0     0x17 0x1728     0x18 0x1680     0x19 0x15d8    
0x1a 0x152c     0x1b 0x14c0     0x1c 0x1454     0x1d 0x13d8     0x1e 0x1310    
0x1f 0x1268     0x20 0x11bc     0x21 0x1150     0x22 0x10cc     0x23 0x1068    
0x24 0x0fbc     0x25 0x0f40     0x26 0x0ed4     0x27 0x0e58     0x28 0x0dd4    
0x29 0x0d78     0x2a 0x0d0c     0x2b 0x0cac     0x2c 0x0c4c     0x2d 0x0ba0    
0x2e 0x0b1c     0x2f 0x0abc     0x30 0x0a5c     0x31 0x09d0     0x32 0x0994    
0x33 0x0934     0x34 0x08d4     0x35 0x0880     0x36 0x07c8     0x37 0x0728    
0x38 0x0674     0x39 0x05cc     0x3a 0x0524     0x3b 0x047c     0x3c 0x03d4    
0x3d 0x032c     0x3e 0x0280     0x3f 0x0214     0x40 0x01a8     0x41 0x013c    
 

* Rec #0x36  slt: 0x0e  objn: 9783(0x00002637)  objd: 9783  tblspc: 4(0x00000004)
*       Layer:  11 (Row)   opc: 1   rci 0x00  
Undo type:  Regular undo    Begin trans    Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
rdba: 0x00000000
*-----------------------------
uba: 0x008015a2.0026.31 ctl max scn: 0x0000.0002ab19 prv tx scn: 0x0000.0002ab23
txn start scn: scn: 0x0000.0002bc3d logon user: 27
 prev brb: 8394130 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x04  ver: 0x01 
op: L  itl: xid:  0x0009.021.0000006d uba: 0x00800bea.002f.0e
                      flg: C---    lkc:  0     scn: 0x0000.0002bc32
Array Update of 1 rows:
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 10
ncol: 2 nnew: 1 size: 0
KDO Op code:  21 row dependencies Disabled
  xtype: XAxtype KDO_KDOM2 flags: 0x00000080  bdba: 0x01000156  hdba: 0x01000153
itli: 1  ispac: 0  maxfr: 4858
vect = 4
col  1: [ 7]  6a 69 61 6e 67 78 69    
 

SQL> select dump('jiangxi',16)from dual;

DUMP('JIANGXI',16)
----------------------------------
Typ=96 Len=7: 6a,69,61,6e,67,78,69

 

此时的ITL为

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0001.00e.00000070  0x008015a2.0026.36  --U-    1  fsc 0x0000.0002bc44  (OLD)

0x01   0x0009.021.0000006d  0x00800bea.002f.0e  C---    0  scn 0x0000.0002bc32
0x02   0x0002.020.0000006a  0x00800e5a.0049.0f  C---    0  scn 0x0000.0002bc3d

 

在和scn为179253进行比较，发现SCN为0x0000.0002bc3d 在事务槽中为最大的，从scn对应的uba进行回滚。。

计算
0x00800e5a.0049.0f  
 
  file 2 block 3674

alter system dump 2 block  3674

dump的记录  seq：49  rec：0x0f
UNDO BLK:
xid: 0x0002.023.0000006a  seq: 0x49  cnt: 0x4e  irb: 0x4e  icl: 0x0   flg: 0x0000

 Rec Offset      Rec Offset      Rec Offset      Rec Offset      Rec Offset
---------------------------------------------------------------------------
0x01 0x1f70     0x02 0x1f18     0x03 0x1ec4     0x04 0x1e78     0x05 0x1e44
0x06 0x1de4     0x07 0x1d88     0x08 0x1ca8     0x09 0x1c44     0x0a 0x1b98
0x0b 0x1b14     0x0c 0x1ab4     0x0d 0x1a54     0x0e 0x19c8     0x0f 0x193c
0x10 0x18d0     0x11 0x1860     0x12 0x181c     0x13 0x17bc     0x14 0x1778
0x15 0x1718     0x16 0x16d4     0x17 0x1674     0x18 0x1630     0x19 0x15c0
0x1a 0x157c     0x1b 0x151c     0x1c 0x14d8     0x1d 0x1478     0x1e 0x1434
0x1f 0x13d4     0x20 0x1390     0x21 0x1330     0x22 0x12ec     0x23 0x128c
0x24 0x1248     0x25 0x11e8     0x26 0x11a4     0x27 0x1144     0x28 0x1100
0x29 0x10a0     0x2a 0x1010     0x2b 0x0f2c     0x2c 0x0dfc     0x2d 0x0d18
0x2e 0x0c78     0x2f 0x0bc8     0x30 0x0b68     0x31 0x0ac8     0x32 0x0a8c
0x33 0x0a14     0x34 0x09d0     0x35 0x0968     0x36 0x0924     0x37 0x08bc
0x38 0x0878     0x39 0x0810     0x3a 0x07cc     0x3b 0x0764     0x3c 0x0720
0x3d 0x06a8     0x3e 0x0664     0x3f 0x05fc     0x40 0x05b8     0x41 0x0550
0x42 0x050c     0x43 0x0494     0x44 0x0450     0x45 0x03e8     0x46 0x03a4
0x47 0x033c     0x48 0x02f8     0x49 0x0290     0x4a 0x024c     0x4b 0x01e4
0x4c 0x01a0     0x4d 0x0138     0x4e 0x00fc   

* Rec #0xf  slt: 0x20  objn: 9783(0x00002637)  objd: 9783  tblspc: 4(0x00000004)
*       Layer:  11 (Row)   opc: 1   rci 0x00 
Undo type:  Regular undo    Begin trans    Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
rdba: 0x00000000
*-----------------------------
uba: 0x00800e5a.0049.0e ctl max scn: 0x0000.0002ab1b prv tx scn: 0x0000.0002ab25
txn start scn: scn: 0x0000.0002bc32 logon user: 27
 prev brb: 8392271 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x03  ver: 0x01
op: Z
KDO Op code: URP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x01000156  hdba: 0x01000153
itli: 2  ispac: 0  maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 12
ncol: 2 nnew: 1 size: 2
col  1: [ 9]  67 75 61 6e 67 7a 68 6f 75  

 

SQL> select dump('guagnzhou',16)from dual;

DUMP('GUAGNZHOU',16)
----------------------------------------
Typ=96 Len=9: 67,75,61,67,6e,7a,68,6f,75