手工清除回滚段的几种方法
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
本文链接地址: 手工清除回滚段的几种方法
在某些情况下，我们需要手动去清除一些有问题的回滚段，如果该回滚段中
包含活动事务，那么使用正常的方式将无法drop，所以此时你drop undo tablespace 也将失败。
可能就会遇到如下的错误：
|
1 2 3 4 5 |
SQL> drop tablespace undotbs1 including contents and datafiles; drop tablespace undotbs1 including contents and datafiles * ERROR at line 1: ORA-01561: failed to remove all objects in the tablespace specified |
下面介绍几种方法：
####### 方法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 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 |
SQL> show parameter undo NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string AUTO undo_retention integer 900 undo_tablespace string UNDOTBS1 SQL> create undo tablespace undotbs2 datafile '/oracle/product/oradata/roger/undotbs2.dbf' 2 size 50m autoextend off; Tablespace created. SQL> create undo tablespace undotbs3 datafile '/oracle/product/oradata/roger/undotbs3.dbf' 2 size 50m autoextend off; Tablespace created. SQL> conn roger/roger Connected. SQL> create table ht_01 as select * from dba_objects where rownum <10; Table created. SQL> delete from ht_01 where rownum <5; 4 rows deleted. SQL> -- 不提交 SQL> select owner,segment_name,SEGMENT_ID,FILE_ID,BLOCK_ID,STATUS,tablespace_name 2 from dba_rollback_segs; OWNER SEGMENT_NAME SEGMENT_ID FILE_ID BLOCK_ID STATUS TABLESPACE_NAME ------ -------------- ---------- ---------- ---------- -------- --------------- SYS SYSTEM 0 1 9 ONLINE SYSTEM PUBLIC _SYSSMU1$ 1 2 9 ONLINE UNDOTBS1 PUBLIC _SYSSMU2$ 2 2 25 ONLINE UNDOTBS1 PUBLIC _SYSSMU3$ 3 2 41 ONLINE UNDOTBS1 PUBLIC _SYSSMU4$ 4 2 57 ONLINE UNDOTBS1 PUBLIC _SYSSMU5$ 5 2 73 ONLINE UNDOTBS1 PUBLIC _SYSSMU6$ 6 2 89 ONLINE UNDOTBS1 PUBLIC _SYSSMU7$ 7 2 105 ONLINE UNDOTBS1 PUBLIC _SYSSMU8$ 8 2 121 ONLINE UNDOTBS1 PUBLIC _SYSSMU9$ 9 2 137 ONLINE UNDOTBS1 PUBLIC _SYSSMU10$ 10 2 153 ONLINE UNDOTBS1 PUBLIC _SYSSMU11$ 11 6 9 OFFLINE UNDOTBS2 PUBLIC _SYSSMU12$ 12 6 25 OFFLINE UNDOTBS2 PUBLIC _SYSSMU13$ 13 6 41 OFFLINE UNDOTBS2 PUBLIC _SYSSMU14$ 14 6 57 OFFLINE UNDOTBS2 PUBLIC _SYSSMU15$ 15 6 73 OFFLINE UNDOTBS2 PUBLIC _SYSSMU16$ 16 6 89 OFFLINE UNDOTBS2 PUBLIC _SYSSMU17$ 17 6 105 OFFLINE UNDOTBS2 PUBLIC _SYSSMU18$ 18 6 121 OFFLINE UNDOTBS2 PUBLIC _SYSSMU19$ 19 6 137 OFFLINE UNDOTBS2 PUBLIC _SYSSMU20$ 20 6 153 OFFLINE UNDOTBS2 PUBLIC _SYSSMU21$ 21 7 9 OFFLINE UNDOTBS3 PUBLIC _SYSSMU22$ 22 7 25 OFFLINE UNDOTBS3 PUBLIC _SYSSMU23$ 23 7 41 OFFLINE UNDOTBS3 PUBLIC _SYSSMU24$ 24 7 57 OFFLINE UNDOTBS3 PUBLIC _SYSSMU25$ 25 7 73 OFFLINE UNDOTBS3 PUBLIC _SYSSMU26$ 26 7 89 OFFLINE UNDOTBS3 PUBLIC _SYSSMU27$ 27 7 105 OFFLINE UNDOTBS3 PUBLIC _SYSSMU28$ 28 7 121 OFFLINE UNDOTBS3 PUBLIC _SYSSMU29$ 29 7 137 OFFLINE UNDOTBS3 PUBLIC _SYSSMU30$ 30 7 153 OFFLINE UNDOTBS3 31 rows selected. SQL> select xidusn,xidslot,xidsqn,ubablk,ubafil,ubarec from v$transaction; XIDUSN XIDSLOT XIDSQN UBABLK UBAFIL UBAREC ---------- ---------- ---------- ---------- ---------- ---------- 9 13 299 439 2 47 SQL> select usn,name from v$rollname where usn=9; USN NAME ---------- -------------------------------------------------- 9 _SYSSMU9$ SQL> c/file_name/file_name,tablespace_name 1* select file_id,file_name,tablespace_name from dba_data_files order by 1 SQL> / FILE_ID FILE_NAME TABLESPACE_NAME ---------- ----------------------------------------------- --------------- 1 /oracle/product/oradata/roger/system01.dbf SYSTEM 2 /oracle/product/oradata/roger/undotbs01.dbf UNDOTBS1 3 /oracle/product/oradata/roger/sysaux01.dbf SYSAUX 4 /oracle/product/oradata/roger/users01.dbf USERS 5 /oracle/product/oradata/roger/roger01.dbf ROGER 6 /oracle/product/oradata/roger/undotbs2.dbf UNDOTBS2 7 /oracle/product/oradata/roger/undotbs3.dbf UNDOTBS3 7 rows selected. SQL> show parameter undo NAME TYPE VALUE ------------------------------------ ----------- ------------------- undo_management string AUTO undo_retention integer 900 undo_tablespace string UNDOTBS1 SQL> alter system set undo_tablespace=undotbs2; System altered. SQL> alter database datafile 2 offline; Database altered. SQL> shutdown abort; ORACLE instance shut down. 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> drop tablespace undotbs1 including contents and datafiles; drop tablespace undotbs1 including contents and datafiles * ERROR at line 1: ORA-01548: active rollback segment '_SYSSMU9$' found, terminate dropping tablespace -- 我们看到报错了, 意思是说该回滚段中还有活动事务. SQL> conn roger/roger Connected. SQL> show parameter undo NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ _collect_undo_stats boolean TRUE _gc_dissolve_undo_affinity boolean FALSE _gc_initiate_undo_affinity boolean TRUE _gc_undo_affinity boolean TRUE _gc_undo_affinity_locks boolean TRUE _in_memory_undo boolean TRUE _kcl_undo_grouping integer 32 _kcl_undo_locks integer 128 _optimizer_undo_changes boolean FALSE _optimizer_undo_cost_change string 10.2.0.4 _smon_undo_seg_rescan_limit integer 10 _undo_autotune boolean TRUE _undo_debug_mode integer 0 _undo_debug_usage integer 0 _verify_undo_quota boolean FALSE undo_management string AUTO undo_retention integer 900 undo_tablespace string UNDOTBS2 SQL> alter system set "_smu_debug_mode" = 4; System altered. SQL> alter rollback segment "_SYSSMU9$" offline; alter rollback segment "_SYSSMU9$" offline * ERROR at line 1: ORA-01598: rollback segment '_SYSSMU9$' is not online SQL> drop rollback segment "_SYSSMU9$"; drop rollback segment "_SYSSMU9$" * ERROR at line 1: ORA-01545: rollback segment '_SYSSMU9$' specified not available SQL> select owner,segment_name,SEGMENT_ID,FILE_ID,BLOCK_ID,STATUS 2 from dba_rollback_segs 3 where segment_name='_SYSSMU9$'; OWNER SEGMENT_NAME SEGMENT_ID FILE_ID BLOCK_ID STATUS ------ -------------------- ---------- ---------- ---------- ---------------- PUBLIC _SYSSMU9$ 9 2 137 NEEDS RECOVERY |
用如下隐含参数：
_offline_rollback_segments=(_SYSSMU9$)
_corrupted_rollback_segments=(_SYSSMU9$)
|
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> startup mount pfile='/oracle/pfile.ora'; 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. SQL> alter database open; Database altered. SQL> select owner,segment_name,SEGMENT_ID,FILE_ID,BLOCK_ID,STATUS 2 from dba_rollback_segs 3 where segment_name='_SYSSMU9$'; OWNER SEGMENT_NAME SEGMENT_ID FILE_ID BLOCK_ID STATUS ------ -------------------- ---------- ---------- ---------- ---------------- PUBLIC _SYSSMU9$ 9 2 137 NEEDS RECOVERY SQL> drop rollback segment "_SYSSMU9$"; Rollback segment dropped. SQL> drop tablespace undotbs1 including contents and datafiles; Tablespace dropped. SQL> select count(*) from ht_01; COUNT(*) ---------- 5 -- 数据丢失 |
这里可能有人会问上面的_undo_debug_mode参数为啥不管用？其实不是没效果，
是针对这种情况下，没用而已，对于自动undo管理模式，可以使用该参数来手工
删除某个回滚段，前提是该回滚段无活动事务。
######## 方法2 通过更改数据字典表来删除回滚段和undo tablespace
|
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 |
SQL> show parameter undo NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string AUTO undo_retention integer 900 undo_tablespace string UNDOTBS3 SQL> conn roger/roger Connected. SQL> select count(*) from ht_01; COUNT(*) ---------- 3 SQL> delete from ht_01 where rownum <2; 1 row deleted. SQL> -- 不提交 SQL> select xidusn,xidslot,xidsqn,ubablk,ubafil,ubarec from v$transaction; XIDUSN XIDSLOT XIDSQN UBABLK UBAFIL UBAREC ---------- ---------- ---------- ---------- ---------- ---------- 2 5 310 345 7 6 SQL> select usn,name from v$rollname where usn=2; USN NAME ---------- -------------------------------------------------- 2 _SYSSMU2$ SQL> alter system set undo_tablespace=undotbs4; System altered. SQL> alter database datafile 7 offline; Database altered. SQL> shutdown abort; ORACLE instance shut down. 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> drop tablespace undotbs3 including contents and datafiles; drop tablespace undotbs3 including contents and datafiles * ERROR at line 1: ORA-01548: active rollback segment '_SYSSMU2$' found, terminate dropping tablespace SQL> select ts# from ts$ where name='UNDOTBS3'; TS# ---------- 7 SQL> select file#,block#,TYPE#,TS# from seg$ where ts#=7; FILE# BLOCK# TYPE# TS# ---------- ---------- ---------- ---------- 7 9 10 7 7 25 10 7 7 41 10 7 7 57 10 7 7 73 10 7 7 89 10 7 7 105 10 7 7 121 10 7 7 137 10 7 7 153 10 7 7 265 3 7 7 281 10 7 12 rows selected. SQL> select owner,segment_name,SEGMENT_ID,FILE_ID,BLOCK_ID,STATUS 2 from dba_rollback_segs 3 where file_id=7; OWNER SEGMENT_NAME SEGMENT_ID FILE_ID BLOCK_ID STATUS ------ ------------- ---------- ---------- ---------- ---------------- PUBLIC _SYSSMU2$ 2 7 281 NEEDS RECOVERY PUBLIC _SYSSMU21$ 21 7 9 OFFLINE PUBLIC _SYSSMU22$ 22 7 25 OFFLINE PUBLIC _SYSSMU23$ 23 7 41 OFFLINE PUBLIC _SYSSMU24$ 24 7 57 OFFLINE PUBLIC _SYSSMU25$ 25 7 73 OFFLINE PUBLIC _SYSSMU26$ 26 7 89 OFFLINE PUBLIC _SYSSMU27$ 27 7 105 OFFLINE PUBLIC _SYSSMU28$ 28 7 121 OFFLINE PUBLIC _SYSSMU29$ 29 7 137 OFFLINE PUBLIC _SYSSMU30$ 30 7 153 OFFLINE 11 rows selected. SQL> update seg$ set type# = 3 where ts#=7 and file#=7 and BLOCK#=281; 1 row updated. SQL> commit; Commit complete. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. 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# from undo$ where file#=7; US# NAME FILE# BLOCK# ---------- --------------- ------- ---------- 1 _SYSSMU1$ 7 265 2 _SYSSMU2$ 7 281 21 _SYSSMU21$ 7 9 22 _SYSSMU22$ 7 25 23 _SYSSMU23$ 7 41 24 _SYSSMU24$ 7 57 25 _SYSSMU25$ 7 73 26 _SYSSMU26$ 7 89 27 _SYSSMU27$ 7 105 28 _SYSSMU28$ 7 121 29 _SYSSMU29$ 7 137 30 _SYSSMU30$ 7 153 12 rows selected. SQL> delete from undo$ where ts#=7 and US#=2; 1 row deleted. SQL> delete from seg$ where ts#=7 and file#=7 and block#=281; 1 row deleted. SQL> commit; Commit complete. SQL> drop rollback segment "_SYSSMU2$"; drop rollback segment "_SYSSMU2$" * ERROR at line 1: ORA-01545: rollback segment '_SYSSMU2$' specified not available -- 由于我们已经从几个数据字典表中将该段清除了，所以需要用包检查下 SQL> execute hcheck.full PL/SQL procedure successfully completed. SQL> drop rollback segment "_SYSSMU2$"; Rollback segment dropped. SQL> drop tablespace undotbs3 including contents and datafiles; drop tablespace undotbs3 including contents and datafiles * ERROR at line 1: ORA-01561: failed to remove all objects in the tablespace specified 对于该错误，处理起来就非常容易了，如下： SQL> update seg$ set type#=3 where ts#=7; 11 rows updated. SQL> commit; Commit complete. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. 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> drop tablespace undotbs3 including contents and datafiles; Tablespace dropped. -- Drop Tablespace 成功 |
简单的总结下，其实我们只要通过如下步骤就能轻易的删除：
1. 将回滚段更改为临时段
2. 重启实例
3. 从seg$中删除记录
4. 从undo$中删除记录
需要注意一下的是，如果不使用hcheck.full 那么直接drop tablespace可能遇到如下错误：
|
1 2 3 4 5 |
SQL> drop tablespace undotbs2 including contents and datafiles; drop tablespace undotbs2 including contents and datafiles * ERROR at line 1: ORA-00600: internal error code, arguments: [ktssdrp1], [5], [6], [25], [], [], [], [] |
########## 方法3 使用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 |
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。
|
1 2 3 4 5 6 7 |
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 |
|
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 |
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 |
|
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 |
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 |
5 Responses to “手工清除回滚段的几种方法”
不错，转帖过来。
厉害。。。
[…] 原文链接：http://www.killdb.com/?p=196 […]
bbed修改是比较简单 只要把状态改成2就行了
SQL> startup mount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 1573527552 bytes
Fixed Size 1344420 bytes
Variable Size 721423452 bytes
Database Buffers 838860800 bytes
Redo Buffers 11898880 bytes
Database mounted.
SQL> alter database datafile 6 offline drop;
Database altered.
SQL> alter database open;
Database altered.
SQL> DROP tablespace undotbs including contents AND datafiles;
DROP tablespace undotbs including contents AND datafiles
*
ERROR at line 1:
ORA-01548: active rollback segment ‘_SYSSMU13_14654527$’ found, terminate dropping tablespace
前面必须加个offline drop的步骤。有点问题
Leave a Reply
You must be logged in to post a comment.