java.sql.SQLException: ORA-01017: invalid username/password; logon denied for glogdba after cloning OTM

After cloning OTM database and instance, when we started the services and tried accessing

Configuration and Administration > Cluster Management > Scalability Overview, we got this error in glog.exception.log

java.sql.SQLException: ORA-01017: invalid username/password; logon denied

I was able to diagnose that this was coming for glogdba user by switching on auditing on the database.

The encrypted password was correct for glogdba in glog.properties.

After a lot of R&D we found out that this was occurring because the encrypted password of glogdba user in data_source table was incorrect.


It had the encrypted password of the glogdba user in production. We reset the glogdba password to default glogdba after clone.

Doing this update statement and bouncing apache and weblogic did the trick:

update data_source set oracle_password='Z2xvZ2RiYQ=='
where data_source_gid='UNPOOLED_DBA';
commit;

Oracle error 20001: java.sql.SQLException: ORA-20001: Oracle error -6508: ORA-06508: PL/SQL: could not find program unit being called has been detected in fnd.plsql.MO_UTILS.GET_MULTI_ORG_FLAG. ORA-06512

Anand  pinged me today.  After cloning an 11i instance, the following error was coming when AppsLocalLogin.jsp was accessed:

PRE>Unable to create anonymous session. Your session is no longer valid. Oracle error 20001: java.sql.SQLException: ORA-20001: Oracle error -6508: ORA-06508: PL/SQL: could not find program unit being called has been detected in fnd.plsql.MO_UTILS.GET_MULTI_ORG_FLAG. ORA-06512: at "APPS.MO_UTILS", line 27 ORA-06512: at "APPS.MO_UTILS", line 232 ORA-06512: at "APPS.FND_SESSION_MANAGEMENT", line 2109 ORA-06512: at "APPS.FND_SESSION_MANAGEMENT", line 369 ORA-06512: at "APPS.FND_SESSION_MANAGEMENT", line 538 ORA-06512: at "APPS.FND_AOLJ_UTIL", line 217 ORA-06512: at line 1 has been detected in createSession(int, String,Hashtable)(userid=6','D3F9A80801171DD4E043447920036C9924997405341204718936747621840314').

META name="fwk-error-detail" content="oracle.apps.fnd.framework.OAException%3A+Application%3A+FND%2C+Message+Name%3A+SQL_PLSQL_ERROR.+Tokens%3A+ROUTINE+%3D+createSession(int%2C+String%2CHashtable)(userid%3D6'%2C'D3F9A80801171DD4E043447920036C9924997405341204718936747621840314')%3B+REASON+%3D+java.sql.SQLException%3A+ORA-20001%3A+Oracle+error+-6508%3A+ORA-06508%3A+PL%2FSQL%3A+could+not+find+program+unit+being+called+has+been+detected+in+fnd.plsql.MO_UTILS.GET_MULTI_ORG_FLAG.%0AORA-06512%3A+at+%22APPS.MO_UTILS%22%2C+line+27%0AORA-06512%3A+at+%22APPS.MO_UTILS%22%2C+line+232%0AORA-06512%3A+at+%22APPS.FND_SESSION_MANAGEMENT%22%2C+line+2109%0AORA-06512%3A+at+%22APPS.FND_SESSION_MANAGEMENT%22%2C+line+369%0AORA-06512%3A+at+%22APPS.FND_SESSION_MANAGEMENT%22%2C+line+538%0AORA-06512%3A+at+%22APPS.FND_AOLJ_UTIL%22%2C+line+217%0AORA-06512%3A+at+line+1%0A%3B+ERRNO+%3D+20001%3B+%0A%09at+oracle.apps.fnd.framework.server.OAExceptionUtils.processAOLJErrorStack(OAExceptionUtils.java%3A974)%0A%09at+oracle.apps.fnd.framework.OACommonUtils.processAOLJErrorStack(OACommonUtils.java%3A867)%0A%09at+oracle.apps.fnd.framework.webui.OAPageBean.validateUser(OAPageBean.java%3A4762)%0A%09at+oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java%3A713)%0A%09at+oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java%3A515)%0A%09at+oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java%3A436)%0A%09at+_oa__html._OA._jspService(_OA.java%3A84)%0A%09at+oracle.jsp.runtime.HttpJsp.service(HttpJsp.java%3A119)%0A%09at+oracle.jsp.app.JspApplication.dispatchRequest(JspApplication.java%3A417)%0A%09at+oracle.jsp.JspServlet.doDispatch(JspServlet.java%3A267)%0A%09at+oracle.jsp.JspServlet.internalService(JspServlet.java%3A186)%0A%09at+oracle.jsp.JspServlet.service(JspServlet.java%3A156)%0A%09at+javax.servlet.http.HttpServlet.service(HttpServlet.java%3A588)%0A%09at+org.apache.jserv.JServConnection.processRequest(JServConnection.java%3A456)%0A%09at+org.apache.jserv.JServConnection.run(JServConnection.java%3A294)%0A%09at+java.lang.Thread.run(Thread.java%3A662)%0A%23%23+Detail+0+%23%23%0Aoracle.apps.fnd.framework.OAException%3A+Application%3A+FND%2C+Message+Name%3A+FND_GENERIC_MESSAGE.+Tokens%3A+MESSAGE+%3D+java.sql.SQLException%3A+ORA-20001%3A+Oracle+error+-6508%3A+ORA-06508%3A+PL%2FSQL%3A+could+not+find+program+unit+being+called+has+been+detected+in+fnd.plsql.MO_UTILS.GET_MULTI_ORG_FLAG.%0AORA-06512%3A+at+%22APPS.MO_UTILS%22%2C+line+27%0AORA-06512%3A+at+%22APPS.MO_UTILS%22%2C+line+232%0AORA-06512%3A+at+%22APPS.FND_SESSION_MANAGEMENT%22%2C+line+2109%0AORA-06512%3A+at+%22APPS.FND_SESSION_MANAGEMENT%22%2C+line+369%0AORA-06512%3A+at+%22APPS.FND_SESSION_MANAGEMENT%22%2C+line+538%0AORA-06512%3A+at+%22APPS.FND_AOLJ_UTIL%22%2C+line+217%0AORA-06512%3A+at+line+1%0A%3B+%0A%09at+oracle.apps.fnd.framework.OAException.wrapperException(OAException.java%3A891)%0A%09at+oracle.apps.fnd.framework.OAException.wrapperException(OAException.java%3A865)%0A%09at+oracle.apps.fnd.framework.server.OAExceptionUtils.processAOLJErrorStack(OAExceptionUtils.java%3A980)%0A%09at+oracle.apps.fnd.framework.OACommonUtils.processAOLJErrorStack(OACommonUtils.java%3A867)%0A%09at+oracle.apps.fnd.framework.webui.OAPageBean.validateUser(OAPageBean.java%3A4762)%0A%09at+oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java%3A713)%0A%09at+oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java%3A515)%0A%09at+oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java%3A436)%0A%09at+_oa__html._OA._jspService(_OA.java%3A84)%0A%09at+oracle.jsp.runtime.HttpJsp.service(HttpJsp.java%3A119)%0A%09at+oracle.jsp.app.JspApplication.dispatchRequest(JspApplication.java%3A417)%0A%09at+oracle.jsp.JspServlet.doDispatch(JspServlet.java%3A267)%0A%09at+oracle.jsp.JspServlet.internalService(JspServlet.java%3A186)%0A%09at+oracle.jsp.JspServlet.service(JspServlet.java%3A156)%0A%09at+javax.servlet.http.HttpServlet.service(HttpServlet.java%3A588)%0A%09at+org.apache.jserv.JServConnection.processRequest(JServConnection.java%3A456)%0A%09at+org.apache.jserv.JServConnection.run(JServConnection.java%3A294)%0A%09at+java.lang.Thread.run(Thread.java%3A662)%0A%23%23+Detail+0+%23%23%0Ajava.sql.SQLException%3A+ORA-20001%3A+Oracle+error+-6508%3A+ORA-06508%3A+PL%2FSQL%3A+could+not+find+program+unit+being+called+has+been+detected+in+fnd.plsql.MO_UTILS.GET_MULTI_ORG_FLAG.%0AORA-06512%3A+at+%22APPS.MO_UTILS%22%2C+line+27%0AORA-06512%3A+at+%22APPS.MO_UTILS%22%2C+line+232%0AORA-06512%3A+at+%22APPS.FND_SESSION_MANAGEMENT%22%2C+line+2109%0AORA-06512%3A+at+%22APPS.FND_SESSION_MANAGEMENT%22%2C+line+369%0AORA-06512%3A+at+%22APPS.FND_SESSION_MANAGEMENT%22%2C+line+538%0AORA-06512%3A+at+%22APPS.FND_AOLJ_UTIL%22%2C+line+217%0AORA-06512%3A+at+line+1%0A%0A%09at+oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java%3A134)%0A%09at+oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java%3A289)%0A%09at+oracle.jdbc.ttc7.Oall7.receive(Oall7.java%3A590)%0A%09at+oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java%3A1973)%0A%09at+oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java%3A1119)%0A%09at+oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java%3A2191)%0A%09at+oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java%3A2064)%0A%09at+oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java%3A2989)%0A%09at+oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java%3A658)%0A%09at+oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java%3A736)%0A%09at+oracle.apps.fnd.common.WebAppsContext.createSession(WebAppsContext.java%3A2938)%0A%09at+oracle.apps.fnd.common.WebAppsContext.createSession(WebAppsContext.java%3A2825)%0A%09at+oracle.apps.fnd.common.WebAppsContext.createSession(WebAppsContext.java%3A2754)%0A%09at+oracle.apps.fnd.common.WebAppsContext.createAnonymousSession(WebAppsContext.java%3A5111)%0A%09at+oracle.apps.fnd.common.WebRequestUtil.setGuestSession(WebRequestUtil.java%3A1510)%0A%09at+oracle.apps.fnd.framework.webui.OAPageBean.validateUser(OAPageBean.java%3A4760)%0A%09at+oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java%3A713)%0A%09at+oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java%3A515)%0A%09at+oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java%3A436)%0A%09at+_oa__html._OA._jspService(_OA.java%3A84)%0A%09at+oracle.jsp.runtime.HttpJsp.service(HttpJsp.java%3A119)%0A%09at+oracle.jsp.app.JspApplication.dispatchRequest(JspApplication.java%3A417)%0A%09at+oracle.jsp.JspServlet.doDispatch(JspServlet.java%3A267)%0A%09at+oracle.jsp.JspServlet.internalService(JspServlet.java%3A186)%0A%09at+oracle.jsp.JspServlet.service(JspServlet.java%3A156)%0A%09at+javax.servlet.http.HttpServlet.service(HttpServlet.java%3A588)%0A%09at+org.apache.jserv.JServConnection.processRequest(JServConnection.java%3A456)%0A%09at+org.apache.jserv.JServConnection.run(JServConnection.java%3A294)%0A%09at+java.lang.Thread.run(Thread.java%3A662)%0Aoracle.apps.fnd.framework.OAException%3A+Application%3A+FND%2C+Message+Name%3A+FND_GENERIC_MESSAGE.+Tokens%3A+MESSAGE+%3D+java.sql.SQLException%3A+ORA-20001%3A+Oracle+error+-6508%3A+ORA-06508%3A+PL%2FSQL%3A+could+not+find+program+unit+being+called+has+been+detected+in+fnd.plsql.MO_UTILS.GET_MULTI_ORG_FLAG.%0AORA-06512%3A+at+%22APPS.MO_UTILS%22%2C+line+27%0AORA-06512%3A+at+%22APPS.MO_UTILS%22%2C+line+232%0AORA-06512%3A+at+%22APPS.FND_SESSION_MANAGEMENT%22%2C+line+2109%0AORA-06512%3A+at+%22APPS.FND_SESSION_MANAGEMENT%22%2C+line+369%0AORA-06512%3A+at+%22APPS.FND_SESSION_MANAGEMENT%22%2C+line+538%0AORA-06512%3A+at+%22APPS.FND_AOLJ_UTIL%22%2C+line+217%0AORA-06512%3A+at+line+1%0A%3B+%0A%09at+oracle.apps.fnd.framework.OAException.wrapperException(OAExcept

JSP Error

--------------------------------------------------------------------------------
Exception:
java.lang.NullPointerException

I concentrated on the first part of the error message:
ORA-06508: PL/SQL: could not find program unit being called has been detected in fnd.plsql.MO_UTILS.GET_MULTI_ORG_FLAG. ORA-06512: at "APPS.MO_UTILS", line 27 ORA-06512: at "APPS.MO_UTILS", line 232

I tried calling this package and function by executing this query from sqlplus:  
SQL> conn apps/apps
SQL> select mo_utils.get_multi_org_flag() from dual;

select mo_utils.get_multi_org_flag() from dual
*

ERROR at line 1:

ORA-20001: Oracle error -6508: ORA-06508: PL/SQL: could not find program unit
being called has been detected in fnd.plsql.MO_UTILS.GET_MULTI_ORG_FLAG.
ORA-06512: at "APPS.MO_UTILS", line 27
ORA-06512: at "APPS.MO_UTILS", line 232

I checked the code in the function mo_utils.get_multi_org_flag by:

select line,text
from dba_source
where name='MO_UTILS'
and line between 220 and 240;

LINE   TEXT
--------------------------------------------------------------------------------
FUNCTION Get_Multi_Org_Flag
RETURN VARCHAR2
IS

BEGIN
227
228
RETURN mo_global.is_multi_org_enabled;
229
230
EXCEPTION
231
WHEN OTHERS THEN
232
Generic_Error( 'fnd.plsql.MO_UTILS.GET_MULTI_ORG_FLAG'
233
, sqlcode
234
, sqlerrm);
235
236
END Get_Multi_Org_Flag;

So the only line of code in this function is on line 237:

RETURN mo_global.is_multi_org_enabled;


I tried to execute this from sqlplus:

SQL> select mo_global.is_multi_org_enabled() from dual;

select mo_global.is_multi_org_enabled() from dual
*
ERROR at line 1:

ORA-04063: package body "APPS.MO_GLOBAL" has errors

Then I tried compiling the package body:

SQL> alter package APPS.MO_GLOBAL compile body;
Warning: Package Body altered with compilation errors.


SQL> show errors
Errors for PACKAGE BODY APPS.MO_GLOBAL:


LINE/COL ERROR
-------- -----------------------------------------------------------------
167/27 PLS-00201: identifier 'V$SQLAREA' must be declared
167/27 PL/SQL: Item ignored
302/4 PL/SQL: Statement ignored
302/53 PLS-00320: the declaration of the type of this expression is incomplete or malformed

I checked line 167:

LINE
----------
TEXT
--------------------------------------------------------------------------------
167
l_action_name v$sqlarea.action%TYPE;

That means, it is unable to query v$sqlarea.

So I logged in as sys and

SQL> grant select on v$sqlarea to apps;

grant select on v$sqlarea to apps
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views

Ok, that was a mistake, the correct grant is:

SQL> grant select on v_$sqlarea to apps;


Grant succeeded.

Compiled the package MO_GLOBAL:
SQL> alter package APPS.MO_GLOBAL compile body;

Package body altered.

Tested both the sqls which were previously erroring out:

SQL> conn apps/apps

Connected.

SQL> select mo_global.is_multi_org_enabled() from dual;

MO_GLOBAL.IS_MULTI_ORG_ENABLED()
--------------------------------------------------------------------------------
Y

SQL> select mo_utils.get_multi_org_flag() from dual;

MO_UTILS.GET_MULTI_ORG_FLAG()
--------------------------------------------------------------------------------
Y

We tried with IE and the same error still came.

So we tried with a new Firefox session, and we were able to login without issues.  IE also worked after clearing Temporary Internet Files.

That was an interesting troubleshoot.  I wonder, how the grant was missing in the first place.