Re: Corrupted ORA-00604, ORA-01578, ORA-01110

Sorry, there is no easy answer; corruption is a pain any way you slice it and it has many causes. That being said, there are two types: hardware and software corruption.

Hardware corruption means the disk or controller have gone bad/are going bad. When Oracle informs you of corruption during an operation it usually identifies the block that is corrupt(one of them anyway). If you rerun the operation and you a. don't get the corruption message or b. it is a different block identified, then it is likely hardware corruption. This assumes you are not using any degree of parallelism.

The solution is have your sysadmin check the disk that the datafile is on + controller and replace it if necessary.

If you have software corruption the same block will show as corrupt no matter what. You can run dbv against the datafile to see all of the corruption in the datafile and see if the corruption is consistently in the same blocks. If so, you have software corruption.

If the corruption is in an index, you can just recreate the index and all is well(except whatever caused the corruption may reoccur). If it is in a table, you can set event 10xxx (don't remember the event number off the top of my head) in the init.ora to ignore the data in the corrupted blocks assuming you can afford to lose the data(this is a cheap way out though, and doesn't always work :-)
With corruption now ignored, do a create table newtable as select * from originaltable and then drop the original table. This will get rid of software corruption, with the loss of any data in the corrupt blocks as this will copy any data NOT in corrupt blocks to the new table.

If you can't afford data loss, restore from backup. Be aware that software corruption may be in your backups so you might have to go back a ways until the corruption isn't there.

I ran into this problem with Quick IO (veritas). The product caused corruption in SOME datafiles when the files were read from and written to an OS file. In other words when the (good)datafiles were backed up they became corrupted on tape. When I had to recover this database, I found that all my backups were junk. Now that was a fun time. Veritas made a patch to fix this "small bug", but that didn't undo the bad backups.

In the end, I have been where you are and don't envy you :-)

--
Dave A


"Bernd Schneider" <bschnei_at_snafu.de> wrote in message
news:39B87B5F.7C094773_at_snafu.de...


> Hallo,

>

> on a Oracle7 - Server i got a corrupted datafile "sys1oxxx.ora".

>

> How can this happen, could someone please give me some advice.

>

>

> thanks in advance

>

> Bernd Schneider