ORA-01578 : Best solution?

[Kyris]

We ahve a server OS NT4 SP4 running Oracle 8.0.4
I am not an Orcle DBA so please forgive my 'ignorance'..

When I run a select statement on a table I get ORA-01578: Oracle data block corrupted...
ORA-01110: data file 12:.....

I know that it affects one of the tables only and none other. I can only access data after the 03.05.2005 and none before. The system crashed on the 02.05.2005

Can someone please suggest a way out? The client has no backups at all.
I am thinking of running a create table ...as select * from table_name where date > '03-MAY-2005';
Then delete the original table and rename the new one or do the reverse of above.

Also what will happen to the corrupt block? I run dbv and found the "Total Pages Marked Corrupt : 1 "
If I delete the table and recreate it, will the corrupted block be marked as corrupt and not used again or what?

Thanks
kyris

[Steven E. Protter]

If only one table is affected, you should be ablt to drop the table and recreate it with sql.

Officially you probably should restore and recover the database/tablespace.

SEP

[Brian Crabtree]

I would also recommend using the 'dbv' utility on the file, to make sure that there are no other blocks that are corrupted in the file.

Brian

[Yogeeraj_1]

hi,

please refer to metalink note: :47955.1 - Block Corruption FAQ

it refers to 3 alternatives namely:
o Restore and recover the database from backup (recommended).
o Recover the object from an export.
o Select the data out of the table bypassing the corrupted block(s).

hope this helps!
regards
yogeeraj

[Eric Antunes]

Hi Kyris,

Since you have no backups you can do this "select * from table_name where date > '03-MAY-2005';". But after that run the dbverify utility to check if the database or that specific tablespace has no more corruption...

For more info. about solving this block corruptions read Metalink [NOTE:28814.1]

Best Regards,

Eric

[Eric Antunes]

Hi,

After doing that select, drop the old_table_name before running the dbverify utility.

Eric

[Kyris]

Back after a 3 day break...Thank you all for your replies...
I am not sure still on one point.
Once I do the create table, drop the old table..run dbv - what will happen to the corrupted block. Will ORACLE mark it as corrupt and NOT use it again ? Will this corrupt block give me any more trouble ?

[Eric Antunes]

Hi Kyris,

If you droped the table, that bad block doesn't exist anymore. Dbverify is for you to be sure that they aren't no more bad blocks.

You must shudown the database before using dbverify! Here are 2 examples:

$dbv help=y -- gives you the help menu...

$dbv file=/.../system01.dbf blocksize=8192 feedback=100 -- to verify one system datafile...

Best Regards,

Eric

[Eric Antunes]

Hi again,

The output of the above example for the first system datafile:

DBVERIFY - Verification complete

Total Pages Examined : 153600
Total Pages Processed (Data) : 83079
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 32884
Total Pages Failing (Index): 0
Total Pages Empty : 0
Total Pages Marked Corrupt : 0
Total Pages Influx : 0

Eric

[Kyris]

At the moment when I run dbv the pages marked as corrupt = 1. I presume once the table is deleted and re-created, the dbv should report no corrupted pages??
ALSO
Is it worh doing the follow ?
1- alter database datafile ..... OFFLINE;
2 - recover automatic datafile .....;
3 - alter database datafile ... ONLINE;

Thanks
kyris