当前位置:首页 > 开发 > 数据库 > 正文

SCN之fast cleanout

发表于: 2014-06-07   作者:czmmiao   来源:转载   浏览次数:
out
摘要: SQL> select dbms_rowid.rowid_relative_fno(rowid) file#,           dbms_rowid.rowid_block_number(rowid) block#,      &

SQL> select dbms_rowid.rowid_relative_fno(rowid) file#,
           dbms_rowid.rowid_block_number(rowid) block#,
           dbms_rowid.rowid_row_number(rowid) row# from scn_test ;
     FILE#     BLOCK#       ROW#
---------- ---------- ----------
         6         31          0
         6         31          1
SQL> select ora_rowscn,t.* from scn_test t ;
ORA_ROWSCN  OBJECT_ID NAME
---------- ---------- ----------
   5052657          1 synonym
   5052657          2 view
-- dump 数据块
SQL> oradebug setmypid
Statement processed.
SQL> alter system dump datafile 6 block 31;
System altered.
SQL> oradebug tracefile_name
/sharevg/oracle/admin/thinking/udump/thinking_ora_11534376.trc
数据块本身有三处记录的相应的SCN:数据块头的SCN(block scn)、ktbbh结构下的 kscnbas,kscnwrp(cleanout scn)、ITL信息中的scn/fsc(commit scn 有时候会是control scn)
1.如下查看dump出的数据块:
-- block scn
*** 2013-06-23 21:44:15.119
*** ACTION NAME:() 2013-06-23 21:44:15.104
*** MODULE NAME:(sqlplus@node1svr (TNS V1-V3)) 2013-06-23 21:44:15.104
*** SERVICE NAME:(SYS$USERS) 2013-06-23 21:44:15.104
*** SESSION ID:(310.103) 2013-06-23 21:44:15.104
Start dump data blocks tsn: 9 file#: 6 minblk 31 maxblk 31
buffer tsn: 9 rdba: 0x0180001f (6/31)
scn: 0x0000.004d18f1 seq: 0x02 flg: 0x06 tail: 0x18f10602
frmt: 0x02 chkval: 0x9524 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
-- block scn (cache layer记录的block scn)
BBED> p kcbh
struct kcbh, 20 bytes                       @0
   ub1 type_kcbh                            @0        0x06
   ub1 frmt_kcbh                            @1        0xa2
   ub1 spare1_kcbh                          @2        0x00
   ub1 spare2_kcbh                          @3        0x00
   ub4 rdba_kcbh                            @4        0x0180001f
   ub4 bas_kcbh                             @8        0x004d18f1
   ub2 wrp_kcbh                             @12       0x0000
-- cleanout scn (csc)
BBED> p ktbbh
struct ktbbh, 72 bytes                      @20
   ub1 ktbbhtyp                             @20       0x01 (KDDBTDATA)
   union ktbbhsid, 4 bytes                  @24
      ub4 ktbbhsg1                          @24       0x0000fe04
      ub4 ktbbhod1                          @24       0x0000fe04
   struct ktbbhcsc, 8 bytes                 @28
      ub4 kscnbas                           @28       0x004d18cf
      ub2 kscnwrp                           @32       0x0000
-- ITL 部分的SCN(scn/fsc)
Block header dump:  0x0180001f
 Object id on Block? Y
 seg/obj: 0xfe04  csc: 0x00.4d18cf  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x1800019 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0005.019.000006a7  0x008007c8.04a4.34  C---    0  scn 0x0000.004d1733
