How to check Oracle Corrupt index and CREATE INDEX ONLINE
How to check Oracle Corrupt index and CREATE INDEX ONLINE
With Oracle 12.1.0.2, you might encounter one or more of the following symptoms:
ORA-00600 [kdsgrp1] when querying table data
ORA-00600 [25027] when creating optimizer statistics for an index
ORA-08102 when trying to delete a row from a table
ORA-01499 when running “analyze <table_name> validate structure cascade online”
ORA-12012 in the Oracle alert log
Other Terms: create index online, ORA-00600 [kdsgrp1] , ORA-600 [kdsgrp1] , ORA-00600 [25027] , ORA-600 [25027] , ORA-08102, ORA-8102, ORA-01499, ORA-1499 , ORA-12012 , 10038517 , 21532755 , _fix_control, wrong results
Reason and Prerequisites
When creating an index with the online option “CREATE INDEX .. ONLINE” while a high DML workload is ongoing against the underlying table, you might encounter the above listed symptoms as the index might contain less rows than it should.
This is due to Oracle bug 21532755.
Solution
There is no SBP available yet which includes the fix for bug 21532755.
Workaround:
To avoid running into bug 21532755 when using CREATE INDEX ONLINE, please turn off the fix for bug 10038517 by setting
“_fix_control” = ‘10038517:OFF’
Make sure you add value ‘10038517:OFF’ to the list of values for _fix_control which have already been set in your database and restart the database afterwards.
How to identify which indexes are impacted:
Likely candidates are those for which you find an entry similar to
online index (re)build cleanup: objn=
in the alert log.
The objn is the object id for the corrupt index.
You can find the index name by using
SQL> select object_name from dba_objects where object_id=<objn>;
- If index and table names are already known:
This method checks for a difference between the amount of rows in the table and a specific index. It has to be executed for each index per table.
This approach is the fastest option in case the index name is already known.
In case the query returns a value > 0, the index is corrupt and needs to be dropped and recreated.
Example for table BSIS:
select count(1) “Missing in Index” from
(
select /*+ full(XX) */ ORA_HASH(MANDT || rowid) hshval, rowid row_id
from “SAPR3″.”BSIS” XX
MINUS
select /*+ index_ffs(BSIS “BSIS~ZZZ”) */ ORA_HASH(MANDT || rowid), rowid row_id
from “SAPR3″.”BSIS” BSIS where MANDT > ‘ ‘
);
Missing in Index
—————-
52108
- ANALYZE TABLE VALIDATE STRUCTURE CASCADE ONLINE
Execute in SQLPLUS:
SQL> analyze table SAPR3.BSIS validate structure cascade online;
If analyze returns
ORA-01499: table/index cross reference failure – see trace file
Identify trace file name by using:
SQL> select tracefile from v$process where pid=userenv(‘pid’);
immediately after ANALYZE has finished in the same SQLPLUS session.
Next, to identify the index, please open tracefile and search for string
“index objn=”
or use grep:
oracle> grep “index objn=” O12_ora_23578.trc
table block tsn=6 rdba=0x01802756 index objn=23505
The returned objn=23050 is the object id for the corrupted index.
You can find the index name by using
SQL> select object_name from dba_objects where object_id=23505;
OBJECT_NAME
————————
BSIS~ZZZ
III. Other methods
– RMAN backup check logical – does not detect this corruption
– DBVerify – does not detect this corruption
What to do if you encountered bug 21532755:
To fix indexes which already encountered the problem, drop them and create them again after implementing the workaround or without using the online option.
