RE: Datafile Corruption ........... or Not

Typically 1578s are due to hardware hickups or a known bug. Dbverify will not capture all types of data corruption. The only truly safe way to capture physical corruption of a datablock is to get it into the buffer cache with a statement that exercises the datablock.

The easiest way to determine the extent of the corruption is to dump the logical representation of the datablock(s) to a trace file. If this dump is successful it will give you the content of the block in a very raw format. It will also give you information regarding the type of block it is. Here is that syntax while logged in as sysdba:

Alter system dump datafile 7 block 191659;

If this is really important data you may want to open a TAR with Oracle Support as they have a tool that will interpret the data out of this block for you. I would also suggest that you look for other possible corruptions around this block by dumping them and selecting the data appropriately. Proactively, you may want to select from all the data you can that exists on that device.

If the data is corrupted beyond recognition then you can try to mine the data out of related objects (e.g. if it is a table's datablock, then you can look at the related index or visa versa). You just have to be creative with your select statements (force the use of an index or not).

To learn more about what is affected you can run the following query: SELECT tablespace_name, segment_type, owner, segment_name

          FROM dba_extents
         WHERE file_id = 7
           and 191659 between block_id AND block_id + blocks - 1;

For more detail see the Oracle Metalink note number 28814.1.

Regards,

Michael Sale
Co-author: Oracle 9i on Windows 2000 Tips & Techniques

-----Original Message-----
Faroult
Sent: Tuesday, May 28, 2002 2:05 PM
To: Multiple recipients of list ORACLE-L

Kevin Lange wrote:
>
> Hey gang;
> I have an 8.0.5.0 database running on a Solaris platform.
>
> The developer is running a simple query which returns the error
>
> ERROR at line 1:
> ORA-01578: ORACLE data block corrupted (file # 7, block # 191659)
> ORA-01110: data file 7: '/u08/oradata/TTCT/dynamici01.dbf'
>
> So I run dbverify (dbv) against the file and it says all is OK.
>
> $ dbv file=dynamici01.dbf
> DBVERIFY: Release 8.0.5.0.0 - Production on Tue May 28 13:2:7 2002
> (c) Copyright 1998 Oracle Corporation. All rights reserved.
> DBVERIFY - Verification starting : FILE = dynamici01.dbf
> DBVERIFY - Verification complete
> Total Pages Examined : 409600
> Total Pages Processed (Data) : 0
> Total Pages Failing (Data) : 0
> Total Pages Processed (Index): 158376
> Total Pages Failing (Index): 0
> Total Pages Empty : 0
> Total Pages Marked Corrupt : 0
> Total Pages Influx : 0
>
> Does anyone have any suggestions about this other than rebuilding the
> database (it is a clone of our production ... it will just set the
> developer back if we have to clone it) ?
>
> Thanks
>
> Kevin

Kevin,

   Try to check what kind of segment this block belongs to (DBA_EXTENTS + DBA_SEGMENTS - extents are identified by the block# of their first block plus the number of contiguous blocks, so you have tolook for the extent for which 191659 is >= first block and < first block + extent size. It will tell you whether it's a table (and which one), index, temp or rollback segment, and you will be better armed to take a decision. There may be less time-costly than cloning the production database again.

-- 
Regards,

Stephane Faroult
Oriole Software
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.com


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Michael P Sale
  INET: Michael.Sale_at_oracle.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).