ORA-30025: DROP segment ‘_SYSSMU559$’ (in undo tablespace) not allowed
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
本文链接地址: ORA-30025: DROP segment ‘_SYSSMU559$’ (in undo tablespace) not allowed
这里是今天一些学生的问题，下午没事研究了一下。 发现非常的奇怪，大家一起来探讨下。 对于没有活动事务的情况，其回滚段的状态居然是Pending offline.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
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 该回滚段头发现比较怪异：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
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地址居然是0x000000000.
针对这个问题，我进行了一个测试，花了不少时间。
Session 1:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 |
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下该回滚段头:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
******************************************************************************** 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来完成.
+++ 首先把测试表里面这个数据块中的事务给提交了
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 |
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，数据块修改完毕了，下面来修改回滚段头。
首先定位该回滚段的段头块地址：
|
1 2 3 4 5 6 7 8 |
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等一系列动作
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 |
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: 0x0007 –> ctl: 0x0008
index:07的事务，修改为如下： 状态从10改成9，uel从ffff改成08，同时该事务对于的scn稍微修改大一点点，我这里加3.
uel是指向下一个slot的信息，因此这里还需要把index：08的对应的uel改成ffff. 表明该事务是当前最新的一个. 修改完知道的情况：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
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。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
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 发现本质上也读取的回滚段头：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
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表空间的一个回滚段头的地址：
|
1 2 3 4 5 6 |
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版本中，最简单的方式就是这样：
|
1 2 3 4 5 6 7 8 9 10 |
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. |
随便玩玩，不要再生产随便弄，概不负责哈！大家一起来探讨下这个问题！
Leave a Reply
You must be logged in to post a comment.