ORA-06502: PL/SQL: numeric or value error: associative array shape is not consistent with session parameters

During peak usage today, in the newly upgraded 11.5.10.2/10.2.0.3 instance, users were unable to login to E-Business Suite and reported errors like Internal Server Error, or

You have encountered an unexpected error. Please contact the System Administrator for assistance

Following error was logged in jserv.log:

[Oct 31, 2007 10:19:12 AM EDT]:1193840352057:Thread[Thread-1045,10,main]:-1:-1:justanexample.com:192.168.4.1
:8000:16680:UNEXPECTED:[fnd.framework.OAException]:Application: FND, Message Name: FND_GENERIC_MESSAGE. Tokens: MESSAGE = java
.sql.SQLException: ORA-20001: Oracle error -6502: ORA-06502: PL/SQL: numeric or
value error: associative array shape is not consistent with session parameters
has been detected in fnd_global.put(PERMISSION_CODE,FND_PERMIT_0001).
ORA-06512: at "APPS.APP_EXCEPTION", line 72
ORA-06512: at "APPS.FND_GLOBAL", line 240
ORA-06512: at "APPS.FND_GLOBAL", line 428
ORA-06512: at "APPS.FND_GLOBAL", line 447
ORA-06512: at "APPS.FND_GLOBAL", line 2376
ORA-06512: at "APPS.FND_SESSION_MANAGEMENT", line 821
ORA-06512: at "APPS.FND_SESSION_MANAGEMENT", line 1127
ORA-06512: at "APPS.FND_AOLJ_UTIL", line 421
ORA-06512: at line 1

I logged SR 6604364.992 for a possible solution. They suggested a few things but we solved it on our own. Here's the content of the TAR towards the end:

Vikram:

We have not faced the problem again after applying the DB patch 5890966 mentioned in the begining of the TAR. I have also run autoconfig after ensuring the value of s_sesstimeout / 60*1000 does not result in a decimal number. Soft close this SR for 15 days, just in case we get the error again.

- Vikram



19-NOV-07 15:16:00 GMT

(Update for record id(s): 110662639,110663600)

CAUSE DETERMINATION
===================
Upgrade to 10.2.0.3 results in
ORA-20001: Oracle error -6502: ORA-06502: PL/SQL: numeric or
value error: associative array shape is not consistent with session parameters

during peak load

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

This is explained in the following bug:
Bug 5890966 - INTERMITTENT ORA-06502 DURING PEAK LOADING - is the cause of this issue on RDBMS 10.2.0.3

Note 455882.1 Expenses Workflow Error: "ORA-06502: PL/SQL: numeric or
value error: associative array shape is not consistent with
session parameters has been detected in fnd_global.
put(CONC_LOGIN_ID,-1)"

Note 436084.1 ORA-6502 Associative Array Shape Is Not Consistent After
Applying Oracle Database Patch

ISSUE CLARIFICATION
===================
-- Problem Statement:
On 11.5.10.2 in Production:
When attempting to login to Apps 11i after upgrade to RUP5 at peak load,
the following error occurs:

ERROR
-----------------------
Internal Server error 500

-- Steps To Reproduce:
The issue can be reproduced at will with the following steps at peak load:
1. Login to Apps 11i after upgrade to RUP5

-- Business Impact:
The issue has the following business impact:
Due to this issue, users cannot login at peak load

19-NOV-07 15:19:04 GMT

(Update for record id(s): 110663613,110663621)

PROPOSED SOLUTION(S)
====================
Apply Patch 5890966 - INTERMITTENT ORA-06502 DURING PEAK LOADING

PROPOSED SOLUTION JUSTIFICATION(S)
==================================
Note 455882.1 Expenses Workflow Error: "ORA-06502: PL/SQL: numeric or
value error: associative array shape is not consistent with
session parameters has been detected in fnd_global.
put(CONC_LOGIN_ID,-1)"

Note 436084.1 ORA-6502 Associative Array Shape Is Not Consistent After
Applying Oracle Database Patch

SOLUTION / ACTION PLAN
======================
-- To implement the solution, please execute the following steps::
Apply Patch 5890966 - INTERMITTENT ORA-06502 DURING PEAK LOADING on top of RDBMS 10.2.0.3



19-NOV-07 15:41:03 GMT

(Update for record id(s): 110665240)

KNOWLEDGE CONTENT
=================
Created a new note: Note 467688.1 Associative Array Shape Is Not Consistent With Session Parameter at Peak Load

Drop invalid java classes after 11.5.10 upgrade

We got two invalid java classes after applying a few patches on top of a freshly upgraded 11.5.10.2 instance:

OBJECT_TYPE OBJECT_NAME
----------------------------------------------------
JAVA CLASS /ae799f28_DirectDepositConcurr
JAVA CLASS /e6bc455_DocumentGenerator

Metalink Note 301345.1 advises dropping them:

Symptoms

The following 2 java classes are invalid after an Oracle Applications 11.5.10 upgrade:
/e6bc455_DocumentGenerator
/ae799f28_DirectDepositConcurr

Cause

These are invalid because CP classes are not loaded into the database,
and concurrent programs that reference them should not be loaded either.

Fix

Drop the invalid java objects:
/e6bc455_DocumentGenerator, and
/ae799f28_DirectDepositConcurr

So issue the following commands and drop them:

SQL> drop java class apps."/ae799f28_DirectDepositConcurr";

Java dropped.

SQL> drop java class apps."/e6bc455_DocumentGenerator";

Java dropped.

Log file locations for middle tier in Apps 11i and R12

In Apps 11i, if you have to troubleshoot a problem with Apache, Jserv, JVM the directories to look for log files are:

$IAS_ORACLE_HOME/Apache/Apache/logs
$IAS_ORACLE_HOME/Apache/Jserv/logs
$IAS_ORACLE_HOME/Apache/Jserv/logs/jvm

In R12, the techstack has changed and you have Apache, OC4J and OPMN. These are the directories to look for log files:

$LOG_HOME/ora/10.1.3/Apache
$LOG_HOME/ora/10.1.3/j2ee
$LOG_HOME/ora/10.1.3/opmn

catdwgrd.sql DataBase downgrade from the current release to original release

$ORACLE_HOME/rdbms/admin/catdwgrd.sql is a script used for downgrading your database from the current release you have installed to the release from which you upgraded. If we open this file and go through its contents:

For 11g (11.1.0.6):

BEGIN
-- Get the previous version of the CATPROC component
SELECT prv_version INTO p_prv_version
FROM registry$ WHERE cid='CATPROC';

IF p_prv_version IS NULL THEN
RAISE_APPLICATION_ERROR(-20000,
'Downgrade not supported - database has not been upgraded');
END IF;

IF substr(p_prv_version, 1, 6) NOT IN ('10.1.0', '10.2.0','11.1.0') THEN
RAISE_APPLICATION_ERROR(-20000,
'Downgrade not supported to version ' || p_prv_version );
END IF;


For 10g (10.2.0.3):

BEGIN
-- Get the previous version of the CATPROC component
SELECT prv_version INTO p_prv_version
FROM registry$ WHERE cid='CATPROC';

IF substr(p_prv_version, 1, 5) = '9.2.0' THEN
RETURN '0902000';
ELSIF substr(p_prv_version, 1, 6) = '10.1.0' THEN
RETURN '1001000';
ELSIF substr(p_prv_version, 1, 6) = '10.2.0' THEN
RETURN '1002000';
ELSE
RAISE_APPLICATION_ERROR(-20000,
'Downgrade not supported to version ' || p_prv_version );
END IF;
END version_script;
/

So you can:

Downgrade from 11.1.0.x to 10.2.0 or 10.1.0 or 11.1.0.x
Downgrade from 10.2.0.x to 9.2.0 or 10.1.0 or 10.2.0.x

This file doesn't exist in 9.2.0.x ORACLE_HOME.

Stateful and Stateless connections

From whatis.com:

Stateful and stateless are adjectives that describe whether a computer or computer program is designed to note and remember one or more preceding events in a given sequence of interactions with a user, another computer or program, a device, or other outside element. Stateful means the computer or program keeps track of the state of interaction, usually by setting values in a storage field designated for that purpose. Stateless means there is no record of previous interactions and each interaction request has to be handled based entirely on information that comes with it. Stateful and stateless are derived from the usage of state as a set of conditions at a moment in time. (Computers are inherently stateful in operation, so these terms are used in the context of a particular set of interactions, not of how computers work in general.)

The Internet's basic protocol, the Internet Protocol (IP), is an example of a stateless interaction. Each packet travels entirely on its own without reference to any other packet. When you request a Web page from a Web site, the request travels in one or more packets, each independent of the other as far as the Internet Protocol program itself is concerned. (The upper layer Transmission Control Protocol - TCP - does relate packets to each other, but uses the information within the packet rather than some external information to do this.) The term connectionless is also used to describe communication in which a connection is made and terminated for each message that is sent. IP is connectionless as well as stateless.

The Web's Hypertext Transfer Protocol (HTTP), an application layer above TCP/IP, is also stateless. Each request from a user for a Web page or URL results in the requested pages being served, but without the Web (HTTP) server remembering the request later. In other words, there is no recorded continuity. Each communication is discrete and unrelated to those that precede or follow. In order to have stateful communication, a site developer must furnish a special program that the server can call that can record and retrieve state information. Web browsers such as Netscape Explorer and Microsoft Internet Explorer provide an area in their subdirectories where state information can be stored and accessed. The area and the information that Web browsers and server applications put in this area is called a cookie.

