cancel
Showing results for 
Search instead for 
Did you mean: 

ORA-01578 : Best solution?

SOLVED
Go to solution
Kyris
Frequent Advisor

ORA-01578 : Best solution?

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
22 REPLIES
Steven E. Protter
Exalted Contributor

Re: ORA-01578 : Best solution?

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
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
Brian Crabtree
Honored Contributor

Re: ORA-01578 : Best solution?

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
Honored Contributor

Re: ORA-01578 : Best solution?

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Eric Antunes
Honored Contributor

Re: ORA-01578 : Best solution?

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
Each and every day is a good day to learn.
Eric Antunes
Honored Contributor

Re: ORA-01578 : Best solution?

Hi,

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

Eric
Each and every day is a good day to learn.
Kyris
Frequent Advisor

Re: ORA-01578 : Best solution?

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
Honored Contributor

Re: ORA-01578 : Best solution?

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
Each and every day is a good day to learn.
Eric Antunes
Honored Contributor

Re: ORA-01578 : Best solution?

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
Each and every day is a good day to learn.
Kyris
Frequent Advisor

Re: ORA-01578 : Best solution?

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
Honored Contributor

Re: ORA-01578 : Best solution?

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
Each and every day is a good day to learn.
Kyris
Frequent Advisor

Re: ORA-01578 : Best solution?

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
Honored Contributor
Solution

Re: ORA-01578 : Best solution?

No, unfortunately, you cannot.

Eric
Each and every day is a good day to learn.
Volker Borowski
Honored Contributor

Re: ORA-01578 : Best solution?

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
Trusted Contributor

Re: ORA-01578 : Best solution?

Hi Kyris,

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

Cheerio,

Renarios
Nothing is more successfull as failure
Kyris
Frequent Advisor

Re: ORA-01578 : Best solution?

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
Frequent Advisor

Re: ORA-01578 : Best solution?

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
Honored Contributor

Re: ORA-01578 : Best solution?

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
Each and every day is a good day to learn.
Kyris
Frequent Advisor

Re: ORA-01578 : Best solution?

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
Honored Contributor

Re: ORA-01578 : Best solution?

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
Each and every day is a good day to learn.
Eric Antunes
Honored Contributor

Re: ORA-01578 : Best solution?

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

SQL/SVRMGR> alter system disable restricted session;
Each and every day is a good day to learn.
Volker Borowski
Honored Contributor

Re: ORA-01578 : Best solution?

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
Frequent Advisor

Re: ORA-01578 : Best solution?

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