| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: corrupted block
Hi Suzi,
The first thing I would suggest is to determine if it is actualy in use by the database (ie allocated to an object)... dbv has an "os perspective" on the file and hence does not understand what objects contain what blocks. Metalink note Doc ID: 28814.1 has some good basic information on block corruptions as well.
A query such as:
SELECT tablespace_name, segment_type, owner, segment_name
FROM dba_extents
WHERE file_id = <AFN>
and <BL> between block_id AND block_id + blocks - 1;
will help to answer this first question... once that question is answered, you can move on to other options (what do do about it)... Perhaps it is simply an index and can be dropped / recreated... If not, the note discussed various ways to extract the good data in the case of a table. (dbms_repair, events, select using rowids to exclude the block,etc)
There is also always the question of why. In this case it may be quite difficult to figure out why based on its existence for some time.
Additions and corrections welcomed!
Regards,
Paul
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: Suzy Vordos
INET: lvordos@qwest.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@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).
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: PAUL.HOOD INET: PAUL.HOOD_at_oracle.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).Received on Mon Feb 24 2003 - 12:14:32 CST
 |
 |