Troubleshooting the Personal Home Page Login Problems in 11.5.x

Subject:  Troubleshooting the Personal Home Page Login Problems in 11.5.x
  Doc ID:  Note:135949.1 Type:  TROUBLESHOOTING
  Last Revision Date:  31-MAR-2005 Status:  PUBLISHED

             Troubleshooting Login Problems to the Personal Home Page

Introduction
------------
   This note is designed to help you investigate problems with logging into the
   Personal Home Page with your Applications 11i system. (Previously known as Self
   Service)

   The references in this note are primarily aimed at Release 11.5.3 on Unix, but
   most of the steps will be the same for NT and also for later releases of 11i.  
   11.5.1 used WebDb listeners in addition to the Oracle HTTP (Apache) Web Listener,
   which is not catered for in this note.

What happens when you login to the Personal Home Page?
------------------------------------------------------
   There are two modes of running the signon, either PLSQL or JAVA (Servlet) based.
   This note assumes you are using the default PLSQL method.
   The starting point is normally the HTML file ICXINDEX.htm. 
   When the user clicks on the "Connect" button to signon, it calls a PLSQL
   procedure "OracleMyPage.home" via the mod_plsql module.  
   The URI call from within the HTML will include the modplsql reference and the
   Agent Name, for example if you view the page source for ICXINDEX.htm you should
   see an entry similar to the following:-

                ACTION="/pls/<AgentName>/oraclemypage.home"

   The full URL called would be:

                http(s)://<host><domain>:<port>/pls/<AgentName>/oraclemypage.home

   for example:

                http://myHost.myDomain.com:2002/pls/myAgent/oraclemypage.home

   OracleMyPage PLSQL package calls other PLSQL packages, such as
   ICX_SEC.ValidatePassword,
   The PLSQL Toolkit packages, such as HTP and OWA_UTIL also get used. 
   If this process completes successfully, the username/password are correct, and
   there are valid responsibilities for the user, then the login will complete and
   the user will see the screen with the various pages and responsibilities
   available for that user.

   This sequence is shown in summary below:

       HTML  -> ICXINDEX.htm
       PLSQL -> oraclemypage.home
       PLSQL -> icx_sec.validatePassword
       PLSQL -> icx_cabo.container

   If there is some kind of error or failure at any of these stages, the user
   will get an error screen displayed advising a reason why the failure
   occurred.

Approach to Troubleshooting
----------------------------
Error messages will not necessarily indicate the root cause of the problem so it is
important to collate information on the scope and nature of the problem.

*  Was it working previously ?  If so, what has changed, are all the Server
   processes running ?
*  Are all users effected ?   If not, what is common link to users with
   problem ?
*  Is the problem confined to one network segment or one PC ?
*  Do you get different behaviour in Internet Explorer to Netscape browser ?

The troubleshooting tips below have been split into the quick checks that can
be done easily on the fly, and detailed checks that may involve bouncing servers
and may require editing files/packages so will need to ensure they can be
restored if required.

Quick Checks
------------

Note: These checks should not interfere with the running of a live system,
so can be run while users are on the system.

1. Run the following SQL to find out the DBC filename that Self Service
   is expecting to be looking for:

     select host_name||'_'||instance_name from v$instance;

   This may be different to the name actually generated by the AdminAppServer
   utility if you have a remote database and you have not called your TNS
   alias the same as your ORACLE_SID.  If this is the case, you need to
   set-up a TNS Alias with the same name as you ORACLE_SID.
  
   Check this file exists in the $FND_TOP/secure directory and the permissions
   are correct for the Web Server user to access.

