ORA-01578: ORACLE data block corrupted ORA-01110: data file 5: ‘+DATA/yjt/datafile/undotbs2.264.994

一 问题描述

SQL> alter database open;

alter database open

*

第 1 行出现错误:

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-01578: ORACLE data block corrupted (file # 5, block # 224)

ORA-01110: data file 5: '+DATA/yjt/datafile/undotbs2.264.994538299'

进程 ID: 25264

会话 ID: 3508 序列号: 5

二 出错原因

硬件损坏

三 解决办法

#先将这个回滚表空间的数据文件offline，以先打开数据库

SQL> alter database datafile '+DATA/yjt/datafile/undotbs2.264.994538299' offline;

数据库已更改。

SQL> alter database open;

数据库已更改。

#创建一个新的回滚表空间

SQL> CREATE UNDO TABLESPACE UNDOTBS3 DATAFILE '+DATA/yjt/datafile/UNDOTBS03.DBF' SIZE 10G autoextend on ;

表空间已创建。

#切换默认回滚表空间

SQL> alter system set undo_tablespace='UNDOTBS3';

系统已更改。

#查看目前使用的默认回滚表空间

SQL> show parameter undo;

NAME                               TYPE                         VALUE

------------------------------------ -------------------------------- ------------------------------

undo_management                 string                        AUTO

undo_retention                        integer                      900

undo_tablespace              string                        UNDOTBS3

删除有问题的回滚表空间，报错：

SQL> drop tablespace undotbs2 including contents and datafiles;

drop tablespace undotbs2 including contents and datafiles

*

第 1 行出现错误:

ORA-01548: 已找到活动回退段 '_SYSSMU17_1393856127$', 终止删除表空间

而且此时，刚OFFLINE的UNDO表空间中保存着需要进行回滚的记录，某些业务还是有受到影响的，示例：

所以，我们需要借助初始化参数文件，增加一些隐藏参数以后才能删除这些回滚段，处理方法如下：

① 先查询下回滚段的状态

SQL> SELECT SEGMENT_NAME, OWNER,TABLESPACE_NAME, STATUS
FROM DBA_ROLLBACK_SEGS   where tablespace_name='UNDOTBS2';

SEGMENT_NAME		       OWNER  TABLESPACE_NAME		     STATUS
------------------------------ ------ ------------------------------ ----------------
_SYSSMU11_1774804298$	       PUBLIC UNDOTBS2			     OFFLINE
_SYSSMU12_1491669929$	       PUBLIC UNDOTBS2			     OFFLINE
_SYSSMU13_3006988832$	       PUBLIC UNDOTBS2			     OFFLINE
_SYSSMU14_1387576216$	       PUBLIC UNDOTBS2			     OFFLINE
_SYSSMU15_3798169006$	       PUBLIC UNDOTBS2			     OFFLINE
_SYSSMU16_1964237961$	       PUBLIC UNDOTBS2			     OFFLINE
_SYSSMU17_1393856127$	       PUBLIC UNDOTBS2			     NEEDS RECOVERY
_SYSSMU18_3086742067$	       PUBLIC UNDOTBS2			     NEEDS RECOVERY
_SYSSMU19_2353704619$	       PUBLIC UNDOTBS2			     NEEDS RECOVERY
_SYSSMU20_2980352211$	       PUBLIC UNDOTBS2			     NEEDS RECOVERY
_SYSSMU21_3311765242$	       PUBLIC UNDOTBS2			     NEEDS RECOVERY

SEGMENT_NAME		       OWNER  TABLESPACE_NAME		     STATUS
------------------------------ ------ ------------------------------ ----------------
_SYSSMU22_537585832$	       PUBLIC UNDOTBS2			     OFFLINE
_SYSSMU23_3869750197$	       PUBLIC UNDOTBS2			     OFFLINE
_SYSSMU32_1895550787$	       PUBLIC UNDOTBS2			     OFFLINE
_SYSSMU34_52634041$	       PUBLIC UNDOTBS2			     OFFLINE
_SYSSMU36_1618763995$	       PUBLIC UNDOTBS2			     OFFLINE
_SYSSMU38_1724589771$	       PUBLIC UNDOTBS2			     OFFLINE
_SYSSMU39_915961497$	       PUBLIC UNDOTBS2			     OFFLINE
_SYSSMU40_3078112810$	       PUBLIC UNDOTBS2			     OFFLINE
_SYSSMU42_1985348147$	       PUBLIC UNDOTBS2			     OFFLINE
_SYSSMU44_844208204$	       PUBLIC UNDOTBS2			     OFFLINE
_SYSSMU46_804710500$	       PUBLIC UNDOTBS2			     OFFLINE

SEGMENT_NAME		       OWNER  TABLESPACE_NAME		     STATUS
------------------------------ ------ ------------------------------ ----------------
_SYSSMU48_75331038$	       PUBLIC UNDOTBS2			     OFFLINE
_SYSSMU50_472225114$	       PUBLIC UNDOTBS2			     OFFLINE
_SYSSMU52_2127017356$	       PUBLIC UNDOTBS2			     OFFLINE
_SYSSMU54_1609385369$	       PUBLIC UNDOTBS2			     OFFLINE
_SYSSMU56_2936253248$	       PUBLIC UNDOTBS2			     OFFLINE
_SYSSMU58_3477115074$	       PUBLIC UNDOTBS2			     OFFLINE
_SYSSMU60_28287539$	       PUBLIC UNDOTBS2			     OFFLINE
_SYSSMU62_1955481428$	       PUBLIC UNDOTBS2			     OFFLINE
_SYSSMU64_169414028$	       PUBLIC UNDOTBS2			     OFFLINE
_SYSSMU66_120984452$	       PUBLIC UNDOTBS2			     OFFLINE
_SYSSMU68_1938484422$	       PUBLIC UNDOTBS2			     OFFLINE

SEGMENT_NAME		       OWNER  TABLESPACE_NAME		     STATUS
------------------------------ ------ ------------------------------ ----------------
_SYSSMU70_2697949514$	       PUBLIC UNDOTBS2			     OFFLINE
_SYSSMU72_1284278233$	       PUBLIC UNDOTBS2			     OFFLINE
_SYSSMU74_2499975961$	       PUBLIC UNDOTBS2			     OFFLINE
_SYSSMU76_1750530886$	       PUBLIC UNDOTBS2			     OFFLINE
_SYSSMU78_3049241765$	       PUBLIC UNDOTBS2			     OFFLINE
_SYSSMU80_2250094618$	       PUBLIC UNDOTBS2			     OFFLINE
_SYSSMU82_1071767212$	       PUBLIC UNDOTBS2			     OFFLINE
_SYSSMU84_3846298343$	       PUBLIC UNDOTBS2			     OFFLINE
_SYSSMU86_729730271$	       PUBLIC UNDOTBS2			     OFFLINE
_SYSSMU88_2274792367$	       PUBLIC UNDOTBS2			     OFFLINE
_SYSSMU90_3734824523$	       PUBLIC UNDOTBS2			     OFFLINE

SEGMENT_NAME		       OWNER  TABLESPACE_NAME		     STATUS
------------------------------ ------ ------------------------------ ----------------
_SYSSMU92_2134628816$	       PUBLIC UNDOTBS2			     OFFLINE
_SYSSMU94_2068833727$	       PUBLIC UNDOTBS2			     OFFLINE
_SYSSMU96_1366658957$	       PUBLIC UNDOTBS2			     OFFLINE
_SYSSMU97_1449854804$	       PUBLIC UNDOTBS2			     OFFLINE
_SYSSMU98_3820244125$	       PUBLIC UNDOTBS2			     OFFLINE
_SYSSMU99_3768996552$	       PUBLIC UNDOTBS2			     OFFLINE
_SYSSMU100_2995972363$	       PUBLIC UNDOTBS2			     OFFLINE

已选择51行。

计划删掉status为NEEDS RECOVERY的回滚段。

① 关闭rac的所有节点

shutdown abort; 

② 在其中一个rac节点上做以下操作：

a.创建pfile文件

create pfile '/home/oracle/pfile.ora' from spfile

b.修改pfile文件，添加以下参数： 

*.undo_management='MANUAL'

_corrupted_rollback_segments=('_SYSSMU17_1393856127$','_SYSSMU18_3086742067$','_SYSSMU19_2353704619$','_SYSSMU20_2980352211$','_SYSSMU21_3311765242$')

c.用pfile文件启动数据库

startup pfile='/home/oracle/pfile.ora'

d.删除这几个回滚段
SQL> drop rollback segment "_SYSSMU17_1393856127$";

回退段已删除。

SQL> drop rollback segment "_SYSSMU18_3086742067$";

回退段已删除。

SQL> drop rollback segment "_SYSSMU19_2353704619$";

回退段已删除。

SQL> drop rollback segment "_SYSSMU20_2980352211$";

回退段已删除。

SQL> drop rollback segment "_SYSSMU21_3311765242$";

回退段已删除。

e.删除回滚表空间

SQL> drop tablespace undotbs2 including contents and datafiles;

表空间已删除。

f.改回用spfile启动数据库

SQL> shutdown abort;
ORACLE 例程已经关闭。
SQL> startup;
 

③ 启动另一个rac节点

SQL> startup;
 

--本篇文章参考了https://blog.csdn.net/u012232730/article/details/54864852及

https://www.iteye.com/blog/com1com4-1974961