oracle ora1578,ORA-1578 ORA-26040 in a LOB segment (Doc ID 293515.1)

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 , 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

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

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

XMLTYPE

There is the case where the lob segment might be associated to a XMLTYPE:

select table_name

from dba_lobs

where segment_name = 'SYS_LOB0000013274C00003$$'

and owner = 'SCOTT';

TABLE_NAME

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

TABLE_WITH_XML_COLUMN

SQL> describe scott.TABLE_WITH_XML_COLUMN

Name Null?      Type

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

FILENAME        VARCHAR2(64)

XML_DOCUMENT    XMLTYPE

XML_DOCUMENT is the lob column in this case.

Fix

3. Identify the table rowid's referencing the corrupted lob segment blocks by running the following plsq script:

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;

PRAGMA EXCEPTION_INIT(ora1578, -1578);

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

/

undefine lob_column

select * from bad_rows;

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

XMLTYPE

If the lob segment is related to a XMLTYPE, then replace cursor_lob.&&lob_column by cursor_lob.&&lob_column.getCLOBVal() (CLOB ) or getBLOBVal() (BLOB) in the above plsql; the entire line for a CLOB will then be:

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

Similarly when prompted by variable values, in our example it would be:

Enter value for lob_column: XML_DOCUMENT

Enter value for table_owner: SCOTT

Enter value for table_with_lob: TABLE_WITH_XML_COLUMN

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

If &lob_column is a CLOB datatype, replace empty_blob by empty_clob.

XMLTYPE

If the lob segment is related to a XMLTYPE use XMLType.createXML('') instead of empty lob:

SQL> update scott.TABLE_WITH_XML_COLUMN

set XML_DOCUMENT = XMLType.createXML('')

where rowid in (select row_id from bad_rows);

5. Observations

Note that the data inside the corrupt lob blocks is not salvageable because the information there is not readable. The block is now corrupt with NOLOGGING format.

Setting the corrupt lob to empty lob will add the blocks formerly mapped to this lob to the freelist.  Eventually when PCTVERSION or RETENTION criteria causes the space to be salvaged and reused for new data, error ORA-1578/ORA-26040 can be seen again in the same LOB blocks. Empty lob means that the pointer to the corrupt lob referenced in that column is cleared. The corrupt block itself is not touched/repaired; it is just marked as free in the freelist metadata for the lob segment. If the lob segment continues growing using more space, the corrupt block can be attempted to be reused (as the block is free) and corruption error will be produced again for an INSERT or an UPDATE of the lob segment requesting more space.  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);

If alter table MOVE is executed check for UNUSABLE table indexes to be REBUILD as the alert log may have the next messages:

Some indexes or index [sub]partitions of table have been marked unusable

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 in dbms_lob.instr is a fake value to verify the lob content. dbms_lob.instr is not supposed to find that string so the variable "n" should always return 0.