How do you correct a corrupted index in Oracle?

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:

SEGMENT_NAME             SEGMENT_TYPE   TABLESPACE_NAME     OWNER
-----------------------    ------------     ----------------    ---------------
PK_ZXCATSET_CATID      INDEX            MYSRVR_IDX_TB    MYSRVR

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!

EDIT If I do MYSRVR.PK_ZXCATSET_CATID the alter completes... but I still don't think my problem is fixed!

Answer 1

Seems like you have media corruption. I would consult Automatic Diagnostic Repository (ADR) contents if there are open failures in your database. You can do that via Enterprise Manager or using RMAN command-line utility:

[oracle@oca ~]$ rman target=/

RMAN> list faliure;

If there are failures with status OPEN listed, you can ask Data Recovery Advisor (DRA) to analyze them and recommend you the solution to repair the failures:

RMAN> advise failure;

Since you have block corruption, DRA will probably suggest you to recover the corrupted blocks and will create the appropriate script which you can run manually or in the same flow with RMAN:

RMAN> repair failure;

After failures are repaired they're automatically closed by DRA. You can check if all failures are closed with one more list failure command in RMAN, and if not you can get another advice from DRA.

EDIT:

Since the recovery you performed didn't last long (elapsed time: 00:00:00), I infer that the datafile wasn't written to by RMAN, and maybe even checked. Thus I would recommend you to proactively validate the database; to do that, just issue validate database in RMAN and analyze the output. RMAN will check all datafiles (including control file and spfile) and show you if there are any corrupted blocks.

In your comment, you said that RMAN returns the message "Block Media Recovery requires Enterprise Edition" in response to one of your commands, and based on the response from RMAN to validate database command I suspect this feature is also not available in your software edition. If your database is running in ARCHIVELOG mode (check with select log_mode from v$database) you could just completely restore and recover your database without loss of committed transaction, but before doing this I would make sure that your media (hard disk) is not damaged.

Answer 2

Ok, the "RMAN> list failure;" thing didn't work, but it did lead me in the right direction. To get things going again I did this:

$  rman target sys/myuser@mytargetsrvr

RMAN> recover datafile 8

// Which gave this

ORA-00283: recovery session canceled due to errors
ORA-01124: cannot recover data file 8 - file is in use or recovery

// instead I did this first

RMAN> sql 'alter tablespace MYSRVR_IDX_TB offline';

sql statement: alter tablespace MYSRVR_IDX_TB offline

RMAN> recover datafile 8;

Starting recover at 25-APR-13
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 25-APR-13

RMAN> sql 'alter tablespace  MYSRVR_IDX_TB online';

sql statement: alter tablespace  MYSRVR_IDX_TB online

Big thanks to Yasir Arsanukaev!

Answer 3

alter index "MYSRVR"."PK_ZXCATSET_CATID" rebuild tablespace MYSRVR_IDX_TB;

or alter index MYSRVR.PK_ZXCATSET_CATID rebuild tablespace MYSRVR_IDX_TB;