Re: Import Data Into Table With ORA-01578 Error

From: joel garry <joel-garry_at_home.com>
Date: Thu, 13 Nov 2008 11:46:50 -0800 (PST)
Message-ID: <f86b1ca7-9d03-41f1-b079-4065a2a2635f@i24g2000prf.googlegroups.com>


On Nov 13, 7:15 am, myocarditis_recovering <myocarditis_recover..._at_yahoo.com> wrote:
> Hi,
>
> We have a case, in Windows (9i), of "ORA-01578: ORACLE data
> block corrupted (file # x, block # y)". We know the table which this
> affects,
> as well as the primary keys of the records stored in this block and
> file.
> (For the purposes of this post, say, table A, keys PK1 and PK2).
>
> What's more, we know that the ORA-01578 had occurred in a previous
> server, not the existing one into which this database was copied.
> (Through dbv of previous backups, etc..)
>
> We also have other databases with identical data for the records
> in the corrupted data blocks.
>
> My intention is to, somehow, export the data from another database
> and import it into the one with the ORA-01578 error. I can export the
> data; That is not the issue.
>
> At first, I tried deleting keys PK1 and PK2 from table A,
> but ORA-01578 prevented this.(As I had expected).
>
> Then,  I tried copying table A to table B and bypassing the corrupted
> block (With the intention of dropping table A, importing the missing
> data into table B, and  copying it to A) through the command:
>
>  create table B as select * from A where
>  dbms_rowid.rowid_block_number(rowid)  <> y;
>
> However, the ORA-01578 error appeared (For block y)
>
> Is there a way for me to copy the identical data from another
> database
> into the one with the ORA-01578 error (Though the physical corruption
> was on another server).
>
> Any ideas would be appreciated.
>
> Thanks,
> DF

Can you use dbms_repair to mark the block corrupt and skip it? See metalink Note: 28814.1 and 556733.1, and otherwise check the package out, including skip_corrupt_blocks.

jg

--
@home.com is bogus.
Let's eat!
Received on Thu Nov 13 2008 - 13:46:50 CST

Original text of this message