Oracle LOB坏块处理

03

08

Oracle LOB坏块处理

995 0 0

作者:*._cnWuSX = true

参考文档：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);

注意：该操作后会导致表上所有索引失效，需要重建索引；

内容来源于网络如有侵权请私信删除

文章来源: 博客园

原文链接: https://www.cnblogs.com/cnwusx/p/13392140.html

标签： oracle数据库 orcale开发 oracle技术文章

你还没有登录，请先登录或注册！

还没有人评论，欢迎说说您的想法！

热门标签

oracle数据库 orcale开发 oracle技术文章

官方群

服务时间：

https://imgs.itxueyuan.com/advPicture/adv-1662379508-4007-pic.jpeg