维护的一个库出现坏块,执行查询如下:
SQL>select * from user_poc;
select * from user_poc
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file# 4, block # 20)
ORA-01110: data file 4:'/u02/app/oracle/oradata/ora10g/users01.dbf'
4号文件,第20个块出现损坏,查看损坏的对象类型,如果是索引,直接rebuild,是表的话就有点麻烦了
SQL> l
1 select owner, segment_name, segment_type, tablespace_name
2 from DBA_EXTENTS
3 where file_id = 4
4* and 20 between block_id and block_id + blocks
SQL> /
OWNER SEGMENT_NA SEGMENT_TY TABLESPACE
---------- ---------- ---------- ----------
LIHM USER_POC TABLE USERS
修复办法:
1) 如果有RMAN备份的话那太好了直接搞
RMAN> block recover datafile 4 block 20;
2) 没备份的话,可能要丢失数据了,可以设置10231事件来跳过坏块,尽量的挽回数据
SQL> select * from user_poc;
select* from user_poc
*
ERRORat line 1:
ORA-01578:ORACLE data block corrupted (file # 4, block # 20)
ORA-01110:data file 4: '/u02/app/oracle/oradata/ora10g/users01.dbf'
SQL> alter session setevents '10231 trace name context forever,level 10';
Sessionaltered.
SQL> create tableuser_poc_tmp as select * from user_poc;
Table created.
坏块处理后,请SA检查硬件,原来RAID卡有问题。