ORA-1578 ORA-26040 in a LOB segment - Script to solve the errors (Doc ID 293515.1)

Last updated on NOVEMBER 03, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 8.1.7.0 to 12.1.0.2 [Release 8.1.7 to 12.1]
Oracle Database - Standard Edition - Version 8.1.7.0 to 12.1.0.2 [Release 8.1.7 to 12.1]
Information in this document applies to any platform.
***Checked for relevance on 10-Feb-2011***
***Checked for relevance on 3-Jul-2015***


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 reading 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 <dba number>, already marked corrupted
DBV-00201: Block, DBA <rdba>, 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


IMPORTANT

When ORA-26040 is not produced along with ORA-1578 the block is then corrupt by a different reason and Block Media recovery can be used to repair the corruption like RMAN BLOCKRECOVER.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms