sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 故障案例 >

【学习笔记】清除回滚段之使用Oracle BBED工具修改元数据修复回滚段

时间:2016-11-15 20:00   来源:Oracle研究中心   作者:网络   点击:

天萃荷净 Oracle研究中心学习笔记:分享一篇关于Oracle BBEd工具使用案例,使用BBED工具修复数据元数据来修复回滚段的方法笔记。

SQL> purge recyclebin;

Recyclebin purged.

SQL> CREATE TABLE ht01 AS SELECT owner,object_name,object_id
  2  FROM dba_objects
  3  WHERE object_id <100;

TABLE created.

SQL> SELECT COUNT(*) FROM ht01;

  COUNT(*)
----------
        98

SQL> DELETE FROM ht01 WHERE object_id <10;

8 ROWS deleted.

SQL>   -- 不提交

SQL> SHOW parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       INTEGER     900
undo_tablespace                      string      UNDOTBS4

SQL> SELECT owner,segment_name,SEGMENT_ID,FILE_ID,BLOCK_ID,STATUS
  2  FROM dba_rollback_segs
  3  WHERE tablespace_name='UNDOTBS4';

OWNER  SEGMENT_NAME  SEGMENT_ID    FILE_ID   BLOCK_ID  STATUS
------ ------------- ---------- ---------- ----------  ----------------
PUBLIC _SYSSMU1$              1          2        169  ONLINE
PUBLIC _SYSSMU2$              2          2        185  ONLINE
PUBLIC _SYSSMU3$              3          2          9  OFFLINE
PUBLIC _SYSSMU4$              4          2         25  OFFLINE
PUBLIC _SYSSMU5$              5          2         41  OFFLINE
PUBLIC _SYSSMU6$              6          2         57  OFFLINE
PUBLIC _SYSSMU7$              7          2         73  OFFLINE
PUBLIC _SYSSMU8$              8          2         89  OFFLINE
PUBLIC _SYSSMU10$            10          2        105  OFFLINE
PUBLIC _SYSSMU11$            11          2        121  OFFLINE
PUBLIC _SYSSMU12$            12          2        137  OFFLINE
PUBLIC _SYSSMU31$            31          2        153  OFFLINE

12 ROWS selected.

SQL> SELECT xidusn,xidslot,xidsqn,ubablk,ubafil,ubarec FROM v$transaction;

    XIDUSN    XIDSLOT     XIDSQN     UBABLK     UBAFIL     UBAREC
---------- ---------- ---------- ---------- ---------- ----------
         2          9          3        191          2         10

SQL> SELECT usn,name FROM v$rollname WHERE usn=2;

       USN NAME
---------- --------------------------------------------------
         2 _SYSSMU2$

SQL> SELECT file_id,file_name,STATUS FROM dba_data_files ORDER BY 1;

   FILE_ID FILE_NAME                                       STATUS
---------- ----------------------------------------------- ---------
         1 /oracle/product/oradata/roger/system01.dbf      AVAILABLE
         2 /oracle/product/oradata/roger/undotbs4.dbf      AVAILABLE
         3 /oracle/product/oradata/roger/sysaux01.dbf      AVAILABLE
         4 /oracle/product/oradata/roger/users01.dbf       AVAILABLE
         5 /oracle/product/oradata/roger/roger01.dbf       AVAILABLE
         6 /oracle/product/oradata/roger/undotbs5.dbf      AVAILABLE

6 ROWS selected.

SQL> ALTER system SET undo_tablespace=undotbs5;

System altered.

SQL> ALTER DATABASE datafile 2 offline;

DATABASE altered.

SQL> SELECT owner,SEGMENT_NAME,SEGMENT_TYPE,HEADER_FILE,HEADER_BLOCK
  2  FROM dba_segments
  3  WHERE segment_name='_SYSSMU2$';

OWNER       SEGMENT_NAME         SEGMENT_TYPE       HEADER_FILE HEADER_BLOCK
----------- -------------------- ------------------ ----------- ------------
SYS         _SYSSMU2$            TYPE2 UNDO                   2          185

单纯的通过修改undo$的STATUS$字典是无法DROP的,如下:

SQL> UPDATE undo$ SET STATUS$=4 WHERE file#=2 AND block#=185;
1 ROW updated.

SQL> commit;
Commit complete.

SQL> SELECT US#,NAME,FILE# ,BLOCK# ,STATUS$ FROM undo$;

       US# NAME                   FILE#     BLOCK#    STATUS$
