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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