2. Check that the CGI environment and mod_plsql is functioning.
   If you are unable to run the procedure below because of an 'internal
   error' or a 'cannot find host' or a similar error, then it could indicate
   that there is a problem with the PLSQL Configuration.

   a. Run the following URL:
        http://<hostname>:<port>/pls/<AgentName>/FND_WEB.PING

        For example:
        http://myHost.myDomain.com:2002/pls/myAgent/FND_WEB.PING

      This should output the results of running FND_WEB.PING package.

   b. Set-up and run the following procedure as the APPS user:

        create or replace procedure mzCGI
          is
          begin
                htp.htmlOpen;
                htp.headOpen;
                htp.title('Show CGI parameters');
                htp.headClose;
                htp.bodyOpen;
                owa_util.signature;
  htp.p('<br>');
                htp.p('CGI parameters follow ....');
                owa_util.print_cgi_env;
                htp.bodyClose;
                htp.htmlClose;
          end;
          /

   b. Run the above through browser using the PLSQL agent already setup as
      below:

        http://<hostname>:<port>/pls/<AgentName>/mzCGI

        For example:
        http://myHost.myDomain.com:2002/pls/myAgent/mzCGI

      If this fails with a 'Security Error' message then you
      may need to add this procedure to the FND_ENABLED_PLSQL table (with
      ENABLED set to 'Y') by using the following SQL:

        insert into fnd_enabled_plsql
        values ('PROCEDURE', 'MZCGI', 'Y', sysdate, 1, sysdate, 1, 1, null);

      If it fails with some other error you need to check for the
      following:

        - Problems with mod_plsql
        - Problems with RDBMS (For example - invalid packages, rdbms crashed)
        - Connectivity problem (TNS alias wrong perhaps)

3. Use the following programs to verify the installation and check that the
   JServlets are functioning. 
   This is not strictly an issue that would prevent the initial signon from
   working (unless you are using the Java login method) but will need to be
   working for many of the Self Service options to work.

   Check that the $FND_TOP/secure directory only has valid DBC files in it
   - usually your servername_sid.dbc.  All other DBC files should be moved or
   deleted.  If you have other files such as 'template.dbc', these may cause
   aoljtest.jsp to report an error incorrectly.  Point your browser to:

   a)   http(s)://<host><domain>:<port>/servlets/IsItWorking
 If this one fails, then (b) and (c) below will also fail... and this also
        indicates an issue with your Jserv set-up.

   b)   http(s)://<host><domain>:<port>/servlets/oracle.apps.icx.common.ConnectionTest

   c)   http(s)://<host><domain>:<port>/servlets/oracle.apps.icx.common.SystemInfoTest
        NOTE - older versions of this utility would give "Cannot make a connection"
        error message at the end of the output.   This message can be ignored.

   d)   http(s)://<host><domain>:<port>/OA_HTML/jsp/fnd/aoljtest.jsp
       Follow the screen prompts through several screens of information.
4. Check the profile options 'Applications Web Agent', 'Applications Help Web Agent'  and 'Apps Servlet Agent'   are correctly specified.  
      For example : 
            Applications Web Agent should be of the format :  http://myHost.myDomain.com:8000/pls/myAgent
            Apps Servlet Agent should be of the format :  http://myHost.myDomain.com:8000/servlets/
            Applications Help Web Agent should be of the format :  http://myHost.myDomain.com:8000/OA_HTML/jsp/fnd/fndhelp.jsp?dbc=e:/oracle/prodappl/fnd/11.5.0/secure/hostname_prod.dbc
            POR: Servlet Virtual Path should be of the format :  servlets

   Internet Explorer 5.5 can give "Your session is no longer valid" if the domain name is not specified in these profile options.

5. Check the GUEST user information. Run following SQL:
     select user_name, start_date, end_date
     from   fnd_user
     where  user_name = 'GUEST';

   This should return one row, end_date should be NULL or in advance of
   today's date, and start_date should be before today's date.

   Note: This assumes GUEST is the user name you have got configured in the dbc file.

6. Are there any additional errors other than that reported directly on screen?
   Use the 'View Source' option within your Web Browser to see if any additional
   information is shown.

