Blog dedicated to Oracle Applications (E-Business Suite) Technology; covers Apps Architecture, Administration and third party bolt-ons to Apps

Wednesday, July 9, 2008

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.

4 comments:

Niall said...

How did you decide which row to delete? What if anything was the attitude of support.

I guess my core point is that this is an unsupported data fix (though one I likely would have done myself) and so you need to be pretty sure what you are doing and why.

Vikram Das said...

Hi Niall,

I forgot to mention that this fix is mentioned in Metalink Note 430673.1 and you have to delete the row which is older. I am now including this information in the post.

- Vikram

Anonymous said...

As per your recommendation that ICXDLTMP.sql program should be executed at least once a week to clean up ICX_TRANSACTIONS and ICX_SESSION tables, Is it safe run to ICXDLTMP.sql even if the the application tier process is running?

Thanks

Vikram Das said...

Hi,

The ICXDLTMP.sql is used to delete records over 4 hours old. You may refine this by altering the
(4 * 60 * 60) to the number of seconds you want to delete from.

It deletes from icx_session table those sessions which have exited graciously
or sessions have been lingering for more than 24 hours over the ICX_LIMIT_TIME.

The important thing to keep in mind is not to delete information about sessions which are alive. So if you don't have any sessions which are more than 4 hours long, you can safely run this when your app tier is up. Otherwise it is safer to run when app tier is down and no user sessions exist.

- Vikram