了解你所不知道的SMON功能(八):Transaction Recover

SMON的作用还包括启动(startup)时的Transaction Recover:

SMON: enabling cache recovery
Archived Log entry 87 added for thread 1 sequence 58 ID 0xa044e7d dest 1:
[15190] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:421305354 end:421305534 diff:180 (1 seconds)
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery

<了解你所不知道的SMON功能(五):Recover Dead transaction>中我们介绍了SMON清理死事务的功能,数据库打开时由SMON所启动的TX recovery与Recover Dead transaction所作的工作是类似的,fast_start_parallel_rollback参数决定了SMON在回滚事务时使用的并行度(详见原帖)。

但是请注意,实际startup时的TX recovery要比普通的Dead transaction recover复杂的多。其大致步骤如下:

1.在SYSTEM回滚段(Undo Segment Number为o)中的Active Transaction将被第一时间优先回滚

2.在其他回滚段中的Active Transaction将被标记为’DEAD’

3.之后SMON将扫描非SYSTEM的回滚段并实施对死事务的回滚,其典型的调用堆栈stack call如下:

 kturec <- kturax <- ktprbeg <- ktmmon <- ktmSmonMain

4.SMON仍将扫描_OFFLINE_ROLLBACK_SEGMENTS所列出的回滚段,但对其上的Active Transaction不做回滚,若发现corrupted则只报错

5.SMON将忽略_CORRUPTED_ROLLBACK_SEGMENTS所列出的回滚段,甚至在启动时不做扫描,所有指向这类回滚段地事务都被认为已经提交了。

具体SMON在对ktuini的函数调用中启动Transaction Recover,该function的经典stack call如下:

adbdrv -> ktuini -> ktuiup -> kturec -> kturrt
or
adbdrv -> ktuini -> ktuiof -> ktunti -> kqrpre -> kqrpre1 -> ktuscr

其中由ktuiof函数判断_OFFLINE_ROLLBACK_SEGMENTS和_CORRUPTED_ROLLBACK_SEGMENTS的值,并将这些重要的回滚段信息转存到fixed array。
注意SYSTEM回滚段是bootstrap的重要对象,所以我们不能指定system rollback segment为offline或者corrupted。

SMON执行Transaction Recover时的大致步骤如下:

调用ktuiof保存_OFFLINE_ROLLBACK_SEGMENTS和_CORRUPTED_ROLLBACK_SEGMENTS所列出的回滚段

调用ktuiup函数,开始恢复回滚段上的死事务

第一优先级地恢复USN=0的SYSTEM回滚段上的事务,由kturec函数控制

对undo$字典基表上的记录循环:

FOR usn in undo$ loop
IF usn==0

恢复SYSTEM回滚段上在第一轮中未完成的事务,同样由kturec控制;

ELSE

将任何活动事务标记为DEAD,由kturec控制;

USN++

end loop

相关诊断事件

