oracle表重建前无法访问,【案例】Oracle undo回滚表空间异常重建的处理步骤思路...

天萃荷净

分享一篇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回滚表空间异常重建的处理步骤思路