Oracle LOB坏块处理
参考文档：Troubleshooting Assistant: Handling Block Corruptions in Oracle7 / 8 / 8i / 9i / 10g / 11g (文档 ID 1598103.2)
通常坏块有如下报错：
ORA-01578: ORACLE data block corrupted (file # 36, block # 721776)
处理：
1、定位坏块所在对象类型和名称：
col TABLESPACE_NAME for a20
col OWNER for a10
col SEGMENT_NAME for a40
select tablespace_name,segment_type,owner,segment_name From dba_extents Where RELATIVE_FNO=36 and 721776 between block_id and block_id+blocks-1;
TABLESPACE_NAME SEGMENT_TYPE OWNER SEGMENT_NAME
-------------------- ------------------------------------ ---------- ----------------------------------------
TS_PFM LOBSEGMENT DRM_PFM SYS_LOB0000092671C00013$$
通过LOB段定位所在表：
select owner,table_name from dba_lobs where owner='DRM_PFM' and segment_name='SYS_LOB0000092671C00013$$';
OWNER TABLE_NAME
------------------------------------------------------------ ------------------------------------------------------------
DRM_PFM ASYNCHRON_REQUEST
由于该坏块在LOB段上，而LOB段我们并不能直接访问，故而不能通过构造ROWID的方式来定位损坏的数据行，只能通过扫描的方式来定位了，有如下匿名块：
set serverout on
exec dbms_output.enable(100000);
declare
page number;
len number;
c varchar2(10);
charpp number := 8132/2;
begin
for r in (select rowid rid, dbms_lob.getlength (JMS_BODY_MSG) len
from DRM_PFM.ASYNCHRON_REQUEST) loop
if r.len is not null then
for page in 0..r.len/charpp loop
begin
select dbms_lob.substr (JMS_BODY_MSG, 1, 1+ (page * charpp))
into c
from DRM_PFM.ASYNCHRON_REQUEST
where rowid = r.rid;
exception
when others then
dbms_output.put_line ('Error on rowid ' ||R.rid||' page '||page);
dbms_output.put_line (sqlerrm);
end;
end loop;
end if;
end loop;
end;
/
输出： Error on rowid AAAj2MAAgAAApENAAC page 0 ORA-01578: ORACLE data block corrupted (file # 34, block # 799130) ORA-01110: data file 34: '/oradata/drmdb/DRMDBSTD/datafile/o1_mf_ts_pfm_fy4kb9cn_.dbf' ORA-26040: Data block was loaded using the NOLOGGING option ORA-06512: at "SYS.DBMS_LOB", line 1092 ORA-06512: at line 1 Error on rowid AAAj2MAAgAAApENAAC page 1 ORA-01578: ORACLE data block corrupted (file # 34, block # 799130) ORA-01110: data file 34: '/oradata/drmdb/DRMDBSTD/datafile/o1_mf_ts_pfm_fy4kb9cn_.dbf' ORA-26040: Data block was loaded using the NOLOGGING option ORA-06512: at "SYS.DBMS_LOB", line 1092 ORA-06512: at line 1 Error on rowid AAAj2MAAgAAApENAAC page 2 ORA-01578: ORACLE data block corrupted (file # 34, block # 799131) ORA-01110: data file 34: '/oradata/drmdb/DRMDBSTD/datafile/o1_mf_ts_pfm_fy4kb9cn_.dbf' ORA-26040: Data block was loaded using the NOLOGGING option ORA-06512: at "SYS.DBMS_LOB", line 1092 ORA-06512: at line 1 Error on rowid AAAj2MAAgAAApENAAC page 3 ORA-01578: ORACLE data block corrupted (file # 34, block # 799193) ORA-01110: data file 34: '/oradata/drmdb/DRMDBSTD/datafile/o1_mf_ts_pfm_fy4kb9cn_.dbf' ORA-26040: Data block was loaded using the NOLOGGING option ORA-06512: at "SYS.DBMS_LOB", line 1092 ORA-06512: at line 1 Error on rowid AAAj2MAAgAAApENAAC page 4 ORA-01578: ORACLE data block corrupted (file # 34, block # 799253) ORA-01110: data file 34: '/oradata/drmdb/DRMDBSTD/datafile/o1_mf_ts_pfm_fy4kb9cn_.dbf' ORA-26040: Data block was loaded using the NOLOGGING option ORA-06512: at "SYS.DBMS_LOB", line 1092 ORA-06512: at line 1 Error on rowid AAAj2MAAgAAApENAAC page 5 ORA-01578: ORACLE data block corrupted (file # 34, block # 799134) ORA-01110: data file 34: '/oradata/drmdb/DRMDBSTD/datafile/o1_mf_ts_pfm_fy4kb9cn_.dbf' ORA-26040: Data block was loaded using the NOLOGGING option ORA-06512: at "SYS.DBMS_LOB", line 1092 ORA-06512: at line 1 Error on rowid AAAj2MAAgAAApENAAC page 6 ORA-01578: ORACLE data block corrupted (file # 34, block # 799254) ORA-01110: data file 34: '/oradata/drmdb/DRMDBSTD/datafile/o1_mf_ts_pfm_fy4kb9cn_.dbf' ORA-26040: Data block was loaded using the NOLOGGING option ORA-06512: at "SYS.DBMS_LOB", line 1092 ORA-06512: at line 1 Error on rowid AAAj2MAAgAAApENAAC page 7 ORA-01578: ORACLE data block corrupted (file # 34, block # 799317) ORA-01110: data file 34: '/oradata/drmdb/DRMDBSTD/datafile/o1_mf_ts_pfm_fy4kb9cn_.dbf' ORA-26040: Data block was loaded using the NOLOGGING option ORA-06512: at "SYS.DBMS_LOB", line 1092 ORA-06512: at line 1 Error on rowid AAAj2MAAgAAApENAAC page 8 ORA-01578: ORACLE data block corrupted (file # 34, block # 799258) ORA-01110: data file 34: '/oradata/drmdb/DRMDBSTD/datafile/o1_mf_ts_pfm_fy4kb9cn_.dbf' ORA-26040: Data block was loaded using the NOLOGGING option ORA-06512: at "SYS.DBMS_LOB", line 1092 ORA-06512: at line 1 Error on rowid AAAj2MAAgAAApENAAC page 9 ORA-01578: ORACLE data block corrupted (file # 34, block # 799318) ORA-01110: data file 34: '/oradata/drmdb/DRMDBSTD/datafile/o1_mf_ts_pfm_fy4kb9cn_.dbf' ORA-26040: Data block was loaded using the NOLOGGING option ORA-06512: at "SYS.DBMS_LOB", line 1092 ORA-06512: at line 1 Error on rowid AAAj2MAAgAAApENAAC page 10 ORA-01578: ORACLE data block corrupted (file # 34, block # 799320) ORA-01110: data file 34: '/oradata/drmdb/DRMDBSTD/datafile/o1_mf_ts_pfm_fy4kb9cn_.dbf' ORA-26040: Data block was loaded using the NOLOGGING option ORA-06512: at "SYS.DBMS_LOB", line 1092 ORA-06512: at line 1 Error on rowid AAAj2MAAgAAApENAAC page 11 ORA-01578: ORACLE data block corrupted (file # 34, block # 799321) ORA-01110: data file 34: '/oradata/drmdb/DRMDBSTD/datafile/o1_mf_ts_pfm_fy4kb9cn_.dbf' ORA-26040: Data block was loaded using the NOLOGGING option ORA-06512: at "SYS.DBMS_LOB", line 1092 ORA-06512: at line 1 Error on rowid AAAj2MAAgAAApENAAC page 12 ORA-01578: ORACLE data block corrupted (file # 34, block # 799381) ORA-01110: data file 34: '/oradata/drmdb/DRMDBSTD/datafile/o1_mf_ts_pfm_fy4kb9cn_.dbf' ORA-26040: Data block was loaded using the NOLOGGING option ORA-06512: at "SYS.DBMS_LOB", line 1092 ORA-06512: at line 1 Error on rowid AAAj2MAAgAAApENAAC page 13 ORA-01578: ORACLE data block corrupted (file # 34, block # 799322) ORA-01110: data file 34: '/oradata/drmdb/DRMDBSTD/datafile/o1_mf_ts_pfm_fy4kb9cn_.dbf' ORA-26040: Data block was loaded using the NOLOGGING option ORA-06512: at "SYS.DBMS_LOB", line 1092 ORA-06512: at line 1 Error on rowid AAAj2MAAgAAApF1AAY page 0 ORA-01578: ORACLE data block corrupted (file # 36, block # 725489) ORA-01110: data file 36: '/oradata/drmdb/DRMDBSTD/datafile/o1_mf_ts_pfm_fy4kbb37_.dbf' ORA-26040: Data block was loaded using the NOLOGGING option ORA-06512: at "SYS.DBMS_LOB", line 1092 ORA-06512: at line 1 Error on rowid AAAj2MAAgAAApF1AAY page 1 ORA-01578: ORACLE data block corrupted (file # 36, block # 725489) ORA-01110: data file 36: '/oradata/drmdb/DRMDBSTD/datafile/o1_mf_ts_pfm_fy4kbb37_.dbf' ORA-26040: Data block was loaded using the NOLOGGING option ORA-06512: at "SYS.DBMS_LOB", line 1092 ORA-06512: at line 1 Error on rowid AAAj2MAAgAAApF1AAY page 2 ORA-01578: ORACLE data block corrupted (file # 36, block # 725553) ORA-01110: data file 36: '/oradata/drmdb/DRMDBSTD/datafile/o1_mf_ts_pfm_fy4kbb37_.dbf' ORA-26040: Data block was loaded using the NOLOGGING option ORA-06512: at "SYS.DBMS_LOB", line 1092 ORA-06512: at line 1 Error on rowid AAAj2MAAgAAApF1AAY page 3 ORA-01578: ORACLE data block corrupted (file # 36, block # 725617) ORA-01110: data file 36: '/oradata/drmdb/DRMDBSTD/datafile/o1_mf_ts_pfm_fy4kbb37_.dbf' ORA-26040: Data block was loaded using the NOLOGGING option ORA-06512: at "SYS.DBMS_LOB", line 1092 ORA-06512: at line 1 Error on rowid AAAj2MAAgAAApIoAAe page 0 ORA-01578: ORACLE data block corrupted (file # 32, block # 748627) ORA-01110: data file 32: '/oradata/drmdb/DRMDBSTD/datafile/o1_mf_ts_pfm_fy4kb8m4_.dbf' ORA-26040: Data block was loaded using the NOLOGGING option ORA-06512: at "SYS.DBMS_LOB", line 1092 ORA-06512: at line 1 Error on rowid AAAj2MAAgAAApIoAAe page 1 ORA-01578: ORACLE data block corrupted (file # 32, block # 748627) ORA-01110: data file 32: '/oradata/drmdb/DRMDBSTD/datafile/o1_mf_ts_pfm_fy4kb8m4_.dbf' ORA-26040: Data block was loaded using the NOLOGGING option ORA-06512: at "SYS.DBMS_LOB", line 1092 ORA-06512: at line 1 PL/SQL procedure successfully completed. 通过UE排序后，可得到全部报错的ROWID 2、定位到损坏的行后，通过将行Lob列置空的形式可以重置该块（数据清空） update DRM_PFM.ASYNCHRON_REQUEST set JMS_BODY_MSG=empty_clob() where rowid in ('AAAj2MAAgAAApENAAC','AAAj2MAAgAAApF1AAY','AAAj2MAAgAAApIoAAe'); commit; alter table DRM_PFM.ASYNCHRON_REQUEST move lob(JMS_BODY_MSG);
注意：该操作后会导致表上所有索引失效，需要重建索引；
exec dbms_output.enable(100000);
declare
page number;
len number;
c varchar2(10);
charpp number := 8132/2;
begin
for r in (select rowid rid, dbms_lob.getlength (JMS_BODY_MSG) len
from DRM_PFM.ASYNCHRON_REQUEST) loop
if r.len is not null then
for page in 0..r.len/charpp loop
begin
select dbms_lob.substr (JMS_BODY_MSG, 1, 1+ (page * charpp))
into c
from DRM_PFM.ASYNCHRON_REQUEST
where rowid = r.rid;
exception
when others then
dbms_output.put_line ('Error on rowid ' ||R.rid||' page '||page);
dbms_output.put_line (sqlerrm);
end;
end loop;
end if;
end loop;
end;
/
输出： Error on rowid AAAj2MAAgAAApENAAC page 0 ORA-01578: ORACLE data block corrupted (file # 34, block # 799130) ORA-01110: data file 34: '/oradata/drmdb/DRMDBSTD/datafile/o1_mf_ts_pfm_fy4kb9cn_.dbf' ORA-26040: Data block was loaded using the NOLOGGING option ORA-06512: at "SYS.DBMS_LOB", line 1092 ORA-06512: at line 1 Error on rowid AAAj2MAAgAAApENAAC page 1 ORA-01578: ORACLE data block corrupted (file # 34, block # 799130) ORA-01110: data file 34: '/oradata/drmdb/DRMDBSTD/datafile/o1_mf_ts_pfm_fy4kb9cn_.dbf' ORA-26040: Data block was loaded using the NOLOGGING option ORA-06512: at "SYS.DBMS_LOB", line 1092 ORA-06512: at line 1 Error on rowid AAAj2MAAgAAApENAAC page 2 ORA-01578: ORACLE data block corrupted (file # 34, block # 799131) ORA-01110: data file 34: '/oradata/drmdb/DRMDBSTD/datafile/o1_mf_ts_pfm_fy4kb9cn_.dbf' ORA-26040: Data block was loaded using the NOLOGGING option ORA-06512: at "SYS.DBMS_LOB", line 1092 ORA-06512: at line 1 Error on rowid AAAj2MAAgAAApENAAC page 3 ORA-01578: ORACLE data block corrupted (file # 34, block # 799193) ORA-01110: data file 34: '/oradata/drmdb/DRMDBSTD/datafile/o1_mf_ts_pfm_fy4kb9cn_.dbf' ORA-26040: Data block was loaded using the NOLOGGING option ORA-06512: at "SYS.DBMS_LOB", line 1092 ORA-06512: at line 1 Error on rowid AAAj2MAAgAAApENAAC page 4 ORA-01578: ORACLE data block corrupted (file # 34, block # 799253) ORA-01110: data file 34: '/oradata/drmdb/DRMDBSTD/datafile/o1_mf_ts_pfm_fy4kb9cn_.dbf' ORA-26040: Data block was loaded using the NOLOGGING option ORA-06512: at "SYS.DBMS_LOB", line 1092 ORA-06512: at line 1 Error on rowid AAAj2MAAgAAApENAAC page 5 ORA-01578: ORACLE data block corrupted (file # 34, block # 799134) ORA-01110: data file 34: '/oradata/drmdb/DRMDBSTD/datafile/o1_mf_ts_pfm_fy4kb9cn_.dbf' ORA-26040: Data block was loaded using the NOLOGGING option ORA-06512: at "SYS.DBMS_LOB", line 1092 ORA-06512: at line 1 Error on rowid AAAj2MAAgAAApENAAC page 6 ORA-01578: ORACLE data block corrupted (file # 34, block # 799254) ORA-01110: data file 34: '/oradata/drmdb/DRMDBSTD/datafile/o1_mf_ts_pfm_fy4kb9cn_.dbf' ORA-26040: Data block was loaded using the NOLOGGING option ORA-06512: at "SYS.DBMS_LOB", line 1092 ORA-06512: at line 1 Error on rowid AAAj2MAAgAAApENAAC page 7 ORA-01578: ORACLE data block corrupted (file # 34, block # 799317) ORA-01110: data file 34: '/oradata/drmdb/DRMDBSTD/datafile/o1_mf_ts_pfm_fy4kb9cn_.dbf' ORA-26040: Data block was loaded using the NOLOGGING option ORA-06512: at "SYS.DBMS_LOB", line 1092 ORA-06512: at line 1 Error on rowid AAAj2MAAgAAApENAAC page 8 ORA-01578: ORACLE data block corrupted (file # 34, block # 799258) ORA-01110: data file 34: '/oradata/drmdb/DRMDBSTD/datafile/o1_mf_ts_pfm_fy4kb9cn_.dbf' ORA-26040: Data block was loaded using the NOLOGGING option ORA-06512: at "SYS.DBMS_LOB", line 1092 ORA-06512: at line 1 Error on rowid AAAj2MAAgAAApENAAC page 9 ORA-01578: ORACLE data block corrupted (file # 34, block # 799318) ORA-01110: data file 34: '/oradata/drmdb/DRMDBSTD/datafile/o1_mf_ts_pfm_fy4kb9cn_.dbf' ORA-26040: Data block was loaded using the NOLOGGING option ORA-06512: at "SYS.DBMS_LOB", line 1092 ORA-06512: at line 1 Error on rowid AAAj2MAAgAAApENAAC page 10 ORA-01578: ORACLE data block corrupted (file # 34, block # 799320) ORA-01110: data file 34: '/oradata/drmdb/DRMDBSTD/datafile/o1_mf_ts_pfm_fy4kb9cn_.dbf' ORA-26040: Data block was loaded using the NOLOGGING option ORA-06512: at "SYS.DBMS_LOB", line 1092 ORA-06512: at line 1 Error on rowid AAAj2MAAgAAApENAAC page 11 ORA-01578: ORACLE data block corrupted (file # 34, block # 799321) ORA-01110: data file 34: '/oradata/drmdb/DRMDBSTD/datafile/o1_mf_ts_pfm_fy4kb9cn_.dbf' ORA-26040: Data block was loaded using the NOLOGGING option ORA-06512: at "SYS.DBMS_LOB", line 1092 ORA-06512: at line 1 Error on rowid AAAj2MAAgAAApENAAC page 12 ORA-01578: ORACLE data block corrupted (file # 34, block # 799381) ORA-01110: data file 34: '/oradata/drmdb/DRMDBSTD/datafile/o1_mf_ts_pfm_fy4kb9cn_.dbf' ORA-26040: Data block was loaded using the NOLOGGING option ORA-06512: at "SYS.DBMS_LOB", line 1092 ORA-06512: at line 1 Error on rowid AAAj2MAAgAAApENAAC page 13 ORA-01578: ORACLE data block corrupted (file # 34, block # 799322) ORA-01110: data file 34: '/oradata/drmdb/DRMDBSTD/datafile/o1_mf_ts_pfm_fy4kb9cn_.dbf' ORA-26040: Data block was loaded using the NOLOGGING option ORA-06512: at "SYS.DBMS_LOB", line 1092 ORA-06512: at line 1 Error on rowid AAAj2MAAgAAApF1AAY page 0 ORA-01578: ORACLE data block corrupted (file # 36, block # 725489) ORA-01110: data file 36: '/oradata/drmdb/DRMDBSTD/datafile/o1_mf_ts_pfm_fy4kbb37_.dbf' ORA-26040: Data block was loaded using the NOLOGGING option ORA-06512: at "SYS.DBMS_LOB", line 1092 ORA-06512: at line 1 Error on rowid AAAj2MAAgAAApF1AAY page 1 ORA-01578: ORACLE data block corrupted (file # 36, block # 725489) ORA-01110: data file 36: '/oradata/drmdb/DRMDBSTD/datafile/o1_mf_ts_pfm_fy4kbb37_.dbf' ORA-26040: Data block was loaded using the NOLOGGING option ORA-06512: at "SYS.DBMS_LOB", line 1092 ORA-06512: at line 1 Error on rowid AAAj2MAAgAAApF1AAY page 2 ORA-01578: ORACLE data block corrupted (file # 36, block # 725553) ORA-01110: data file 36: '/oradata/drmdb/DRMDBSTD/datafile/o1_mf_ts_pfm_fy4kbb37_.dbf' ORA-26040: Data block was loaded using the NOLOGGING option ORA-06512: at "SYS.DBMS_LOB", line 1092 ORA-06512: at line 1 Error on rowid AAAj2MAAgAAApF1AAY page 3 ORA-01578: ORACLE data block corrupted (file # 36, block # 725617) ORA-01110: data file 36: '/oradata/drmdb/DRMDBSTD/datafile/o1_mf_ts_pfm_fy4kbb37_.dbf' ORA-26040: Data block was loaded using the NOLOGGING option ORA-06512: at "SYS.DBMS_LOB", line 1092 ORA-06512: at line 1 Error on rowid AAAj2MAAgAAApIoAAe page 0 ORA-01578: ORACLE data block corrupted (file # 32, block # 748627) ORA-01110: data file 32: '/oradata/drmdb/DRMDBSTD/datafile/o1_mf_ts_pfm_fy4kb8m4_.dbf' ORA-26040: Data block was loaded using the NOLOGGING option ORA-06512: at "SYS.DBMS_LOB", line 1092 ORA-06512: at line 1 Error on rowid AAAj2MAAgAAApIoAAe page 1 ORA-01578: ORACLE data block corrupted (file # 32, block # 748627) ORA-01110: data file 32: '/oradata/drmdb/DRMDBSTD/datafile/o1_mf_ts_pfm_fy4kb8m4_.dbf' ORA-26040: Data block was loaded using the NOLOGGING option ORA-06512: at "SYS.DBMS_LOB", line 1092 ORA-06512: at line 1 PL/SQL procedure successfully completed. 通过UE排序后，可得到全部报错的ROWID 2、定位到损坏的行后，通过将行Lob列置空的形式可以重置该块（数据清空） update DRM_PFM.ASYNCHRON_REQUEST set JMS_BODY_MSG=empty_clob() where rowid in ('AAAj2MAAgAAApENAAC','AAAj2MAAgAAApF1AAY','AAAj2MAAgAAApIoAAe'); commit; alter table DRM_PFM.ASYNCHRON_REQUEST move lob(JMS_BODY_MSG);
注意：该操作后会导致表上所有索引失效，需要重建索引；