oracle.apps.fnd.common.PoolException: Not able to create database connection

Sandeep reported this error which was coming when the AppsLocalLogin.jsp page was accessed after applying ATG_PF.H RUP6:

oracle.apps.fnd.common.AppsException: oracle.apps.fnd.common.PoolException:
Not able to create new database connection.

jserv.log showed these errors:

[20/01/2009 15:22:24:984 EST] oracle.apps.mwa.wap.engine.WapServlet: init
[20/01/2009 15:22:24:985 EST] oracle.apps.mwa = $MWA_TOP
Exception in static block of jtf.cache.appsimpl.AppsCacheLogger. Stack trace is: oracle.apps.fnd.common.AppsException: oracle.apps.fnd.common.PoolException:
Not able to create new database connection.
at oracle.apps.fnd.profiles.Profiles.getProfileOption(Profiles.java:1509)
at oracle.apps.fnd.profiles.Profiles.getProfile(Profiles.java:362)
at oracle.apps.fnd.profiles.ExtendedProfileStore.getSpecificProfileFromDB(ExtendedProfileStore.java:210)
at oracle.apps.fnd.profiles.ExtendedProfileStore.getSpecificProfile(ExtendedProfileStore.java:169)
at oracle.apps.fnd.profiles.ExtendedProfileStore.getProfile(ExtendedProfileStore.java:148)
at oracle.apps.fnd.common.logging.DebugEventManager.configureUsingDatabaseValues(DebugEventManager.java:1201)
at oracle.apps.fnd.common.logging.DebugEventManager.configureLogging(DebugEventManager.java:1044)
at oracle.apps.fnd.common.logging.DebugEventManager.internalReinit(DebugEventManager.java:1013)
at oracle.apps.fnd.common.logging.DebugEventManager.reInitialize(DebugEventManager.java:980)
at oracle.apps.fnd.common.logging.DebugEventManager.reInitialize(DebugEventManager.java:967)
at oracle.apps.fnd.common.AppsLog.reInitialize(AppsLog.java:570)
at oracle.apps.fnd.common.AppsContext.initLog(AppsContext.java:593)
at oracle.apps.fnd.common.AppsContext.initializeContext(AppsContext.java:570)
at oracle.apps.fnd.common.AppsContext.initLog(AppsContext.java:593)
at oracle.apps.fnd.common.AppsContext.initializeContext(AppsContext.java
at oracle.apps.fnd.common.AppsContext.initializeContext(AppsContext.java
at oracle.apps.fnd.common.AppsContext.<init>(AppsContext.java:292)
at oracle.apps.fnd.common.WebAppsContext.<init>(WebAppsContext.jav
at oracle.apps.fnd.sso.Utils.getAppsContext(Utils.java:518)
at oracle.apps.fnd.sso.SSOAccessEnabler.getAppsContext(SSOAccessEnabler.
at _oa__html._AppsLocalLogin._jspService(_AppsLocalLogin.java:198)
at oracle.jsp.runtime.HttpJsp.service(HttpJsp.java:119)
at oracle.jsp.app.JspApplication.dispatchRequest(JspApplication.java:417
at oracle.jsp.JspServlet.doDispatch(JspServlet.java:267)
at oracle.jsp.JspServlet.internalService(JspServlet.java:186)
at oracle.jsp.JspServlet.service(JspServlet.java:156)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:588)
at org.apache.jserv.JServConnection.processRequest(JServConnection.java:
at org.apache.jserv.JServConnection.run(JServConnection.java:294)
at java.lang.Thread.run(Thread.java:534)
Caused by: java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'FND_ENCRYPTED_PWD'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
at oracle.apps.fnd.common.AppsContext.initLog(AppsContext.java:593)
at oracle.apps.fnd.common.AppsContext.initializeContext(AppsContext.java
:570)
at oracle.apps.fnd.common.AppsContext.initializeContext(AppsContext.java
:524)
at oracle.apps.fnd.common.AppsContext.<init>(AppsContext.java:292)
at oracle.apps.fnd.common.WebAppsContext.<init>(WebAppsContext.jav
a:1002)
at oracle.apps.fnd.sso.Utils.getAppsContext(Utils.java:518)
at oracle.apps.fnd.sso.SSOAccessEnabler.getAppsContext(SSOAccessEnabler.
java:50)
at _oa__html._AppsLocalLogin._jspService(_AppsLocalLogin.java:198)
at oracle.jsp.runtime.HttpJsp.service(HttpJsp.java:119)
at oracle.jsp.app.JspApplication.dispatchRequest(JspApplication.java:417
)
at oracle.jsp.JspServlet.doDispatch(JspServlet.java:267)
at oracle.jsp.JspServlet.internalService(JspServlet.java:186)
at oracle.jsp.JspServlet.service(JspServlet.java:156)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:588)
at org.apache.jserv.JServConnection.processRequest(JServConnection.java:
456)
at org.apache.jserv.JServConnection.run(JServConnection.java:294)
at java.lang.Thread.run(Thread.java:534)
at oracle.apps.fnd.common.AppsContext.initLog(AppsContext.java:593)
at oracle.apps.fnd.common.AppsContext.initializeContext(AppsContext.java:570)
at oracle.apps.fnd.common.AppsContext.initializeContext(AppsContext.java:524)
at oracle.apps.fnd.common.AppsContext.<init>(AppsContext.java:292)
at oracle.apps.fnd.common.WebAppsContext.<init>(WebAppsContext.java:1002)
at oracle.apps.fnd.sso.Utils.getAppsContext(Utils.java:518)
at oracle.apps.fnd.sso.SSOAccessEnabler.getAppsContext(SSOAccessEnabler.java:50)
at _oa__html._AppsLocalLogin._jspService(_AppsLocalLogin.java:198)
at oracle.jsp.runtime.HttpJsp.service(HttpJsp.java:119)
at oracle.jsp.app.JspApplication.dispatchRequest(JspApplication.java:417)
at oracle.jsp.JspServlet.doDispatch(JspServlet.java:267)
at oracle.jsp.JspServlet.internalService(JspServlet.java:186)
at oracle.jsp.JspServlet.service(JspServlet.java:156)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:588)
at org.apache.jserv.JServConnection.processRequest(JServConnection.java:456)
at org.apache.jserv.JServConnection.run(JServConnection.java:294)
at java.lang.Thread.run(Thread.java:534)
Caused by: java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'FND_ENCRYPTED_PWD'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:590)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1973)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1119)
at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2191)
at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:2064)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2989)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:658)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:736)
at oracle.apps.fnd.security.AppsConnectionManager.getEncApplsysPwd(AppsConnectionManager.java:1328)
at oracle.apps.fnd.security.AppsConnectionManager.localAppsConnect(AppsConnectionManager.java:1189)
at oracle.apps.fnd.security.AppsConnectionManager.localAppsConnect(AppsConnectionManager.java:1109)
at oracle.apps.fnd.security.AppsConnectionManager.localAppsConnect(AppsConnectionManager.java:1098)
at oracle.apps.fnd.security.AppsConnectionManager.makeGuestConnection(AppsConnectionManager.java:783)
at oracle.apps.fnd.security.DBConnObj.<init>(DBConnObj.java:246)
... 41 more

