某客户回滚段达到32765处理

某客户数据库的版本为11.2.0.3，如下所示：
SQL> select * from v$version;

BANNER
------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

数据库使用的UNDO表空间为UNDOTBS1，且为自动管理，如下所示：
SQL> show parameter undo_management

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
SQL> show parameter undo_tablespace

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace                      string      UNDOTBS1

某日，业务程序运行时突然出现ORA-1628错误，数据库警告日志如下所示：
Fri Jun 07 16:25:48 2013
ORA-1628: max # extents  32765 reached for rollback segment _SYSSMU985_640904372$
Fri Jun 07 16:58:14 2013
ORA-1628: max # extents  32765 reached for rollback segment _SYSSMU1496_3116906519$

进一步检查dba_undo_extents视图发现，_SYSSMU985_640904372$和_SYSSMU1496_3116906519$回滚段中的区数量确实已经达到了32765个，如下所示：
SQL> select SEGMENT_NAME,count(*) from dba_undo_extents where TABLESPACE_NAME='UNDOTBS1'                                 
  2  group by SEGMENT_NAME order by 2;
SEGMENT_NAME                     COUNT(*)
------------------------------ ----------
。。。。。。
_SYSSMU985_640904372$               32765
_SYSSMU1496_3116906519$             32765


检查_SYSSMU985_640904372回滚段中的每个区大小，发现每个区大小只有64k，这在区大小分配方式为系统分配下是极为不正常的，如下所示：
SQL> select EXTENT_MANAGEMENT,ALLOCATION_TYPE from dba_tablespaces where TABLESPACE_NAME='UNDOTBS1';

EXTENT_MAN ALLOCATIO
---------- ---------
LOCAL      SYSTEM

SQL> select distinct BYTES from dba_undo_extents where SEGMENT_NAME='_SYSSMU985_640904372';

     BYTES
----------
     65536
由于回滚段_SYSSMU985_640904372$中每个区的大小只有64k，所以整个回滚段大小只有2G不到，如下所示：  
SQL> select BYTES/1024/1024/1024 from dba_segments where SEGMENT_NAME='_SYSSMU985_640904372$';

BYTES/1024/1024/1024
--------------------
          1.99981689

在回滚段中，当事务所使用的块从一个区延伸到下一个区时，就是一次WRAP。当事务所使用的块发生WARP时，如果下一个区有活动事务，那么即使下下个区有空闲块，
Oracle也不会跳过下一区去使用下下一区，此时就会发生区扩展（EXTEND），Oracle会从回滚段中分配出一个空闲区。
从v$rollstat视图中可以看出，在32765个区中，区扩展总共发生了32763次，这是不正常的，而且这极有可能是同一个事务导致的，如下所示：
SQL>  select EXTENTS,EXTENDS,WRAPS,STATUS,CURBLK,CUREXT from v$rollstat where USN=985;

   EXTENTS    EXTENDS      WRAPS STATUS              CURBLK     CUREXT
---------- ---------- ---------- --------------- ---------- ----------
     32765      32763          0 ONLINE                   3          0
    
由于发生ORA-1628时，事务已经回滚结束，所以不能从v$transaction视图中查到信息，于是dump回滚段头查看相关信息，如下所示：
SQL> select header_file,header_block from dba_segments where segment_name='_SYSSMU985_640904372$';

HEADER_FILE HEADER_BLOCK
----------- ------------
         16       163393

SQL> oradebug setmypid
Statement processed.
SQL> alter system dump datafile 16 block 163393;
SQL> oradebug TRACEFILE_NAME
/oracle/app/diag/rdbms/orazw/orazw1/trace/orazw1_ora_5440100.trc

检查dump下来的跟踪文件，事务槽的uel列表示事务的当前区，scn表示事务开始时的SCN。
可以看到06号事务槽上的事务曾经使用过第32765个块，由于其state标记位9，这说明已经提交或回滚了。如下所示
Start dump data blocks tsn: 1 file#:25 minblk 524041 maxblk 524041

      
      
