Operating System - HP-UX
1748230 Members
4169 Online
108759 Solutions
New Discussion юеВ

datafile of index tablespace corrupt

 
SOLVED
Go to solution

datafile of index tablespace corrupt

The index tablespace have 80G data, and make up by 40 datafiles(disk). only one disk fail. By now, the database running smoothly.
I have checked the corrupted datafile have 20 very large index table on it.
Because this is a production database, and do not allow long time shudown.
I think the best way is move the data on corrupted datafile to another good datafile online. But I don't know how to do it or does oracle support it.
because OS command will copy corrupt data to new datafile, I can't use it.
Can some one tell me a good way to resolve this problem?
I only know 2 way:
using datacopy function of raman. it will need offline the full tablespace.
export tablespace or related index table, it will lock the data for a long time.
rebuild related index on other tablespace. It will cost a long time and maybe affect user's current operation.

Other question, Because oracle looks Ok, If I don't do any action, how much dangerous I will face?
8 REPLIES 8
twang
Honored Contributor

Re: datafile of index tablespace corrupt

At this state, I would suggest you to do a checking on indexes in whole system. After finding out all problem indexes, then rebuilding them.
If you suspect an index is corrupt then it can be checked against the table using the following command:
ANALYZE TABLE VALIDATE STRUCTURE CASCADE;
This will cross check the table with all of its indexes. This can take a while and will block DML while it runs. It will report an ORA-1499 error if there is a mismatch between the table and one of its indexes.
But this will not detect IOT data loss.

An alternative but less rigorous check is to use statements of the form:
rem Make the queries consistent with respect to each other
SET TRANSACTION READ ONLY;
rem Find the total number of entries in the table
SELECT /*+ FULL(tabname) */ count(*) FROM tabname;
rem Find the total number of entries in the index
SELECT /*+ INDEX_FFS(tabname index_name) */ count(*) FROM tabname;
This technique is much faster and does not block any DML. It can however:
a. Only show a mismatch in row counts between index and table
b. Only be used if the index is on a NOT NULL column otherwise the INDEX_FFS hint will be ignored. If there is a mismatch then the ANALYZE TABLE command can be used to check the problem in more detail.
Massimo Bianchi
Honored Contributor
Solution

Re: datafile of index tablespace corrupt

Hi,
my suggestion is a little hard, but maybe with a lesser impact on production.

Create a new tablespace for the indexes,
and, 1 by 1, drop the index from the corrupted tablespace and recreate them in the new tablespace.

No downtime requested, there are some obvious drawback: lesser performance due to the index rebuild, need for space in temp tablespace.

pro: new index, no downtime, index are reorganized (and this is really good).

It's week-end, so i imagine less load on the system.


You said that his operation will request long time... use parallel index creation, if you are on oracle 817 it can save you much time.

I don't think that using rman datacopy function can help, if data is corrupted, even the copy will be corrupted.

Massimo

BTW, oracle is running fine because if it found an index unusuable, it will bypass it and read the data from the table directly. Just a smaller performance.

Re: datafile of index tablespace corrupt

Thank Twang and Massimo. your reply is very usefull.

I have a question again.
The problem of datafile like some data block(disk section) was corrupted. If any process want to read data from these data block, it will hang.
Because the oracle have not read these data block, oracle is OK now.
My question is:
if oracle read dat from these bad data block later, what action will oracle take?
disable the affected index table or offline the whole index tablespace or crash oracle abnormally?
If it crash oracle or offline the whole tablespace, can I online it again?
twang
Honored Contributor

Re: datafile of index tablespace corrupt

Most probably, your alert.log file will contain an entry like : Tue May 29 13:10:16 2003 Errors in file /users/ora817/rdbms/log/ora_4696.trc: ORA-00600: internal error code, arguments: [12700], [2989], .... So the trace file containing the ora-600 dump is named ora_4696.trc
And the trace file will contain something helpful in identifying the possibly corrupted data object or corrupted indexes.
In your case, if you find index corruption, you can re-create the corrupted index.
kenny chia
Regular Advisor

Re: datafile of index tablespace corrupt

Hi
Is this a critical system?
It will be better to use a RAID5 system to prevent this sort of problem.
You are quite lucky that the disk that failed contain only the indexes and not the datafiles...

All Your Bases Are Belong To Us!
Indira Aramandla
Honored Contributor

Re: datafile of index tablespace corrupt

Hi,

yes check the alert log and udump directory for error logs.


Before you rebuild / re-create indexes check to see if there are indexes in invalid status.

then try to validate the structure by analyze table validate structure cascade as Twang said. This will verify the table all the underlying indexes it has and should display the result as "Table analyzed" or an error message if the index is corrupted.

If the index is corrupted then do as Massimo suggested. Create a new tablespace in a new datafile. Then give quota to this user on the new tablespace. Now as this user drop the index thsat was reported a corrupted from the analyze statement, and re-create the index in the new tablespace. This will not have any down time but poor performance until the index has been recreated.

if you have very few indexes to be re-created then re-create them.

Never give up, Keep Trying
zhuchao
Advisor

Re: datafile of index tablespace corrupt

hi libing:
The correct way is to restore the datafile to other good disk and do your recovery.
Or, you will have to rebuild all your indexes in that tablespace to new tablespace and drop the old tablespace.
Single datafile cannot be dropped from the tablespace.
www.happyit.net
Massimo Bianchi
Honored Contributor

Re: datafile of index tablespace corrupt

>My question is:
>if oracle read dat from these bad data block >later, what action will oracle take?
>disable the affected index table or offline the >whole index tablespace or crash oracle >abnormally?
>If it crash oracle or offline the whole >tablespace, can I online it again?

Oracle will offline the datafile, and probably also the tablespace. Since there are indexes, the db should not crash, but i admit that i cannot put my hands over ( and not on :) ) the fire.

For sure you will have messages stating that blocks cannot be read, ORA-600 and so on, and the index will be put offline.

You can check it now, querying the dba_indexes and see the column "status". If it is <> valid, the index is flagged as unusable.

Massimo