FAILED: file icxwtab.odf during adpatch

Recently while patching, the worker running icxwtab.odf failed:

ATTENTION: All workers either have failed or are waiting:

FAILED: file icxwtab.odf on worker 1.

ATTENTION: Please fix the above failed worker(s) so the manager can continue.

adworker log showed:

Start time for statement below is: Wed Jul 09 2008 17:12:20

CREATE UNIQUE INDEX ICX.ICX_TRANSACTIONS_U1 ON ICX.ICX_TRANSACTIONS
(TRANSACTION_ID) LOGGING STORAGE (INITIAL 4K NEXT 104K MINEXTENTS 1
MAXEXTENTS UNLIMITED PCTINCREASE 0 FREELIST GROUPS 4 FREELISTS 4 ) PCTFREE
10 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS TABLESPACE ICXX

Statement executed.

AD Worker error:
The index cannot be created as the table has duplicate keys.


Use the following SQL statement to identify the duplicate keys:

SELECT TRANSACTION_ID, count(*)
FROM ICX.ICX_TRANSACTIONS
GROUP BY TRANSACTION_ID
HAVING count(*)>1

AD Worker error:
Unable to compare or correct tables or indexes or keys because of the error above

As specified in Metalink Note 430673.1:

Symptoms

adpatch fails on script icxwtab.odf with the following errors:

ERROR
The table is missing the index ICX_TRANSACTIONS_U1
or index ICX_TRANSACTIONS_U1 exists on another table.
Create it with the statement:

Start time for statement below is: Mon May 07 2007 14:23:44

CREATE UNIQUE INDEX ICX.ICX_TRANSACTIONS_U1 ON ICX.ICX_TRANSACTIONS
(TRANSACTION_ID) LOGGING STORAGE (INITIAL 4K NEXT 104K MINEXTENTS 1
MAXEXTENTS UNLIMITED PCTINCREASE 0 FREELIST GROUPS 4 FREELISTS 4 ) PCTFREE
10 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS TABLESPACE ICXX

Statement executed.
AD Worker error:
The index cannot be created as the table has duplicate keys.

Use the following SQL statement to identify the duplicate keys:

SELECT TRANSACTION_ID, count(*)
FROM ICX.ICX_TRANSACTIONS
GROUP BY TRANSACTION_ID
HAVING count(*)>1

AD Worker error:
Unable to compare or correct tables or indexes or keys
because of the error above

SPECIFIC DATA
Ran the suggested query, and here is the output:

TRANSACTION_ID COUNT(*)
-------------------------- -------------
148341124 2
431640607 2
555224577 2
1202811809 2

Cause

These duplicate transactions are there because the concurrent program that deletes temporary
session data (program that removes old entries in ICX_SESSIONS and ICX_TRANSACTIONS) is not
executed on a regular basis. As a result, these tables grow in space and there is the possibility
that the sequences cycle and restart, creating duplicate primary keys.

The following justifies how the issue is related to this specific customer:

SELECT TRANSACTION_ID, count(*)
FROM ICX.ICX_TRANSACTIONS
GROUP BY TRANSACTION_ID
HAVING count(*)>1

TRANSACTION_ID COUNT(*)
-------------------------- --------------
148341124 2
431640607 2
555224577 2
1202811809 2

This is explained in the following unpublished bug: Bug 5001287 PERFORMANCE PROBLEM WHEN APPROVING POS WITH ICX_TRANSACTIONS

Solution

To implement the solution, please execute the following steps:

1. Run the purge program:
a. The name of the program is "Purge Inactive Sessions" located under the "Apps for the Web Manager" responsibility.
b. The internal name is ICXDLTMP.
c. Also you can find this SQL script under $ICX_TOP/sql (named ICXDLTMP.sql).

2. Rerun the failed worker (icxwtab.odf).

3. Migrate the solution as appropriate to other environments.

4. This program should be executed at least once a week to clean up ICX_TRANSACTIONS and ICX_SESSION tables, otherwise they will continue to grow.

Running this sql returned two rows:

SQL> SELECT TRANSACTION_ID, count(*)
FROM ICX.ICX_TRANSACTIONS
GROUP BY TRANSACTION_ID
HAVING count(*)>1
2 3 4 5
SQL> /

TRANSACTION_ID COUNT(*)
-------------- ----------
746007924 2

SQL> desc icx_transactions
Name Null? Type
----------------------------------------- -------- ----------------------------
TRANSACTION_ID NOT NULL NUMBER
SESSION_ID NOT NULL NUMBER
RESPONSIBILITY_APPLICATION_ID NUMBER
RESPONSIBILITY_ID NUMBER
SECURITY_GROUP_ID NUMBER
MENU_ID NUMBER


1 select rowid,transaction_id,session_id
2 from icx_transactions
3* where transaction_id='746007924'
SQL> /

ROWID TRANSACTION_ID SESSION_ID
------------------ -------------- ----------
AAAaZsAGeAAAIsjAAU 746007924 499638533
AAAaZsAAbAAAHb4AAV 746007924 888513258

SQL> delete icx_transactions
2 where rowid='AAAaZsAGeAAAIsjAAU'
3 /

1 row deleted.

SQL> commit;

Commit complete.

Failed worker was restarted through adctrl and it went fine.