index  state cflags  wrap#    uel         scn            dba            parent-xid    nub     stmt_num    cmt
  ------------------------------------------------------------------------------------------------
   0x00    9    0x00  0x0002  0x0001  0x0c32.ac6b6eed  0x0647ff0b  0x0000.000.00000000  0x00000001   0x00000000  1370402686
   0x01    9    0x00  0x0002  0x0002  0x0c32.ac6c5183  0x00000000  0x0000.000.00000000  0x00000000   0x00000000  1370415835
   0x02    9    0x00  0x0002  0x0003  0x0c32.ac6c62e0  0x0647ff0b  0x0000.000.00000000  0x00000001   0x00000000  1370415848
   0x03    9    0x00  0x0002  0x0004  0x0c32.ac6c708c  0x00000000  0x0000.000.00000000  0x00000000   0x00000000  1370415857
   0x04    9    0x00  0x0002  0x0005  0x0c33.6108d3f8  0x00000000  0x0000.000.00000000  0x00000000   0x0647ff0b  1370581497
   0x05    9    0x00  0x0002  0x0007  0x0c33.724c35af  0x0647ff0c  0x0000.000.00000000  0x00000001   0x00000000  1370589582
   0x06    9    0x00  0x0002  0xffff  0x0c33.7ceb4633  0x00000000  0x0000.000.00000000  0x00000000   0x0647ff0c  1370595925
   0x07    9    0x00  0x0002  0x0006  0x0c33.7ceb44a7  0x00000000  0x0000.000.00000000  0x00000000   0x00000000  1370595900

利用scn_to_timestamp函数将将06号事务槽上的事务开始SCN转换成时间为2013年7月2号，11点45分11秒。这和发生故障的时间点相差了2天多，这说明这是一个长事务，如下所示：
SQL> select scn_to_timestamp(13415278659123) from dual;

SCN_TO_TIMESTAMP(13415278659123)
---------------------------------------------------------------------------
02-JUL-13 11.45.11.000000000 PM


由于发生ORA-1628错误时，事务已经回滚结束，所以使用ASH报告查看错误发生时刻的事务运行情况，获取ASH报告过程（仅仅采样错误发生时间点前后）如下所示：
SQL> @?/rdbms/admin/ashrpt.sql

Current Instance
~~~~~~~~~~~~~~~~

   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
1315204055 ORAZW               1 orazw1


Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: text

Type Specified:  text


Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name      Instance     Host
------------ -------- ------------ ------------ ------------
* 1315204055        1 ORAZW        orazw1       zwdb01
  1315204055        1 ORAZW        orazw        zwdb01
  1315204055        2 ORAZW        orazw2       zwdb02

Defaults to current database

Using database id: 1315204055

Enter instance numbers. Enter 'ALL' for all instances in a
RAC cluster or explicitly specify list of instances (e.g., 1,2,3).
Defaults to current instance.

Using instance number(s): 1








ASH Samples in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Oldest ASH sample available:  05-Jun-13 11:24:22   [   3554 mins in the past]
Latest ASH sample available:  02-Jul-13 10:36:15   [ -35277 mins in the past]


Specify the timeframe to generate the ASH report
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter begin time for report:

--    Valid input formats:
--      To specify absolute begin time:
--        [MM/DD[/YY]] HH24:MI[:SS]
--        Examples: 02/23/03 14:30:15
--                  02/23 14:30:15
--                  14:30:15
--                  14:30
--      To specify relative begin time: (start with '-' sign)
--        -[HH24:]MI
--        Examples: -1:15  (SYSDATE - 1 Hr 15 Mins)
--                  -25    (SYSDATE - 25 Mins)

Defaults to -15 mins
Enter value for begin_time: 16:24
Report begin time specified: 16:24

Enter duration in minutes starting from begin time:
Defaults to SYSDATE - begin_time
Press Enter to analyze till current time
Enter value for duration: 2

查看ASH报告，发现整个ASH报告只有1条insert语句，而且该条insert语句处理的数据量也不大，所以导致回滚段区数量不足的SQL很可能就是该条insert语句，如下所示：
ASH Report For ORAZW/orazw1

DB Name         DB Id    Instance     Inst Num Release     RAC Host
------------ ----------- ------------ -------- ----------- --- ------------
ORAZW         1315204055 orazw1              1 11.2.0.3.0  YES zwdb01

