ORA-01578 : Best solution?

[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