7. Check browser settings:
   Ensure your browser has cookies enabled.  In Netscape this can be
   checked from Edit -> Preferences -> Advanced.  In Internet Explorer
   v5.x, this can be checked from Tools -> Internet Options -> Security   Then select
   the correct zone for your Web Server and click on 'Custom Level'   Scroll down to
   the 'Cookies' section.   Oracle Applications uses 'per-session' cookies.
   It is worth verifying the same problem occurs in both IE and Netscape.
   Try setting the cookies setting to "prompt" to ensure they are being set correctly.

   With Internet Explorer, you should also try adding your Web Server to the list of
   'trusted sites' to see if this resolves or changes the nature of the problem.   
   Be aware that this changes default security settings, so may need to check this
   does not go against your Comapny Security policies before retaining this setting
   as a permanent change.

8. Check package versions are correct and are valid.  The most important
    packages are ORACLEMYPAGE and ICX_SEC.  Check for the versions you
    has loaded in the database using the following SQL:

      select text
      from   all_source
      where  name in ('ICX_SEC', 'ORACLEMYPAGE', 'FND_WEB_CONFIG', 'ICX_CABO') and line < 4;

    The following values are expected for Release 11.5.3 but will obviously
    be different for earlier/later releases.

      AGWBCFGB.pls  v115.13
      ICXCABOB.pls  v115.6
      ICXSEMB.pls   v115.34
      ICXSEMS.pls   v115.3.1150.1
      ICXSESEB.pls  v115.66

    Also run the following script to ensure there are no invalid objects:

      select owner, object_name, object_type
      from   all_objects
      where  status != 'VALID'
      order by owner, object_type, object_name;

    Ideally, this should return no rows, but some invalid objects may be
    acceptable, depending on what they are.

9.  Check Tablespace free space:
     Ensure that the database tablespaces have not run out of room.  This can result in "-1" errors and/or "Session expired" errors if the system cannot add data to the ICX tables.

     To test that rows can be succesfully inserted, you can run the script below:-
       rem START OF SCRIPT
       insert into icx_sessions
         (session_id, user_id, created_by, creation_date,last_updated_by, last_update_date)
       values
         (-99999, -99, -1, sysdate, -1, sysdate)
       /
       insert into icx_failures
         (created_by, creation_date,last_updated_by, last_update_date)
       values
         (-1, sysdate, -1, sysdate)
       /
       rollback
       /
       rem END OF SCRIPT

     IMPORTANT NOTE - ensure these two insert statements are rolled back - you do not want these dummy records saved.

    
10.  Check profile options:
     ICX: Language
     -------------
     If not set to 'American English', try temporarily changing it to 'American English'. 

     Possible problems encountered would be that the login works, but no icons or responsibilities are shown for the user.

     ICX: Limit Connect
     ------------------
     Should be set to sensible value, default is 1000.

     ICX: Limit time
     ---------------
     Should be set to sensible value, default is 4.    Try setting higher if system is running slow and user reporting problems.

     ICX_ HTML Directory
     -------------------
     If not already set to 'OA_HTML', change it to 'OA_HTML'. 

     POR: Servlet Virtual Path
     ------------------------
     Should have the value 'servlets'

11.  Errors in log files.
     Check the Apache/logs and jserv/log directories for log files.    These log files may have error messages and/or error stacks which could point to the cause of the problem.

     Check the database alert.log file for errors.   For example ORA-600 and ORA-7445 errors may point to issues as described in Bug 2042715

12.  Double check CLASSPATH entries
     Check all files listed in the jserv.properties files "wrapper.classpath" entries do actually exist.
     For example if you get message in mod_jserv.log similar to
        ajp12: Servlet Error: jaba.lang.NoClassDefFoundError org/w3c/dom/Node
     this could indicate that the xmlparserv2.zip file is missing from the classpath defined in jserv.properties.

13.  If the error messages are indicating issues with JSP, then try removing the subdirectories and their contents below the "/OA_HTML/_pages"
directory and restart Apache.   This will clear out the compiled JSP classes causing them to be automatically recompiled next time they are accessed.

