Re: corrupted block

Rama Velpuri's book had the answer to how to copy rows from a table when a corrupted block exists. The downside is the table is roughly 18GB, and has LONG.

So my next question, is there any way to determine by trace file when the block corruption occurred? I'm still under the assumption that all backups will have the corrupted block.

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: Suzy Vordos
> INET: lvordos_at_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_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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Suzy Vordos
  INET: lvordos_at_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_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).