How do you correct a corrupted index in Oracle? [duplicate]

Posted on

Question :

I have this ORA error in our logs:

Caused by: java.sql.BatchUpdateException: ORA-01578: ORACLE data block corrupted (file # 8, block # 22921)
ORA-01110: data file 8: ‘/data/app/oracle/oradata/MYSRVR/datafile/o1_mf_mysrvr_88m82mdj.dbf’

I tried running this in sqlplus:

select segment_name,segment_type,tablespace_name, owner from sys.dba_extents where file_id=8 and 22921 between block_id and block_id + blocks -1;

output is:

-----------------------    ------------     ----------------    ---------------

Now I am stuck on which index is that actual segment name. (I think :/ )
I tried doing this to rebuild that index:

SQL> alter index PK_ZXCATSET_CATID rebuild tablespace MYSRVR_IDX_TB;

ERROR at line 1:
ORA-01418: specified index does not exist

Even though if I do “select index_name from dba_indexes”, that index name shows up.
What am I doing wrong? This is a 10.2 Oracle server.

Thanks in advance!

Answer :

There is a command line utility dbv which can scan a datafile a will report and mark corrupted blocks. If you want for “fix” it you can either use RMAN block recover option – if you run Ent. Edition and if you have RMAN backup.

Otherwise search for document named “How to format corrupted block” on Metalink. This describes a situation when a table block is corrupted but it is applicable for indexes too.
First of all you mast mark the block as corrupted.

PS: the is also an SQL comment “ALTER TABLE VALIDATE STRUCTURE CASCADE”, this will also generate a report, but will not fix anything.

Leave a Reply

Your email address will not be published. Required fields are marked *