---------- ----------------  ---------- ---------- ----------
         0 SYSTEM                     1          9          3
         1 _SYSSMU1$                  2        169          2
         2 _SYSSMU2$                  2        185          4
         3 _SYSSMU3$                  2          9          2
         4 _SYSSMU4$                  2         25          2
         5 _SYSSMU5$                  2         41          2
         6 _SYSSMU6$                  2         57          2
         7 _SYSSMU7$                  2         73          2
         8 _SYSSMU8$                  2         89          2
         9 _SYSSMU9$                  2        137          1
        10 _SYSSMU10$                 2        105          2
        11 _SYSSMU11$                 2        121          2
        12 _SYSSMU12$                 2        137          2
        13 _SYSSMU13$                 6         41          2
        14 _SYSSMU14$                 6         57          2
        15 _SYSSMU15$                 6         73          2
        16 _SYSSMU16$                 6         89          2
        17 _SYSSMU17$                 6        105          2
        18 _SYSSMU18$                 6        121          2
        19 _SYSSMU19$                 6        137          2
        20 _SYSSMU20$                 6        153          2
        21 _SYSSMU21$                 6          9          3
        22 _SYSSMU22$                 6         25          3
        23 _SYSSMU23$                 6         41          3
        24 _SYSSMU24$                 6         57          3
        25 _SYSSMU25$                 6         73          3
        26 _SYSSMU26$                 6         89          3
        27 _SYSSMU27$                 6        105          3
        28 _SYSSMU28$                 6        121          3
        29 _SYSSMU29$                 6        137          3
        30 _SYSSMU30$                 6        153          3
        31 _SYSSMU31$                 2        153          2

32 ROWS selected.

SQL> DROP ROLLBACK segment "_SYSSMU2$";
DROP ROLLBACK segment "_SYSSMU2$"
*
ERROR at line 1:
ORA-01545: ROLLBACK segment '_SYSSMU2$' specified NOT available

SQL> ALTER system SET "_smu_debug_mode" = 4;

System altered.

SQL> DROP ROLLBACK segment "_SYSSMU2$";
DROP ROLLBACK segment "_SYSSMU2$"
*
ERROR at line 1:
ORA-01545: ROLLBACK segment '_SYSSMU2$' specified NOT available

下面我们通过bbed来修改元数据,也就是直接修改回滚段的状态,将其修改为offline。

SQL> SELECT dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) blk#
  2  FROM undo$
  3  WHERE file#=2 AND block#=185;

     FILE#       BLK#
---------- ----------
         1        106
BBED> set file 1 block 106

        FILE#           1
        BLOCK#          106

BBED> p kdbr

sb2 kdbr[0]                                 @86       8079
sb2 kdbr[1]                                 @88       5234
sb2 kdbr[2]                                 @90       4754
sb2 kdbr[3]                                 @92       6654
sb2 kdbr[4]                                 @94       7860
sb2 kdbr[5]                                 @96       7805
sb2 kdbr[6]                                 @98       6818
sb2 kdbr[7]                                 @100      5123
sb2 kdbr[8]                                 @102      5068
sb2 kdbr[9]                                 @104      5940
sb2 kdbr[10]                                @106      7525
sb2 kdbr[11]                                @108      5013
sb2 kdbr[12]                                @110      4858
sb2 kdbr[13]                                @112      6053
sb2 kdbr[14]                                @114      7309
sb2 kdbr[15]                                @116      7255
sb2 kdbr[16]                                @118      7201
sb2 kdbr[17]                                @120      7146
sb2 kdbr[18]                                @122      7091
sb2 kdbr[19]                                @124      5885
sb2 kdbr[20]                                @126      6981
sb2 kdbr[21]                                @128      5290
sb2 kdbr[22]                                @130      5780
sb2 kdbr[23]                                @132      5726
sb2 kdbr[24]                                @134      5672
sb2 kdbr[25]                                @136      5618
sb2 kdbr[26]                                @138      5564
sb2 kdbr[27]                                @140      5509
sb2 kdbr[28]                                @142      5454
sb2 kdbr[29]                                @144      5399
sb2 kdbr[30]                                @146      5344
sb2 kdbr[31]                                @148      4803

BBED> p *kdbr[2]

rowdata[0]
----------
ub1 rowdata[0]                              @4822     0x2c

BBED> x /1rnnnnnnnnnnnnnn

rowdata[0]                                  @4822  
----------
flag@4822: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@4823: 0x00
cols@4824:   17