The Internet (including the World Wide Web) can be thought of as a stateless system or machine. Most computers, human beings, and elephants are stateful.

As per metalink note 186981.1: Oracle E-Business Suite Release 11i Self-Service Web Applications are generally stateful. There are a few exceptions in the Customer Relationship Management product family, but Release 11i HTML applications should be assumed to be stateful as the default.

Readme of Oct 2007 CPU DB patch 6395038 (for 9.2.0.8) has incorrect query

Today Sandeep pinged me asking for clarification about a DB patch I had recommended. Step 5 in section 3.3.2 of the readme of Oct2007 CPU DB (9.2.0.8) patch 6395038 reads like this:

This step is required only if your database contains an Identity Management Metadata Repository. If you are not sure whether you need to perform this step, you can you can enter the following query (a non-null result means you should perform the step):

SQL> select USERNAME from dba_users where upper(USERNAME) like '%ODS%';

This returns the result WEBMETHODS in our environment.

As we all know WEBMETHODS has nothing to do with OID or Identity Management Metadata. The Oracle Internet Directory runs on an Oracle database and creates two database users: ODS and ODSCOMMON. ODS is the schema owner that contains all of the database objects (tables, views, objects, etc.) used for OID functionality and directory storage. When the OID needs to login to the database, it uses the ODS database account which has a default password of ODS. You should secure this database user account before putting the LDAP directory into production.

So the correct query should be:

select username from dba_users where upper(username) in ('ODS','ODSCOMMON');

I logged an SR to get this corrected. Oracle was quick in response:

ISSUE CLARIFICATION
====================
Readme for PATCH 6395038 SECTION 3.3.2 POINT 5 includes a very unreliable
method for testing for the presence of OID. The test is to perform the query:

select USERNAME from dba_users where upper(USERNAME) like '%ODS%';

For this customer they have a schema named WEBMETHODS which causes the test
to incorrectly identify the instance as one that contains OID.


.
ISSUE VERIFICATION
===================
Verified

.
CAUSE DETERMINATION
====================
Unclear logic

CAUSE JUSTIFICATION
====================
Unclear logic

.
PROPOSED SOLUTION(S)
======================

Modify the query to be:

select USERNAME from dba_users where upper(USERNAME) like 'ODS';

PROPOSED SOLUTION JUSTIFICATION(S)
====================================
REASON: Any instance which contains OID will have the 'ODS' schema
specifically. Older versions of OID also included the ODS_COMMON schema,
however even in these cases the ODS schema was always present.

.
SOLUTION / ACTION PLAN
=======================
As we are specifically looking for the schema's ODS or ODS_COMMON schemas, we can ignore any result other than these.

.
KNOWLEDGE CONTENT
=================
Platform / Port Specific? = NO

Created a new note: TBC
Created Doc Bug : Bug: 6531776

26-OCT-07 23:50:15 GMT

Vikram,
Documentation bug 6531776 and Metalink Note: 464734.1 have been created for this issue. It may take a few days for the note to be reviewed and become
available.
For clarity, the workaround is to ignore any value returned other than the 2 specific values of 'ODS' and 'ODS_COMMON'.

Eddie

26-OCT-07 23:51:55 GMT

.
UPDATE
=======
Hi Vikram,

I am inactivating this Service Request, as I believe I have provided you with the solution to your issue. If this is not the case, please update the Service Request within the next two weeks and I will be glad
to assist you further. Otherwise, no update is necessary and after two weeks, th
e Service Request will automatically close.

Best Regards,
Eddie
Global Customer Services

27-OCT-07 00:49:02 GMT

Hi Eddie,

Thanks for the quick response. However I have two points:

1. The OID schemas are ODS and ODSCOMMON. You have mentioned the schema name as ODS_COMMON which is again incorrect.
2. Metalink Note 464734.1 which you mention is not visible to me. Maybe you have
classified as internal only.

- Vikram

29-OCT-07 15:55:04 GMT

Vikram,
ODS_COMMON is indeed correct, however it is a LEGACY OID schema and not present with the later versions of OID. As I mentioned the note will be review
ed before becoming available to you.
Eddie

29-OCT-07 15:55:19 GMT

I get zero hits when I query for ODS_COMMON on metalink or google. However I get a lot of hits when I query for ODSCOMMON. Was ODSCOMMON schema called ODS_COMMON in previous versions. If yes, then it is very strange that ODS_COMMON doesn't return anything.

- Vikram