sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 故障案例 >

【案例】Oracle undo回滚表空间异常重建的处理步骤思路

时间:2016-06-29 15:43   来源:Oracle研究中心   作者:惜分飞   点击:

天萃荷净 分享一篇Oracle undo异常处理干货。详细介绍undo回滚表空间异常时,删除重建的详细步骤与说明

1、启动数据库,发现错误
startup

2、查看是否是undo文件损坏引起,并查看是否是当前undo,不是当前undo直接offline,然后open数据库,再删除掉该数据该undo即可
select a.ts#,a.name,b.name from v$datafile a,v$tablespace b where a.ts#=b.ts#;
show parameter undo_tablespace;

3、损坏undo离线,创建pfile文件
alter database datafile n offline drop;
create pfile=’/tmp/pfile’ from spfile;

4、打开数据库,如果打开失败,请继续5,如果成功Oracle undo回滚表空间重建详细步骤与说明方法处理
alter database open;

5、如果数据库不能正常打开,而是提示,如下错误:
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced

6、检查日志文件,发现如下:
SMON: about to recover undo segment 21
SMON: mark undo segment 21 as needs recovery
SMON: about to recover undo segment 22
SMON: mark undo segment 22 as needs recovery
SMON: about to recover undo segment 23
SMON: mark undo segment 23 as needs recovery
SMON: about to recover undo segment 24
SMON: mark undo segment 24 as needs recovery
SMON: about to recover undo segment 25
SMON: mark undo segment 25 as needs recovery
SMON: about to recover undo segment 26
SMON: mark undo segment 26 as needs recovery
SMON: about to recover undo segment 27
SMON: mark undo segment 27 as needs recovery
SMON: about to recover undo segment 28
SMON: mark undo segment 28 as needs recovery
SMON: about to recover undo segment 29
SMON: mark undo segment 29 as needs recovery
SMON: about to recover undo segment 30
SMON: mark undo segment 30 as needs recovery

7、编辑pfile文件,内容为
*.undo_management=’MANUAL’
*._allow_resetlogs_corruption=true
*._corrupted_rollback_segments=(_SYSSMU21$,_SYSSMU22$,_SYSSMU23$,_SYSSMU24$,
_SYSSMU25$,_SYSSMU26$,_SYSSMU27$,_SYSSMU28$,_SYSSMU29$,_SYSSMU30$)
*.undo_tablespace=’SYSTEM’

8、退出当前sqlplus,重新登录,利用pfile启动数据库
startup

9、创建新undo表空间
CREATE UNDO TABLESPACE UNDOTBSNEW DATAFILE
‘/opt/oracle/oradata/xifenfei/UNDOTBS01.dbf
SIZE 50M autoextend on next 10m maxsize 30G;

10、查询回滚段信息,为下面删除损坏undo的回滚段做到心中有底
select segment_name,status from dba_rollback_segs;

11、删除损坏undo
drop tablespace UNDOTBSOLD including contents and datafiles;

12、查看回滚段状态
select segment_name,status from dba_rollback_segs;

13、如果有损坏表空间回滚段还存在,手工删除
drop rollback segment “_SYSSMUx$”;

14、修改pfile内容
*.undo_management=’AUTO’
#*._allow_resetlogs_corruption=true
#*._corrupted_rollback_segments=(_SYSSMU21$,_SYSSMU22$,_SYSSMU23$,_SYSSMU24$,
_SYSSMU25$,_SYSSMU26$,_SYSSMU27$,_SYSSMU28$,_SYSSMU29$,_SYSSMU30$)
*.undo_tablespace=’UNDOTBSNEW’

15、重启数据库
shutdown immediate
startup

说明:可以先删除需要恢复的回滚段,再删除损坏的undo表空间


--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【案例】Oracle undo回滚表空间异常重建的处理步骤思路

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/626.html

Oracle研究中心

关键词:

Oracle undo损坏恢复

删除重建undo表空间

undo表空间损坏的处理思路

SMON: mark undo segment 27 as needs recovery