col    0[2] @4825: 2
col    1[9] @4828: -0
col    2[2] @4838: 1
col    3[2] @4841: 2
col    4[3] @4844: 185
col    5[1] @4848: 0
col    6[1] @4850: 0
col    7[1] @4852: 0
col    8[1] @4854: 0
col    9[1] @4856: 0
col   10[2] @4858: 4
col   11[2] @4861: 8
col   12[0] @4864: *NULL*
col   13[0] @4865: *NULL*
col   14[0] @4866: *NULL*
col   15[0] @4867: *NULL*
col   16[2] @4868: 1

BBED> modify /x 02 offset 4860

File: /oracle/product/oradata/roger/system01.dbf (1)
Block: 106              Offsets: 4860 to 5371           Dba:0x0040006a
------------------------------------------------------------------------
0202c109 ffffffff 02c1022c 001102c1 200a5f53 5953534d 55333124 02c10202
c10303c2 023604c3 53491901 8002c103 02c10201 8002c103 02c109ff ffffff02
c1022c00 1102c10d 0a5f5359 53534d55 31322402 c10202c1 0303c202 2604c353
49250180 02c10302 c1020180 02c10302 c109ffff ffff02c1 022c0011 02c1200a
5f535953 534d5533 312402c1 0202c103 03c20236 01800180 01800180 018002c1
0402c109 ffffffff 02c1022c 001102c1 0d0a5f53 5953534d 55313224 02c10202
c10303c2 02260180 01800180 01800180 02c10402 c109ffff ffff02c1 022c0011
02c10c0a 5f535953 534d5531 312402c1 0202c103 03c20216 04c35349 1b018002
c10802c1 04018002 c10302c1 09ffffff ff02c102 2c001102 c109095f 53595353
4d553824 02c10202 c10302c1 5a04c353 491d0180 03c20345 03c2033e 018002c1
0302c109 ffffffff 02c1022c 001102c1 08095f53 5953534d 55372402 c10202c1
0302c14a 04c35349 27018003 c2036403 c2052801 8002c103 02c109ff ffffff02
c1023c01 1102c103 095f5359 53534d55 322402c1 0202c108 03c20352 04c34a3a
1b018003 c2036403 c2031601 8002c106 02c108ff ffffff02 c1022c00 1102c102
095f5359 53534d55 312402c1 0202c103 03c20246 04c35351 51018003 c2033003
c2035101 8002c103 02c109ff ffffff02 c1022c01 1102c116 0a5f5359 53534d55

<32 bytes per line>

BBED> sum apply

Check value for File 1, Block 106:
current = 0x32cb, required = 0x32cb

BBED> verify

DBVERIFY - Verification starting
FILE = /oracle/product/oradata/roger/system01.dbf
BLOCK = 106

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
SQL> startup

ORACLE instance started.
Total System Global Area  167772160 bytes
Fixed SIZE                  1266392 bytes
Variable SIZE             104860968 bytes
DATABASE Buffers           58720256 bytes
Redo Buffers                2924544 bytes
DATABASE mounted.
DATABASE opened.

SQL> SELECT US#,NAME,FILE# ,BLOCK# ,STATUS$ FROM undo$ WHERE file#=2;

       US# NAME                    FILE#     BLOCK#    STATUS$
---------- ------------------ ---------- ---------- ----------
         1 _SYSSMU1$                   2        169          2
         2 _SYSSMU2$                   2        185          1
         3 _SYSSMU3$                   2          9          2
         4 _SYSSMU4$                   2         25          2
         5 _SYSSMU5$                   2         41          2
         6 _SYSSMU6$                   2         57          2
         7 _SYSSMU7$                   2         73          2
         8 _SYSSMU8$                   2         89          2
         9 _SYSSMU9$                   2        137          1
        10 _SYSSMU10$                  2        105          2
        11 _SYSSMU11$                  2        121          2
        12 _SYSSMU12$                  2        137          2
        31 _SYSSMU31$                  2        153          2

13 ROWS selected.

SQL> DROP ROLLBACK segment "_SYSSMU2$";

ROLLBACK segment dropped.

-- 成功drop回滚段。

SQL> conn roger/roger        
Connected.                   

SQL> SELECT COUNT(*) FROM ht01;

  COUNT(*)                   
----------                   
        90

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】清除回滚段之使用Oracle BBED工具修改元数据修复回滚段

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1281.html

Oracle研究中心

关键词:

Oracle bbed工具使用案例

使用bbed工具修复回滚段