0x02   0x0002.01b.000005e5  0x008002eb.0312.06  --U-    1  fsc 0x0000.004d18f1
-- 显示为U,表示是up bound scn(undo header 中的control scn)
可见目前三者是相同的 4d18f1=5052657
block scn 记录的是block 最后一次改变时的SCN.一般情况下会等于ITL上最新的commit SCN(因为可能有delayed cleanout block发生)。
2. 什么时候发生delay block cleanout :
As a transaction modifies blocks, the buffer header addresses are recorded in a commit cleanout data structure, which can hold pointers to up to 10% of the buffers in the cache. When the transaction commits, it traverses its commit cleanout data structure in reverse order and cleans out its ITL entries. The commit cleanout of a block can fail if the block has already been written to disk, or for several other reasons. If a commit cleanout fails for any reason, then the block is written to disk with the ITL and row-level locks not yet cleaned out. This is where delayed block cleanout comes in.
也就是说data buffer有一个记录modified block 的list,用来记录每个transaction更改的block,而这个List最多只能记录占data buffer 10%的block,当事务提交时,这部分记录在list中的block会做fast commit cleanout,没有记录到的list中的块,会在没有清除ITL 中lck标记的情况下 写入到磁盘的datafile中,这部分块在下次再访问时,会做一次delay block cleanout,而刷新block块中的三处SCN的值。
事务没有提交前,block scn是不会改变的,因为它只记录block改变时的SCN。
fast commit cleanout :会将事务提交时的SCN作为commit scn,更新block 上itl 和undo segment header上transaction table 相应slot上的scn, 并修改block scn,这时候三个是一致的。
delayed block cleanout : 当发生时,已经commit的事务对应的undo segment header上transaction table 相应slot还没有被覆盖,可以在slot上找到确切的commit scn,用这个SCN更新block scn和ITL上的scn/fsc值 。如果slot已经被覆盖了,那么会用undo segment header中的control scn来做为commit scn更新ITL中的scn ,也就是所谓的upper bound scn. 同时更新block scn为 delayed block cleanout 发生时的SCN。
block header中的ITL信息有一列是flag,其不同值的含义如下 :
—- = transaction is active, or committedpending cleanout
C— = transaction has been committed andlocks cleaned out
-B– = this undo record contains the undofor this ITL entry
–U- = transaction committed (maybe longago); SCN is an upper bound
—T = transaction was still active atblock cleanout SCN
3. control scn :
control scn 存在于undo segment header中,这个SCN 是最近一个被重用的事务槽的SCN,事务槽的重用是按事务的先后顺序重用的。也就是这个scn是目前在这个undo header中能确定的提交的最小的SCN。
如果现在有一个事务,发生的大量的更新和commit,导致其相应的transaction table slot被重用,切其对应的uba前镜像也被重用,这时,如果再有一个事务查询前面事务更新的数据块时,会用查询时的SCN(snapshot scn) ,根据ITL上xid找到transaction table 相应的slot,由于相应的slot已经覆盖,无法获得slot的SCN,和查询时的SCN相比较,这时就去和control scn比较,因为control scn是目前这个segment上能确认的提交的最小的SCN,如果这时snapshot scn还是小于这个control scn, 则直接 给也ORA-1555的错误,因为oracle 实在无法构造能一致读的数据块了。如果这时snapshot scn大于control scn,则直接使用这个数据块,因为snapshot scn肯定也大于事务提交时的SCN。因为control scn 肯定大于之前事务提交时的SCN。
—————————————————————————————————————
A :下面对fast cleanout block 验证,commit时,会同时更新block_scn、itl scn、transaction table slot scn
1.执行如下过程:
SQL> ! cat csc_test.sql
select current_scn from v$database;
create table csc_test (id number) ;
select current_scn from v$database;
insert into csc_test values (1);
select current_scn from v$database;
commit;
select current_scn from v$database;
insert into csc_test values (2);
select current_scn from v$database;
commit;
select current_scn from v$database;
alter system checkpoint;
select current_scn from v$database;
SQL> @csc_test.sql
CURRENT_SCN
-----------
    5622039
Table created.
CURRENT_SCN
-----------
    5622055
1 row created.
CURRENT_SCN
-----------
    5622059
Commit complete.
CURRENT_SCN
-----------
    5622062
1 row created.
CURRENT_SCN
-----------
    5622063
Commit complete.
CURRENT_SCN
-----------
    5622066
System altered.
CURRENT_SCN
-----------
    5622068
