Oracle数据库提示ORA-19566 LOB怎么处理

本篇内容介绍了“Oracle数据库提示ORA-19566 LOB怎么处理”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

1.故障现象：

在晚上的生产库自动备份时，备份失败，出现以下错误提示：

RMAN-03009: failure of backup command on c1 channel at 06/11/2020 03:31:02

ORA-19566: exceeded limit of 0 corrupt blocks for file +DATA/orcl/datafile/data12.dbf

continuing other job steps, job failed will not be re-run.

1. 对坏块进行诊断

使用DBV进行坏块检测:

dbv file=+DATA/orcl/datafile/data12.dbf blocksize=8192

DBVERIFY: Release 19.0.0.0.0 - Production on Sun Jun 21 20:49:57 2020

Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

DBVERIFY - Verification starting : FILE = +DATA/orcl/datafile/data12.dbf

Page 1539240 is marked corrupt

Corrupt block relative dba: 0x05177ca8 (file 20, block 1539240)

Bad header found during dbv:

Data in bad block:

type: 6 format: 2 rdba: 0x0cdceea8

last change scn: 0x0000.057e.07c6ceb9 seq: 0x2 flg: 0x04

spare3: 0x0

consistency value in tail: 0xceb90602

check value in block header: 0x4328

computed block checksum: 0x0

最终显示有96个坏块

使用另外一种方法检测，检查结果相同

rman target /

RMAN> run{

2> allocate channel d1 type disk;

3> backup check logical validate datafile 20;

4> release channel d1;

5> }

诊断结果显示，有96个坏块，坏块的详细编号如下：

[root  @hisdb01 ~]# cat /u01/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_ora_266550.trc | grep ‘Corrupt block’

Corrupt block relative dba: 0x05177ca8 (file 20, block 1539240)

SQL> select * from V$DATABASE_BLOCK_CORRUPTION;

  FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO     CON_ID

    20    1540136         24                  0 CORRUPT            0    20    1540328         24                  0 CORRUPT            0    20    1539240         24                  0 CORRUPT            0    20    1539432         24                  0 CORRUPT            0

为了保险起见，对整个库进行诊断

RMAN> configure device type disk parallelism 4; 
RMAN> backup validate check logical database;

还好，其他数据文件没有发现坏块。

检查坏块上的数据对象：

select owner,segment_name,segment_type,tablespace_name from dba_extents where file_id=20 and 1540350 between block_id and block_id+blocks-1;

OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME

test SYS_LOB0000098274C00002$$ LOBSEGMENT data

全都是一张表上的LOB 字段。

SQL> select OWNER,TABLE_NAME from dba_lobs where SEGMENT_NAME=’SYS_LOB0000098274C00002$$’;

OWNER TABLE_NAME

test mytable

尝试跳过坏块

SQL> execute DBMS_REPAIR.SKIP_CORRUPT_BLOCKS(‘OWNER’,’TABLE_NAME’);

PL/SQL procedure successfully completed.

结果RMAN备份还是报错。

在rman中设置允许出现的坏块最大值

run{

set maxcorrupt for datafile 20 to 97;

allocate channel c1 device type disk;

allocate channel c2 device type disk;

backup database FORMAT ‘/expdp/his %d%T_%U.bak’;

crosscheck backupset;

release channel c1;

release channel c2;

}

结果备份成功。

但expdp导出时，仍然报错

ORA-02354: error in exporting/importing data

ORA-01578: ORACLE data block corrupted (file # 20, block # 1540158)

网上有资料说设置10231事件可以跳过错误

alter system set events=’10231 trace name context forever,level 10’;

设置以后。expdp导出仍然报错。

3.处理坏块

按照metalink文档上的资料 Doc ID 1900424.1 和 Doc ID 472231.1) 解决方案如下：

（1）用备份恢复坏块

rman> catalog datafilecopy ‘/u01/backup/users01.dbf’;

rman> catalog archivelog ‘/u01/backup/archivelog/Arch_ocl_1_30.dbf’

rman> blockrecover datafile 5 block 99,100,101;

但当前没有可用的备份，这条路走不通。

（2）对出现坏块的表记录进行清理

只剩最后一招了， 对出现坏块的表记录进行清理， Doc ID 293515.1操作 说明如下：

drop table bad_rows;

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;

ora600 EXCEPTION;

PRAGMA EXCEPTION_INIT(ora1578, -1578);

PRAGMA EXCEPTION_INIT(ora600, -600);

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 ora600 then bad_rows := bad_rows + 1; insert into bad_rows values(cursor_lob.rid,600); 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;

/

select * from bad_rows;

When prompted by variable values and following our example:

nter value for lob_column: EMPLOYEE_ID_LOB

Enter value for table_owner: SCOTT

Enter value for table_with_lob: EMP

Update the lob column with empty lob to avoid ORA-1578 and ORA-26040:

SQL> set concat off

SQL> update &table_owner.&table_with_lob

    set &lob_column = empty_blob() where rowid in (select row_id from bad_rows);

查出lob字段坏块所在的行，然后把那个LOB字段的列，置为 empty_blob()

SQL> create table corrupted_data (corrupted_rowid rowid);

Table created.

SQL> set concat off

SQL> declare

2 error_1578 exception;

3 pragma exception_init(error_1578,-1578);

4 n number;

5 begin

6 for cursor_lob in (select rowid r, &&lob_column from &table_owner.&table_with_lob) loop

7 begin

8 n:=dbms_lob.instr(cursor_lob.&&lob_column,hextoraw(‘889911’)) ;

9 exception

10 when error_1578 then

11 insert into corrupted_data values (cursor_lob.r);

12 commit;

13 end;

14 end loop;

15 end;

16 /

Enter value for lob_column: DATA

Enter value for table_owner: owner

Enter value for table_with_lob: table_name

old 6: for cursor_lob in (select rowid r, &&lob_column from &table_owner.&table_with_lob) loop

new 6: for cursor_lob in (select rowid r, DATA from owner.table_name) loop

old 8: n:=dbms_lob.instr(cursor_lob.&&lob_column,hextoraw(‘889911’)) ;

new 8: n:=dbms_lob.instr(cursor_lob.DATA,hextoraw(‘889911’)) ;

SQL> set concat off 
SQL> update &table_owner.&table_with_lob 
set &lob_column = empty_blob() 
where rowid in (select corrupted_rowid from corrupted_data);