Oracle-L: Re: corrupted block

From: Thomas Day <tday6_at_csc.com>
Date: Mon, 24 Feb 2003 11:09:02 -0800
Message-ID: <F001.005571FC.20030224110902@fatcity.com>

oops. See http://metalink.oracle.com/ note=34371.1

Need to convert the file# and block# to hex.

                                                                                                                                       
                      "Thomas Day"                                                                                                     
                      <tday6                   To:      Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>                    
                      @csc.com>                cc:                                                                                     
                      Sent by: root            Subject: Re: corrupted block                                                            
                                                                                                                                       
                                                                                                                                       
                      02/24/2003 12:19                                                                                                 
                      PM                                                                                                               
                      Please respond                                                                                                   
                      to ORACLE-L

Have you tried copying it into a new table?

Assuming that you have tried and failed, try creating a new table something like this:

Create new_table as (select * from old_table where substr(rowid,1,8) != 02457856);

I believe that that's the way the rowid was set up in Oracle 7.3.4 but my understand comes from a script that Dave Hungle, dhungle_at_dbcorp.com , DBCORP Information Systems Inc. posted here.

HTH

                      Suzy Vordos

                      <lvordos                 To:      Multiple recipients
of list ORACLE-L <ORACLE-L_at_fatcity.com>
                      @qwest.com>              cc:

                      Sent by: root            Subject: corrupted block



                      02/24/2003 11:09

                      AM

                      Please respond

                      to ORACLE-L

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: Thomas Day
INET: tday6_at_csc.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

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Thomas Day
INET: tday6_at_csc.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