【正文】 ENT 7 209向EP_TABLE表的PLATEPICT字段，插入一張9MB大小的圖片后結(jié)果：1 T_LOG RZ SYS_LOB0000052726C00002$$ LOBSEGMENT 7 172 EP_TABLE PLATEPICT SYS_LOB0000052738C00016$$ LOBSEGMENT 7 1133 EP_TABLE PLATEPICT SYS_LOB0000052738C00016$$ LOBSEGMENT 7 2734 EP_TABLE PLATEPICT SYS_LOB0000052738C00016$$ LOBSEGMENT 7 2815 EP_TABLE PLATEPICT SYS_LOB0000052738C00016$$ LOBSEGMENT 7 2896 EP_TABLE PLATEPICT SYS_LOB0000052738C00016$$ LOBSEGMENT 7 2977 EP_TABLE PLATEPICT SYS_LOB0000052738C00016$$ LOBSEGMENT 7 3058 EP_TABLE PLATEPICT SYS_LOB0000052738C00016$$ LOBSEGMENT 7 3139 EP_TABLE PLATEPICT SYS_LOB0000052738C00016$$ LOBSEGMENT 7 32110 EP_TABLE PLATEPICT SYS_LOB0000052738C00016$$ LOBSEGMENT 7 32911 EP_TABLE PLATEPICT SYS_LOB0000052738C00016$$ LOBSEGMENT 7 33712 EP_TABLE PLATEPICT SYS_LOB0000052738C00016$$ LOBSEGMENT 7 34513 EP_TABLE PLATEPICT SYS_LOB0000052738C00016$$ LOBSEGMENT 7 35314 EP_TABLE PLATEPICT SYS_LOB0000052738C00016$$ LOBSEGMENT 7 36115 EP_TABLE PLATEPICT SYS_LOB0000052738C00016$$ LOBSEGMENT 7 36916 EP_TABLE PLATEPICT SYS_LOB0000052738C00016$$ LOBSEGMENT 7 37717 EP_TABLE PLATEPICT SYS_LOB0000052738C00016$$ LOBSEGMENT 7 38518 EP_TABLE PLATEPICT SYS_LOB0000052738C00016$$ LOBSEGMENT 6 919 EP_TABLE PLATEPICT SYS_LOB0000052738C00016$$ LOBSEGMENT 7 39320 EP_TABLE PLATEPICT SYS_LOB0000052738C00016$$ LOBSEGMENT 6 13721 EP_TABLE PLATEPICT SYS_LOB0000052738C00016$$ LOBSEGMENT 7 52122 EP_TABLE PLATEPICT SYS_LOB0000052738C00016$$ LOBSEGMENT 6 26523 EP_TABLE PLATEPICT SYS_LOB0000052738C00016$$ LOBSEGMENT 7 64924 EP_TABLE PLATEPICT SYS_LOB0000052738C00016$$ LOBSEGMENT 6 39325 EP_TABLE PLATEPICT SYS_LOB0000052738C00016$$ LOBSEGMENT 7 77726 EP_TABLE PLATEPICT SYS_LOB0000052738C00016$$ LOBSEGMENT 6 52127 EP_TABLE PANORAMAPICT SYS_LOB0000052738C00017$$ LOBSEGMENT 7 12928 EP_TABLE THIRDPICT SYS_LOB0000052738C00018$$ LOBSEGMENT 7 14529 EP_PECC PLATEPICT SYS_LOB0000052746C00016$$ LOBSEGMENT 7 17730 EP_PECC PANORAMAPICT SYS_LOB0000052746C00017$$ LOBSEGMENT 7 19331 EP_PECC THIRDPICT SYS_LOB0000052746C00018$$ LOBSEGMENT 7 209查test用戶(hù)blob容量：select sum() from dba_extents de,dba_lobs dl where =39。 and =39。1 TEST T_LOG RZ SYS_LOB0000052726C00002$$ TEST SYS_IL0000052726C00002$$ 8192 900 NO YES YES NOT APPLICABLE NO2 TEST EP_TABLE PLATEPICT SYS_LOB0000052738C00016$$ TEST SYS_IL0000052738C00016$$ 8192 900 NO YES YES NOT APPLICABLE NO3 TEST EP_TABLE PANORAMAPICT SYS_LOB0000052738C00017$$ TEST SYS_IL0000052738C00017$$ 8192 900 NO YES YES NOT APPLICABLE NO4 TEST EP_TABLE THIRDPICT SYS_LOB0000052738C00018$$ TEST SYS_IL0000052738C00018$$ 8192 900 NO YES YES NOT APPLICABLE NO5 TEST EP_PECC PLATEPICT SYS_LOB0000052746C00016$$ TEST SYS_IL0000052746C00016$$ 8192 900 NO YES YES NOT APPLICABLE NO6 TEST EP_PECC PANORAMAPICT SYS_LOB0000052746C00017$$ TEST SYS_IL0000052746C00017$$ 8192 900 NO YES YES NOT APPLICABLE NO7 TEST EP_PECC THIRDPICT SYS_LOB0000052746C00018$$ TEST SYS_IL0000052746C00018$$ 8192 900 NO YES YES NOT APPLICABLE NO沒(méi)有任何數(shù)據(jù)時(shí)：select , from dba_extents de,dba_lobs dl where =39。所以這條理論rowid沒(méi)有找到！舉例子補(bǔ)充說(shuō)明：select * from dba_lobs dl where =39。A表示0，B表示1，……，a表示26，……，0表示52，……，+表示62，/表示63可以將其看做一個(gè)64進(jìn)制的數(shù)。將二進(jìn)制數(shù)從右到左，6個(gè)bit一組，然后將這6個(gè)bit組轉(zhuǎn)成10進(jìn)制數(shù)，就是A~Z a~z 0~9 + /這64個(gè)字符的位置（從0開(kāi)始），替換成base64的字符即可。 AND (rowid) BETWEEN 1461742 AND 1461842。)=39。,39。還沒(méi)找到select rowid, (rowid) obj_id, (rowid) df, (rowid) blknum, (rowid) rowno ,from EP_PECC T where (rowid,39。839。EP_PECC39。BS39。)=8 and (rowid) BETWEEN 1461840 AND 1461842。,39。選擇一個(gè)范圍繼續(xù)找：select rowid, from T where (rowid,39。這個(gè)塊無(wú)法找出blob段的rowid。EP_PECC39。BS39。EP_PECC39。查到有壞塊的表為EP_PECC。 where =39。select , from dba_lobs dl where =39。（上圖磁盤(pán)3為移動(dòng)硬盤(pán)）初步檢查到這里我感覺(jué)這次壞的嚴(yán)重了，恢復(fù)可能比較麻煩。黃山壞塊故障就比較復(fù)雜了，硬盤(pán)壞導(dǎo)致多個(gè)數(shù)據(jù)文件的多個(gè)塊故障，其中還有blob對(duì)象。排錯(cuò)過(guò)程登錄數(shù)據(jù)庫(kù)檢查：select count(*) from ep_table t where ptimetrunc(sysdate)30 and alarmtype=0784163select count(*) from ep_table t4281062看來(lái)全表掃描正常select from ep_table t where ptimetrunc(sysdate)31 and ptimetrunc(sysdate)33 and alarmtype=0 and rownum10001索引掃描報(bào)錯(cuò)了，推斷為索引上有壞塊！繼續(xù)查：select owner,file_id,segment_name, segment_type, block_id, blocks　　from dba_extentswhere file_id=16 and block_id=2624066 and (block_id + blocks 1) = 2624066。D:\ORACLE\ORADATA\BS\39。D:\ORACLE\ORADATA\BS\39。Oracle壞塊故障總結(jié) 最近處理了兩次典型的ora01578,ora01115,ora01110故障，一次是平湖索引塊壞，一次是黃山數(shù)據(jù)文件壞、blob數(shù)據(jù)塊壞。平湖的警告日志文件中有以下信息：ORA12012: error on auto execute of job 21ORA01578: ORACLE data block corrupted (file 10, block 2558610)ORA01110: data file 10: 39。ORA12012: error on auto execute of job 1ORA01578: ORACLE data block corrupted (file 16, block 2624066)ORA01110: data file 16: 39。應(yīng)用軟件可以正常使用，偶爾會(huì)報(bào)錯(cuò)ora01578。OWNERFILE_IDSEGMENT_NAMESEGMENT_TYPEBLOCK_IDBLOCKSBS16VHINOINDEX262406616運(yùn)氣真好重建相關(guān)索引后數(shù)據(jù)庫(kù)就恢復(fù)了。Count(*)一張表報(bào)錯(cuò)：查詢(xún)某張表系統(tǒng)事件中有報(bào)錯(cuò)：磁盤(pán)管理中報(bào)錯(cuò)：Dell的服務(wù)器，2塊72G硬盤(pán)，沒(méi)有做raid,8i數(shù)據(jù)庫(kù)沒(méi)有備份。整理一下思路： 不要隨意重啟 記錄損壞的數(shù)據(jù)信息 導(dǎo)出可用數(shù)據(jù) 更換故障硬盤(pán) 重做系統(tǒng)及數(shù)據(jù)庫(kù) 恢復(fù)數(shù)據(jù) 重傳丟失的數(shù)據(jù)（我們的系統(tǒng)架構(gòu)可以這樣做）排錯(cuò)過(guò)程select * from dba_extents where file_id=8 and 1461842 between block_id and block_id+blocks11 BS SYS_LOB0000003770C00017$$ LOBSEGMENT USERS 34570 8 1461842 131072 16 8解釋?zhuān)篵lock_id+blocks1=1461842+這個(gè)區(qū)有多少個(gè)block1查到故障段為L(zhǎng)OBSEGMENT類(lèi)型，SYS_LOB0000003770C00017$$。 BS39。 SYS_LOB0000003770C00017$$39。接著查出這張表的lob索引段Select , from DBA_LOBS A,DBA_SEGMENTS B where = AND =39。當(dāng)時(shí)因?yàn)闆](méi)有記錄，所以我隨便舉例：1 EP_TABLE THIRDPICT SYS_IL0000052738C00018$$ LOBINDEX 7 155 655362 EP_TABLE PANORAMAPICT SYS_IL0000052738C00017$$ LOBINDEX 7 139 655363 EP_TABLE PLATEPICT SYS_IL0000052738C00016$$ LOBINDEX 7 123 65536select rowid from where (rowid,39。,39。)=8 and (rowid)=1461842。本想找后我可以按照rowid清空故障的blob數(shù)據(jù)。BS39。EP_PECC39。還是沒(méi)有找到select rowid, (rowid) obj_id, (rowid) df, (rowid) blknum, (rowid) rowno ,from EP_PECC T where (rowid,39。,39。)=39。 AND (rowid) BETWEEN 1461842 AND 1461842 +1024。BS39。EP_PECC39。839。終于找到了一些AAAA66AAIAAFkrSAAJ 37