0

Why is it not possible to rebuild corrupt LOB indexes? So if my LOB index is corrupted, how to recover it?

2
  • Can you explain what makes you believe your LOB index is corrupt? Jul 24, 2015 at 11:10
  • validate logical database says so.
    – tesla747
    Jul 24, 2015 at 11:27

3 Answers 3

1

The alert log should tell you if a data block is corrupt, no need to validate index structure. That will lock your table until completed. If no backups you might try to exporting the data using expdp. Then drop the table and re-create it and import using impdp. You will need to create the indexes, constraints and grants as well. Could also use CTAS.

4
  • The export will be able to do the job even if the index is corrupted?
    – tesla747
    Jul 26, 2015 at 13:07
  • Yes, just make sure you do a data_only export.
    – Stringer
    Jul 26, 2015 at 13:38
  • And what happens if I leave that corrupted index untouched forever..what impact does it have.
    – tesla747
    Jul 26, 2015 at 18:36
  • A) index will not be used when that column is reference in a query predicate B) each time that column is accessed an error will be written to the alert log
    – Stringer
    Jul 26, 2015 at 22:15
1

In LOB columns, the data and index are intertwined. The index cannot be operated on like a normal index. Use this script to identify the rows that are corrupted:

set serverout on
exec dbms_output.enable(100000);
declare
  page    number;
  len    number;
  c      varchar2(10);
  charpp number := 8132/2;

begin
  for r in (select rowid rid, dbms_lob.getlength (<your_clob_column>) len
            from   <your_table_with_clcob_column>) loop
    if r.len is not null then
      for page in 0..r.len/charpp loop
        begin
          select dbms_lob.substr (<your_clob_column>, 1, 1+ (page * charpp))
          into   c
          from   <your_table_with_clcob_column> 
          where  rowid = r.rid;

        exception
          when others then
            dbms_output.put_line ('Error on rowid ' ||R.rid||' page '||page);
            dbms_output.put_line (sqlerrm);
        end;
      end loop;
    end if;
  end loop;
end;
/

You'll need to either delete the rows or update the LOB column to an empty LOB with:

update <your_lob_table> 
set <your_lob_column> = empty_blob() 
where rowid=<your_corrupt_rowid>;
0

What is the output of:

select * from v$database_block_corruption?

Unlike regular indexes, LOB indexes can't be dropped/ recreated, because you would have to drop the column instead. So, assuming your backups are valid, you can attempt to perform block media recovery (BMR):

http://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmblock.htm

1
  • yes yes have checked everything. I do not have backups.
    – tesla747
    Jul 25, 2015 at 17:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.