transaction

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

2011/07/15 by admin Leave a Comment

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中不能这样做了)：

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$

Filed Under: Oracle, Oracle Internal Research内部原理研究 Tagged With: smon, transaction, tx recovery

Know more about commit

2011/07/13 by admin Leave a Comment

COMMIT操作是RDBMS中事务结束的标志，在Oracle中与commit紧密相关的是SCN(System Change Number)。

引入SCN的最根本目的在于：

为读一致性所用
为redolog中的记录排序，以及恢复

SCN由SCN Base和Scn Wrap组成，是一种6个字节的结构(structure)。其中SCN Base占用4个字节，而SCN wrap占用2个字节。但在实际存储时SCN-like的stucture常会占用8个字节。

 ub4 kscnbas
 ub2 kscnwrp

struct kcvfhcrs, 8 bytes                 @100                              Creation Checkpointed at scn
      ub4 kscnbas                        @100      0x000a8849
      ub2 kscnwrp                        @104      0x0000

在Oracle中一个事务的开始包含以下操作:

绑定一个可用的rollback segment
在事务表(transaction table)上分配一个必要的槽位
从rollback segment中分配undo block

注意system rollback segment是一种特殊的回滚段，在10g以后普通回滚段的类型都变成了”TYPE2 UNDO”，而唯有system rollback segment的类型仍为”ROLLBACK”，这是由其特殊性造就的:

SQL> col segment_name for a20
SQL> col rollback for a20
SQL> select segment_name,segment_type from dba_segments where segment_type='ROLLBACK';

SEGMENT_NAME         SEGMENT_TYPE
-------------------- ------------------
SYSTEM               ROLLBACK

System rollback segment面向的是SYSTEM表空间上数据字典对象相关事务的数据，以及由对用户数据产生的递归SQL调用所产生的数据。

Oracle不使用基于内存锁管理器的行锁，Oracle中的row lock是基于数据块的。数据块中的Interested Transaction List(ITL)是行锁的重要标志。
ITL的分配遵循以下的原则:

找出未被使用的ITL
找出最老的已经事务提交的ITL
做部分的块清理，直到有可用的ITL
扩展ITL区域，一条ITL占用24字节

当事务提交COMMIT时，需要完成以下步骤的操作:

得到一个SCN值
使用得到的SCN更新事务表中的槽位
在redo log buffer中创建一条commit记录
将redo log buffer刷新到磁盘上的在线日志文件
释放表和行上的锁(may cause delayed block cleanout)

Filed Under: Oracle, Oracle Internal Research内部原理研究 Tagged With: commit, ITL, Oracle Lock锁机制, redo, rollback, transaction, undo

监控一个大事务的回滚

2011/05/25 by admin 3 Comments

我们在大的事务失败时往往面临长时间的回滚，在回滚期间表会被加以TM-3 SX sub-exclusive锁，此时一般我们是无法针对表实施DDL操作的。长时间的大事务回滚可能耗尽我们的耐心，不过我们还是有办法预估何时回滚能够完成的，参考中的脚本<Script:when transaction will finish rollback>中的脚本，注意该脚本需要访问x$ktuxe内部视图，所以需要以sysdba身份方能执行。

SQL> select * from v$lock where type in ('TM','TX');

ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
0ED0F30C 0ED0F33C          9 TM      13865          0          3          0       3757          0
2C3975FC 2C39763C          9 TX      65557        677          6          0       3757          0

SQL> select object_name,object_type from dba_objects where object_id=13865;

OBJECT_NAM OBJECT_TYPE
---------- -------------------
SAMPLE     TABLE

因为表上存在TM-3锁，所以此时是无法对表执行需要持有TM-6 exclusive排它锁的DDL操作的

SQL> drop table sample;
drop table sample
           *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

SQL> select * from sample where rownum=1 for update nowait;
select * from sample where rownum=1 for update nowait
              *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

rollback完成之前相关行上的row level lock不会被释放，因此dml操作会被block

SQL> select xidusn,xidslot,xidsqn,status from v$transaction;

    XIDUSN    XIDSLOT     XIDSQN STATUS
---------- ---------- ---------- ----------------
         1         21        677 ACTIVE

从v$tranasction视图中可以看到事务1.21.677处于active状态

使用set time on命令显示当前时间

SQL> set time on;

20:54:26 SQL>

set serveroutput on
set feedback off
prompt
prompt Looking for transactions that are rolling back ...
prompt

declare
  cursor tx is
    select
      s.username,
      t.xidusn,
      t.xidslot,
      t.xidsqn,
      x.ktuxesiz
    from
      sys.x$ktuxe  x,
      sys.v_$transaction  t,
      sys.v_$session  s
    where
      x.inst_id = userenv('Instance') and
      x.ktuxesta = 'ACTIVE' and
      x.ktuxesiz > 1 and
      t.xidusn = x.ktuxeusn and
      t.xidslot = x.ktuxeslt and
      t.xidsqn = x.ktuxesqn and
      s.saddr = t.ses_addr;
  user_name  varchar2(30);
  xid_usn    number;
  xid_slot   number;
  xid_sqn    number;
  used_ublk1 number;
  used_ublk2 number;
