Shulou(Shulou.com)06/01 Report--

This article introduces the relevant knowledge of "Oracle database prompts how to deal with ORA-19566 LOB". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

1. Failure phenomenon:

During the automatic backup of the production library at night, the backup fails with the following error prompt:

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.

Diagnosis of bad blocks

Use DBV for bad block detection:

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

It turns out that there are 96 bad blocks.

Using another method to detect, the result is the same.

Rman target /

RMAN > run {

2 > allocate channel D1 type disk

3 > backup check logical validate datafile 20

4 > release channel D1

5 >}

The diagnostic results show that there are 96 bad blocks, and the detailed numbers of the bad blocks are as follows:

[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 240 CORRUPT 0 20 1540328 240 CORRUPT 0 20 1539240 240 CORRUPT 0 20 1539432 240 CORRUPT 0

To be on the safe side, diagnose the entire library

RMAN > configure device type disk parallelism 4

RMAN > backup validate check logical database

Fortunately, no bad blocks were found in other data files.

Check the data objects on the bad blocks:

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

All are LOB fields on a single table.

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

OWNER TABLE_NAME

Test mytable

Try to skip the bad block

SQL > execute DBMS_REPAIR.SKIP_CORRUPT_BLOCKS ('OWNER','TABLE_NAME')

PL/SQL procedure successfully completed.

As a result, the RMAN backup still reported an error.

Set the maximum allowed bad blocks in 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% Trout% U.bak'

Crosscheck backupset

Release channel c1

Release channel c2

}

The backup was successful as a result.

But when expdp exports, it still reports an error.

ORA-02354: error in exporting/importing data

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

There is information on the Internet that setting 10231 events can skip errors.

Alter system set events='10231 trace name context forever,level 10'

After setting up. The expdp export still reported an error.

3. Deal with bad blocks

According to the information on the metalink document, Doc ID 1900424.1 and Doc ID 472231.1) the solution is as follows:

(1) restore bad blocks with backup

Rman > catalog datafilecopy'/ u01amp backupamp users01.dbf'

Rman > catalog archivelog'/ u01Gar backupUniverse archivelog Archipelago Archipelago 30.dbf'

Rman > blockrecover datafile 5 block 99100101

However, there is no backup available at present, and this road is not available.

(2) Clean up the table records with bad blocks

There is only one last step left. Clean up the table records with bad blocks. The operation instructions for Doc ID 293515.1 are as follows:

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)

Find out the row where the bad block of the lob field is located, and then set the column of that LOB field to 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)

This is the end of the content of "Oracle Database Tips how to deal with ORA-19566 LOB". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.