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-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-00201: Block, DBA
Example:
DBV-00200: Block, dba 54528484, already marked corrupted
.....
The dba can be used to get the relative file number and block 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:
from dba_extents
where file_id = 10
and 2532 between block_id and block_id + blocks - 1;
In our example it returned:
segment_name=SYS_LOB0000029815C00006$$
segment_type=LOBSEGMENT
2. Query dba_lobs to identify the table_name and lob column name:
from dba_lobs
where segment_name = 'SYS_LOB0000029815C00006$$'
and owner = 'SCOTT';
In our example it returned:
column_name = EMPLOYEE_ID_LOB
XMLTYPE
There is the case where the lob segment might be associated to a XMLTYPE:
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:
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 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 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> 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:
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:
- 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.