14.  Ensure ICX module is showing as an "Installed" module.    Run "adutconf.sql" to check this, particularly important to check if migrating to 11i from previous release of Applications.   
     Can get error messages like "The requested URL OA_SERVLET/oracle.apps.icx.common.DirectLogon was not found on this server" if ICX is not set to "Installed".

15.  Go into SQLPlus, logging in as "apps" user.
     Run the following SQL command :-
         select fnd_web_sec.validate_login('SYSADMIN','<SYSADMIN PASSWORD>') FROM DUAL;
     This command should return 'Y' if it is working OK.   If not, you should reload fndsec.jar using the following command
         Login as applmgr user to ensure correct environment is set-up.
         cd $JAVA_TOP/oracle/apps
         loadjava -u apps/<apps password> -force -resolve fnd/jar/fndsec.jar
     Re-test the above select command.

16. Check the "session.topleveldomain" setting in the zone.properties file matches the domain you are using as defined in
    the SESSION_COOKIE_DOMAIN column in ICX_PARAMETERS table.    Also see note (1) in section below.
    

 

Detailed Checks
---------------

It is recommended that you have a current backup your system and have no
users on the system while performing these checks/tests.

1. Run the following SQL:
 select * from icx_parameters;

   Ensure SESSION_COOKIE_DOMAIN matches your system domain name.   If not using a domain, set SESSION_COOKIE_DOMAIN to a literal value of "NODOMAIN"    Internet Explorer could give "Permission Denied" errors if this is not set correctly.
   NOTE - there should only be one row in this table else you may get "ORA-01422: exact fetch returns more than requested number of rows ORA-06502: PL/SQL: numeric or value error: character string buffer too small" errors.

2. Check Authentication status:
   Check the status of the Server Security and toggle the current setting
   to see if the nature of the problem changes.  To check the status go to the command prompt
   set up your Applications environment variables and run the following command:

     java oracle.apps.fnd.security.AdminAppServer appsun/appspw STATUS DBC=<full path to DBC file name>

   NOTE - On NT you need to use the syntax :-
     jre -classpath %CLASSPATH% oracle.apps.fnd.security.AdminAppServer appsun/appspw STATUS DBC=<full path to DBC file name>

   This will return the Server ID and either *OFF, *ON or nothing.  If OFF
   or nothing is returned, turn on security using the following command:

     java oracle.apps.fnd.security.AdminAppServer appsun/appspw AUTHENTICATION ON DBC=<full path to DBC file name>

   Otherwise, use the following command:

     java oracle.apps.fnd.security.AdminAppServer appsun/appspw AUTHENTICATION OFF DBC=<full path to DBC file name>

   Also run the following SQL on against the database:

     select server_id, server_address
     from   fnd_application_servers;

   This should return at least one row.  The server_id is the 20 character
   string which should match the entry in the DBC file.  The server_address
   should match the IP address of the Forms Server.  There may be another
   row which has ON/OFF and * in these values, which will correspond to the
   Authentication status.