This instance is on ATG RUP6. Based on metalink note 744916.1 the root cause is:

oracle.apps.fnd.security.AppsConnectionManager.getEncApplsysPwd code in RUP2 shows:

String sql="BEGIN fnd_security_pkg.fnd_encrypted_pwd(:1,:2,:3,:4,:5); END;";
Whereas in RUP6:

String sql = "BEGIN fnd_security_pkg.fnd_encrypted_pwd(:1,:2,:3,:4,:5,:6,:7,:8); END;";

I checked the code of FND_SECURITY_PKG by giving the command:

select line,text
from dba_source
where name='FND_SECURITY_PKG'
and type='PACKAGE'


LINE
----------
TEXT
--------------------------------------------------------------------------------

PROCEDURE fnd_encrypted_password(p_username IN VARCHAR2,
290 p_server_id IN VARCHAR2,
291 p_user_id IN OUT NOCOPY VARCHAR2,
292 p_password IN OUT NOCOPY VARCHAR2)
293 is
294 begin
295 fnd_encrypted_pwd(p_username, p_server_id, p_user_id, p_password,
296 'UNKNOWN', '');

I grepped for fnd_encrypted_password in $FND_TOP/patch/115/sql and found that the version of FND_SECURITY_PKG was older in the database compared to the one in the file system.

