RE: How to remove corrupted blocks from datafile.

From: Rajesh Dayal <Rajesh_at_ohitelecom.com>
Date: Wed, 06 Jun 2001 01:40:05 -0700
Message-ID: <F001.0031D83F.20010606010600@fatcity.com>

Just for the sake curiosity........

I think we should try to salvage the records which are stored after the corrupted blocks. Following SQL may be helpful for this purpose:

SQL> SELECT /*+ ROWID(TABLE_NAME) */ DOC_ID, ROWID 2 FROM TABLE_NAME
3 WHERE substr(ROWID,1,8) > '00007085' and 4 substr(ROWID,15,4) = '0009'

This could happen that only one Data block is corrupted and rest all next to that are fine. Of course we need to find out this by hit and trial method.....

Please correct me if I am wrong....

Rajesh

-----Original Message-----
Lee - lerobe
Sent: Wednesday, June 06, 2001 12:21 PM
To: Multiple recipients of list ORACLE-L

This what I did last time it happened to me. (Bear in mind that this was Oracle 7.3 and so rowid format is different for later releases of Oracle).

In sqlplus select the following from the table with the corruption

SQL> SELECT /*+ ROWID(TABLE_NAME) */ DOC_ID, ROWID 2 FROM TABLE_NAME
3 WHERE ROWID BETWEEN '00000000.0000.0000' AND 'FFFFFFFF.FFFF.FFFF'; This will stop at the corrupted row.
eg. 00007085.4234.0009 - this is a hexidecimal representation

The first 8 digits are the block number (Block no. 28805 in hex is 7085).
The next four are the row and the final four are the file number. The last record before the corrupted block in this case will be 00007084.FFFF.0009.
The first record of the corrupted block in this case will be 00007085.0000.0009.
The first record after the corrupted block in this case will be 00007086.0000.0009.

To get these unaffected rows back into the table do the following. create table table_name_tmp
as select * from table_name
where 1 = 2;

This will create a temporary table the same as your original. Next backup
the original table structure so you can recreate it. exp username/password file=table_name.dmp rows=n tables=TABLE_NAME then
imp username/passwd file=table_name.dmp indexfile=table_name.sql

Make the necessary amendments to the indexfile (take REM statements and connect statements out)

Next insert the uncorrupted rows into the temporary table. SQL> INSERT INTO TABLE_NAME_TMP SELECT /*+ ROWID(TABLE_NAME) */ * 2 FROM TABLE_NAME
3 WHERE ROWID BETWEEN '00000000.0000.0000' 4 AND '00007084.FFFF.0009'; Next insert the rows after the corruption SQL> INSERT INTO TABLE_NAME_TMP SELECT /*+ ROWID(TABLE_NAME) */ * 2 FROM TABLE_NAME
3 WHERE ROWID BETWEEN '00007086.0000.0009' 4 AND 'FFFFFFFF.FFFF.FFFF';
Next drop the original table
drop table table_name;
and then run your sql file (indexfile) to create the table and associated
indexes.
@table_name

After this reapply all grants (run the recreate grant script) and finally insert new rows.

insert into table_name
select * from table_name_tmp;

Lee Robertson

The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system.
--

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

Author: Robertson Lee - lerobe
INET: lerobe_at_acxiom.co.uk

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

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.com
--

Author: Rajesh Dayal
INET: Rajesh_at_ohitelecom.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------