-- 可知最一个commit scn 肯定在5622063和5622066之间
-- dump 这个block:
SQL> select dbms_rowid.rowid_relative_fno(rowid) file#,
  2         dbms_rowid.rowid_block_number(rowid) block# from csc_test;
     FILE#     BLOCK#
---------- ----------
         6        150
         6        150
SQL> conn / as sysdba
Connected.
SQL> oradebug setmypid
Statement processed.
SQL> alter system dump datafile 6 block 150;
System altered.
SQL> oradebug tracefile_name
/sharevg/oracle/admin/thinking/udump/thinking_ora_9175064.trc
-- 查看dump文件的 itl 信息:
*** SESSION ID:(308.2234) 2013-07-02 09:00:25.734
Start dump data blocks tsn: 9 file#: 6 minblk 150 maxblk 150
buffer tsn: 9 rdba: 0x01800096 (6/150)
scn: 0x0000.0055c930 seq: 0x02 flg: 0x06 tail: 0xc9300602     --- 块SCN(0x0000.0055c930)
frmt: 0x02 chkval: 0x34dc type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00000001103DA000 to 0x00000001103DC000
………………
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0001.02c.00000718  0x00800685.0412.05  --U-    1  fsc 0x0000.0055c92c
0x02   0x0008.02e.0000080b  0x00800f7f.0497.2e  --U-    1  fsc 0x0000.0055c930
data_block_dump,data header at 0x1103da064
-- BBED查看 块150上的ITL信息:
BBED> set dba 6,150
        DBA             0x01800096 (25165974 6,150)
BBED> p kcbh
struct kcbh, 20 bytes                       @0
   ub1 type_kcbh                            @0        0x06
   ub1 frmt_kcbh                            @1        0xa2
   ub1 spare1_kcbh                          @2        0x00
   ub1 spare2_kcbh                          @3        0x00
   ub4 rdba_kcbh                            @4        0x01800096
   ub4 bas_kcbh                             @8        0x0055c930  -- 块scn (block scn)
   ub2 wrp_kcbh                             @12       0x0000
   ub1 seq_kcbh                             @14       0x02
   ub1 flg_kcbh                             @15       0x06 (KCBHFDLC, KCBHFCKV)
   ub2 chkval_kcbh                          @16       0x34dc
   ub2 spare3_kcbh                          @18       0x0000
BBED> p ktbbh
struct ktbbh, 72 bytes                      @20
   ub1 ktbbhtyp                             @20       0x01 (KDDBTDATA)
   union ktbbhsid, 4 bytes                  @24
      ub4 ktbbhsg1                          @24       0x00010157
      ub4 ktbbhod1                          @24       0x00010157
   struct ktbbhcsc, 8 bytes                 @28
      ub4 kscnbas                           @28       0x0055c92a
      ub2 kscnwrp                           @32       0x0000
   b2 ktbbhict                              @36       2
   ub1 ktbbhflg                             @38       0x32 (NONE)
   ub1 ktbbhfsl                             @39       0x00
   ub4 ktbbhfnx                             @40       0x01800091
   struct ktbbhitl[0], 24 bytes             @44
      struct ktbitxid, 8 bytes              @44
         ub2 kxidusn                        @44       0x0001
         ub2 kxidslt                        @46       0x002c
         ub4 kxidsqn                        @48       0x00000718
      struct ktbituba, 8 bytes              @52
         ub4 kubadba                        @52       0x00800685
         ub2 kubaseq                        @56       0x0412
         ub1 kubarec                        @58       0x05
      ub2 ktbitflg                          @60       0x2001 (KTBFUPB)
      union _ktbitun, 2 bytes               @62
         b2 _ktbitfsc                       @62       0
         ub2 _ktbitwrp                      @62       0x0000
      ub4 ktbitbas                          @64       0x0055c92c  -- 第一次commit scn
   struct ktbbhitl[1], 24 bytes             @68
      struct ktbitxid, 8 bytes              @68
         ub2 kxidusn                        @68       0x0008
         ub2 kxidslt                        @70       0x002e
         ub4 kxidsqn                        @72       0x0000080b
      struct ktbituba, 8 bytes              @76
         ub4 kubadba                        @76       0x00800f7f
         ub2 kubaseq                        @80       0x0497
         ub1 kubarec                        @82       0x2e
      ub2 ktbitflg                          @84       0x2001 (KTBFUPB)
      union _ktbitun, 2 bytes               @86
         b2 _ktbitfsc                       @86       0
         ub2 _ktbitwrp                      @86       0x0000
      ub4 ktbitbas                          @88       0x0055c930   -- 第二次commit scn