$ cd $FND_TOP/patch/115/sql
$ grep FND_SECURITY_PKG *
AFSCUSV8.pls:REM ER 5892249 - Password Hash project - Wrapped FND_SECURITY_PKG
AFSCUSV8.pls:1FND_SECURITY_PKG:
AFSCUSV8.pls:1FND_SECURITY_PKG.FND_ENCRYPTED_PWD:
AFSCUSVS.pls:REM | PL/SQL specification for package: FND_SECURITY_PKG |
afpub.sql:GRANT EXECUTE ON FND_SECURITY_PKG TO &3;
afpub.sql:DROP SYNONYM FND_SECURITY_PKG;
afpub.sql:CREATE SYNONYM FND_SECURITY_PKG FOR &1..FND_SECURITY_PKG;
afpubfix.sql:'EXECUTE ON FND_SECURITY_PKG',
afpubfix.sql:'EXECUTE ON FND_SECURITY_PKG',

So I ran AFSCUSV8.pls which complained about not able to find some other object and invalidated fnd_web_sec. So I ran AFSCJAV8.pls which recreated fnd_web_sec. I had multiple invalid objects and no clue what to run to correct it. I recalled that there was a post ATG RUP6 bug 6841295 ( DB SESSIONS FROM JVMS ARE VERY VERY HIGH AFTER ATG RUP6 + JDK 1.6 UPGRADE") which provided a patch 6841295 which recreated FND_SECURITY_PKG. As described in Metalink Note 459353.1 :

There are lots of idle database connections coming from the APPLSYSPUB user.

Use this script to identify if you are experiencing this problem:
select s.sql_hash_value, t.sql_text, s.last_call_et
from v$session s , v$sqltext t
where s.username = 'APPLSYSPUB'
and s.sql_hash_value= t.hash_value
and t.sql_text like '%fnd_security_pkg.fnd_encrypted_pwd%';


These connection leaks are NOT reported in ""AOL/J Database connection pool status" page.

Last SQL executed shows :

BEGIN fnd_security_pkg.fnd_encrypted_pwd(:1,:2,:3,:4,:5,:6,:7,:8); END;
or
BEGIN fnd_security_pkg.fnd_encrypted_pwd(:1,:2,:3,:4,:5); END;

I downloaded that patch and unzipped it. The fnd/patch/115/sql directory had the following files:


I ran all the .pls files and all the invalids were fixed.

However this didn't fix the problem.

On accessing http://dev21.justanexample.com:8000/OA_HTML/AppsLocalLogin.jsp it showed:

JSP Error:

Request URI:/OA_HTML/AppsLocalLogin.jsp
Exception:
java.lang.NoClassDefFoundError


On accessing http://dev21.justanexample.com/oa_servlets/AppsLogin it shows:

Internal Server Error
The server encountered an internal error or misconfiguration and was unable to complete your request.

Please contact the server administrator, applmgr@dev21.justanexample.com and inform them of the time the error occurred, and anything you might have done that may have caused the error.

More information about this error may be available in the server error log.

I went to sleep as it was very late in night and sent a mail to Sandeep about my R&D.

Finally, Sandeep told me that they had run ATG_PF.H RUP6 with the adpatch flag nodbportion by mistake, which had not run any of the database drivers in RUP6. They had to reclone and re-do the whole exercise.