与Transaction Recover密切相关的诊断事件有不少,其中最为重要的是event 10013和10015;10015事件对于普通的dead transaction rollback也有效,之所以把该事件列在<Transaction Recover>功能内,是因为我们经常在非正常手段打开数据库时会遇到一些ORA-600[4xxx]的内部错误,可以通过10015事件了解相关的usn,然后以_SYSSMU(USN#)$的形式加入到_CORRUPTED_ROLLBACK_SEGMENTS以绕过内部错误(注意在11g中不能这样做了):

  1. 10013, 00000, “Instance Recovery”
  2. 10015, 00000, “Undo Segment Recovery”
Event 10013:Monitor transaction recovery during startup

SQL> alter system set event='10013 trace name context forever,level 10' scope=spfile;

Event 10015:Dump undo segment headers before and after transaction recovery

SQL> alter system set event='10015 trace name context forever,level 10' scope=spfile;
System altered.

======================10015 sample trace===========================
UNDO SEG (BEFORE RECOVERY): usn = 0  Extent Control Header
  -----------------------------------------------------------------
  Extent Header:: spare1: 0      spare2: 0      #extents: 6      #blocks: 47
                  last map  0x00000000  #maps: 0      offset: 4128
      Highwater::  0x0040000b  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: 6    obj#: 0      flag: 0x40000000
  Extent Map
  -----------------------------------------------------------------
   0x0040000a  length: 7
   0x00400011  length: 8
   0x00400181  length: 8
   0x00400189  length: 8
   0x00400191  length: 8
   0x00400199  length: 8      

  TRN CTL:: seq: 0x012c chd: 0x0033 ctl: 0x0026 inc: 0x00000000 nfb: 0x0001
            mgc: 0x8002 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
            uba: 0x0040000b.012c.1b scn: 0x0000.021fa595
Version: 0x01
  FREE BLOCK POOL::
    uba: 0x0040000b.012c.1b ext: 0x0  spc: 0x4a0
    uba: 0x00000000.005c.07 ext: 0x2  spc: 0x1adc
    uba: 0x00000000.0034.37 ext: 0x4  spc: 0x550
    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
  ------------------------------------------------------------------------------------------------
   0x00    9    0x00  0x025d  0x002b  0x0000.02215c0b  0x0040000b  0x0000.000.00000000  0x00000001   0x00000000   0x0000
   0x01    9    0x00  0x025d  0x0006  0x0000.0220a58c  0x0040000a  0x0000.000.00000000  0x00000001   0x00000000   0x0000
   0x02    9    0x00  0x025d  0x000e  0x0000.0220a58a  0x0040000a  0x0000.000.00000000  0x00000001   0x00000000   0x0000
   0x03    9    0x00  0x025d  0x000f  0x0000.02215be4  0x0040000b  0x0000.000.00000000  0x00000001   0x00000000   0x0000
   0x04    9    0x00  0x025d  0x0008  0x0000.0220a57a  0x0040000a  0x0000.000.00000000  0x00000001   0x00000000   0x0000
   0x05    9    0x00  0x025d  0x0056  0x0000.0220a583  0x0040000a  0x0000.000.00000000  0x00000001   0x00000000   0x0000
   0x06    9    0x00  0x025d  0x0017  0x0000.0220a58d  0x0040000a  0x0000.000.00000000  0x00000001   0x00000000   0x0000
   0x07    9    0x00  0x025d  0x0050  0x0000.0220a57f  0x0040000a  0x0000.000.00000000  0x00000001   0x00000000   0x0000
   0x08    9    0x00  0x025d  0x0061  0x0000.0220a57c  0x0040000a  0x0000.000.00000000  0x00000001   0x00000000   0x0000
   0x09    9    0x00  0x025d  0x0013  0x0000.02215c01  0x0040000b  0x0000.000.00000000  0x00000001   0x00000000   0x0000
   0x0a    9    0x00  0x025d  0x0022  0x0000.02215bf7  0x0040000b  0x0000.000.00000000  0x00000001   0x00000000   0x0000
   0x0b    9    0x00  0x025d  0x0014  0x0000.02215bdd  0x0040000a  0x0000.000.00000000  0x00000001   0x00000000   0x0000
   0x0c    9    0x00  0x025c  0x003a  0x0000.021ff3fa  0x004001a0  0x0000.000.00000000  0x00000001   0x00000000   0x0000
   0x0d    9    0x00  0x025d  0x0010  0x0000.02215c05  0x0040000b  0x0000.000.00000000  0x00000001   0x00000000   0x0000
   0x0e    9    0x00  0x025d  0x0001  0x0000.0220a58b  0x0040000a  0x0000.000.00000000  0x00000001   0x00000000   0x0000
   0x0f    9    0x00  0x025d  0x001c  0x0000.02215be6  0x0040000b  0x0000.000.00000000  0x00000001   0x00000000   0x0000
   0x10    9    0x00  0x025d  0x002a  0x0000.02215c07  0x0040000b  0x0000.000.00000000  0x00000001   0x00000000   0x0000
   0x11    9    0x00  0x025d  0x0025  0x0000.02215bf2  0x0040000b  0x0000.000.00000000  0x00000001   0x00000000   0x0000
   0x12    9    0x00  0x025d  0x0018  0x0000.02215bee  0x0040000b  0x0000.000.00000000  0x00000001   0x00000000   0x0000
   0x13    9    0x00  0x025d  0x000d  0x0000.02215c03  0x0040000b  0x0000.000.00000000  0x00000001   0x00000000   0x0000
   0x14    9    0x00  0x025d  0x005a  0x0000.02215bdf  0x0040000a  0x0000.000.00000000  0x00000001   0x00000000   0x0000
   0x15    9    0x00  0x025d  0x0058  0x0000.0220a587  0x0040000a  0x0000.000.00000000  0x00000001   0x00000000   0x0000
   0x16    9    0x00  0x025d  0x000a  0x0000.02215bf6  0x0040000b  0x0000.000.00000000  0x00000001   0x00000000   0x0000
   0x17    9    0x00  0x025d  0x000b  0x0000.0220a58e  0x0040000a  0x0000.000.00000000  0x00000001   0x00000000   0x0000
   0x18    9    0x00  0x025d  0x0011  0x0000.02215bf0  0x0040000b  0x0000.000.00000000  0x00000001   0x00000000   0x0000
   0x19    9    0x00  0x025c  0x0044  0x0000.021ff410  0x004001a0  0x0000.000.00000000  0x00000001   0x00000000   0x0000
   0x1a    9    0x00  0x025d  0x005c  0x0000.02215bea  0x0040000b  0x0000.000.00000000  0x00000001   0x00000000   0x0000
   0x1b    9    0x00  0x025d  0x001d  0x0000.02215bfd  0x0040000b  0x0000.000.00000000  0x00000001   0x00000000   0x0000
   0x1c    9    0x00  0x025d  0x001a  0x0000.02215be8  0x0040000b  0x0000.000.00000000  0x00000001   0x00000000   0x0000
   0x1d    9    0x00  0x025d  0x0009  0x0000.02215bff  0x0040000b  0x0000.000.00000000  0x00000001   0x00000000   0x0000
   0x1e    9    0x00  0x025d  0x005f  0x0000.02215bfa  0x0040000b  0x0000.000.00000000  0x00000001   0x00000000   0x0000
   0x1f    9    0x00  0x025c  0x0032  0x0000.021fa59b  0x0040019f  0x0000.000.00000000  0x00000001   0x00000000   0x0000
   0x20    9    0x00  0x025c  0x0038  0x0000.021fa599  0x0040019f  0x0000.000.00000000  0x00000001   0x00000000   0x0000

可用以下命令分析smon的10015 trace,并列出相关回滚段名

[oracle@rh2 bdump]$ cat g10r2_smon_18738.trc|grep "usn ="|grep -v "usn = 0" |awk '{print "_SYSSMU"$7"$"}'|sort -u
_SYSSMU1$
_SYSSMU10$
_SYSSMU2$
_SYSSMU3$
_SYSSMU4$
_SYSSMU5$
_SYSSMU6$
_SYSSMU7$
_SYSSMU8$
_SYSSMU9$

沪ICP备14014813号-2

沪公网安备 31010802001379号