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
