Oracle Performance and Engineering

"A customer is recovering from a DB crash .. after restoring the data they see the following error -
java.io.IOException: ORA-01578: ORACLE data block corrupted (file # 6, block # 184017)
ORA-26040: Data block was loaded using the NOLOGGING option
ORA-01110: data file 6: '/u02/oradata/knta00/knta_data01.dbf'

Problem Analysis -

A quick "oerr ora 26040" printed -

26040, 00000, "Data block was loaded using the NOLOGGING option\n"
//* Cause: Trying to access data in block that was loaded without
//* redo generation using the NOLOGGING/UNRECOVERABLE option
//* Action: Drop the object containing the block.

As evident, some objects (table / index / lob) was in NOLOGGING mode, and thus, was not generating REDO log. So after the recovery - when everything else was recovered, Oracle did not have any data to roll forward / recover the
particular object.

We thus needed to know the following to solve -
(a) WHICH object(s)?
(b) Is it better to drop/ recreate or there's any other way to bypass?

A quick thinking - since Oracle points out some block and data file id - it's most definitely an index or a LOB. Since if it were a table, Oracle would probably print out the table name rather than bothering to print the subset of data blocks.

Diagnosis :

SQL> select table_name, column_name, logging from user_lobs order by logging

DEFAULT_HELP_CONTENTS HELP_CONTENTS NO

HELP_CONTENTS HELP_CONTENTS NO

As you can see there are two important tables with this DEFAULT_HELP_CONTENTS and HELP_CONTENTS. In my instance, none of the columns are set to NO logging. Is it OK to turn on logging for these columns? Eg.

alter table HELP_CONTENTS modify lob (HELP_CONTENTS) (nocache logging);
alter table DEFAULT_HELP_CONTENTS modify lob (HELP_CONTENTS) (nocache logging);"

I was emailed by support before they communicate the issue to the customer. Of course, updating them to LOGGING makes sense. I also added two possible ways they can resolve this issue quickly -

Solution - Method ONE - The "process" method

Query to find out the corrupt ROWIDs -

-- Identifying the LOB table

select owner, segment_name, segment_type  from   dba_extents
where  file_id = 6
and    148017 between block_id and block_id + blocks - 1;

-- Identify the LOB_COLUMN_NAME

select table_name, column_name
from   dba_lobs
where  segment_name = 'SYS_LOB029815C006$$'
-- segment_name returned from above query

-- Identify the corrupt ROWIDs
create table corrupt_data (corrupt_rowid rowid);

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('8899')) ;
   exception
   when error_1578 then
      insert into corrupt_data values (cursor_lob.r);
   end;
 end loop;
end;
/   

-- Finally set the particular ROWIDs to EMPTY_BLOB() (or EMPTY_CLOB() if it were a CLOB) --

update &table_owner.&table_with_lob
    set &lob_column = empty_blob()
    where rowid in (select corrupt_rowid from corrupt_data);

Solution - Method TWO - The "out of the box" method

A quick glance through the table names indicate they could hold static data. Quickly querying the test database
confirmed it. So, I told the customer he can just drop the aforementioned tables and import them back from the
test / dev instance of him. As it turned out, this was less technical and since the tables contained 16 and 0 records
respectively was much faster to recover.

P.S.
I believe the mentioned column were modified to NOLOGGING assuming it would speed up the install process
(the client was running a third party software). But is NOLOGGING worth it?  We will discuss it soon. But let me say
this for now - NOLOGGING was actually called UNRECOVERABLE till some releases back.

posted by Nilendu  # 4:36 PM