ORA-01548删除undo表空间处理

最新推荐文章于 2021-11-03 23:40:56 发布

alphaletry

最新推荐文章于 2021-11-03 23:40:56 发布

阅读量526

点赞数

分类专栏： ORACLE

本文链接：https://blog.csdn.net/qingfengfeixin/article/details/44588329

版权

ORACLE 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

在新建了表空间jinlian_undo之后，并切换为默认undo表空间，不小心删除了创建的dbf文件，但是无法删除jinlian_undo表空间。

查询相关资料进行以下方式解决：

1.删除表空间报错

SQL> drop tablespace jinlian_undo including contents
2 ;
drop tablespace jinlian_undo including contents
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU11_709484581$' found, terminate
dropping tablespace

SQL> select segment_name,status,tablespace_name from dba_rollback_segs where tablespace_name like '%JINLIAN%';

SEGMENT_NAME         STATUS  TABLESPACE_NAME
------------------------------ ---------------- ------------------------------
_SYSSMU11_709484581$        NEEDS RECOVERY JINLIAN_UNDO
_SYSSMU12_3603383151$        NEEDS RECOVERY JINLIAN_UNDO
_SYSSMU13_2669403687$        NEEDS RECOVERY JINLIAN_UNDO
_SYSSMU14_113724357$        NEEDS RECOVERY JINLIAN_UNDO
_SYSSMU15_3807990281$        NEEDS RECOVERY JINLIAN_UNDO
_SYSSMU16_141217913$        NEEDS RECOVERY JINLIAN_UNDO
_SYSSMU17_4029247457$        NEEDS RECOVERY JINLIAN_UNDO
_SYSSMU18_1039758238$        NEEDS RECOVERY JINLIAN_UNDO
_SYSSMU19_705457718$        NEEDS RECOVERY JINLIAN_UNDO
_SYSSMU20_3921701195$        NEEDS RECOVERY JINLIAN_UNDO

10 rows selected.

cd /u01/app/oracle/admin/zf/pfile/

vi init.ora.21920154251

在undo处修改设置

修改前：

###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_tablespace=UNDOTBS1

修改后：

###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_tablespace=UNDOTBS1
undo_management=manual
undo_retention=10800
_CORRUPTED_ROLLBACK_SEGMENTS =(_SYSSMU20_3921701195$,_SYSSMU19_705457718$,_SYSSMU18_1039758238$,_SYSSMU17_4029247457$,_SYSSMU16_141217913$,_SYSSMU15_3807990281$,_SYSSMU14_113724357$,_SYSSMU13_2669403687$,_SYSSMU12_3603383151$,_SYSSMU11_709484581$)

或者把需要删除表空间的相关段加入到以下参数中

_offline_rollback_segments=(_SYSSMU20_3921701195$,_SYSSMU19_705457718$,_SYSSMU18_1039758238$,_SYSSMU17_4029247457$,_SYSSMU16_141217913$,_SYSSMU15_3807990281$,_SYSSMU14_113724357$,_SYSSMU13_2669403687$,_SYSSMU12_3603383151$,_SYSSMU11_709484581$)

SQL> startup pfile=/u01/app/oracle/admin/zf/pfile/init.ora
ORACLE instance started.

Total System Global Area 780824576 bytes
Fixed Size      2217424 bytes
Variable Size    461376048 bytes
Database Buffers   314572800 bytes
Redo Buffers      2658304 bytes
Database mounted.
Database opened.

SQL> drop tablespace jinlian_undo including contents;

Tablespace dropped.

6.后期在遇到undo空间文件损坏或者丢失可以采用上面的方法进行undo空间的重建工作，相关具体案例可以参考

http://www.jb51.net/article/39722.htm