CPUs           SGA Size       Buffer Cache        Shared Pool    ASH Buffer Size
---- ------------------ ------------------ ------------------ ------------------
  64     89,710M (100%)    75,776M (84.5%)    11,520M (12.8%)      128.0M (0.1%)


          Analysis Begin Time:   07-Jun-13 16:24:00
            Analysis End Time:   07-Jun-13 16:26:00
                 Elapsed Time:         2.0 (mins)
            Begin Data Source:   V$ACTIVE_SESSION_HISTORY
              End Data Source:   V$ACTIVE_SESSION_HISTORY
                 Sample Count:          30
      Average Active Sessions:        0.25
  Avg. Active Session per CPU:        0.00
                Report Target:   None specified

Top SQL with Top Row Sources    DB/Inst: ORAZW/orazw1  (Jun 07 16:24 to 16:26)

                                                        Sampled #
                 SQL ID             PlanHash        of Executions     % Activity
----------------------- -------------------- -------------------- --------------
Row Source                               % RwSrc Top Event               % Event
---------------------------------------- ------- ----------------------- -------
          0mg9654rs0wct            340535052                    1          80.00
** Row Source Not Available **             80.00 CPU + Wait for CPU        80.00
INSERT INTO "UCS_SUBS_FREEZE" ("SUBS_FREEZE_ID","SUBS_SCHEME_ID","SUBSCRIPTION_
ID","SERVICE_TYPE","FREEZE_ID","ACCOUNT_ID","SUBJECT_ID","ALLOT_CAL_MODE","ALLOT
_RUN_MODE","ORGINAL_AMOUNT","ALREADY_ALLOT_AMOUNT","ALREADY_ALLOT_MONTH","START_
ALLOT_MONTH","LAST_ALLOT_MONTH","ALLOT_STATUS","USESUBJECTID","LAST_PRESENT_DATE


通过sql id在v$sqltext视图中查看完成的SQL语句，如下所示：
SQL>  select sql_text from v$sqltext where sql_id='0mg9654rs0wct' order by piece;

SQL_TEXT
----------------------------------------------------------------
INSERT  INTO "UCS_SUBS_FREEZE" ("SUBS_FREEZE_ID","SUBS_SCHEME_ID
","SUBSCRIPTION_ID","SERVICE_TYPE","FREEZE_ID","ACCOUNT_ID","SUB
JECT_ID","ALLOT_CAL_MODE","ALLOT_RUN_MODE","ORGINAL_AMOUNT","ALR
EADY_ALLOT_AMOUNT","ALREADY_ALLOT_MONTH","START_ALLOT_MONTH","LA
ST_ALLOT_MONTH","ALLOT_STATUS","USESUBJECTID","LAST_PRESENT_DATE
","THAWFEE","THAWSCALE","MINUSEFEE","CREATE_TIME","ACTIVE_TIME",
"INACTIVE_TIME","REGION_ID","COUNTY_ID","OFFICE_ID","OPERATOR_ID
","TAKE_TYPE","EFFECT_DAYS","DELAY_FLAG","PAY_RULE_ID","ORG_ALLO
T_MONTH","DEAL_NO","DEAL_TIMEINFO","NEXT_PRESENT_DATE") VALUES (
:F1,:F2,:F3,:F4,:F5,:F6,:F7,:F8,:F9,:F10,:F11,:F12,:F13,:F14,:F1
5,:F16,:F17,:F18,:F19,:F20,SYSDATE@!,:F22,:F23,:F24,:F25,:F26,:F
27,:F28,:F29,:F30,:F31,:F32,:F33,:F34,:F35)


经过以上分析，本次回滚段出现区数量不足，从而导致出现ORA-1628错误是正常的，并不是Oracle bug。其理由如下：
1、事务只能在一个回滚段中运行，不能跨回滚段。
2、从事务开始到异常结束历时2天多，这是一个长事务，而长事务是有可能导致出现ORA-1628错误的。
3、观察引起ORA-1628错误的insert语句，发现单次insert的数据量不大，从而导致每次区扩展的区大小只有64k。
如果不改应用，那么Oracle端则做如下修改；
1、回滚段自动管理修改成手动管理
2、修改UNDO表空间数据块大小