【undo】undo 意外刪除處理辦法(非歸檔)

 

1   啟動報ORA-01157 ORA-01110狀態

2   啟動資料庫到mount 狀態

3   alter database datafile offline drop;

4   alter database open

5   建立新的undo 表空間

6   修改database undo表空間到新的undo表空間 alter system set undo_tablespace=undotbs02;

7    select segment_name,status from dba_rollback_segs;查詢undo 段如下

select segment_name,status,tablespace_name from dba_rollback_segs

SEGMENT_NAME STATUS TABLESPACE_NAME
—————————— —————- —————–
_SYSSMU11$ NEEDS RECOVERY UNDOTBS1
_SYSSMU12$ NEEDS RECOVERY UNDOTBS1
_SYSSMU13$ NEEDS RECOVERY UNDOTBS1
_SYSSMU14$ NEEDS RECOVERY UNDOTBS1
_SYSSMU15$ NEEDS RECOVERY UNDOTBS1
_SYSSMU16$ NEEDS RECOVERY UNDOTBS1
_SYSSMU17$ NEEDS RECOVERY UNDOTBS1
_SYSSMU18$ NEEDS RECOVERY UNDOTBS1
_SYSSMU19$ NEEDS RECOVERY UNDOTBS1
_SYSSMU20$ NEEDS RECOVERY UNDOTBS1

刪除以上段將會報如下錯誤

ORA-01548: active rollback segment ‘_SYSSMU11$’ found, terminate dropping
tablespace

8    關閉資料庫 新增如下引數

_corrupted_rollback_segments =('_SYSSMU11$','_SYSSMU12$','_SYSSMU13$','_SYSSMU14$','_SYSSMU15$','_SYSSMU16$','_SYSSMU17$','_SYSSMU18$','_SYSSMU19$','_SYSSMU20$')

9   重啟資料庫刪除NEEDS RECOVERY段

    刪除舊undo表空間

10  去掉隱含引數,重啟資料庫

11  匯出資料重建資料庫(建議)

 

關於隱含引數(_corrupted_rollback_segments和_offline_rollback_segments)的理解參考maclean的兩篇文章