Oracle坏块故障总结最近处理了两次典型的ora-01578,ora-01115,ora-01110故障，一次是平湖索引块坏，一次是黄山数据文件坏、blob数据块坏。平湖的警告日志文件中有以下信息：ORA-12012:erroronautoexecuteofjob21ORA-01578:ORACLEdatablockcorrupted(file#10,block#2558610)ORA-01110:datafile10:'D:\ORACLE\ORADATA\BS\USERS04.DBF'ORA-12012:erroronautoexecuteofjob1ORA-01578:ORACLEdatablockcorrupted(file#16,block#2624066)ORA-01110:datafile16:'D:\ORACLE\ORADATA\BS\USERS10.DBF'应用软件可以正常使用，偶尔会报错ora-01578。排错过程登录数据库检查：selectcount(*)fromep_tabletwhereptimetrunc(sysdate)-33andalarmtype=0andrownum<10001索引扫描报错了，推断为索引上有坏块！继续查：selectowner,file_id,segment_name,segment_type,block_id,blocksfromdba_extentswherefile_id=16andblock_id<=2624066and(block_id+blocks-1)>=2624066;OWNERFILE_IDSEGMENT_NAMESEGMENT_TYPEBLOCK_IDBLOCKSBS16VHINOINDEX262406616运气真好重建相关索引后数据库就恢复了。黄山坏块故障就比较复杂了，硬盘坏导致多个数据文件的多个块故障，其中还有blob对象。Count(*)一张表报错：1查询某张表系统事件中有报错：磁盘管理中报错：2Dell的服务器，2块72G硬盘，没有做raid,8i数据库没有备份。（上图磁盘3为移动硬盘）初步检查到这里我感觉这次坏的严重了，恢复可能比较麻烦。整理一下思路：1、不要随意重启2、记录损坏的数据信息3、导出可用数据4、更换故障硬盘5、重做系统及数据库6、恢复数据7、重传丢失的数据（我们的系统架构可以这样做）排错过程select*fromdba_extentswherefile_id=8and1461842betweenblock_idandblock_id+blocks-11BSSYS_LOB0000003770C00017$$LOBSEGMENTUSERS3457081461842131072168解释：block_id+blocks-1=1461842+这个区有多少个block-1查到故障段为LOBSEGMENT类型，SYS_LOB0000003770C00017$$。selectdl.table_name,dl.SEGMENT_NAMEfromdba_lobsdlwheredl.owner='BS'wheredl.SEGMENT_NAME='SYS_LOB0000003770C00017$$'查到有坏块的表为EP_PECC。接着查出这张表的lob索引段SelectA.TABLE_NAME,A.COLUMN_NAME,B.SEGMENT_NAME,B.SEGMENT_TYPE,B.HEADER_FILE,B.HEADER_BLOCK,B.BYTESfromDBA_LOBSA,DBA_SEGMENTSBwhereA.INDEX_NAME=B.SEGMENT_NAMEANDA.TABLE_NAME='EP_PECC'当时因为没有记录，所以我随便举例：1EP_TABLETHIRDPICTSYS_IL0000052738C00018$$LOBINDEX71556553632EP_TABLEPANORAMAPICTSYS_IL0000052738C00017$$LOBINDEX7139655363EP_TABLEPLATEPICTSYS_IL0000052738C00016$$LOBINDEX712365536selectrowidfromBS.EP_PECCwheredbms_rowid.rowid_to_absolute_fno(rowid,'BS','EP_PECC')=8anddbms_rowid.rowid_block_number(rowid)=1461842;这个块无法找出blob段的rowid。本想找后我可以按照rowid清空故障的blob数据。选择一个范围继续找：selectrowid,T.PTIME,T.PLATEPICT,T.PANORAMAPICTfromBS.EP_PECCTwheredbms_rowid.rowid_to_absolute_fno(rowid,'BS','EP_PECC')=8anddbms_rowid.rowid_block_number(rowid)BETWEEN1461840AND1461842;还是没有找到selectrowid,dbms_rowid.rowid_object(rowid)obj_id,dbms_rowid.rowid_relative_fno(rowid)df#,dbms_rowid.rowid_block_number(rowid)blknum,dbms_rowid.rowid_row_number(rowid)rowno,T.PTIME,T.ALARMTYPE,T.PLATEPICT,T.PANORAMAPICTfromEP_PECCTwheredbms_rowid.rowid_to_absolute_fno(rowid,'BS','EP_PECC')='8'ANDdbms_rowid.rowid_block_number(rowid)BETWEEN1461842AND1461842+1024;还没找到selectrowid,dbms_rowid.rowid_object(rowid)obj_id,dbms_rowid.rowid_relative_fno(rowid)df#,dbms_rowid.rowid_block_number(rowid)blknum,dbms_rowid.rowid_row_number(rowid)rowno,T.PTIME,T.ALARMTYPE,T.PLATEPICT,T.PANORAMAPICTfromEP_PECCTwh...
VIP
