Oracle坏块故障葵花宝典

1Oracle坏块故障总结最近处理了两次典型的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)-30andalarmtype=0784163selectcount(*)fromep_tablet4281062看来全表扫描正常selectfromep_tabletwhereptimetrunc(sysdate)-31andptimetrunc(sysdate)-33andalarmtype=0andrownum10001索引扫描报错了，推断为索引上有坏块！继续查：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(*)一张表报错：2查询某张表系统事件中有报错：磁盘管理中报错：3Dell的服务器，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$$LOBINDEX71556553642EP_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_PECCTwheredbms_rowid.rowid_to_absolute_fno(rowid,'BS','EP_PECC')='8'ANDdbms_rowid.rowid_block_number(rowid)BETWEEN1461742AND1461842;终于找到了一些AAAA66AAIAAFkrSAAJ37708146094692009-10-2619:40:080BLOBBLOB省略一些结果集AAAA66AAIAAFk5JAAG37708146183362009-10-2619:47:080BLOBBLOB查到以下记录时出错，看来按照rowid清空故障的blob数据不行AAAA66AAIAAFk5JAAGROWID_TYPE:1OBJECT_NUMBER:37705RELATIVE_FNO:8BLOCK_NUMBER:1461833ROW_NUMBER:6可以从obj#,rfile#,block#,row#计算得到理论上的rowidObj#3770Rfile#8Block#1461842Row#0实际上就是将十进制数转化成64进制数，当然，从二进制转化的规则比较简单点。将二进制数从右到左，6个bit一组，然后将这6个bit组转成10进制数，就是A~Za~z0~9+/这64个字符的位置（从0开始），替换成base64的字符即可。rowid是base64编码的，用A~Za~z0~9+/共64个字符表示。A表示0，B表示1，……，a表示26，……，0表示52，……，+表示62，/表示63可以将其看做一个64进制的数。obj#=3770转换成二进制，补足成6位base64编码，左边填0，结果为AAAA66rfile#=8=001000=I，补足成3位，得到AAIblock#=1461842=000101100100111001010010=5365718，补足成6位，得到AAFk5Srow#=0，3位AAA合起来就是AAAA66AAIAAFk5SAAA但是不要忘了，这次坏的是blob它会跨多个块的。所以这条理论rowid没有找到！举例子补充说明：select*fromdba_lobsdlwheredl.owner='TEST'1TESTT_LOGRZSYS_LOB0000052726C00002$$TESTSYS_IL0000052726C00002$$8192900NOYESYESNOTAPPLICABLENO2TESTEP_TABLEPLATEPICTSYS_LOB0000052738C00016$$TESTSYS_IL0000052738C00016$$8192900NOYESYESNOTAPPLICABLENO3TESTEP_TABLEPANORAMAPICTSYS_LOB0000052738C00017$$TESTSYS_IL0000052738C00017$$8192900NOYESYESNOTAPPLICABLENO4TESTEP_TABLETHIRDPICTSYS_LOB0000052738C00018$$TESTSYS_IL0000052738C00018$$8192900NOYESYESNOTAPPLICABLENO5TESTEP_PECCPLATEPICTSYS_LOB0000052746C00016$$TESTSYS_IL0000052746C00016$$8192900NOYESYESNOTAPPLICABLENO6TESTEP_PECCPANORAMAPICTSYS_LOB0000052746C00017$$TESTSYS_IL0000052746C00017$$8192900NOYESYESNOTAPPLICABLENO7TESTEP_PECCTHIRDPICTSYS_LOB0000052746C00018$$TESTSYS_IL0000052746C00018$$8192900NOYESYESNOTAPPLICABLENO没有任何数据时：selectdl.TABLE_NAME,dl.COLUMN_NAME,de.segment_name,de.segment_type,de.FILE_ID,de.BLOCK_ID6fromdba_extentsde,dba_lobsdlwherede.owner='TEST'anddl.OWNER='TEST'andde.segment_name=dl.SEGMENT_NAME1T_LOGRZSYS_LOB0000052726C00002$$LOBSEGMENT7172EP_TABLEPLATEPICTSYS_LOB0000052738C00016$$LOBSEGMENT71133EP_TABLEPANORAMAPICTSYS_LOB0000052738C00017$$LOBSEGMENT71294EP_TABLETHIRDPICTSYS_LOB0000052738C00018$$LOBSEGMENT71455EP_PECCPLATEPICTSYS_LOB0000052746C0