ORA-1578 ORA-26040 in a LOB segment - Script to solve the errors [ID 293515.1]

ORA-1578 ORA-26040 in a LOB segment - Script to solve the errors [ID 293515.1]

--------------------------------------------------------------------------------
 
  수정 날짜 10-FEB-2012     유형 PROBLEM     상태 PUBLISHED  

In this Document
  Symptoms
  Cause
  Solution
  References
--------------------------------------------------------------------------------
Applies to:
Oracle Server - Enterprise Edition - Version: 8.1.7.0 to 11.1.0.7 - Release: 8.1.7 to 11.1
Oracle Server - Standard Edition - Version: 8.1.7.0 to 11.1.0.7   [Release: 8.1.7 to 11.1]
Information in this document applies to any platform.
***Checked for relevance on 10-Feb-2011***
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
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

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           

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

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.

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

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

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

 관련 자료

 

--------------------------------------------------------------------------------
제품
--------------------------------------------------------------------------------

•Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Standard Edition
•Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition
키워드
--------------------------------------------------------------------------------
DATA CORRUPTION; DATA_COLLECT; LOB; NOLOGGING
오류
--------------------------------------------------------------------------------
ORA-26040; ORA-1578; ORA-1110; DBV-201; DBV-200