-- 可知最后一次的commit 时的scn是0x0055c930 和上面dump 的block scn一致
-- 由上面可知最一次insert 发生的undo segment 在 8号段上,下面dump undo header,查看slot scn:
SQL> oradebug setmypid
Statement processed.
SQL> alter system dump undo header '_SYSSMU8$';
System altered.
SQL> oradebug tracefile_name
/sharevg/oracle/admin/thinking/udump/thinking_ora_9502842.trc
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
node1svr:/home/oracle/$> more /sharevg/oracle/admin/thinking/udump/thinking_ora_9502842.trc
/sharevg/oracle/admin/thinking/udump/thinking_ora_9502842.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /sharevg/oracle/10.2.0
System name:    AIX
Node name:      node1svr
Release:        1
Version:        6
Machine:        00F74A764C00
Instance name: thinking
Redo thread mounted by this instance: 1
Oracle process number: 26
Unix process pid: 9502842, image: oracle@node1svr (TNS V1-V3)
*** 2013-07-02 09:04:56.411
*** ACTION NAME:() 2013-07-02 09:04:56.403
*** MODULE NAME:(sqlplus@node1svr (TNS V1-V3)) 2013-07-02 09:04:56.403
*** SERVICE NAME:(SYS$USERS) 2013-07-02 09:04:56.403
*** SESSION ID:(324.160) 2013-07-02 09:04:56.403
********************************************************************************
Undo Segment:  _SYSSMU8$ (8)
********************************************************************************
  Extent Control Header
  -----------------------------------------------------------------
  Extent Header:: spare1: 0      spare2: 0      #extents: 3      #blocks: 143
                  last map  0x00000000  #maps: 0      offset: 4080
      Highwater::  0x00800f80  ext#: 2      blk#: 119    ext size: 128
  #blocks in seg. hdr's freelists: 0
  #blocks below: 0
  mapblk  0x00000000  offset: 2
                   Unlocked
     Map Header:: next  0x00000000  #extents: 3    obj#: 0      flag: 0x40000000
  Extent Map
  -----------------------------------------------------------------
   0x0080007a  length: 7
   0x008000a1  length: 8
   0x00800f09  length: 128  
 Retention Table
  -----------------------------------------------------------
 Extent Number:0  Commit Time: 1372687265
 Extent Number:1  Commit Time: 1372687301
 Extent Number:2  Commit Time: 1372687301
  TRN CTL:: seq: 0x0497 chd: 0x001e ctl: 0x0019 inc: 0x00000000 nfb: 0x0002
            mgc: 0x8201 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
            uba: 0x00800f80.0497.1c scn: 0x0000.0055c2d7
Version: 0x01
  FREE BLOCK POOL::
    uba: 0x00000000.0497.1b ext: 0x2  spc: 0x1d4
    uba: 0x00800f78.0497.02 ext: 0x2  spc: 0x1f06
    uba: 0x00800f7d.0497.03 ext: 0x2  spc: 0x1c54
    uba: 0x00000000.047d.01 ext: 0x2  spc: 0x1f88
    uba: 0x00000000.0261.01 ext: 0x2  spc: 0x1f88
  TRN TBL::
  index  state cflags  wrap#    uel         scn            dba            parent-xid    nub     stmt_num    cmt
  ------------------------------------------------------------------------------------------------
   0x00    9    0x00  0x080d  0x0003  0x0000.0055c453  0x00800f7e  0x0000.000.00000000  0x00000001   0x00000000  1372724079
   0x01    9    0x00  0x080e  0x0016  0x0000.0055c9fd  0x00800f80  0x0000.000.00000000  0x00000001   0x00000000  1372726838
   0x02    9    0x00  0x080d  0x0026  0x0000.0055c4fa  0x00800f7e  0x0000.000.00000000  0x00000001   0x00000000  1372724466
