The issue occurred post R12.1.3 Patch application. We started the DB Tier and Apps Tier successfully and it came up nice. But when one tried to load log-in page for Apps, we got 500 - Internal Server Error.
So we checked the log files under $LOG_HOME and found out that one jtf logfile was showing Invalid username/password Error.
Guest is the very important user in EBS/Apps as all the authentication for Applications users go thru Guest user, hence if the guest credentials are improper the login page will never appear.
Few Important points about Guest User -
- Stored in DBC file on Apps node- $FND_TOP/secure/
- Remember that, Guest is an application user not a DB user while APPS/APPLSYS/APPLSYSPUB is a DB user.
- It should be consistent on both the places, DBC File and in FND Profile. Otherwise application will not work.
- Guest user password cannot be changed through FNDCPASS.Most appropiate way to change it through user define form. However there is other way to accomplish this. it can be done through FND_WEB_SEC package stored in apps schema
- It should never be end dated. Otherwise after End Date your application will falter
So it was the time to check the integrity of the Guest user password. Following is the way to do it.
1. Check Value in DBC File
[oracle@appsdbnode appl]$ grep -i GUEST_USER_PWD $FND_SECURE/visr12.dbc
2. Check profile option value in DB
[oracle@appsdbnode appl]$ sqlplus apps/apps@VIS
SQL> select fnd_profile.value('GUEST_USER_PWD') from dual;
Value for step 1 and 2 must be sync
Note - However in my case R12 DB the profile option value for not showing up.
3. Guest user connectivity check
SQL> select FND_WEB_SEC.VALIDATE_LOGIN('GUEST','ORACLE') from dual;
If you are getting above mentioned values everything is file in your Instance.
As I mentioned earlier the fnd_profile value was not appearing. So as per MOS I decided to alter it.
1. Reset GUEST password:
-- delete fnd vault value and change the password
SQL> exec fnd_vault.del('FND');
SQL> select FND_WEB_SEC.CHANGE_GUEST_PASSWORD ('ORACLE','<applsyspassword>') from dual;
-- check for profile exist or not. This check should give you the output with fnd option name. In my case it was showing no rows.
SQL> select profile_option_name from fnd_profile_options_tl where user_profile_option_name like 'Guest%';
no rows selected
2. Test if Profile Updated or not?
stat := FND_PROFILE.SAVE('GUEST_USER_PWD','GUEST/ORACLE','SITE');
IF stat THEN
dbms_output.put_line( 'Stat = TRUE - profile updated' );
dbms_output.put_line( 'Stat = FALSE - profile NOT updated' );
Remember - If one tries to change GUEST user password using FNDCPASS, It will result in error. Instead Use following API:
[oracle@appsdbnode appl]$ java oracle.apps.fnd.security.AdminAppServer APPS/"APPS Password" UPDATE GUEST_USER_PWD=GUEST/"Guest User Password" DB_HOST="Host_Name" DB_PORT="PortNumber" DB_NAME="SID"
4. Check the log of FNDCPASS for any error for GUEST user if there is no error for GUEST user then do the next step
5. Compile JSP
[oracle@appsdbnode appl]$ cd $FND_TOP/patch/115/bin
[oracle@appsdbnode bin]$ ojspCompile.pl --compile --flush
6. Set GUEST_USER_PWD password in $FND_SECURE/<sid>.dbc to GUEST/ORACLE (in my case. could be different in your case)
7. Bounce Middle Tier Services and Retest the issue.
The problem was still not solved and I was back where I was started. Meanwhile I came across one forum which suggested the re-generation of the jar files from adadmin with force could help so I tried it. But no luck for me.
So after all this pain the issue was still not resolved. And I was left with no option after virtually trying everything. I thought may be something not right in config files and its worth trying Autoconfig. May be that can fix the Issue or No Issue!!! I know it was fluke but believe me, it worked !!!!!!
I ran AUTOCONFIG on DB Tier and on Apps Tier and started the Apps. Tried loading the log-in page and it came up nicely this time.
And came back my Login page. Can take sigh of relief now. Hope this will help you in trouble-shooting your Log-in Issues with EBS/Apps
Final Frontier -
Access the following URL and complete all tests (Results from this test are required by MOS, if you have raised an SR/TAR with Oracle)
URL - http://host.domain:port/OA_HTML/jsp/fnd/aoljtest.jsp