Help - Corrupted Block - Lob Segment - NOLOGGING

Our third-party version control software uses an Oracle database as its repository.

Documents are stored in a BLOB column in an Oracle table. It is NOT stored "in_row" and logging is turned off (for performance reasons they told me).

We recently encountered a bad block, and I have since determined there are additional bad blocks, but it is just one block that is continually giving us a problem.

When a user attempts to check an object into the repository, they often get the error message:

E03020003: Database Error: [CAI/PT][ODBC Oracle 8 driver][Oracle]ORA-01578: ORACLE data block corrupted (file # 31, block # 123533)
ORA-01110: data file 31: '/san1/oradata/PROD/harvestblob01.dbf' ORA-26040: Data block was loaded using the NOLOGGING option

This tells me the block still has free space to insert new data; i.e., the block is on the free list.

Here is some information about the tablespace housing this lob segment (from dba_tablespaces):
Force_logging="N'
Extent_management='LOCAL'
Allocation type='system'
Segment_space_management='auto'

Can somebody tell me what I need to do to allow users to continue to check items into this repository without receiving the error message and with minimal data loss; i.e., limiting data loss to the content of corrupted block# 123533. I think I can do this if I am able to permanently get this block off the free list, but how can I do that?

I have contacted Oracle Support, but they have been of limited assistance. They had me review metalink note 293515.1 "ORA-1578 ORA-26040 in a LOB segment - Script to solve the errors". But this has not helped our situation. The metalink note directs us to create a table with a single column of type rowid. Then run an Oracle procedure to populate this table with rowids for rows in the corrupted block. After this we set concat to "#", then update the lob column to "empty_blob". I did this and commited, but users receive the same error message.

Thanks for any and all suggestions.

Sam Bootsma
Oracle Database Administrator
Information Technology Services
George Brown College
Phone: 416-415-5000 x4933
Fax: 416-415-4836
E-mail: sbootsma_at_gbrownc.on.ca

--
http://www.freelists.org/webpage/oracle-l