Re: corrupted block

Suzy,

    The big question is whether or not the block actually contains data. It appears that it does not, if I am reading the last few lines correctly. This means you are in luck. Use a non-full table scan query to extract the data, drop the tablespace and remove the datafile. Recreate the tablespace/datafile/table/indexes and reload the data. I would also recommend having the physical disk(s) checked to see if it is a physical problem.

    Why a non-full table scan? It will read all of the blocks (used or empty) below the high water mark, much like the analyze has done (I think). The block indicated is empty, but below the highwatermark, so it will be read (or attempted) in a full table scan. This will then cause the fts to fail. Using an index will cause only the populated blocks to be read.

Dan FInk

Suzy Vordos wrote:

>I recently inherited a 40GB 7.3.4 database (yes, it needs to upgrade).
>Last night I analyzed the tables and a corrupted block was found. I
>know which table and datafile it is, and it's the only table in the
>affected tablespace.
>
>The database is in archivelog mode so I can recover the datafile, but I
>am not certain when the block corruption occurred. There were no
>proactive measures in place to quickly report a corrupted block. So I
>assume it may have been there a long time, and was just found through
>analyze (tables hadn't been analyzed since Dec-2000).
>
>So my question is, if all backups contain the corrupted block, how would
>I copy all non-corrupted blocks from this table into a new table?
>
>Here is the trace file:
>
>ORACLE data block corrupted (file # 24, block # 57856)
>
>Dump file
>/dbms/ora00/app/oracle/admin/kana03aP/udump/kana03ap_ora_13163.trc
>Oracle7 Server Release 7.3.4.3.0 - Production
>With the distributed, replication, parallel query and Spatial Data
>options
>PL/SQL Release 2.3.4.3.0 - Production
>ORACLE_HOME = /dbms/ora00/app/oracle/product/7.3.4
>System name: SunOS
>Node name: kanadb-co1
>Release: 5.6
>Version: Generic_105181-17
>Machine: sun4u
>Instance name: kana03aP
>Redo thread mounted by this instance: 1
>Oracle process number: 10
>Unix process pid: 13163, image: oraclekana03aP
>
>*** 2003.02.24.02.49.42.000
>*** SESSION ID:(24.1317) 2003.02.24.02.49.41.000
>***
>Corrupt block dba: 0x6000e200 file=24. blocknum=57856. found during
>buffer read
>on disk type:0. ver:0. dba: 0x00000000 inc:0x00000000 seq:0x00000000
>incseq:0x00000000
>Entire contents of block is zero - block never written
>Reread of block=6000e200 file=24. blocknum=57856. found same corupted
>data
>
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Daniel W. Fink
  INET: optimaldba_at_yahoo.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).