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

[Eric Antunes]

Hi 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??"

Yes, I think so. But you should run it after to be realy sure.

"Is it worh doing the follow ?
1- alter database datafile ..... OFFLINE;
2 - recover automatic datafile .....;
3 - alter database datafile ... ONLINE;"

You can only do this if you have the database in archive mode and a complete and cold (executed off-line) backup: do you have does 2 conditions??

Best Regards,

Eric Antunes

Thanks
kyris

[Kyris]

Thanks Eric,

No the DB is in non archive mode and they don't even have any 'proper' cold DB backup.
Therefore I can not do what I suggested on my last post?

[Eric Antunes · Accepted solution]

No, unfortunately, you cannot.

Eric

[Volker Borowski]

Kyris,

first, you should check, if the bad block belongs to the table, or if the statement crashes because it is using an index!!!!

Try to use a FULL Hint, to force Oracle to use a full table scan when accessing data like this:
select /*+ FULL(table_name) */ * from table_name;

If this works, the corruption most likely is related to an index belonging to this table. Re-create the indexes in this case.

To be sure, use this, to identify where the bad block belongs to:

select
segment_name,
segment_type,
block_id,
blocks
from dba_extents
where
( between
block_id and (block_id + blocks - 1))
and
file_id = ;

If it really belongs to the table, you are in trouble. You might be able to read more than the blocks of your where-clause if you can alter the session to ignore these errors.

You can set event 10231 for this (WARNING, not for use in application, only for partially recovery of data! Data in corrupt block will be lost !)
Option two will be to use DBMS_REPAIR.SKIP_CORRUPT_BLOCKS to tag the table to ignore the bad blocks.
Be sure to get Metalink Note 33405.1, read and understand it very carefully before you proceed.

What ever you do, do not drop the defective table at all. Do a rename instead to ensure the data is accessible later, even if you managed to read the original partly to a diffrent table name. It might be possible to dump the defective block later and conclude from a hex view which data was in it.

But again, you first approach should be to verify, that the faulty segment is not an index.

Good luck
Volker

[renarios]

Hi Kyris,

Please, put your production database in archivelog mode, because if not, recovery will be very difficult.

Cheerio,

Renarios

[Kyris]

Thanks Renario...this is something for the client to decide. I certainly agree with you on this point.

I am now trying to see if the index is at fault, as per Volker's posting. It's been running for nearly 3 hours now and still not finished..I am going home so hopefully tomorrow I will get a good surprise !!

[Kyris]

Volker, I did run the querry u have suggested and I am getting the records from the table...this means it's my index that is corrupted.

I tried to do drop index index_name but I am getting ORA-00054:resource busy and acquired with nowait specified.

I tried using drop index index_name FORCED but got ORA-00093:SQL command not properly ended;

How do I get round this problem?

thanks in advance
kyris

[Eric Antunes]

Hi Kyris,

You must kill the session that is locking the index:

select
owner,object_name,
object_type ,
os_user_name osuser,
oracle_username username,
program ,
nvl(lockwait,'ACTIVE') lockwait,
decode(locked_mode,
2, 'ROW SHARE',
3, 'ROW EXCLUSIVE',
4, 'SHARE',
5, 'SHARE ROW EXCLUSIVE',
6, 'EXCLUSIVE', 'UNKNOWN') lockmode,
session_id sid,
serial#
from
sys.v_$locked_object a,
sys.all_objects b,
sys.v_$session c
where
a.object_id = b.object_id and
c.sid = a.session_id and
OBJECT_NAME = ; -- Here you put you index name in CAPS

After this:

alter system kill session ',' -- Here you put the SID and SERIAL# you'll get on the previous query...

Best Regards,

Eric Antunes

[Kyris]

Hello Eric...thanks for the quick response.

I did what u have suggested but of course the database is open so as soon as I kill a process, another takes over.
Do I need to take the database to a state other than OPEN and if so what's the best state and how to achive this.
thanks
kyris

[Eric Antunes]

Hi,

Just shutdown the database and restart it in restrict mode:

SQL/SVRMGR> connect / as sysdba;
...> shutdown immediate;
...> startup restrict;
...> drop index...

Regards,

Eric Antunes

[Eric Antunes]

After the work is done you return to normal mode using:

SQL/SVRMGR> alter system disable restricted session;

[Volker Borowski]

Hi Kyris,

just to make it sure: You did a select with the FULL hint and got a complete list of records of your table without any error message ?

Ok, now you need to check, what indexes are on that table. It might be either secondary indexes, secondary indexes with a UNIQUE constraint or a system Index from a Primary Key Constraint.

Most important would be to ensure, that UNIQUE or Primary Key Constraints are not violated when you temporaryly drop these Constraints.
If the table is highly frequented, you need to consider to take down the application while you drop and recreate the indexes.
Do you know what columns the index is build on ? Or do you just know the name of the index.

I am not sure if a
ALTER INDEX indexname REBUILD ONLINE;
works when the index has bad blocks.
May be it is worth a try.

Did you run the second query on dba_extents to get the name of the segment which holds the bad block ? It might take a while, but this would be a valuable piece of information (i.e. if your Primary Key is affected).

Glad that your data is still available.
Volker

[Kyris]

Thank you all for your kind assistance.
The outcome was that the client was not too bother about the data and being a very slow NT box, it was decided to drop the table and index and recreate both from new. BUT, never-the-less, this has been a very good experience for me.
This makes me proud to belong to a forum with such wonderful people.
Thanks again..