Friday, March 1, 2013

Login Page not Coming Up / Guest Log In Fails / LogIn Issue in EBS / Apps R12


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
GUEST_USER_PWD=GUEST/ORACLE

2. Check profile option value in DB
[oracle@appsdbnode appl]$ sqlplus apps/apps@VIS
SQL> select fnd_profile.value('GUEST_USER_PWD') from dual;
FND_PROFILE.VALUE(’GUEST_USER_PWD’)
——————————————————————————–
GUEST/ORACLE

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;
FND_WEB_SEC.VALIDATE_LOGIN('GUEST','ORACLE')
——————————————————————————–-----
Y

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> commit;
SQL> select FND_WEB_SEC.CHANGE_GUEST_PASSWORD ('ORACLE','<applsyspassword>') from dual;
SQL> commit;

-- 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?

DECLARE
     stat boolean;
BEGIN
     stat := FND_PROFILE.SAVE('GUEST_USER_PWD','GUEST/ORACLE','SITE');
     IF stat THEN
          dbms_output.put_line( 'Stat = TRUE - profile updated' );
     ELSE
          dbms_output.put_line( 'Stat = FALSE - profile NOT updated' );
     END IF;
end;
/

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

2 comments:

  1. Hi

    Manish , i have faced same issue yesterday and tried all above but did not solved the issue .So finally did OWC with Oracle Support and issue was some processes of Apache was not killed .

    ps -ef |grep -i user_name

    out put was concurrent request and Apache processes was running .

    Killed all the process and gave the command

    apache stopall

    apache startall

    It was fine and Prod Server was up and running.

    Regards

    ReplyDelete