eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« DBA手记:临时表空间组导致递归SQL高度解析 | Blog首页 | 参数_smon_internal_errlimit与数据库恢复 »

DBA手记:X$KTUXE与Oracle的死事务恢复

X$KTUXE是数据库中非常神秘的一个对象表,当然其本质上是C定义的一个结构体,在数据库中可以看到其结构:

SQL> desc x$ktuxe

 Name                                      Null?    Type

 ----------------------------------------- -------- -----------------

 ADDR                                               RAW(4)

 INDX                                               NUMBER

 INST_ID                                            NUMBER

 KTUXEUSN                                           NUMBER

 KTUXESLT                                           NUMBER

 KTUXESQN                                           NUMBER

 KTUXERDBF                                          NUMBER

 KTUXERDBB                                          NUMBER

 KTUXESCNB                                          NUMBER

 KTUXESCNW                                          NUMBER

 KTUXESTA                                           VARCHAR2(16)

 KTUXECFL                                           VARCHAR2(24)

 KTUXEUEL                                           NUMBER

 KTUXEDDBF                                          NUMBER

 KTUXEDDBB                                          NUMBER

 KTUXEPUSN                                          NUMBER

 KTUXEPSLT                                          NUMBER

 KTUXEPSQN                                          NUMBER

 KTUXESIZ                                           NUMBER

那么这个表的KTUXE代表的含义是什么呢?

通过数据库的V$TYPE_SIZE视图,我们可以找到Oracle的自解释信息:

SQL> select * from v$type_size where component='KTU';

 

COMPONEN TYPE     DESCRIPTION                       TYPE_SIZE

-------- -------- -------------------------------- ----------

KTU      KTUBH    UNDO HEADER                              16

KTU      KTUXE    UNDO TRANSACTION ENTRY                   40

KTU      KTUXC    UNDO TRANSACTION CONTROL                104

所以KTUXE代表的就是:[K]ernel [T]ransaction [U]ndo Transa[x]tion [E]ntry (table)在这个表中,数据库展示了回滚段头的事务表信息。通过这个结构体我们可以获得数据库事务相关的重要信息。

最早接触到这个表是在Oracle 8i年代,当我们试图获得最接近当前SCN的数值时,可以使用如下SQL来查询:

SQL> select max(ktuxescnw*power(2,32)+ktuxescnb) SCN from x$ktuxe;

 

       SCN

----------

    900538

这个查询,是获取回滚段事务表中最大的SCN BaseSCN Wrap,通过两者计算出最大的SCN。由于SCN和事务紧密相关,通常这个SCN就很接近当前系统最大的SCN值。

当然,在后期的版本中,我们通过dbms_flashback.get_system_change_number可以很容易的获得当前的SCN值:

SQL> select max(ktuxescnw*power(2,32)+ktuxescnb) SCN,

2  dbms_flashback.get_system_change_number from x$ktuxe;

 

       SCN GET_SYSTEM_CHANGE_NUMBER

---------- ------------------------

    900538                   900599

现在看起来这两者之间是具有一定的差距的。不过SCN本质上来自于内存地址,通过内存变量的转储,则可以获得SCN的最本质来源:

SQL> oradebug setmypid

Statement processed.

SQL> oradebug dumpvar sga kcsgscn_

kcslf kcsgscn_ [3E494C0, 3E494E0) = 00000000 000DBE17 000004B3 00000000 00000000 00000000 00000000 03E492B8

SQL> select to_number('DBE17','xxxxx') from dual;

 

TO_NUMBER('DBE17','XXXXX')

--------------------------

                    900631

 

X$KTUXE表的另外一个重要功能是,可以获得无法通过v$transaction来观察的死事务信息,当一个数据库发生异常中断,或者进行延迟事务恢复时,数据库启动后,无法通过V$TRANSACTION来观察事务信息,但是X$KTUXE可以帮助我们获得这些信息。

该表中的KTUXECFL代表了事务的Flag标记,通过这个标记可以找到那些Dead事务:

 

    17:30:37 SQL> select distinct KTUXECFL,count(*) from x$ktuxe group by KTUXECFL;

    KTUXECFL                  COUNT(*)

    ------------------------ ----------

    DEAD                              1

    NONE                          2393

    SCO|COL                          8

 

