一聚教程网:一个值得你收藏的教程网站

最新下载

热门教程

oralce提示ORA-30025: DROP segment ‘_SYSSMU559$’ (in undo tablespace) not allowed错误

时间:2022-06-29 09:40:38 编辑:袖梨 来源:一聚教程网

这里是今天一些学生的问题,下午没事研究了一下。 发现非常的奇怪,大家一起来探讨下。 对于没有活动事务的情况,其回滚段的状态居然是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.111com.net>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.111com.net>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.111com.net>conn roger/roger
Connected.
www.111com.net>create table t_undo(a varchar2(20));
 
Table created.
 
www.111com.net>insert into t_undo values('killdb');
 
1 row created.
 
www.111com.net>insert into t_undo values('roger');
 
1 row created.
 
www.111com.net>commit;
 
Commit complete.
 
www.111com.net>alter system switch logfile;
 
System altered.
 
www.111com.net>select * from t_undo;
 
A
--------------------
killdb
roger
 
www.111com.net>delete from t_undo where a='roger';
 
1 row deleted.
 
www.111com.net>
 
--这里我未提交这个事务.
Session 2:


www.111com.net> 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.111com.net>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.111com.net>show parameter undo
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1
www.111com.net>alter system set undo_tablespace=UNDOTBS2;
 
System altered.
 
www.111com.net>drop rollback segment "_SYSSMU6$";
drop rollback segment "_SYSSMU6$"
*
ERROR at line 1:
ORA-30025: DROP segment '_SYSSMU6$' (in undo tablespace) not allowed
 
www.111com.net>
www.111com.net>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.111com.net>select header_file,header_block from dba_segments where
  2   segment_name='_SYSSMU6$';
 
HEADER_FILE HEADER_BLOCK
----------- ------------
          2         5689
 
www.111com.net>
定位到回滚段头地址之后,我们就可以利用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&nb

热门栏目