手工清除回滚段的几种方式.doc

手工清除回滚段的几种方式一、 概要本文已经发表在个人博客/?p=196在某些情况下，我们需要手动去清除一些有问题的回滚段，如果该回滚段中包含活动事务，那么使用正常的方式将无法drop，所以此时你drop undo tablespace 也将失败。可能就会遇到如下的错误：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. 使用隐含参数SQL show parameter undoNAME TYPE VALUE- - -undo_management string AUTOundo_retention integer 900undo_tablespace string UNDOTBS1SQL 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 SQL conn roger/rogerConnected.SQL create table ht_01 as select * from dba_objects where rownum delete from ht_01 where rownum -不提交SQL select owner,segment_name,SEGMENT_ID,FILE_ID,BLOCK_ID,STATUS 2 ,tablespace_name from dba_rollback_segs;OWNER SEGMENT_NAME SEGMENT_ID FILE_ID BLOCK_ID STATUS TABLESPACE_NAME- - - - - - -SYS SYSTEM 0 1 9 ONLINE SYSTEMPUBLIC _SYSSMU1$ 1 2 9 ONLINE UNDOTBS1PUBLIC _SYSSMU2$ 2 2 25 ONLINE UNDOTBS1PUBLIC _SYSSMU3$ 3 2 41 ONLINE UNDOTBS1PUBLIC _SYSSMU4$ 4 2 57 ONLINE UNDOTBS1PUBLIC _SYSSMU5$ 5 2 73 ONLINE UNDOTBS1PUBLIC _SYSSMU6$ 6 2 89 ONLINE UNDOTBS1PUBLIC _SYSSMU7$ 7 2 105 ONLINE UNDOTBS1PUBLIC _SYSSMU8$ 8 2 121 ONLINE UNDOTBS1PUBLIC _SYSSMU9$ 9 2 137 ONLINE UNDOTBS1PUBLIC _SYSSMU10$ 10 2 153 ONLINE UNDOTBS1PUBLIC _SYSSMU11$ 11 6 9 OFFLINE UNDOTBS2PUBLIC _SYSSMU12$ 12 6 25 OFFLINE UNDOTBS2PUBLIC _SYSSMU13$ 13 6 41 OFFLINE UNDOTBS2PUBLIC _SYSSMU14$ 14 6 57 OFFLINE UNDOTBS2PUBLIC _SYSSMU15$ 15 6 73 OFFLINE UNDOTBS2PUBLIC _SYSSMU16$ 16 6 89 OFFLINE UNDOTBS2PUBLIC _SYSSMU17$ 17 6 105 OFFLINE UNDOTBS2PUBLIC _SYSSMU18$ 18 6 121 OFFLINE UNDOTBS2PUBLIC _SYSSMU19$ 19 6 137 OFFLINE UNDOTBS2PUBLIC _SYSSMU20$ 20 6 153 OFFLINE UNDOTBS2PUBLIC _SYSSMU21$ 21 7 9 OFFLINE UNDOTBS3PUBLIC _SYSSMU22$ 22 7 25 OFFLINE UNDOTBS3PUBLIC _SYSSMU23$ 23 7 41 OFFLINE UNDOTBS3PUBLIC _SYSSMU24$ 24 7 57 OFFLINE UNDOTBS3PUBLIC _SYSSMU25$ 25 7 73 OFFLINE UNDOTBS3PUBLIC _SYSSMU26$ 26 7 89 OFFLINE UNDOTBS3PUBLIC _SYSSMU27$ 27 7 105 OFFLINE UNDOTBS3PUBLIC _SYSSMU28$ 28 7 121 OFFLINE UNDOTBS3PUBLIC _SYSSMU29$ 29 7 137 OFFLINE UNDOTBS3PUBLIC _SYSSMU30$ 30 7 153 OFFLINE UNDOTBS331 rows selected.SQL select xidusn,xidslot,xidsqn,ubablk,ubafil,ubarec from v$transaction; XIDUSN XIDSLOT XIDSQN UBABLK UBAFIL UBAREC- - - - - - 9 13 299 439 2 47SQL select usn,name from v$rollname where usn=9; USN NAME- - 9 _SYSSMU9$SQL SQL c/file_name/file_name,tablespace_name 1* select file_id,file_name,tablespace_name from dba_data_files order by 1SQL / 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 SQL show parameter undo NAME TYPE VALUE- - -undo_management string AUTOundo_retention integer 900undo_tablespace string UNDOTBS1SQL alter system set undo_tablespace=undotbs2;System altered.SQL alter database datafile 2 offline;Database altered.SQL SQL shutdown abort;ORACLE instance shut down.SQL SQL SQL startupORACLE instance started.Total System Global Area 167772160 bytesFixed Size 1266392 bytesVariable Size 104860968 bytesDatabase Buffers 58720256 bytesRedo Buffers 2924544 bytesDatabase 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 tablespaceSQL -我们看到报错了 意思是说该回滚段中还有活动事务SQL conn roger/rogerConnected.SQL show parameter undoNAME 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 _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 FALSEundo_management string AUTOundo_retention integer 900undo_tablespace string UNDOTBS2SQL 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 onlineSQL drop rollback segment _SYSSMU9$;drop rollback segment _SYSSMU9$*ERROR at line 1:ORA-01545: rollback segment _SYSSMU9$ specified not availableSQL select owner,segment_name,SEGMENT_ID,FILE_ID,BLOCK_ID,STATUS 2 from dba_rollback_segs where segment_name=_SYSSMU9$;OWNER SEGMENT_NAME SEGMENT_ID FILE_ID BLOCK_ID STATUS- - - - - -PUBLIC _SYSSMU9$ 9 2 137 NEEDS RECOVERYSQL 用如下隐含参数：_offline_rollback_segments=(_SYSSMU9$)_corrupted_rollback_segments=(_SYSSMU9$)SQL startup mount pfile=/oracle/pfile.ora;ORACLE instance started.Total System Global Area 167772160 bytesFixed Size 1266392 bytesVariable Size 104860968 bytesDatabase Buffers 58720256 bytesRedo Buffers 2924544 bytesDatabase mounted.SQL alter database open;Database altered.SQL select owner,segment_name,SEGMENT_ID,FILE_ID,BLOCK_ID,STATUS 2 from dba_rollback_segs where segment_name=_SYSSMU9$;OWNER SEGMENT_NAME SEGMENT_ID FILE_ID BLOCK_ID STATUS- - - - - -PUBLIC _SYSSMU9$ 9 2 137 NEEDS RECOVERYSQL drop rollback segment _SYSSMU9$;Rollback segment dropped.SQLSQL drop tablespace undotbs1 including contents and datafiles;Tablespace dropped.SQL SQL select count(*) from ht_01; COUNT(*)- 5 -数据丢失这里可能有人会问上面的_undo_debug_mode参数为啥不管用？其实不是没效果，是针对这种情况下，没用而已，对于自动undo管理模式，可以使用该参数来手工删除某个回滚段，前提是该回滚段无活动事务。2. 通过更改数据字典表 来删除回滚段和undo tablespaceSQL show parameter undoNAME TYPE VALUE- - -undo_management string AUTOundo_retention integer 900undo_tablespace string UNDOTBS3SQL conn roger/rogerConnected.SQL select count(*) from ht_01; COUNT(*)- 3SQL delete from ht_01 where rownum -不提交SQL select xidusn,xidslot,xidsqn,ubablk,ubafil,ubarec from v$transaction; XIDUSN XIDSLOT XIDSQN UBABLK UBAFIL UBAREC- - - - - - 2 5 310 345 7 6SQL 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 SQL shutdown abort;ORACLE instance shut down.SQL startupORACLE instance started.Total System Global Area 167772160 bytesFixed Size 1266392 bytesVariable Size 104860968 bytesDatabase Buffers 58720256 bytesRedo Buffers 2924544 bytesDatabase 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 tablespaceSQL select ts# from ts$ where name=UNDOTBS3; TS#- 7SQL 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 712 rows selected.SQL select owner,segment_name,SEGMENT_ID,FILE_ID,BLOCK_ID,STATUS 2 from dba_rollback_segs where file_id=7;OWNER SEGMENT_NAME SEGMENT_ID FILE_ID BLOCK_ID STATUS- - - - - -PUBLIC _SYSSMU2$ 2 7 281 NEEDS RECOVERYPUBLIC _SYSSMU21$ 21 7 9 OFFLINEPUBLIC _SYSSMU22$ 22 7 25 OFFLINEPUBLIC _SYSSMU23$ 23 7 41 OFFLINEPUBLIC _SYSSMU24$ 24 7 57 OFFLINEPUBLIC _SYSSMU25$ 25 7 73 OFFLINEPUBLIC _SYSSMU26$ 26 7 89 OFFLINEPUBLIC _SYSSMU27$ 27 7 105 OFFLINEPUBLIC _SYSSMU28$ 28 7 121 OFFLINEPUBLIC _SYSSMU29$ 29 7 137 OFFLINEPUBLIC _SYSSMU30$ 30 7 153 OFFLINE11 rows selected.SQL 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 startupORACLE instance started.Total System Global Area 167772160 bytesFixed Size 1266392 bytesVariable Size 104860968 bytesDatabase Buffers 58720256 bytesRedo Buffers 2924544 bytesDatabase 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 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 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.fullPL/SQL procedure successfully completed.SQL drop rollback segment _SYSSMU2$;Rollback segment dropped.SQL 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 startupORACLE instance started.Total System Global Area 167772160 bytesFixed Size 1266392 bytesVariable Size 104860968 bytesDatabase Buffers 58720256 bytesRedo Buffers 2924544 bytesDatabase mounted.Database opened.SQL drop tablespace undotbs3 including contents and datafiles;Tablespace dropped.SQL -Drop Tablespace 成功简单的总结下，其实我们只要通过如下步骤就能轻易的删除：1. 将回滚段更改为临时段2. 重启实例3. 从seg$中删除记录4. 从undo$中删除记录 需要注意一下的是，如果不使用hcheck.full 那么直接drop tablespace可能遇到如下错误：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 修改元数据SQL purge recyclebin;Recyclebin purged.SQL create table ht01 as select owner,object_name,object_id 2 from dba_objects where object_id select count(*) from ht01; COUNT(*)- 98SQL delete from ht01 where object_id -不提交SQL show parameter undoNAME TYPE VALUE- - -undo_management string AUTOundo_retention integer 900undo_tablespace string UNDOTBS4SQL select owner,segment_name,SEGMENT_ID,FILE_ID,BLOCK_ID,STATUS 2 from dba_rollback_segs where tablespace_name=UNDOTBS4;OWNER SEGMENT_NAME SEGMENT_ID FILE_ID BLOCK_ID STATUS- - - - - -PUBLIC _SYSSMU1$ 1 2 169 ONLINEPUBLIC _SYSSMU2$ 2 2 185 ONLINEPUBLIC _SYSSMU3$ 3 2 9 OFFLINEPUBLIC _SYSSMU4$ 4 2 25 OFFLINEPUBLIC _SYSSMU5$ 5 2 41 OFFLINEPUBLIC _SYSSMU6$ 6 2 57 OFFLINEPUBLIC _SYSSMU7$ 7 2 73 OFFLINEPUBLIC _SYSSMU8$ 8 2 89 OFFLINEPUBLIC _SYSSMU10$ 10 2 105 OFFLINEPUBLIC _SYSSMU11$ 11 2 121 OFFLINEPUBLIC _SYSSMU12$ 12 2 137 OFFLINEPUBLIC _SYSSMU31$ 31 2 153 OFFLI