…………………………
-- 找到2e的slot: scn同样为 55c930
 0x2e    9    0x00  0x080b  0x002b  0x0000.0055c930  0x00800f7f  0x0000.000.00000000  0x00000001   0x00000000  1372726649
由上面过程可知 commit 时,若发生fast cleanout scn 这时:
block scn : scn: 0×0000.0055c930
itl scn       : fsc 0×0000.0055c930
transaction tables slot scn :0×0000.0055c930
这三者是一致的。
– 说明:
FLAG – status of the txn
. If the cleanout was performed; that is, the block was modified: The block is flagged as containing delayed-logging cleanout (the flag KTBFUPB in ktbitflg indicates in this case a cleanout at commit time)
Flag – Transaction flag
—-         = Uncommitted
-B–        = The UBA (Undo Block Address) contains undo for this ITL
–U-        = Committed by fast commits & delayed block cleanout has not occurred
—T        = Transaction active at block cleanout SCN
-C-U- = Block cleaned by delayed block cleanout, and rollback segment info is    overwritten.
必须使用块修改工具BBED来修改存在问题的数据块将ITL事务槽的FLAG从U修改为C(Commit)。
TRANSACTION_COMMITED = 0×08;
TRANSACTION_UPBOUND = 0×02;
TRANSACTION_ACTIVE = 0×01;
同时注意 OS的大,小端问题。
可能还会有 itl 的commit flag = 0xa的情况,这是发生delayed cleanout 时,oracle 认为这不是一个确切的commit scn,而是一个上限SCN,大与commit scn的上限,所以标记为0xa = 0×08 + 0×02 .
关于block scn,也可参阅

http://czmmiao.iteye.com/blog/2077043

 

参考至:http://www.dbaqhs.com/archives/1691

如有错误,欢迎指正

邮箱:czmcj@163.com

SCN之fast cleanout

  • 0

    开心

    开心

  • 0

    板砖

    板砖

  • 0

    感动

    感动

  • 0

    有用

    有用

  • 0

    疑问

    疑问

  • 0

    难过

    难过

  • 0

    无聊

    无聊

  • 0

    震惊

    震惊

编辑推荐
SCN是什么?The System Change Number system change number (SCN)是一个非常重要的标记,Oracle使
oracle026 系统改变号(SCN)的详解 SCN 系统改变号,是通过某些函数把时间产生某个数;确保数据文件
https://github.com/path/FastImageCache Fast Image Cache is an efficient, persistent, and—abo
How fast is Redis? Redis includes the redis-benchmark utility that simulates running commands
安装部署教程:http://technet.microsoft.com/zh-cn/library/ff381261.aspx 完成安装后,进行搜索老
fast report 里,显示多个标签 procedure MasterData1OnBeforePrint(Sender: TfrxComponent); begin
http://www.codeproject.com/Articles/298519/Fast-Token-Replacement-in-Csharp Fast Token Replac
2014-11-21 Limy UbuntuKylin微平台 如果你在Debian或Ubuntu系统上经常感觉到apt-get 或 aptitude包
在Ubuntu 中安装应用是很简单的事情,通过Ubuntu 软件中心、通过网页的apt 协议、通过apt-get 命令
Join the ServiceStack Google+ group or follow @servicestack for updates. A Fast, Simple, Type
版权所有 IT知识库 CopyRight © 2009-2015 IT知识库 IT610.com , All Rights Reserved. 京ICP备09083238号