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>;

  1. 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

  1. 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.

 

You may also like...

Leave a Reply

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