ORA-01578 ORA-01110 坏块解决方法

一个案例,查看跟踪文件发现如下错误信息 d:Oracleproduct10.2.0admindbserverudumporcl_ora_5888.trcCorrupt block relative dba: 0x09848269 （file 38, block 295529）Bad header found during buffer readData in bad block:type: 1 format: 5 rdba: 0x30322d2dlast change scn: 0xfffe.fffefffe seq: 0xfe flg: 0xffspare1: 0x0 spare2: 0x30 spare3: 0x0consistency value in tail: 0x2d2d3533check value in block header: 0xfffecomputed block checksum: 0x91f9Reread of rdba: 0x09848269 （file 38, block 295529） found same corrupted dataFri Dec 27 08:28:08 2013Corrupt Block FoundTSN = 36, TSNAME = HGHISRFN = 38, BLK = 295529, RDBA = 159679081OBJN = 119544, OBJD = 119544, OBJECT = EMR_MONITOR_RESULT, SUBOBJECT = SEGMENT OWNER = HGHIS, SEGMENT TYPE = Table SegmentFri Dec 27 08:54:18 2013Hex dump of （file 38, block 295561） in trace file d:oracleproduct10.2.0admindbserverudumporcl_ora_6796.trcCorrupt block relative dba: 0x09848289 （file 38, block 295561）Bad header found during buffer readData in bad block:type: 48 format: 0 rdba: 0x020cc100last change scn: 0xb500.080cc100 seq: 0xa5 flg: 0xbaspare1: 0x34 spare2: 0x2 spare3: 0xb0b8consistency value in tail: 0x395e3031check value in block header: 0xcfcbblock checksum disabledReread of rdba: 0x09848289 （file 38, block 295561） found same corrupted dataFri Dec 27 08:54:19 2013Corrupt Block FoundTSN = 36, TSNAME = HGHISRFN = 38, BLK = 295561, RDBA = 159679113OBJN = 119494, OBJD = 119494, OBJECT = SYS_LOB0000119493C00006$$, SUBOBJECT = SEGMENT OWNER = HGHIS, SEGMENT TYPE = Lob SegmentFri Dec 27 09:11:20 2013Hex dump of （file 38, block 295563） in trace file d:oracleproduct10.2.0admindbserverudumporcl_ora_5584.trcCorrupt block relative dba: 0x0984828b （file 38, block 295563）Bad header found during buffer readData in bad block:type: 71 format: 7 rdba: 0x064d0001last change scn: 0x3038.43584400 seq: 0x31 flg: 0x07spare1: 0x44 spare2: 0x4c spare3: 0x771consistency value in tail: 0x000a0000check value in block header: 0x7800computed block checksum: 0xcbf8Reread of rdba: 0x0984828b （file 38, block 295563） found same corrupted dataFri Dec 27 09:11:21 2013Corrupt Block FoundTSN = 36, TSNAME = HGHISRFN = 38, BLK = 295563, RDBA = 159679115OBJN = 119494, OBJD = 119494, OBJECT = SYS_LOB0000119493C00006$$, SUBOBJECT = SEGMENT OWNER = HGHIS, SEGMENT TYPE = Lob SegmentFri Dec 27 09:27:59 2013  根据上述信息得知38号数据文件的295529、295561、295563为坏块，可以使用DBV工具或者RMAN来检查坏块信息DBV FILE="d:oradataDATA.DBF" blocksize=8192orrman target /backup validate check logical database;
select * from V$DATABASE_BLOCK_CORRUPTION ;
  可以根据文件号和块号查出损坏的是对象,表还是LOB segmentselect tablespace_name,segment_type,owner,segment_name from dba_extents where file_id=38 and 295529 between block_id AND block_id + blocks - 1;
38是文件号,295529是block号
如果是对象，可以重建alter index indexname rebuild
如果是表，可以使用10231事件忽略坏块，然后使用CTAS方式重建表最后rename table，别忘记rebuild indexalter session SET EVENTS "10231 trace name context forever,level 10";create table tab_new as select * from tab;rename tab to tab_bak;rename tab_new to new;alter index indexname rebuild;
alter session SET EVENTS "10231 trace name context off";
  如果损坏的是LOB segment,先找出segment信息select owner, segment_name, segment_type from dba_extents where file_id = 38 and 295563 between block_id and block_id + blocks - 1;
输出如下owner=HGHIS
segment_name=SYS_LOB0000119493C00006$$
segment_type=LOBSEGMENT
找到表明和LOB字段select table_name, column_name from dba_lobs where segment_name = "SYS_LOB0000119493C00006$$" and owner = "HGHIS";
输出如下table_name = EMR_CASE
column_name = WORD
找到坏块的bad rowid,使用以下plsql脚本create table bad_rows （row_id ROWID,oracle_error_code number）;set concat off
set serveroutput on

declare
n number;
error_code number;
bad_rows number := 0;
ora1578 EXCEPTION;
PRAGMA EXCEPTION_INIT（ora1578, -1578）;
begin
for cursor_lob in （select rowid rid, &&lob_column from &&table_owner.&table_with_lob） loop
begin
n:=dbms_lob.instr（cursor_lob.&&lob_column,hextoraw（"889911"）） ;
exception
when ora1578 then
bad_rows := bad_rows + 1;
insert into bad_rows values（cursor_lob.rid,1578）;
commit;
when others then
error_code:=SQLCODE;
bad_rows := bad_rows + 1;
insert into bad_rows values（cursor_lob.rid,error_code）;
commit;
end;
end loop;
dbms_output.put_line（"Total Rows identified with errors in LOB column: "||bad_rows）;
end;
/Enter value for lob_column: WORD
Enter value for table_owner: HGHIS
Enter value for table_with_lob: EMR_CASE
可以查询bad rowid
select * from bad_rows;
更新空LOB字段来避免ORA-1578,ORA-26040,如果是CLOB类型，将empty_blob（）改为empty_clob（）
set concat off
update &table_owner.&table_with_lob set &lob_column = empty_blob（） where rowid in （select row_id from bad_rows）;
将bad rowid lob块移到其他表空间alter table &table_owner.&table_with_lob move LOB （&&lob_column） store as （tablespace &tablespace_name）;
最后别忘记rebuild index 相关阅读：Oracle ORA-01555 快照过旧说明 http://www.linuxidc.com/Linux/2012-08/66997.htmORA-01078 和 LRM-00109 报错解决方法 http://www.linuxidc.com/Linux/2012-07/66044.htmORA-01555超长的Query Duration时间 http://www.linuxidc.com/Linux/2013-12/93901.htmORA-00471 处理方法笔记 http://www.linuxidc.com/Linux/2013-09/90017.htmORA-00314,redolog 损坏，或丢失处理方法 http://www.linuxidc.com/Linux/2013-09/90646.htmORA-00257 归档日志过大导致无法存储的解决办法 http://www.linuxidc.com/Linux/2013-09/90594.htm更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12Hbase常用操作（增删改查）Oracle中RMAN的备份与恢复笔记相关资讯 ORA-01110 ORA-01578

ORA-00376/ORA-01110 故障处理办法（今 09:01）
ORA-01157&ORA-01110故障解决（07/17/2015 12:48:04）
rman备份恢复到异机报ORA-01110 （08/02/2014 14:26:57）

备库查询导致的ORA-01110错误及修（04月16日）
bbed修改undo段状态（ORA-01578）（11/11/2014 17:51:27）
legato备份不成功,报ORA-00604, （06/14/2014 21:10:16）

本文评论查看全部评论（0）