当前位置: 开发笔记 > 数据库 > 正文

ORA-30025:DROPsegment‘_SYSSMU559$’(inundotab

作者：亚璨的秘密 | 来源：互联网 | 2017-05-12 15:27

本站文章除注明转载外，均为本站原创：转载自lovewifelovelife—Roger的Oracle技术博客本文链接地址:ORA-30025:DROPsegment_SYSSMU559$(inundotablespace)notallowed这里是今天一些学生的问题，下午没事研究了一下。发现非常的奇怪，大家

本站文章除注明转载外，均为本站原创：转载自love wife love life —Roger 的Oracle技术博客本文链接地址: ORA-30025: DROP segment _SYSSMU559$ (in undo tablespace) not allowed 这里是今天一些学生的问题，下午没事研究了一下。发现非常的奇怪，大家

本站文章除注明转载外，均为本站原创： 转载自love wife & love life —Roger 的Oracle技术博客

本文链接地址: ORA-30025: DROP segment &＃8216;_SYSSMU559$&＃8217; (in undo tablespace) not allowed

这里是今天一些学生的问题，下午没事研究了一下。发现非常的奇怪，大家一起来探讨下。对于没有活动事务的情况，其回滚段的状态居然是Pending offline.

SQL> alter tablespace UNDOTBS2 offline;
alter tablespace UNDOTBS2 offline
*
ERROR at line 1:
ORA-30042: Cannot offline the undo tablespace
SQL> alter rollback segment "_SYSSMU559" offline;
Rollback segment altered.
SQL> select a.usn,a.name,b.XACTS,b.STATUS from v$rollname a,v$rollstat b where a.usn=b.USN and a.usn=559;
       USN NAME                                XACTS STATUS
---------- ------------------------------ ---------- ---------------
       559 _SYSSMU559$                             1 PENDING OFFLINE
SQL> show parameter undo
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_gc_undo_affinity                    boolean     FALSE
_undo_autotune                       boolean     TRUE
undo_management                      string      AUTO
undo_retention                       integer     1500
undo_tablespace                      string      UNDOTBS02
SQL> drop tablespace UNDOTBS2;
drop tablespace UNDOTBS2
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS2' is currently in use
SQL> drop rollback segment  "_SYSSMU559$";
drop rollback segment  "_SYSSMU559$"
*
ERROR at line 1:
ORA-30025: DROP segment '_SYSSMU559$' (in undo tablespace) not allowed

根据描述，这个undo表空间已经切换了1周了，而且数据库中最长的会话登陆时间也就几个小时而已。怀疑是回滚段存在活动事务.

dump 该回滚段头发现比较怪异：

TRN CTL:: seq: 0x1958 chd: 0x00a0 ctl: 0x0004 inc: 0x00000000 nfb: 0x0001
            mgc: 0x8201 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
            uba: 0x0e8003ca.1958.0e scn: 0x0cbb.547f60cf
Version: 0x01
  FREE BLOCK POOL::
    uba: 0x0e8003ca.1958.0e ext: 0x0  spc: 0x7b92
    uba: 0x00000000.1957.21 ext: 0x14 spc: 0x7460
    uba: 0x00000000.193e.01 ext: 0x2  spc: 0x7f88
    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    cmt
------------------------------------------------------------------------------------------------
 0x00    9    0x00  0x533e2  0x006f  0x0cbb.5482de45  0x07c0e48b  0x0000.000.00000000  0x00000001   0x00000000  1411362710
 0x01    9    0x00  0x533f1  0x0075  0x0cbb.5487202a  0x2800bff8  0x0000.000.00000000  0x00000001   0x00000000  1411362757
 0x02    9    0x00  0x53400  0x007f  0x0cbb.54a62d7a  0x05c27bed  0x0000.000.00000000  0x00000023   0x00000000  1411363118
 0x03    9    0x00  0x533ef  0x0022  0x0cbb.5482f598  0x07c0e48b  0x0000.000.00000000  0x00000001   0x00000000  1411362711
 0x04    9    0x00  0x533fe  0xffff  0x0cbc.14a25423  0x00000000  0x0000.000.00000000  0x00000000   0x00000000  1411783108
 0x05    9    0x00  0x533fd  0x00be  0x0cbb.54852538  0x07c0e49c  0x0000.000.00000000  0x00000001   0x00000000  1411362735
 0x06    9    0x00  0x533ec  0x0078  0x0cbb.548228f1  0x07c0e487  0x0000.000.00000000  0x00000001   0x00000000  1411362703
 0x07    9    0x00  0x533fb  0x008e  0x0cbb.54869113  0x2800bff7  0x0000.000.00000000  0x00000001   0x00000000  1411362751
 0x08    9    0x00  0x533ea  0x0083  0x0cbb.5481c78a  0x07c0e486  0x0000.000.00000000  0x00000001   0x00000000  1411362700
 ..........
 0x9e    9    0x00  0x533e4  0x0016  0x0cbb.5484ad61  0x07c0e495  0x0000.000.00000000  0x00000001   0x00000000  1411362730
 0x9f    9    0x00  0x533d3  0x005f  0x0cbb.54859cf3  0x07c0e4a0  0x0000.000.00000000  0x00000001   0x00000000  1411362740
 0xa0    9    0x00  0x533d2  0x0073  0x0cbb.547fa87e  0x00000000  0x0000.000.00000000  0x00000000   0x00000000  1411362676
 0xa1    9    0x00  0x533f1  0x0029  0x0cbb.54a684f8  0x00000000  0x0000.000.00000000  0x00000000   0x00000000  1411363123
 ..........
 0xc9    9    0x00  0x533e9  0x005a  0x0cbb.548481f4  0x07c0e495  0x0000.000.00000000  0x00000001   0x00000000  1411362728

我们知道，index表示slot，而state表示事物状态，9表示inactive，10表示active。从dump 来看，根本就没有活动的事务。
但是Oracle为什么会认为这个回滚段是pending offline的呢？他这里的信息，其实一个地方比较怪异：ktuxc里面的uba地址，应该是跟最新的一个事务对应的undo dba地址是一致的，而这里的最新的事务，index=04这个，dba地址居然是0&＃215;000000000.
针对这个问题，我进行了一个测试，花了不少时间。

Session 1:

www.killdb.com>select file_id,file_name,tablespace_name from dba_data_files
  2  where tablespace_name like '%UNDO%';
   FILE_ID FILE_NAME                                                              TABLESPACE_NAME
---------- ---------------------------------------------------------------------- -------------------
         7 /home/ora10g/oradata/roger/undotbs2.dbf                                UNDOTBS2
         2 /home/ora10g/oradata/roger/undotbs01.dbf                               UNDOTBS1
www.killdb.com>select d.segment_name, d.tablespace_name, s.waits, s.shrinks,
  2   s.wraps, s.status
  3   from v$rollstat s, dba_rollback_segs d
  4   where s.usn = d.segment_id
  5   order by 1;
SEGMENT_NAME                   TABLESPACE_NAME                     WAITS    SHRINKS      WRAPS STATUS
------------------------------ ------------------------------ ---------- ---------- ---------- -------
SYSTEM                         SYSTEM                                  1          0          0 ONLINE
_SYSSMU1$                      UNDOTBS1                                2         11        101 ONLINE
_SYSSMU2$                      UNDOTBS1                                0          6         60 ONLINE
_SYSSMU3$                      UNDOTBS1                                1          7         60 ONLINE
_SYSSMU4$                      UNDOTBS1                                3         10         84 ONLINE
_SYSSMU5$                      UNDOTBS1                                3         15        117 ONLINE
_SYSSMU6$                      UNDOTBS1                                3          1         12 ONLINE
_SYSSMU7$                      UNDOTBS1                                0          0         11 ONLINE
8 rows selected.
www.killdb.com>conn roger/roger
Connected.
www.killdb.com>create table t_undo(a varchar2(20));
Table created.
www.killdb.com>insert into t_undo values('killdb');
1 row created.
www.killdb.com>insert into t_undo values('roger');
1 row created.
www.killdb.com>commit;
Commit complete.
www.killdb.com>alter system switch logfile;
System altered.
www.killdb.com>select * from t_undo;
A
--------------------
killdb
roger
www.killdb.com>delete from t_undo where a='roger';
1 row deleted.
www.killdb.com>
--这里我未提交这个事务.

Session 2:

www.killdb.com> select xidusn,xidslot,xidsqn,ubablk,ubafil,ubarec,START_SCN from v$transaction;
    XIDUSN    XIDSLOT     XIDSQN     UBABLK     UBAFIL     UBAREC  START_SCN
---------- ---------- ---------- ---------- ---------- ---------- ----------
         6          8        387       5699          2         11   22153177
www.killdb.com>select d.segment_name, d.tablespace_name, s.waits, s.shrinks,
  2   s.wraps, s.status
  3   from v$rollstat s, dba_rollback_segs d
  4   where s.usn = d.segment_id
  5   order by 1;
SEGMENT_NAME         TABLESPACE_NAME           WAITS    SHRINKS      WRAPS STATUS
-------------------- -------------------- ---------- ---------- ---------- --------
SYSTEM               SYSTEM                        1          0          0 ONLINE
_SYSSMU1$            UNDOTBS1                      2         11        101 ONLINE
_SYSSMU2$            UNDOTBS1                      0          6         60 ONLINE
_SYSSMU3$            UNDOTBS1                      1          7         60 ONLINE
_SYSSMU4$            UNDOTBS1                      3         10         84 ONLINE
_SYSSMU5$            UNDOTBS1                      3         15        117 ONLINE
_SYSSMU6$            UNDOTBS1                      3          1         12 ONLINE
_SYSSMU7$            UNDOTBS1                      0          0         11 ONLINE
8 rows selected.
www.killdb.com>show parameter undo
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1
www.killdb.com>alter system set undo_tablespace=UNDOTBS2;
System altered.
www.killdb.com>drop rollback segment "_SYSSMU6$";
drop rollback segment "_SYSSMU6$"
*
ERROR at line 1:
ORA-30025: DROP segment '_SYSSMU6$' (in undo tablespace) not allowed
www.killdb.com>
www.killdb.com>select a.usn, a.name, b.XACTS, b.STATUS
  2    from v$rollname a, v$rollstat b
  3   where a.usn = b.USN
  4     and a.usn = 6
  5  /
       USN NAME                                XACTS STATUS
---------- ------------------------------ ---------- ---------------
         6 _SYSSMU6$                               1 PENDING OFFLINE

切换undo之后，我们看到第6号回滚段果然变成了期望的Pending offline.下面我们来dump下该回滚段头:

********************************************************************************
Undo Segment:  _SYSSMU6$ (6)
********************************************************************************
......
  TRN CTL:: seq: 0x01ba chd: 0x0009 ctl: 0x0007 inc: 0x00000000 nfb: 0x0000
            mgc: 0x8201 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
            uba: 0x00801643.01ba.0b scn: 0x0000.0152000d
Version: 0x01
  FREE BLOCK POOL::
    uba: 0x00000000.01ba.0a ext: 0x1  spc: 0x1a56
    uba: 0x00000000.01b6.02 ext: 0x0  spc: 0x1f1a
    uba: 0x00000000.01b6.34 ext: 0x0  spc: 0x862
    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    cmt
  ------------------------------------------------------------------------------------------------
   0x00    9    0x00  0x0183  0x0001  0x0000.0152063c  0x00801642  0x0000.000.00000000  0x00000001   0x00000000  1414387430
   0x01    9    0x00  0x0183  0x0002  0x0000.0152066f  0x00801642  0x0000.000.00000000  0x00000001   0x00000000  1414387550
   0x02    9    0x00  0x0183  0x0005  0x0000.015206a3  0x00801643  0x0000.000.00000000  0x00000001   0x00000000  1414387670
   0x03    9    0x00  0x0183  0x0007  0x0000.01520786  0x00801643  0x0000.000.00000000  0x00000001   0x00000000  1414388210
   0x04    9    0x00  0x0183  0x0006  0x0000.0152071f  0x00801643  0x0000.000.00000000  0x00000001   0x00000000  1414387968
   0x05    9    0x00  0x0183  0x0004  0x0000.015206d7  0x00801643  0x0000.000.00000000  0x00000001   0x00000000  1414387790
   0x06    9    0x00  0x0183  0x0003  0x0000.01520753  0x00801643  0x0000.000.00000000  0x00000001   0x00000000  1414388090
   0x07    9    0x00  0x0183  0xffff  0x0000.015207b9  0x00801643  0x0000.000.00000000  0x00000001   0x00000000  1414388330
   0x08   10    0x80  0x0183  0x0001  0x0000.015207d9  0x00801643  0x0000.000.00000000  0x00000001   0x00000000  0
   0x09    9    0x00  0x0182  0x000a  0x0000.0152004c  0x0080163c  0x0000.000.00000000  0x00000001   0x00000000  1414383863
   .........
   0x2e    9    0x00  0x0182  0x002f  0x0000.015205e5  0x00801642  0x0000.000.00000000  0x00000001   0x00000000  1414387243
   0x2f    9    0x00  0x0182  0x0000  0x0000.01520608  0x00801642  0x0000.000.00000000  0x00000001   0x00000000  1414387310

我们可以看到，index=08这个事务的状态是10，表明是active. 这跟文档描述是一致的。然而学生这里的问题是没有active的事务。
为了模拟的更逼真一点，我手工把这个事务给提交了,通过bbed来完成.

+++ 首先把测试表里面这个数据块中的事务给提交了

BBED> set file 5 block 29197
        FILE#           5
        BLOCK#          29197
BBED> map
 File: /home/ora10g/oradata/roger/roger01.dbf (5)
 Block: 29197                                 Dba:0x0140720d
------------------------------------------------------------
 KTB Data Block (Table/Cluster)
 struct kcbh, 20 bytes                      @0       
 struct ktbbh, 72 bytes                     @20      
 struct kdbh, 14 bytes                      @100     
 struct kdbt[1], 4 bytes                    @114     
 sb2 kdbr[2]                                @118     
 ub1 freespace[8047]                        @122     
 ub1 rowdata[19]                            @8169    
 ub4 tailchk                                @8188    
BBED> d /v offset 84 count 2
 File: /home/ora10g/oradata/roger/roger01.dbf (5)
 Block: 29197   Offsets:   84 to   85  Dba:0x0140720d
-------------------------------------------------------
 0100                                l ..
 <16 bytes per line>
BBED> modify /x 0080
 File: /home/ora10g/oradata/roger/roger01.dbf (5)
 Block: 29197            Offsets:   84 to   85           Dba:0x0140720d
------------------------------------------------------------------------
 0080 
 <32 bytes per line>
BBED> d /v offset 86 count 2
 File: /home/ora10g/oradata/roger/roger01.dbf (5)
 Block: 29197   Offsets:   86 to   87  Dba:0x0140720d
-------------------------------------------------------
 0700                                l ..
 <16 bytes per line>
BBED> modify /x 00 offset 86
 File: /home/ora10g/oradata/roger/roger01.dbf (5)
 Block: 29197            Offsets:   86 to   87           Dba:0x0140720d
------------------------------------------------------------------------
 0000 
 <32 bytes per line>
BBED> p kdbr
sb2 kdbr[0]                                 @118      8078
sb2 kdbr[1]                                 @120      8069
BBED> p *kdbr[1]
rowdata[0]
----------
ub1 rowdata[0]                              @8169     0x3c
BBED> x /rccccccccccccccccccccc
rowdata[0]                                  @8169
----------
flag@8169: 0x3c (KDRHFL, KDRHFF, KDRHFD, KDRHFH)
lock@8170: 0x02
cols@8171:    0
BBED> d /v offset 8169 count 2
 File: /home/ora10g/oradata/roger/roger01.dbf (5)
 Block: 29197   Offsets: 8169 to 8170  Dba:0x0140720d
-------------------------------------------------------
 3c02                                l <.
 <16 bytes per line>
BBED> modify /x 2c00
 File: /home/ora10g/oradata/roger/roger01.dbf (5)
 Block: 29197            Offsets: 8169 to 8170           Dba:0x0140720d
------------------------------------------------------------------------
 2c00 
 <32 bytes per line>
BBED> sum apply
Check value for File 5, Block 29197:
current = 0x5159, required = 0x5159
BBED> verify
DBVERIFY - Verification starting
FILE = /home/ora10g/oradata/roger/roger01.dbf
BLOCK = 29197
Block Checking: DBA = 21000717, Block Type = KTB-managed data block
data header at 0xb7e4b264
kdbchk: space available on commit is incorrect
        tosp=8056 fsc=0 stb=0 avsp=8047
Block 29197 failed with check code 6111
DBVERIFY - Verification complete
Total Blocks Examined         : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing   (Data) : 1
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0
BBED> p kdbh
struct kdbh, 14 bytes                       @100
   ub1 kdbhflag                             @100      0x00 (NONE)
   b1 kdbhntab                              @101      1
   b2 kdbhnrow                              @102      2
   sb2 kdbhfrre                             @104     -1
   sb2 kdbhfsbo                             @106      22
   sb2 kdbhfseo                             @108      8069
   b2 kdbhavsp                              @110      8047
   b2 kdbhtosp                              @112      8056
BBED> d /v offset 110 count 4
 File: /home/ora10g/oradata/roger/roger01.dbf (5)
 Block: 29197   Offsets:  110 to  113  Dba:0x0140720d
-------------------------------------------------------
 6f1f781f                            l o.x.
 <16 bytes per line>
BBED> modify /x 781f offset 110
 File: /home/ora10g/oradata/roger/roger01.dbf (5)
 Block: 29197            Offsets:  110 to  113           Dba:0x0140720d
------------------------------------------------------------------------
 781f781f 
 <32 bytes per line>
BBED> sum apply
Check value for File 5, Block 29197:
current = 0x514e, required = 0x514e
BBED> verify
DBVERIFY - Verification starting
FILE = /home/ora10g/oradata/roger/roger01.dbf
BLOCK = 29197
Block Checking: DBA = 21000717, Block Type = KTB-managed data block
data header at 0xb7e4b264
kdbchk: the amount of space used is not equal to block size
        used=41 fsc=0 avsp=8056 dtl=8088
Block 29197 failed with check code 6110
DBVERIFY - Verification complete
Total Blocks Examined         : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing   (Data) : 1
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0
BBED> d /v offset 102
 File: /home/ora10g/oradata/roger/roger01.dbf (5)
 Block: 29197   Offsets:  102 to  105  Dba:0x0140720d
-------------------------------------------------------
 0200ffff                            l ....
 <16 bytes per line>
BBED> modify /x 01 offset 102
 File: /home/ora10g/oradata/roger/roger01.dbf (5)
 Block: 29197            Offsets:  102 to  105           Dba:0x0140720d
------------------------------------------------------------------------
 0100ffff 
 <32 bytes per line>
BBED> sum apply
Check value for File 5, Block 29197:
current = 0x514d, required = 0x514d
BBED> verify
DBVERIFY - Verification starting
FILE = /home/ora10g/oradata/roger/roger01.dbf
BLOCK = 29197
Block Checking: DBA = 21000717, Block Type = KTB-managed data block
data header at 0xb7e28264
kdbchk: fsbo(22) wrong, (hsz 20)
Block 29197 failed with check code 6129
DBVERIFY - Verification complete
Total Blocks Examined         : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing   (Data) : 1
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0
BBED> d /v offset 106 count 2
 File: /home/ora10g/oradata/roger/roger01.dbf (5)
 Block: 29197   Offsets:  106 to  107  Dba:0x0140720d
-------------------------------------------------------
 1600                                l ..
 <16 bytes per line>
BBED> modify /x 14 offset 106
 File: /home/ora10g/oradata/roger/roger01.dbf (5)
 Block: 29197            Offsets:  106 to  107           Dba:0x0140720d
------------------------------------------------------------------------
 1400 
 <32 bytes per line>
BBED> sum apply
Check value for File 5, Block 29197:
current = 0x514f, required = 0x514f
BBED> verify
DBVERIFY - Verification starting
FILE = /home/ora10g/oradata/roger/roger01.dbf
BLOCK = 29197
Block Checking: DBA = 21000717, Block Type = KTB-managed data block
data header at 0xb7e28264
kdbchk: row count in table index incorrect
Block 29197 failed with check code 6125
DBVERIFY - Verification complete
Total Blocks Examined         : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing   (Data) : 1
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0
BBED> p kdbt
struct kdbt[0], 4 bytes                     @114
   b2 kdbtoffs                              @114      0
   b2 kdbtnrow                              @116      2
BBED> d /v offset 116 count 2
 File: /home/ora10g/oradata/roger/roger01.dbf (5)
 Block: 29197   Offsets:  116 to  117  Dba:0x0140720d
-------------------------------------------------------
 0200                                l ..
 <16 bytes per line>
BBED> modify /x 01 offset 116
 File: /home/ora10g/oradata/roger/roger01.dbf (5)
 Block: 29197            Offsets:  116 to  117           Dba:0x0140720d
------------------------------------------------------------------------
 0100 
 <32 bytes per line>
BBED> sum apply
Check value for File 5, Block 29197:
current = 0x514c, required = 0x514c
BBED> verify
DBVERIFY - Verification starting
FILE = /home/ora10g/oradata/roger/roger01.dbf
BLOCK = 29197
Block Checking: DBA = 21000717, Block Type = KTB-managed data block
data header at 0xb7e28264
kdbchk: the amount of space used is not equal to block size
        used=30 fsc=0 avsp=8056 dtl=8088
Block 29197 failed with check code 6110
DBVERIFY - Verification complete
Total Blocks Examined         : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing   (Data) : 1
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0
BBED> p kdbh
struct kdbh, 14 bytes                       @100
   ub1 kdbhflag                             @100      0x00 (NONE)
   b1 kdbhntab                              @101      1
   b2 kdbhnrow                              @102      1
   sb2 kdbhfrre                             @104     -1
   sb2 kdbhfsbo                             @106      20
   sb2 kdbhfseo                             @108      8069
   b2 kdbhavsp                              @110      8056
   b2 kdbhtosp                              @112      8056
BBED> d /v offset 110 count 4
 File: /home/ora10g/oradata/roger/roger01.dbf (5)
 Block: 29197   Offsets:  110 to  113  Dba:0x0140720d
-------------------------------------------------------
 781f781f                            l x.x.
 <16 bytes per line>
BBED> modify /x 7a1f7a
 File: /home/ora10g/oradata/roger/roger01.dbf (5)
 Block: 29197            Offsets:  110 to  113           Dba:0x0140720d
------------------------------------------------------------------------
 7a1f7a1f 
 <32 bytes per line>
BBED> sum apply
Check value for File 5, Block 29197:
current = 0x514c, required = 0x514c
BBED> verify
DBVERIFY - Verification starting
FILE = /home/ora10g/oradata/roger/roger01.dbf
BLOCK = 29197
DBVERIFY - Verification complete
Total Blocks Examined         : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing   (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0

ok，数据块修改完毕了，下面来修改回滚段头。

首先定位该回滚段的段头块地址：

www.killdb.com>select header_file,header_block from dba_segments where
  2   segment_name='_SYSSMU6$';
HEADER_FILE HEADER_BLOCK
----------- ------------
          2         5689
www.killdb.com>

定位到回滚段头地址之后，我们就可以利用bbed来进行修改了。注意，bbed是无法直接map非system 回滚段头块的，因此修改

起来相对比较费劲，但是其结构和system 回滚段头的结构并没有太大的差异。
+++ 根据ffff进行搜索，定位事务，修改uel等一系列动作

BBED> set file 2 block 5689
        FILE#           2
        BLOCK#          5689
BBED> find /x ffff
 File: /home/ora10g/oradata/roger/undotbs01.dbf (2)
 Block: 5689             Offsets: 6137 to 6186           Dba:0x00801639
------------------------------------------------------------------------
 ffff7f00 000000ba 010a0001 00561a00 000000b6 01020000 001a1f00 000000b6
 01340000 00620800 00000000 00000000 0000 
 <32 bytes per line>
BBED> f
 File: /home/ora10g/oradata/roger/undotbs01.dbf (2)
 Block: 5689             Offsets: 6498 to 6547           Dba:0x00801639
------------------------------------------------------------------------
 ffff0000 00000000 00000000 00000100 00006ada 4d548301 00004316 8000d907
 52010000 00000900 01000000 00000000 0000 
 <32 bytes per line>
BBED> d /v offset 6480 count 100
 File: /home/ora10g/oradata/roger/undotbs01.dbf (2)
 Block: 5689    Offsets: 6480 to 6579  Dba:0x00801639
-------------------------------------------------------
 83010000 43168000 b9075201 00000000 l ....C.....R.....
 0900ffff 00000000 00000000 00000000 l ................
 01000000 6ada4d54 83010000 43168000 l ....j.MT....C...
 d9075201 00000000 09000100 00000000 l ..R.............
 00000000 00000000 01000000 00000000 l ................
 82010000 3c168000 4c005201 00000000 l ....<...L.R.....
 09000a00                            l ....
 <16 bytes per line>
BBED> modify /x 0001 offset 6498
 File: /home/ora10g/oradata/roger/undotbs01.dbf (2)
 Block: 5689             Offsets: 6498 to 6597           Dba:0x00801639
------------------------------------------------------------------------
 00010000 00000000 00000000 00000100 00006ada 4d548301 00004316 8000d907
 52010000 00000900 01000000 00000000 00000000 00000100 00000000 00008201
 00003c16 80004c00 52010000 00000900 0a000000 00000000 00000000 00000100
 0000f7c8 
 <32 bytes per line>
BBED> modify /x 0009 offset 6535
 File: /home/ora10g/oradata/roger/undotbs01.dbf (2)
 Block: 5689             Offsets: 6535 to 6542           Dba:0x00801639
------------------------------------------------------------------------
 0009ffff 00000000 
 <32 bytes per line>
BBED> modify /x 00ffff offset 6537
 File: /home/ora10g/oradata/roger/undotbs01.dbf (2)
 Block: 5689             Offsets: 6537 to 6544           Dba:0x00801639
------------------------------------------------------------------------
 00ffff00 00000000 
 <32 bytes per line>
BBED>
BBED> find /x 0700 TOP
 File: /home/ora10g/oradata/roger/undotbs01.dbf (2)
 Block: 5689             Offsets:  112 to  119           Dba:0x00801639
------------------------------------------------------------------------
 07000000 41168000 
 <32 bytes per line>
BBED> f
 File: /home/ora10g/oradata/roger/undotbs01.dbf (2)
 Block: 5689             Offsets: 6126 to 6133           Dba:0x00801639
------------------------------------------------------------------------
 07000182 01006800 
 <32 bytes per line>
BBED> f
 File: /home/ora10g/oradata/roger/undotbs01.dbf (2)
 Block: 5689             Offsets: 6338 to 6345           Dba:0x00801639
------------------------------------------------------------------------
 07000000 00000000 
 <32 bytes per line>
BBED> f
BBED-00212: search string not found
BBED> modify /x 08 offset 6126
 File: /home/ora10g/oradata/roger/undotbs01.dbf (2)
 Block: 5689             Offsets: 6126 to 6133           Dba:0x00801639
------------------------------------------------------------------------
 08000182 01006800 
 <32 bytes per line>
BBED> find /x 6ada4d54 TOP
 File: /home/ora10g/oradata/roger/undotbs01.dbf (2)
 Block: 5689             Offsets: 6516 to 6523           Dba:0x00801639
------------------------------------------------------------------------
 6ada4d54 83010000 
 <32 bytes per line>
BBED> modify /x 6d offset 6516
 File: /home/ora10g/oradata/roger/undotbs01.dbf (2)
 Block: 5689             Offsets: 6516 to 6523           Dba:0x00801639
------------------------------------------------------------------------
 6dda4d54 83010000 
 <32 bytes per line>
BBED> sum apply
Check value for File 2, Block 5689:
current = 0x74dd, required = 0x74dd
BBED> verify
DBVERIFY - Verification starting
FILE = /home/ora10g/oradata/roger/undotbs01.dbf
BLOCK = 5689
Block Checking: DBA = 8394297, Block Type = System Managed Segment Header Block
ERROR: SMU Segment Header Corrupted.  Error Code = 38504
ktu4smck: SCN commited txn list is not sorted.
  previous txn slot=7, scn=0x0000.015207b9
  offending txn slot=1, scn=0x0000.0152066f
  TRN CTL:: seq: 0x01ba chd: 0x0009 ctl: 0x0008 inc: 0x00000000 nfb: 0x0000
            mgc: 0x8201 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
            uba: 0x00801643.01ba.0b scn: 0x0000.0152000d
            Version: 0x01
  FREE BLOCK POOL::
    uba: 0x00000000.01ba.0a  ext: 0x1  spc: 0x1a56
    uba: 0x00000000.01b6.02  ext: 0x0  spc: 0x1f1a
    uba: 0x00000000.01b6.34  ext: 0x0  spc: 0x862
    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       bcl     cmt
  -----------------------------------------------------------------------------------------
   0x00    9    0x00  0x0183  0x0001  0x0000.0152063c  0x00801642  0x0000.000.00000000  0x00000001   0x00000000    1414387430
   0x01    9    0x00  0x0183  0x0002  0x0000.0152066f  0x00801642  0x0000.000.00000000  0x00000001   0x00000000    1414387550
   0x02    9    0x00  0x0183  0x0005  0x0000.015206a3  0x00801643  0x0000.000.00000000  0x00000001   0x00000000    1414387670
   0x03    9    0x00  0x0183  0x0007  0x0000.01520786  0x00801643  0x0000.000.00000000  0x00000001   0x00000000    1414388210
   0x04    9    0x00  0x0183  0x0006  0x0000.0152071f  0x00801643  0x0000.000.00000000  0x00000001   0x00000000    1414387968
   0x05    9    0x00  0x0183  0x0004  0x0000.015206d7  0x00801643  0x0000.000.00000000  0x00000001   0x00000000    1414387790
   0x06    9    0x00  0x0183  0x0003  0x0000.01520753  0x00801643  0x0000.000.00000000  0x00000001   0x00000000    1414388090
   0x07    9    0x00  0x0183  0x0001  0x0000.015207b9  0x00801643  0x0000.000.00000000  0x00000001   0x00000000    1414388333
   0x08    9    0x00  0x0183  0xffff  0x0000.015207d9  0x00801643  0x0000.000.00000000  0x00000001   0x00000000    0
   0x09    9    0x00  0x0182  0x000a  0x0000.0152004c  0x0080163c  0x0000.000.00000000  0x00000001   0x00000000    1414383863
   0x0a    9    0x00  0x0182  0x000b  0x0000.0152007f  0x0080163c  0x0000.000.00000000  0x00000001   0x00000000    1414383985
   。。。。。。。
   0x2b    9    0x00  0x0182  0x002c  0x0000.01520561  0x00801642  0x0000.000.00000000  0x00000001   0x00000000    1414386947
   0x2c    9    0x00  0x0182  0x002d  0x0000.01520596  0x00801642  0x0000.000.00000000  0x00000001   0x00000000    1414387070
   0x2d    9    0x00  0x0182  0x002e  0x0000.015205ca  0x00801642  0x0000.000.00000000  0x00000001   0x00000000    1414387190
   0x2e    9    0x00  0x0182  0x002f  0x0000.015205e5  0x00801642  0x0000.000.00000000  0x00000001   0x00000000    1414387243
   0x2f    9    0x00  0x0182  0x0000  0x0000.01520608  0x00801642  0x0000.000.00000000  0x00000001   0x00000000    1414387310
DBVERIFY - Verification complete
Total Blocks Examined         : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing   (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0
BBED>
BBED> find /x b90752 TOP
 File: /home/ora10g/oradata/roger/undotbs01.dbf (2)
 Block: 5689             Offsets: 6488 to 6495           Dba:0x00801639
------------------------------------------------------------------------
 b9075201 00000000 
 <32 bytes per line>
BBED> modify /x bc offset 6488
 File: /home/ora10g/oradata/roger/undotbs01.dbf (2)
 Block: 5689             Offsets: 6488 to 6495           Dba:0x00801639
------------------------------------------------------------------------
 bc075201 00000000 
 <32 bytes per line>
BBED> sum apply
Check value for File 2, Block 5689:
current = 0x74d8, required = 0x74d8
BBED> verify
DBVERIFY - Verification starting
FILE = /home/ora10g/oradata/roger/undotbs01.dbf
BLOCK = 5689
Block Checking: DBA = 8394297, Block Type = System Managed Segment Header Block
ERROR: SMU Segment Header Corrupted.  Error Code = 38504
ktu4smck: SCN commited txn list is not sorted.
  previous txn slot=7, scn=0x0000.015207bc
  offending txn slot=1, scn=0x0000.0152066f
  TRN CTL:: seq: 0x01ba chd: 0x0009 ctl: 0x0008 inc: 0x00000000 nfb: 0x0000
            mgc: 0x8201 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
            uba: 0x00801643.01ba.0b scn: 0x0000.0152000d
            Version: 0x01
  FREE BLOCK POOL::
    uba: 0x00000000.01ba.0a  ext: 0x1  spc: 0x1a56
    uba: 0x00000000.01b6.02  ext: 0x0  spc: 0x1f1a
    uba: 0x00000000.01b6.34  ext: 0x0  spc: 0x862
    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       bcl     cmt
  -----------------------------------------------------------------------------------------
   0x00    9    0x00  0x0183  0x0001  0x0000.0152063c  0x00801642  0x0000.000.00000000  0x00000001   0x00000000    1414387430
   0x01    9    0x00  0x0183  0x0002  0x0000.0152066f  0x00801642  0x0000.000.00000000  0x00000001   0x00000000    1414387550
   0x02    9    0x00  0x0183  0x0005  0x0000.015206a3  0x00801643  0x0000.000.00000000  0x00000001   0x00000000    1414387670
   0x03    9    0x00  0x0183  0x0007  0x0000.01520786  0x00801643  0x0000.000.00000000  0x00000001   0x00000000    1414388210
   0x04    9    0x00  0x0183  0x0006  0x0000.0152071f  0x00801643  0x0000.000.00000000  0x00000001   0x00000000    1414387968
   0x05    9    0x00  0x0183  0x0004  0x0000.015206d7  0x00801643  0x0000.000.00000000  0x00000001   0x00000000    1414387790
   0x06    9    0x00  0x0183  0x0003  0x0000.01520753  0x00801643  0x0000.000.00000000  0x00000001   0x00000000    1414388090
   0x07    9    0x00  0x0183  0x0001  0x0000.015207bc  0x00801643  0x0000.000.00000000  0x00000001   0x00000000    1414388333
   0x08    9    0x00  0x0183  0xffff  0x0000.015207d9  0x00801643  0x0000.000.00000000  0x00000001   0x00000000    0
   0x09    9    0x00  0x0182  0x000a  0x0000.0152004c  0x0080163c  0x0000.000.00000000  0x00000001   0x00000000    1414383863
   0x0a    9    0x00  0x0182  0x000b  0x0000.0152007f  0x0080163c  0x0000.000.00000000  0x00000001   0x00000000    1414383985
   0x0b    9    0x00  0x0182  0x000c  0x0000.015200b3  0x0080163c  0x0000.000.00000000  0x00000001   0x00000000    1414384103
   0x0c    9    0x00  0x0182  0x000d  0x0000.015200e6  0x0080163c  0x0000.000.00000000  0x00000001   0x00000000    1414384225
   。。。。。。
   0x2e    9    0x00  0x0182  0x002f  0x0000.015205e5  0x00801642  0x0000.000.00000000  0x00000001   0x00000000    1414387243
   0x2f    9    0x00  0x0182  0x0000  0x0000.01520608  0x00801642  0x0000.000.00000000  0x00000001   0x00000000    1414387310
DBVERIFY - Verification complete
Total Blocks Examined         : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing   (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0
BBED> modify /x 0x0800 offset 6498
 File: /home/ora10g/oradata/roger/undotbs01.dbf (2)
 Block: 5689             Offsets: 6498 to 6505           Dba:0x00801639
------------------------------------------------------------------------
 08000000 00000000 
 <32 bytes per line>
BBED> sum apply
Check value for File 2, Block 5689:
current = 0x74d1, required = 0x74d1
BBED>
BBED> verify
DBVERIFY - Verification starting
FILE = /home/ora10g/oradata/roger/undotbs01.dbf
BLOCK = 5689
DBVERIFY - Verification complete
Total Blocks Examined         : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing   (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0

通过一系列修改之后，我们发现bbed校验不在报错了。这里我们主要修改了如下几个地方：
ctl: 0&＃215;0007 &＃8211;> ctl: 0&＃215;0008

index:07的事务，修改为如下：状态从10改成9，uel从ffff改成08，同时该事务对于的scn稍微修改大一点点，我这里加3.
uel是指向下一个slot的信息，因此这里还需要把index：08的对应的uel改成ffff. 表明该事务是当前最新的一个. 修改完知道的情况：

 TRN CTL:: seq: 0x01ba chd: 0x0009 ctl: 0x0008 inc: 0x00000000 nfb: 0x0000
            mgc: 0x8201 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
            uba: 0x00801643.01ba.0b scn: 0x0000.0152000d
Version: 0x01
  FREE BLOCK POOL::
    uba: 0x00000000.01ba.0a ext: 0x1  spc: 0x1a56
    uba: 0x00000000.01b6.02 ext: 0x0  spc: 0x1f1a
    uba: 0x00000000.01b6.34 ext: 0x0  spc: 0x862
    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    cmt
  ------------------------------------------------------------------------------------------------
   0x00    9    0x00  0x0183  0x0001  0x0000.0152063c  0x00801642  0x0000.000.00000000  0x00000001   0x00000000  1414387430
   0x01    9    0x00  0x0183  0x0002  0x0000.0152066f  0x00801642  0x0000.000.00000000  0x00000001   0x00000000  1414387550
   0x02    9    0x00  0x0183  0x0005  0x0000.015206a3  0x00801643  0x0000.000.00000000  0x00000001   0x00000000  1414387670
   0x03    9    0x00  0x0183  0x0007  0x0000.01520786  0x00801643  0x0000.000.00000000  0x00000001   0x00000000  1414388210
   0x04    9    0x00  0x0183  0x0006  0x0000.0152071f  0x00801643  0x0000.000.00000000  0x00000001   0x00000000  1414387968
   0x05    9    0x00  0x0183  0x0004  0x0000.015206d7  0x00801643  0x0000.000.00000000  0x00000001   0x00000000  1414387790
   0x06    9    0x00  0x0183  0x0003  0x0000.01520753  0x00801643  0x0000.000.00000000  0x00000001   0x00000000  1414388090
   0x07    9    0x00  0x0183  0x0008  0x0000.015207bc  0x00801643  0x0000.000.00000000  0x00000001   0x00000000  1414388333
   0x08    9    0x00  0x0183  0xffff  0x0000.015207d9  0x00801643  0x0000.000.00000000  0x00000001   0x00000000  0
   0x09    9    0x00  0x0182  0x000a  0x0000.0152004c  0x0080163c  0x0000.000.00000000  0x00000001   0x00000000  1414383863
   0x0a    9    0x00  0x0182  0x000b  0x0000.0152007f  0x0080163c  0x0000.000.00000000  0x00000001   0x00000000  1414383985
   0x0b    9    0x00  0x0182  0x000c  0x0000.015200b3  0x0080163c  0x0000.000.00000000  0x00000001   0x00000000  1414384103

修改完之后的情况和实际的情况就有点符合了，测试了一把，发现仍然无法直接drop。

www.killdb.com>drop rollback segment "_SYSSMU6$";
drop rollback segment "_SYSSMU6$"
*
ERROR at line 1:
ORA-30025: DROP segment '_SYSSMU6$' (in undo tablespace) not allowed
www.killdb.com>select d.segment_name, d.tablespace_name, s.waits, s.shrinks,
  2   s.wraps, s.status
  3   from v$rollstat s, dba_rollback_segs d
  4   where s.usn = d.segment_id
  5   order by 1;
SEGMENT_NAME        TABLESPACE_NAME        WAITS    SHRINKS      WRAPS STATUS
------------------- ----------------- ---------- ---------- ---------- ---------------
SYSTEM              SYSTEM                     1          0          0 ONLINE
_SYSSMU11$          UNDOTBS2                   2          0         11 ONLINE
_SYSSMU12$          UNDOTBS2                   0          0          0 ONLINE
_SYSSMU6$           UNDOTBS1                   3          1         12 PENDING OFFLINE
www.killdb.com>set autot on
www.killdb.com>select usn,STATUS from v$rollstat;
       USN STATUS
---------- ---------------
         0 ONLINE
        11 ONLINE
        12 ONLINE
         6 PENDING OFFLINE
Execution Plan
----------------------------------------------------------
Plan hash value: 3398314359
----------------------------------------------------------------------------
| Id  | Operation        | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |         |     1 |    52 |     0   (0)| 00:00:01 |
|*  1 |  FIXED TABLE FULL| X$KTURD |     1 |    52 |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("KTURDSIZ"<>0 AND "INST_ID"=USERENV('INSTANCE') AND
              BITAND("KTURDFLG",7)<>3)

可以看到这个试图的数据本质是来自这个x表。通过kturdflg来判断回滚段的状态。不过这里仍然无法知道Oracle是如何来进行实际判断的。通过10046 发现本质上也读取的回滚段头：

www.killdb.com>alter session set events '10046 trace name context forever ,level 12';
Session altered.
www.killdb.com>select usn,STATUS from v$rollstat;
       USN STATUS
---------- ---------------
         0 ONLINE
        11 ONLINE
        12 ONLINE
         6 PENDING OFFLINE
www.killdb.com>alter session set events '10046 trace name context off';
Session altered.
10046 trace内容如下：
=====================
PARSING IN CURSOR #1 len=33 dep=0 uid=0 oct=3 lid=0 tim=1381242131173055 hv=629838979 ad='51dcac2c'
select usn,STATUS from v$rollstat
END OF STMT
PARSE #1:c=1999,e=31353,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1381242131173042
BINDS #1:
EXEC #1:c=1000,e=8133,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1381242131181315
WAIT #1: nam='SQL*Net message to client' ela= 7 driver id=1650815232 #bytes=1 p3=0 obj#=335 tim=1381242131181469
WAIT #1: nam='db file sequential read' ela= 28 file#=1 block#=9 blocks=1 obj#=0 tim=1381242131181698
FETCH #1:c=0,e=243,p=1,cr=0,cu=1,mis=0,r=1,dep=0,og=1,tim=1381242131181776
WAIT #1: nam='SQL*Net message from client' ela= 246 driver id=1650815232 #bytes=1 p3=0 obj#=335 tim=1381242131182134
WAIT #1: nam='SQL*Net message to client' ela= 4 driver id=1650815232 #bytes=1 p3=0 obj#=335 tim=1381242131182232
WAIT #1: nam='db file sequential read' ela= 22 file#=7 block#=25 blocks=1 obj#=0 tim=1381242131182426
WAIT #1: nam='db file sequential read' ela= 21 file#=2 block#=5689 blocks=1 obj#=0 tim=1381242131182568

从10046 跟踪来看，Oracle这里一共读取了3个块：file 1 ,block 9file 7 block 25,file 2 block 5689
file 1 block 9,不用想，肯定是system回滚段.
这里的file 7 block 25是当前的undo表空间的一个回滚段头的地址：

www.killdb.com>select owner,segment_name,HEADER_FILE,HEADER_BLOCK from
  2  dba_Segments where HEADER_FILE=7 and HEADER_BLOCK=25;
OWNER                          SEGMENT_NAME              HEADER_FILE HEADER_BLOCK
------------------------------ ------------------------- ----------- ------------
SYS                            _SYSSMU12$                          7           25

而file 2 block 5689 就是我们之前有问题的这个回滚段头地址。
最后想了很久，也没有明白，oracle这里的回滚段状态pending offline是如何判断出来的。
最后处理这个问题，其实方法很多，几年前写过一篇文章，用隐含参数，bbed等方式都可以很容易处理。
在10gR2版本中，最简单的方式就是这样：

www.killdb.com>alter system set "_smu_debug_mode"=4;
System altered.
www.killdb.com>drop rollback segment "_SYSSMU6$";
Rollback segment dropped.
www.killdb.com>alter system set "_smu_debug_mode"=0;
System altered.

随便玩玩，不要再生产随便弄，概不负责哈！大家一起来探讨下这个问题！