Blank Page accessing Oracle EBS R12 AppsLogin page

Posted byIndraneil SealPosted inOracle EBusiness Suite

Hi folks, here is a post highlighting a very common issue most Apps DBAs have faced at least once in his or her career – that is the infamous “Blank Page” while accessing EBS R12 AppsLogin page

Usually the issue is accompanied with messages like the ones below in the oacore application log –

vi $INST_TOP/logs/ora/10.1.3/j2ee/oacore/oacore_default_group_1/application.log

19/03/16 09:50:35.10 html: chain failed
javax.servlet.ServletException: java.lang.RuntimeException: Unable to create user session. Please contact your System Administrator.
at com.evermind[Oracle Containers for J2EE 10g (10.1.3.5.0) ].server.http.EvermindPageContext.handlePageThrowable(EvermindPageContext.java:911)
at com.evermind[Oracle Containers for J2EE 10g (10.1.3.5.0) ].server.http.EvermindPageContext.handlePageException(EvermindPageContext.java:828)
at _AppsLocalLogin._jspService(_AppsLocalLogin.java:307)
at com.orionserver[Oracle Containers for J2EE 10g (10.1.3.5.0) ].http.OrionHttpJspPage.service(OrionHttpJspPage.java:59)

Some of the common steps we immediately undertake in this situation.

  1. Remove persistence files & restart apps

rm -fr $INST_TOP/ora/10.1.3/j2ee/oacore/persistence/*
rm -fr $INST_TOP/ora/10.1.3/j2ee/oafm/persistence/*
rm -fr $INST_TOP/ora/10.1.3/j2ee/forms/persistence/*

2. Clear _pages and recompile jsp pages

cd $COMMON_TOP/_pages
rm -rf *
perl $fnd_top/patch/115/bin/ojspCompile.pl –compile –flush -p 2

3. Reset asadmin password

  1. Check if GUEST password is working by running SQL> select FND_WEB_SEC.VALIDATE_LOGIN(‘GUEST’,’ORACLE’) from dual;

However, none of the above steps worked in this particular case. So I decided to investigate further and checked the database alert log and tried to see if any information is being written in any of the trace files. In one such trace file, I noticed the APPS.FND_TRACE was being referenced.

*** 2019-03-16T09:50:34.986551-04:00 (EBSSIT(3))
*** SESSION ID:(293.40291) 2019-03-16T09:50:34.986591-04:00
*** CLIENT ID:(ANONYMOUS) 2019-03-16T09:50:34.986596-04:00
*** SERVICE NAME:(ebs_EBSSIT) 2019-03-16T09:50:34.986601-04:00
*** MODULE NAME:(e::bes:oracle.apps.icx.security.session.created) 2019-03-16T09:50:34.986605-04:00
*** ACTION NAME:(/) 2019-03-16T09:50:34.986609-04:00
*** CLIENT DRIVER:(jdbcthin) 2019-03-16T09:50:34.986613-04:00
*** CONTAINER ID:(3) 2019-03-16T09:50:34.986616-04:00

KGI Error-3 = 5

LibraryHandle: Address=0xc9fa0ee0 Hash=72f58175 LockMode=N PinMode=S LoadLockMode=0 Status=VALD
ObjectName: Name=EBSSIT.APPS.FND_TRACE
FullHashValue=ea30b204fb5a8ca2af9d3a4372f58175 Namespace=TABLE/PROCEDURE(01) Type=PACKAGE(09) ContainerId=3 ContainerUid=1859608411 Identifier=171022 OwnerIdn=118
Statistics: InvalidationCount=0 ExecutionCount=0 LoadCount=1 ActiveLocks=1 TotalLockCount=2 TotalPinCount=2
Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=0 Version=0 BucketInUse=0 HandleInUse=0 HandleReferenceCount=0
Concurrency: DependencyMutex=0xc9fa0f90(0, 2, 0, 0) Mutex=0xc9fa1030(293, 31, 0, 6)
Flags=PIN/TIM/[00002801] Flags2=[0000]
WaitersLists:
Lock=0xc9fa0f70[0xc9fa0f70,0xc9fa0f70]

FND_TRACE is an Enhanced Performance Diagnostics feature available from 12.1.3 onwards to gather SQL Trace and other diagnostics for Oracle E-Business Suite sessions. This is invoked by using the profile option “Initialization SQL Statement – Custom”. It is likely that the profile option was enabled at Site level or user level in the source instance and after the refresh the same got propagated in the cloned instance.

I decided to check the object status and discovered that the package body FND_TRACE was INVALID. Compiling the package body failed but it led me to the object it’s referring which is DBMS_SYSTEM.

SQL> alter package apps.FND_TRACE compile body;

Warning: Package Body altered with compilation errors.

SQL> show errors;
Errors for PACKAGE BODY APPS.FND_TRACE:

235/6 PL/SQL: Statement ignored
235/6 PLS-00201: identifier ‘DBMS_SYSTEM.SET_EV’ must be declared
238/10 PL/SQL: Statement ignored
238/10 PLS-00201: identifier ‘DBMS_SYSTEM.SET_EV’ must be declared
294/7 PL/SQL: Statement ignored

On researching further in oracle metalink, I came across the note Doc ID 2255029.1. I read through the note and found that the public synonym DBMS_SYSTEM was missing in the cloned instance causing issue with compilation of FND_TRACE package.

Following the note, I performed the below steps & that fixed the login page issue.

sqlplus SYS/****
SQL> alter session set container=EBSSIT;
SQL> CREATE PUBLIC SYNONYM DBMS_SYSTEM FOR SYS.DBMS_SYSTEM;
SQL> GRANT EXECUTE ON DBMS_SYSTEM TO PUBLIC;

Then compile the APPS.FND_TRACE manually

SQL> alter package FND_TRACE compile body;

Finally, restarting the application services brought up the EBS landing page.

Published by Indraneil Seal

I originally hail from Kolkata, India, and I've dedicated a significant portion of my professional journey to both India and the United States before relocating to Canada during the pandemic. I’m a member of MongoDB's esteemed Technical Services team. Before joining this exceptional group, I held the role of Senior Apps DBA at the Government of Ontario. Prior to that, I spent many years honing my technical(DBA/Cloud) and soft skills with TCS, KBACE Technologies(which later got acquired by Cognizant Technology Solutions) and Oracle Corporation. Throughout my career, I was deeply immersed in day-to-day operations and spearheaded significant projects, including the modernization of platforms, Oracle application and database upgrades. In my last stint at the Government of Ontario, I was also responsible for overseeing various automation initiatives including out-of-place patching, automated EBS Application patching. Outside of my professional life, I have a strong passion for reading, blogging, spending quality time with my family, and my feline buddies, Smokey & Louis. I also relish solitary walks and jogging as personal interests. As a proponent of open source technologies, I'm looking forward to sharing my knowledge and expertise as well as contribute as much as possible to the success of the IT fraternity thereby perpetually expanding my skillset.

Leave a comment