KTUXESIZ用来记录事务使用的回滚段块数,可以通过观察这个字段来评估恢复进度:

    16:59:47 SQL> select ADDR,KTUXEUSN,KTUXESLT,KTUXESQN,KTUXESIZ

                2 from x$ktuxe where  KTUXECFL ='DEAD';

 

    ADDR              KTUXEUSN  KTUXESLT  KTUXESQN  KTUXESIZ

    ---------------- ---------- ---------- ---------- ----------

    FFFFFFFF7D07B91C        10        39    2567412    1086075

    17:02:12 SQL> select ADDR,KTUXEUSN,KTUXESLT,KTUXESQN,KTUXESIZ

                2 from x$ktuxe where  KTUXECFL ='DEAD';

 

    ADDR              KTUXEUSN  KTUXESLT  KTUXESQN  KTUXESIZ

    ---------------- ---------- ---------- ---------- ----------

    FFFFFFFF7D07B91C        10        39    2567412    1086067

曾经遇到的一个案例,某个事务回滚经过测算需要大约2.55天:

SQL> declare

 2  l_start number;

 3  l_end    number;

 4  begin

 5    select ktuxesiz into l_start from x$ktuxe where  KTUXEUSN=10 and KTUXESLT=39;

 6    dbms_lock.sleep(60);

 7    select ktuxesiz into l_end from x$ktuxe where  KTUXEUSN=10 and KTUXESLT=39;

 8    dbms_output.put_line('time est Day:'|| round(l_end/(l_start -l_end)/60/24,2));

 9  end;

 10  /

 

time est Day:2.55

 

可以进一步的通过我们熟悉的手段将KTUXE中的Entry信息转储出来,辅助查看。这一次让我们选择SYSTEM回滚段:

SQL> select * from v$rollname where usn=0;

 

       USN NAME

---------- ------------------------------

         0 SYSTEM

 

SQL> select file_id,block_id,blocks from dba_extents where segment_name='SYSTEM';

   FILE_ID   BLOCK_ID     BLOCKS

---------- ---------- ----------

         1          9          8

         1         17          8

         1        385          8

         1        393          8

         1        401          8

         1        409          8

我们将BLOCK 9转储出来:

SQL> alter system dump datafile 1 block 9;

 

System altered.

以下是跟踪文件的摘录:

Start dump data blocks tsn: 0 file#: 1 minblk 9 maxblk 9

buffer tsn: 0 rdba: 0x00400009 (1/9)

scn: 0x0000.000db42c seq: 0x01 flg: 0x04 tail: 0xb42c0e01

frmt: 0x02 chkval: 0xf71c type: 0x0e=KTU UNDO HEADER W/UNLIMITED EXTENTS

Hex dump of block: st=0, typ_found=1

  Extent Control Header

  -----------------------------------------------------------------

  Extent Header:: spare1: 0      spare2: 0      #extents: 6      #blocks: 47   

                  last map  0x00000000  #maps: 0      offset: 4128 

      Highwater::  0x004001a0  ext#: 5      blk#: 7      ext size: 8    

  #blocks in seg. hdr's freelists: 0    

  #blocks below: 0    

  mapblk  0x00000000  offset: 5    

                   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: 0x0035 chd: 0x0010 ctl: 0x001a inc: 0x00000000 nfb: 0x0001

            mgc: 0x8002 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)

            uba: 0x004001a0.0035.20 scn: 0x0000.000bcb32

Version: 0x01

  FREE BLOCK POOL::

    uba: 0x004001a0.0035.20 ext: 0x5  spc: 0x2d6  

    uba: 0x00000000.0033.21 ext: 0x3  spc: 0x11e4 

    uba: 0x00000000.0032.37 ext: 0x2  spc: 0x62c  

    uba: 0x00000000.002b.0c ext: 0x1  spc: 0x19de 

    uba: 0x00000000.0000.00 ext: 0x0  spc: 0x0    

  TRN TBL::

 

  index  state cflags  wrap#    uel         scn            dba            parent-xid  

  -------------------------------------------------------------------------------------

   0x00    9    0x00  0x002c  0x002f  0x0000.000db422  0x004001a0  0x0000.000.00000000

   0x01    9    0x00  0x002c  0x0013  0x0000.000d9c2a  0x0040019f  0x0000.000.00000000

   0x02    9    0x00  0x002c  0x004e  0x0000.000d9763  0x0040019f  0x0000.000.00000000

   0x03    9    0x00  0x002c  0x0047  0x0000.000d9c0e  0x0040019f  0x0000.000.00000000

   0x04    9    0x00  0x002c  0x005c  0x0000.000d975b  0x0040019f  0x0000.000.00000000

   0x05    9    0x00  0x002c  0x0024  0x0000.000d9c24  0x0040019f  0x0000.000.00000000

