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