Oracle數據庫提示ORA-19566 LOB怎么處理

本篇內容介紹了“Oracle數據庫提示ORA-19566 LOB怎么處理”的有關(guān)知識，在實(shí)際案例的操作過(guò)程中，不少人都會(huì )遇到這樣的困境，接下來(lái)就讓小編帶領(lǐng)大家學(xué)習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學(xué)有所成！

1.故障現象：

在晚上的生產(chǎn)庫自動(dòng)備份時(shí)，備份失敗，出現以下錯誤提示：

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. 對壞塊進(jìn)行診斷

使用DBV進(jìn)行壞塊檢測:

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個(gè)壞塊

使用另外一種方法檢測，檢查結果相同

rman target /

RMAN> run{

2> allocate channel d1 type disk;

3> backup check logical validate datafile 20;

4> release channel d1;

5> }

診斷結果顯示，有96個(gè)壞塊，壞塊的詳細編號如下：

[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

為了保險起見(jiàn)，對整個(gè)庫進(jìn)行診斷

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

還好，其他數據文件沒(méi)有發(fā)現壞塊。

檢查壞塊上的數據對象：

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

嘗試跳過(guò)壞塊

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導出時(shí)，仍然報錯

ORA-02354: error in exporting/importing data

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

網(wǎng)上有資料說(shuō)設置10231事件可以跳過(guò)錯誤

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;

但當前沒(méi)有可用的備份，這條路走不通。

（2）對出現壞塊的表記錄進(jìn)行清理

只剩最后一招了， 對出現壞塊的表記錄進(jìn)行清理， Doc ID 293515.1操作 說(shuō)明如下：

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字段壞塊所在的行，然后把那個(gè)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);