沉寂 燃烧

- LOB segment has been defined as NOLOGGING
- LOB Blocks were marked as corrupted by Oracle after a datafile restore / recovery.


Identify the table referencing the lob segment - Example
=========================================================
Error example when accessing the lob column by a sql statement:

ORA-01578 : ORACLE data block corrupted (file #13 block # 2532)
ORA-01110 : datafile 13: '/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

select owner, segment_name, segment_type
from   dba_extents
where  file_id = 13
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:

rem ********************* Script begins here ********************


create table corrupted_data (corrupted_rowid rowid);

set concat #

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


rem ********************* Script ends here ********************


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 #
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.
 

.

___________________________________

This entry was posted on 2008-12-26 17:29:24 and is filed under hxre. You can follow any responses. Tags:lob ora-01578