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的两篇文章

http://www.askmaclean.com/archives/_offline_rollback_segments.html

http://www.askmaclean.com/archives/_corrupted_rollback_segments.html

ITPub博客

【undo】undo 意外删除处理办法(非归档)

最新文章