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.
This works fine, as long as the index structure is not corrupted. If it does, the standard recommendation to fix this was some procedure like this:
- Stop your SAP-system to prevent the entry of duplicate keys (these are nasty to remove later on!)
- Drop the index
- Rebuild the index
- Start the SAP again
There are several things that are not so nice about this:
- It requires downtime.
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.
Solution #1
First, the nice and easy one, that now has found its way into the #365481 - Block corruptions note:
Use ALTER INDEX ... REBUILD ONLINE.
It is as simple as that.
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.
What the Oracle documentation does not mention is that the behaviour for a REBUILD ONLINE is quite different. When used with the ONLINE option the REBUILD accesses the table directly instead of the old index (an elaborate discussion on this can be found in Oracles Metalink: Note:278600.1)
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").
Solution #2
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.
To explain my solution, I will make up a little example:
SQL> create table pktest (mandt varchar2(3) not null, name varchar2(40) not null, id integer);
Table created.
SQL> create unique index i_pktest on pktest (mandt, name);
Index created.
SQL> insert into pktest values ('100', 'Test1', 1);
1 row created.
SQL> insert into pktest values ('100', 'Test2', 2);
1 row created.
SQL> insert into pktest values ('100', 'Test3', 3);
1 row created.
SQL> insert into pktest values ('100', 'Test1', 3);
insert into pktest values ('100', 'Test1', 3)
*
ERROR at line 1:
ORA-00001: unique constraint (SAPR3.I_PKTEST) violated
==> 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!
SQL> commit;
Commit complete.
==> Now let's make the index broken...
SQL> alter index i_pktest unusable;
Index altered.
SQL> insert into pktest values ('100', 'Test1', 3);
insert into pktest values ('100', 'Test1', 3)
*
ERROR at line 1:
ORA-01502: index 'SAPR3.I_PKTEST' or partition of such index is
in unusable state
==> 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.
==> Here comes the solution...:
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 a equivalent uniqueness check!
SQL> create unique index i_pktest2 on pktest (name, mandt);
Index created.
==> get rid of the broken index:
SQL> drop index i_pktest;
Index dropped.
SQL> insert into pktest values ('100', 'Test1', 3);
insert into pktest values ('100', 'Test1', 3)
*
ERROR at line 1:
ORA-00001: unique constraint (SAPR3.I_PKTEST2) violated
==> the new index/constraint works well
==> now we've all the time in the world to fix the broken index:
SQL> create unique index i_pktest on pktest (mandt, name);
Index created.
==> a little cleanup, et voilà!
SQL> drop index i_pktest2;
Index dropped.
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 - than you can think about this approach.
Best regards,
Lars
