ITPub博客

首页 > 自动化运维 > 应用服务器 > Oracle数据库出现ORA-19566 LOB坏块的处理记录

Oracle数据库出现ORA-19566 LOB坏块的处理记录

应用服务器 作者:929794951 时间:2020-06-22 17:46:36 0 删除 编辑

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);

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/69976494/viewspace-2700010/,如需转载,请注明出处,否则将追究法律责任。

全部评论

注册时间:2020-06-04

  • 博文量
    5
  • 访问量
    1266