begin
  open tx;
  loop
    fetch tx into user_name, xid_usn, xid_slot, xid_sqn, used_ublk1;
    exit when tx%notfound;
    if tx%rowcount = 1
    then
      sys.dbms_lock.sleep(10);
    end if;
    select
      sum(ktuxesiz)
    into
      used_ublk2
    from
      sys.x$ktuxe
    where
      inst_id = userenv('Instance') and
      ktuxeusn = xid_usn and
      ktuxeslt = xid_slot and
      ktuxesqn = xid_sqn and
      ktuxesta = 'ACTIVE';
    if used_ublk2 < used_ublk1
    then
      sys.dbms_output.put_line(
        user_name ||
        '''s transaction ' ||
        xid_usn  || '.' ||
        xid_slot || '.' ||
        xid_sqn  ||
        ' will finish rolling back at approximately ' ||
        to_char(
          sysdate + used_ublk2 / (used_ublk1 - used_ublk2) / 6 / 60 / 24,
          'HH24:MI:SS DD-MON-YYYY'
        )
      );
    end if;
  end loop;
  if user_name is null
  then
    sys.dbms_output.put_line('No transactions appear to be rolling back.');
  end if;
end;
/

MACLEAN's transaction 1.21.677 will finish rolling back at approximately 21:46:45 25-MAY-2011

以上脚本给出了估计的1.21.677事务的回滚结束时间为21:46:45，即还有50分钟，
注意这只是根据之前单位时间内rollback的进度所得到的一个估算值，所以并不精确，但还是可以做为一个参考

再次运行以上脚本，会发现2次的结果不一定一致

SQL> /
MACLEAN's transaction 1.21.677 will finish rolling back at approximately 21:35:47 25-MAY-2011

在产品数据库中应当尽可能避免出现大事务回滚的现象，因为除去lock的影响外，large transaction rollback还会导致CPU使用率大幅上升并产生往往比事务本身所产生的更多的redo重做日志记录。

Filed Under: Oracle Tagged With: monitor, rollback, transaction

Script:when transaction will finish rollback

2007/04/19 by admin Leave a Comment

-------------------------------------------------------------------------------
--
-- Script:	rolling_back.sql
-- Purpose:	to predict when transactions will finish rolling back
-- For:		9.0+
--
-- Copyright:	(c) Ixora Pty Ltd
-- Author:	Steve Adams
--
-------------------------------------------------------------------------------
@save_sqlplus_settings

set serveroutput on
set feedback off
prompt
prompt Looking for transactions that are rolling back ...
prompt

declare
  cursor tx is
    select
      s.username,
      t.xidusn,
      t.xidslot,
      t.xidsqn,
      x.ktuxesiz
    from
      sys.x$ktuxe  x,
      sys.v_$transaction  t,
      sys.v_$session  s
    where
      x.inst_id = userenv('Instance') and
      x.ktuxesta = 'ACTIVE' and
      x.ktuxesiz > 1 and
      t.xidusn = x.ktuxeusn and
      t.xidslot = x.ktuxeslt and
      t.xidsqn = x.ktuxesqn and
      s.saddr = t.ses_addr;
  user_name  varchar2(30);
  xid_usn    number;
  xid_slot   number;
  xid_sqn    number;
  used_ublk1 number;
  used_ublk2 number;
begin
  open tx;
  loop
    fetch tx into user_name, xid_usn, xid_slot, xid_sqn, used_ublk1;
    exit when tx%notfound;
    if tx%rowcount = 1
    then
      sys.dbms_lock.sleep(10);
    end if;
    select
      sum(ktuxesiz)
    into
      used_ublk2
    from
      sys.x$ktuxe
    where
      inst_id = userenv('Instance') and
      ktuxeusn = xid_usn and
      ktuxeslt = xid_slot and
      ktuxesqn = xid_sqn and
      ktuxesta = 'ACTIVE';
    if used_ublk2 < used_ublk1
    then
      sys.dbms_output.put_line(
        user_name ||
        '''s transaction ' ||
        xid_usn  || '.' ||
        xid_slot || '.' ||
        xid_sqn  ||
        ' will finish rolling back at approximately ' ||
        to_char(
          sysdate + used_ublk2 / (used_ublk1 - used_ublk2) / 6 / 60 / 24,
          'HH24:MI:SS DD-MON-YYYY'
        )
      );
    end if;
  end loop;
  if user_name is null
  then
    sys.dbms_output.put_line('No transactions appear to be rolling back.');
  end if;
end;
/

prompt
@restore_sqlplus_settings

Filed Under: Oracle, Oracle Internal Research内部原理研究, Oracle脚本script Tagged With: Oracle脚本script, rollback, transaction, x$ktuxe