3. Recreate the DBC file using the AdminAppServer utility as described on page G-5 of the System Administrators Manual (Part # A75396)
   Also check that the ssp_init.txt file is pointing to the correct DBC file name/location.  
   When you run "ConnectionTest" it should give final message "Status OK" - if not it may not be looking for the correct DBC file.
  

4. Check file permissions are not causing any problems:
   Check file permissions are appropriate. In particular, the
   liboci806jdbc.so (.sl on HP, .DLL on NT) shared library should have read,
   write and execute permissions.  It may be advisable to temporarily change all
   permissions in $JAVA_TOP to read, write and execute to see if it
   resolves the problem.  On UNIX, you can use the command 'chmod -R 777
   $JAVA_TOP', assuming $JAVA_TOP has been set correctly in your
   environment.

5. Regenerate JAVA_TOP:
   Assuming you have a full backup of your system, and you are confident
   that the files in the APPL_TOP are correct, then it may be worth
   regenerating the JAVA_TOP to ensure all the files are there and that
   they are the correct versions.  When a patch is applied, it copies the
   Java/Media/HTML files into the product area first (FND_TOP for example)
   and then it copies the file from the product area to the JAVA_TOP/OAM_TOP
   or OAH_TOP as appropriate.  Regenerating the JAVA_TOP will just do the
   second part of this (i.e. copying the java/html/media files from the
   product areas to the JAVA_TOP.  Any files already in the JAVA_TOP/OAM_TOP
   or OAH_TOP will potentially be overwritten, thus losing any modification
   you may have made to any of the standard files.  To run this process,
   run adadmin and select Maintain Applications Files then Copy Files to
   Destinations.

6. Generate database trace and SQL*Net traces.
   To set-up SQL*Net Trace:

     On the Web Server set TRACE_LEVEL_CLIENT = 16 in the SQLNET.ORA file.
     By default the SQL*Net trace file will be called 'sqlnet.trc'.

   To set-up Database Trace:

     On the Database Server modify the init<SID>.ora file to add the line
     SQL_TRACE = TRUE.  This takes effect once the database has been
     restarted and generated .trc files for all database sessions until
     the line is removed and the database restarted.

7.  Identify any other external influences
    Are there any proxy servers or firewalls between the Database Server to Web Server, or the Web Server to the client PC ?
    If so, try using a different PC within the firewall or temporarily disable the Proxy for that particular client.

8.  Are you using SSL
    If using SSL, does not using SSL change or resolve the problem ?

 

Checklist of things to send to Oracle Support
---------------------------------------------

*********************************************************************************
IMPORTANT NOTE - the majority of the information required by Oracle Support for
troubleshooting these types of issues can be obtained easily by using the
Applications Remote Diagnostic Agent (APPS RDA)

This utility is available from and described in Metalink Note 161474.1

Please use this mechanism where possible to send Oracle Support the required
configuration and log file information.
*********************************************************************************

General notes
-------------

1. Files should be sent as attachments.
2. Output from SQL statements should be generated by spooling to a file
   then sending this file in as an attachment.
3. Screen output should ideally be sent in as screen dumps inserted into
   MS Word or similar package. This can be achieved by pressing the
   <Printscreen> key in Windows.  This puts the screen into the Windows
   Clipboard which can then be pasted into another application as a graphic
   image. Otherwise if output required is from a browser screen, the text
   can be highlighted then copied/pasted.

Send in the following files:-

Apache Configuration files
--------------------------
httpds.conf
oracle_apache.conf
xml.conf
ojsp.conf
apps.conf
jserv.conf
jserv.properties
zone.properties
plsql.conf
wdbsvr.app
ssp_init.txt

Apache log files
----------------
jserv.log
mod_jserv.log
access_log
error_log

Others
------
ICXINDEX.htm
hostname_sid.dbc

From the Quick Checks
---------------------
1.  Output from SQL
2.  Screen output generated
3.  Confirm "IsItWorking" worked or not.  Output from ConnectionTest
4.  Advise what profile option is set to
5.  Output from SQL
6.  Confirm extra errors or not
7.  Confirm Cookies enabled
8.  Output from SQL
9.  Confirm all tablespaces have sufficient free disk/extents
10. Advise values of profile options listed.
11. No additional information required.
12. No additional information required.
13. Confirm have removed these directories and are getting same error.
14. Output from adutconf.sql.

From the Detailed Checks
------------------------
1.  Output from this step.
2.  Confirmation of results
3.  Confirmation this step was succesful
4.  Confirm this step was tested.
5.  Confirmation this step was succesful
6.  sqlnet.trc and appropriate tkproffed trc files.
7.  Confirm answers to these questions.
8.  Confirm answer to this question.

References
----------
Note 135027.1 - Troubleshooting Personal Home Page Login Problems in 11.5.1 & 11.5.2
Note 154347.1 - Internet Procurement Troubleshooting
Note 118321.1 - 11i Personal Home Page Requires Logon Twice

Bug 2057138   - Frequent ORA-600 and ORA-7445 errors crashing Applications 11i instance.
Bug 1936188   - ora-164 error when login to Oracle Applications 11i