时间：2021-07-01 10:21:17 帮助过：95人阅读
本站文章除注明转载外，均为本站原创： 转载自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 ‘_SYSSMU559$’ (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×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×0007 –> ctl: 0×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.
随便玩玩，不要再生产随便弄，概不负责哈！大家一起来探讨下这个问题！
Related posts: