oracle 验证 lob 坏块,LOB字段出现坏块的处理方法

ORA-1578 ORA-26040 in a LOB segment - Script to solve the

errors [ID 293515.1]

In this Document

Symptoms

Cause

Solution

References

--------------------------------------------------------------------------------

Applies to:

Oracle Server - Enterprise Edition - Version: 8.1.7.0 to

11.1.0.7

Oracle Server - Standard Edition - Version: 8.1.7.0 to

11.1.0.7

Information in this document applies to any platform.

Symptoms

Purpose:

- The purpose of this article is to provide a script to update

a lob column, that is referencing a lob block marked as corrupted

due to NOLOGGING operations, with an empty lob.

- It will avoid errors ORA-1578 / ORA-26040 when the lob

column is accessed by a sql statement like a SELECT and a table

export can be produced if needed.

Problem:

- ORA-1578 and ORA-26040 are produced when accesing a lob

column in a table:

ORA-1578 : ORACLE data block corrupted (file # %s, block #

%s)

ORA-26040: Data block was loaded using the NOLOGGING option

- dbverify for the datafile that produces the errors fails

with error DBV-200 (rdbms version < 10.2.0.4) or DBV-201 (rdbms

version >= 10.2.0.4):

DBV-00200: Block, dba , already marked corrupted

DBV-00201: Block, DBA , marked corrupt for invalid redo

application

Example:

dbv file=/oracle/oradata/data.dbf blocksize=8192

DBV-00200: Block, dba 54528484, already marked corrupted

.....

The dba can be used to get the relative file number and block

number:

Relative File number:

SQL> select dbms_utility.data_block_address_file(54528484)

from dual;

DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(54528484)

----------------------------------------------

13

Block Number:

SQL> select dbms_utility.data_block_address_block(54528484)

from dual;

DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(54528484)

-----------------------------------------------

2532

Cause

LOB segment has been defined as NOLOGGING and LOB Blocks were

marked as corrupted by Oracle after a datafile recovery.

Solution

Identify the table referencing the lob segment

Error example when accessing the lob column by a sql

statement:

ORA-01578 : ORACLE data block corrupted (file #13 block #

2532)

ORA-01110 : datafile 10: '/oracle/oradata/data.dbf'

ORA-26040 : Data block was loaded using the NOLOGGING option.

1. Query dba_extents to find out the lob segment name.

Take the Data File number from the error ORA-1110 above as it

represents the absolute file number (AFN) and run the next query to

identify the affected Lob Segment:

select owner, segment_name, segment_type

from dba_extents

where file_id = 10

and 2532

between block_id and block_id + blocks - 1;

In our example it returned:

owner=SCOTT

segment_name=SYS_LOB0000029815C00006$$

segment_type=LOBSEGMENT

2. Query dba_lobs to identify the table_name and lob column

name:

select table_name, column_name

from dba_lobs

where segment_name =

'SYS_LOB0000029815C00006$$'

and owner =

'SCOTT';

In our example it returned:

table_name = EMP

column_name = EMPLOYEE_ID_LOB

Fix

1. Identify the table rowid's referencing the corrupted lob segment

blocks by running the following plsq script:

create table corrupted_data (corrupted_rowid rowid);

set concat off

declare

error_1578 exception;

pragma exception_init(error_1578,-1578);

n number;

begin

for cursor_lob in (select rowid r, &&lob_column from

&table_owner.&table_with_lob) loop

begin

n:=dbms_lob.instr(cursor_lob.&&lob_column,hextoraw('889911'))

;

exception

when error_1578 then

insert into corrupted_data values (cursor_lob.r);

commit;

end;

end loop;

end;

/

undefine lob_column

When prompted by variable values and following our

example:

Enter value for lob_column: EMPLOYEE_ID_LOB

Enter value for table_owner: SCOTT

Enter value for table_with_lob:

EMP

2. 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 corrupted_rowid from corrupted_data);

If &lob_column is a CLOB datatype, replace empty_blob by

empty_clob.

Observations

- Note that the data inside the corrupted lob blocks is not

salvageable.

- Setting the corrupted lob to empty lob will add the blocks

formerly mapped to this lob to the freelist. Eventually when PCTVERSION or RETENTION criteria cause the space to

be salvaged and reused for new data, error ORA-1578/ORA-26040 can

be seen again in the same LOB blocks. In that case and after

applying the above procedure the lob segment can be moved to a new

segment:

alter table &table_owner.&table_with_lob move LOB

(&&lob_column) store as (tablespace

&tablespace_name);

- dbverify will still produce errors DBV-200 / DBV-201 until

the extent of the block marked as corrupted is reused by another

segment.

- In the plsql code above, the value 889911 passed to

procedure hextoraw is a fake value to verify the lob content.

References

NOTE:290161.1 - The Gains and Pains of Nologging Operations

NOTE:794505.1 - ORA-1578 / ORA-26040 Corrupt blocks by NOLOGGING -

Error explanation and solution

Related

--------------------------------------------------------------------------------

Products

--------------------------------------------------------------------------------

Oracle Database Products > Oracle Database > Oracle

Database > Oracle Server - Enterprise Edition

Oracle Database Products > Oracle Database > Oracle Database

> Oracle Server - Standard Edition

Keywords

--------------------------------------------------------------------------------

NOLOGGING; LOB; DATA CORRUPTION; DATA_COLLECT

Errors

--------------------------------------------------------------------------------

DBV-201; DBV-200; ORA-26040; ORA-1578; ORA-1110