.........

   0x60    9    0x00  0x002b  0x003e  0x0000.000d974e  0x0040019e  0x0000.000.00000000

   0x61    9    0x00  0x002b  0x000f  0x0000.000db40e  0x004001a0  0x0000.000.00000000

End dump data blocks tsn: 0 file#: 1 minblk 9 maxblk 9

如果用BBED再来查看以下段头的数据结构,一切就会变得清晰明了:

D:\oracle\9.2.0\bin>bbed parfile=parfile.txt

口令:

 

BBED: Release 2.0.0.0.0 - Limited Production on 星期四 8 12 15:50:08 2010

 

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

 

************* !!! For Oracle Internal Use only !!! ***************

 

BBED> set file 1 block 10

        FILE#           1

        BLOCK#          10

 

BBED> map /v

 File: D:\ORACLE\ORADATA\ENMO\SYSTEM01.DBF (1)

 Block: 10                                    Dba:0x0040000a

------------------------------------------------------------

 Unlimited Undo Segment Header

 

 struct kcbh, 20 bytes                      @0

    ub1 type_kcbh                           @0

    ub1 frmt_kcbh                           @1

    ub1 spare1_kcbh                         @2

    ub1 spare2_kcbh                         @3

    ub4 rdba_kcbh                           @4

    ub4 bas_kcbh                            @8

    ub2 wrp_kcbh                            @12

    ub1 seq_kcbh                            @14

    ub1 flg_kcbh                            @15

    ub2 chkval_kcbh                         @16

    ub2 spare3_kcbh                         @18

 

 struct ktech, 72 bytes                     @20

    ub4 spare1_ktech                        @20

    word tsn_ktech                          @24

    ub4 lastmap_ktech                       @28

    ub4 mapcount_ktech                      @32

    ub4 extents_ktech                       @36

    ub4 blocks_ktech                        @40

    ub2 mapend_ktech                        @44

    struct hwmark_ktech, 32 bytes           @48

    struct locker_ktech, 8 bytes            @80

    ub4 flag_ktech                          @88

 

 struct ktemh, 16 bytes                     @92

    ub4 count_ktemh                         @92

    ub4 next_ktemh                          @96

    ub4 obj_ktemh                           @100

    ub4 flag_ktemh                          @104

 

 struct ktetb[6], 48 bytes                  @108

    ub4 ktetbdba                            @108

    ub4 ktetbnbk                            @112

 

 struct ktuxc, 104 bytes                    @4148

    struct ktuxcscn, 8 bytes                @4148

    struct ktuxcuba, 8 bytes                @4156

    sb2 ktuxcflg                            @4164

    ub2 ktuxcseq                            @4166

    sb2 ktuxcnfb                            @4168

    ub4 ktuxcinc                            @4172

    sb2 ktuxcchd                            @4176

    sb2 ktuxcctl                            @4178

    ub2 ktuxcmgc                            @4180

    ub4 ktuxcopt                            @4188

    struct ktuxcfbp[5], 60 bytes            @4192

 

 struct ktuxe[204], 8160 bytes              @4252

    ub4 ktuxexid                            @4252

    ub4 ktuxebrb                            @4256

    struct ktuxescn, 8 bytes                @4260

    sb4 ktuxesta                            @4268

    ub1 ktuxecfl                            @4269

    sb2 ktuxeuel                            @4270

 

 ub4 tailchk                                @8188

结合前面的V$TYPE_SIZE视图,参考上面的BBED输出,可以看到KTU等事务信息,以及KTE的区间使用信息等:

SQL> select * from v$type_size where component in ('KTE','KTU');

 

COMPONEN TYPE     DESCRIPTION                       TYPE_SIZE

-------- -------- -------------------------------- ----------

KTE      KTECT    EXTENT CONTROL                           44

KTE      KTECH    EXTENT CONTROL                           72

KTE      KTETB    EXTENT TABLE                              8

KTU      KTUBH    UNDO HEADER                              16

KTU      KTUXE    UNDO TRANSACTION ENTRY                   40

KTU      KTUXC    UNDO TRANSACTION CONTROL                104

 


历史上的今天...
    >> 2012-03-07文章:
    >> 2010-03-07文章:
    >> 2009-03-07文章:
    >> 2008-03-07文章:
    >> 2007-03-07文章:
    >> 2005-03-07文章:
           十年

无觅

By eygle on 2011-03-07 08:47 | Comments (0) | Backup&Recovery | 2741 |


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com