Only Four Left…

I was asked to investigate an EBS login page failure on a production system last night. Well, actually, I was asked if I had any crazy ideas, since someone else was already working the problem with Oracle Support. Management thought a fresh pair of eyes might help, as the East Coast eyes that had been looking were getting tired. The solution turned out to be somewhat obscure, but I'm throwing it up here in case it helps a desperate, flailing Googler in the future.

Symptoms and observations

• R12 landing page (Navigator) was returning the dreaded, generic "You have encountered an unexpected error. Please contact the System Administrator for assistance" error.
• As far as anyone knew, there hadn't been any changes, patches, or service restarts; the system had just spontaneously stopped working. (Don't you just hate that?)
• SSO and non-SSO logins were authenticating users, but no work could be done because the EBS home page was inaccessible.
• Web services had already been restarted, to no avail
• AOL/J diagnostic tests were passing, implying that this wasn't a systemic Java problem

Investigation and resolution

My first thought was that there were corrupt JSPs (probably because I had my last blog post on the brain), but the application server log files were not consistent with that problem. Then I noticed that the other troubleshooters had activated FND Diagnostics, which had the benefit of adding a "Click here for exception details" link. Knowing that this often leads to a mostly-unintelligible (to me, anyway) Java stack trace, I didn't have high hopes, but I had a look. Surprisingly, I found something interesting:

java.lang.NumberFormatException: For input string: "200000000000"
   at java.lang.NumberFormatException.forInputString(NumberFormatException.java:48)
   at java.lang.Integer.parseInt(Integer.java:459)
   at java.lang.Integer.valueOf(Integer.java:553)
   at oracle.apps.fnd.framework.server.OAUtility.getProfileVoMaxFetchSize(OAUtility.java:245)

So, Java was having trouble chewing on a really large number, and it looks like it was getting that value from a system profile option. A quick look at recently-changed profile options (query here) revealed:

applmgr@prodsys:~> sqlplus apps @last_profile_changes
SQL*Plus: Release 10.1.0.5.0 - Production on Wed Jan 20 02:45:27 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Enter value for number_of_items: 4
Profile Option  Option Level  Value           Set on    Scoldee
--------------- ------------- --------------- --------- ---------------
FND: Diagnostic Site          Y               20-JAN-10 SYSADMIN
s
GL Journals: La User          LESS            19-JAN-10 XXXXXX
st Find Window
State
FND: View Objec Site          200000000000    19-JAN-10 XXXXXX
t Max Fetch Siz
e
GL Journals: La User          LESS            19-JAN-10 XXXXXXXX
st Find Window
State

As is often the case, the key part of the phrase, "no changes as far as anyone knew" was the last five words. That number (200000000000) is several orders of magnitudes larger than typical values of "FND: View Object Max Fetch Size," and after resetting it to something more sane, the system became usable again. Whew!