ORA-1578 ORA-26040 in a LOB segment - Script to solve the errors -- cnDBA.cn

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.http://www.cndba.cn/dave/article/2326

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
…..http://www.cndba.cn/dave/article/2326

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:http://www.cndba.cn/dave/article/2326

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.http://www.cndba.cn/dave/article/2326

Fix

3. Identify the table rowid’s referencing the corrupted lob segment blocks by running the following plsql 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;
  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;
/

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:http://www.cndba.cn/dave/article/2326

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

Similarly when prompted by variable values, in our example it would be:http://www.cndba.cn/dave/article/2326

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 or NCLOB datatype, replace empty_blob by empty_clob.http://www.cndba.cn/dave/article/2326

XMLTYPE

If the lob segment is related to a XMLTYPE use XMLType.createXML(‘’) instead of empty lob:http://www.cndba.cn/dave/article/2326

SQL> update scott.TABLE_WITH_XML_COLUMN 
        set XML_DOCUMENT = XMLType.createXML('') 
      where rowid in (select row_id from bad_rows);

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

For a LOB Partition:

alter table &table_owner.&table_with_lob MOVE PARTITION &partition_name TABLESPACE &tablespace_name 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 <name> 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.

签到成功

CNDBA社区