How to take care of index corruptions in primary key indexes.
Primary key constraints in Oracle are enforced by using an index. The index does not need to be a unique index, but it has to be possible for the database to look into it and find out if a given key is already present or not.
We're living in 2008 and still, the recreation of a secondary data structure can only be done in a downtime!? Not too nice.
It requires the DBA to be handy with the Oracle Dictionary.
He/She will have to find out the index-definition commands (DDL) before the index is dropped. Of course, it is no problem to do so, but you have to know how – and many do not. (You can use DBMS_METADATA.GET_DDL, the EXP/IMP tools or the Oracle dictionary directly, just to mention three of the options to get the DDL for the index).
It involves the risk, that – although the SAP is shutdown – duplicates keys can be entered into the table.
Ok, this is rather unlikely, but it is a potential risk – it is not bulletproof this procedure.
To get around this, I made a little research and found two possible ways out.
Use ALTER INDEX … REBUILD ONLINE.
Why didn't we use that earlier?
Because the Oracle documentation says that for the REBUILD of an index, the necessary data is taken from the existing Index segment and not from the table (they changed that for Oracle 11g! See http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/data_acc.htm#i7530 "14.1.7 Re-creating Indexes" ).
So if the existing index is corrupt, we cannot use the REBUILD clause.
With this knowledge, it is now possible to perform the Index rebuild while SAP is up and running. (Please do check the mentioned note before nevertheless. Even a REBUILD ONLINE has it's "dangers").
So, that was solution number one. It is easy, it is safe – you should use it if necessary. But actually, it was the second solution I found.
The first one was still based on the assumption that for an index rebuild the old index has to be read.
==> as we see the unique index takes care of our primary key constraint.
Be aware that SAP does not create any primary key constraints as such on the database.
Instead, the key columns all get the NOT NULL constraint and a unique index is defined on the key columns. While this makes technically no difference, it is a semantic difference compared to a primary key constraint!
==> as we see it does not work anymore.
Unfortunately, with a real index corruption, the index is not automatically flagged UNUSABLE. Therefore, inserts/updates/deletes can happen as long as they do not require access to the corrupted blocks.
We create a new index that provides the same uniqueness constraint (that all combinations of "mandt" and "name" have to be unique. To do that we've to choose a different order for the columns in the index.
NOTE: this only works because the NOT NULL constraints are in place. Otherwise changing the column order in the index would not lead to an equivalent uniqueness check!
==> a little cleanup, et voilà!
The same pattern can be applied if really unique or primary constraints have been defined on the table. In these cases, it will be necessary to create additional constraint definition on the key columns as well.
Of course, this solution should not be used, if a REBUILD ONLINE is possible, but it is a working solution to the problem and it may happen that REBUILD ONLINE won't work – then you can think about this approach.
Previous PostPoint-in-time-recovery considered harmfulNext PostNot my fault – whose then?