topic Re: ORA-01578 : Best solution? in Operating System - HP-UX

ORA-01578 : Best solution?

Kyris — Thu, 12 May 2005 09:56:13 GMT

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

Re: ORA-01578 : Best solution?

Steven E. Protter — Thu, 12 May 2005 10:36:51 GMT

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

Re: ORA-01578 : Best solution?

Brian Crabtree — Thu, 12 May 2005 15:13:21 GMT

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

Re: ORA-01578 : Best solution?

Yogeeraj_1 — Thu, 12 May 2005 23:19:45 GMT

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

Re: ORA-01578 : Best solution?

Eric Antunes — Fri, 13 May 2005 05:53:47 GMT

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

Re: ORA-01578 : Best solution?

Eric Antunes — Fri, 13 May 2005 05:55:11 GMT

Hi,

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

Eric

Re: ORA-01578 : Best solution?

Kyris — Mon, 16 May 2005 01:02:08 GMT

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 ?

Re: ORA-01578 : Best solution?

Eric Antunes — Mon, 16 May 2005 03:20:48 GMT

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

Re: ORA-01578 : Best solution?

Eric Antunes — Mon, 16 May 2005 03:22:08 GMT

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

Re: ORA-01578 : Best solution?

Kyris — Mon, 16 May 2005 06:33:27 GMT

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

Re: ORA-01578 : Best solution?

Eric Antunes — Mon, 16 May 2005 06:57:59 GMT

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

Re: ORA-01578 : Best solution?

Kyris — Mon, 16 May 2005 07:06:10 GMT

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?

Re: ORA-01578 : Best solution?

Eric Antunes — Mon, 16 May 2005 07:15:24 GMT

No, unfortunately, you cannot.

Eric

Re: ORA-01578 : Best solution?

Volker Borowski — Mon, 16 May 2005 15:00:13 GMT

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

Re: ORA-01578 : Best solution?

renarios — Wed, 18 May 2005 06:15:43 GMT

Hi Kyris,

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

Cheerio,

Renarios

Re: ORA-01578 : Best solution?

Kyris — Wed, 18 May 2005 08:47:22 GMT

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

Re: ORA-01578 : Best solution?

Kyris — Thu, 19 May 2005 05:38:16 GMT

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

Re: ORA-01578 : Best solution?

Eric Antunes — Thu, 19 May 2005 05:53:51 GMT

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

Re: ORA-01578 : Best solution?

Kyris — Thu, 19 May 2005 07:28:01 GMT

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

Re: ORA-01578 : Best solution?

Eric Antunes — Thu, 19 May 2005 08:45:48 GMT

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