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.

2. 對壞塊進行診斷

使用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

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

Oracle資料庫出現ORA-19566 LOB壞塊的處理記錄

相關文章