Oracle Database Discussions

Hi.

I´m working with Oracle 9.2.0.7.0 on Windows 2003 SP2.

I´ve a corruption issue on a PK index that I not am able to repair...

I´ve detected it when I tried make a RMAN backup.This is the message:

RMAN-03009: failure of backup command on ORA_DISK_1 channel at 03/02/2012 10:12:45
ORA-19566: exceeded limit of 0 corrupt blocks for file...


Then, I try to rebuild the index, but I get the following errors;

ORA-01115: IO error reading block from file 8 (block # 447127)
ORA-01110: data file 8: 'F:\ORACLE\ORADATA\INDEXES02.DBF'
ORA-27091: unable to queue I/O
ORA-27070: async read/write failed
OSD-04006: ReadFile() failure, unable to read from file
O/S error : (OS 121) The semophore timeout period has expired.

The next step was to connect to rman catalog and try to recover that block:

RMAN> blockrecover datafile 8 block 447127;

Starting blockrecover at 02-FEB-12 using channel ORA_DISK_1

Finished blockrecover at 02-FEB-12

I try to rebuild index again but I´ve the same errors but on block 447128 this time...

I do a new blockrecover and try to do a new rebuild index but it fails on block 447129...

I´ve repeated this procces many times until arrive to block 447134. On this block, I have done a blockrecover correctly but when I do a rebuild index it fails on the same block again.

Finally, after many retries, when I do a new rebuild but in this case, i get a

ORA-08103: object no longer exists

...is absurd. The object yet exist!!


Then I did BACKUP VALIDATE DATABASE from Rman catalog, and then query the view v$database_block_corruption.

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTION_TYPE
--- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
8 447120 5 0 ALL ZERO
8 447125 1 0 FRACTURED

2 rows selected.

I don´t know what mean this... Anyway when I try to make BLOCKRECOVER CORRUPTION LIST; rman returns:

RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 8 found to restore

and it´s true...I only have backup of the archivelogs...
........

Then, I decided to try something different using DBMS_REPAIR.

I created the REPAIR_TABLE with this script:

BEGIN
SYS.DBMS_REPAIR.ADMIN_TABLES (
TABLE_NAME => 'REPAIR_TABLE',
TABLE_TYPE => SYS.dbms_repair.repair_table,
ACTION => SYS.dbms_repair.create_action,
TABLESPACE => 'USERS');
END;
/

Then try save the information of bad blocks in the table...

SET SERVEROUTPUT ON
DECLARE num_corrupt INT;
BEGIN
num_corrupt := 0;
SYS.DBMS_REPAIR.CHECK_OBJECT (
SCHEMA_NAME => 'MYSCHM',
OBJECT_NAME => 'PK_CORX2',
REPAIR_TABLE_NAME => 'REPAIR_TABLE',
CORRUPT_COUNT => num_corrupt);
SYS.DBMS_OUTPUT.PUT_LINE('number corrupt: ' || TO_CHAR (num_corrupt));
END;
/

...but I get

ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_REPAIR", line 284


now, I include the OBJECT_TYPE parameter

SET SERVEROUTPUT ON
DECLARE num_corrupt INT;
BEGIN
num_corrupt := 0;
SYS.DBMS_REPAIR.CHECK_OBJECT (
SCHEMA_NAME => 'MYSCHM',
OBJECT_NAME => 'PK_CORX2',
OBJECT_TYPE => 'INDEX_TYPE',
REPAIR_TABLE_NAME => 'REPAIR_TABLE',
CORRUPT_COUNT => num_corrupt);
SYS.DBMS_OUTPUT.PUT_LINE('number corrupt: ' || TO_CHAR (num_corrupt));
END;
/

and then, message is

ORA-06502: PL/SQL: numeric or value error string. Causa: An arithmetic, numeric, string, conversion, or constraint error occurred.
ORA-06502 in line 4

I don´t know how to fix that...


The only thing that occurs to me is shutdown the instance, export the table, drop the table, import again and then recreate index... but I prefer not to stop the database because it is production, And I'm not sure this will solve the problem...

Have anybody any suggestion??? will be attentively listened...

note: I also did a scandisk of the unit if there were errors on the disk ...

Thanks everybody.

Regards