Troubleshooting Login Problems in Oracle Applications 11i RDBMS Checks

Troubleshooting Login Problems in Oracle Applications 11i RDBMS Checks

1. Check the GUEST user information.

a. 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.

b. Validate the GUEST username/password combinations from the DBC file.

Using the GUEST_USER_PWD value in the DBC file (see step 2) run the following sql:

select fnd_web_sec.validate_login('GUEST',' ') from dual ;

This should return Yes

If this returns N, then do:

select fnd_message.get from dual;

This should give a reason why the validation failed, or an error message.
If this fails with a database error, confirm the problem is not specific to the GUEST user.

Redo the SQL command with a different userid (like sysadmin)

select fnd_web_sec.validate_login('SYSADMIN','') from dual;

Again, this command should return 'Y' if it is working OK.

If not, you should reload the jar files to the database using adadmin.

2. 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.

3. 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

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

4. Review your Alert.log.

Does the RDBMS alert log show any errors? What are they? Report them in a TAR.

5. Review any database trace files.

cd to the directory specified by user_dump_dest in the init.ora file.&nbttempt at login may create a .trc file

6. Validate the FND_NODES table:

Run the following sql:

select NODE_NAME, NODE_ID , SERVER_ID , SERVER_ADDRESS from FND_NODES;

Each SERVER_ID and NODE_ID should be unique.

The Node Name for all servers involved for the instance should appear here. We typically expect this to be e hostname or virtual hostname (alias), not the IP address. If the servers do not appear, use OAM to register them.

The SERVER_ADDRESS is optional, but if present should reflect the IP of the host. If necessary, update the IP using the system administrator responsibility.

For 11.5.2 – 11.5.9 this should return 56 rows.
For 11.5.10 this should return 131 rows.
(11.5.10+) Note: in 11.5.10 the required plsql patckages can be quickly enabled


using the script: $FND_TOP/patch/sql/txkDisableModPLSQL.sql Y


7. Validate the FND_ENABLED_PLSQL table Run the script in Appendix B. For 11.5.2 – 11.5.9 this should return 56 rows. For 11.5.10 this should return 131 rows. (11.5.10+)

Note: in 11.5.10 the required plsql packages can be quickly enabled using the script: $FND_TOP/patch/sql/txkDisableModPLSQL.sql Y :

8. Validate the 'ICX_SESSIONS_S' synonym

This should be shown as owned by ICX, if not, drop the synonym and run adadmin to regenerate grants and synonyms.

9. 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.

10. Validate the ICX_PARAMETERS table.

Run the following SQL:

select count(*) from icx_parameters;

This should only return one row.

11. Check for Bug 3275654

Run the following SQL:

SELECT c.function_id, nvl(b.prompt,c.user_function_name) prompt,c.TYPE,a.menu_id from
fnd_responsibility_vl a,
fnd_menu_entries_vl b,
fnd_form_functions_vl c
where a.responsibility_id=21584
and a.menu_id=b.menu_id
and b.function_id=c.function_id
and c.type in ('WWW','WWK','SERVLET','JSP','FORM','INTEROPJSP');

This should only return one row. If not, apply Patch 3275654

12. Oracle 8i ONLY do the following sql:

select fnd_crypto.SmallRandomNumber from dual;

If this errors with: ORA-28235: algorithm not available
Then make sure you have the file $ORACLE_HOME/lib/naeet.o (7112 bytes) in place.
If not, you need to get the file from a known good source (like another instance or from the CD). Again use the size of the file to validate.

Next relink the Oracle Executables:

cd $ORACLE_HOME/rdbms/lib

make -f ins_rdbms.mk install

$ORACLE_HOME/bin/genclntsh.sh


OA Framework Checks
These are only applicable to OA Framework 5.7 and above. (11.5.9+)

13. Disable all personalizations on the homepage

Run the following sql:

set serverout on;
exec jdr_utils.listcustomizations(‘/oracle/apps/fnd/framework/navigate/webui/HomePG’);

If this returns any rows then you have personalizations.
Disable your personalizations by logging into forms directly and setting the profile option:

Disable Self Service Personal = Yes.

Then retest the login.

If the homepage works, you need to remove your “bad” personalization.

See Note 304670.1

14. You can turn on OA Framework diagnostics logging in several ways Please refer to Note 139863.1, section 4 Profile Options Reference for more information, if required. Set the following profile options at USER level for one Applications user. It is important this is setup for one user only. You can launch Core Forms directly to gain access to the profile options, as you may not be able to login if you are reading this note!


Name Value
---------------------------------------------
FND: Debug Log Enabled Yes
FND: Debug Log Filename /tmp/OAF_Debug.txt
FND: Debug Log Module %
FND: Debug Log Level STATEMENT


Login to Applications using the Apps Username you specified above. Any error or problem you experiance will still occur, but the file /tmp/OAF_Debug.txt should be created with some diagnostics information. Please upload this file to Oracle Support for further analysis. After you have created this diagnostics file, you should disable logging by setting the USER level profile option listed below:


Name Value
---------------------------------------------
FND: Debug Log Enabled No

Detailed checks
==============

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

15. Enable detailed logging in iAS

Follow the steps in Appendix C to enable detailed logging for iAS.
Review the logs for errors.

16.Temporarily enable iAS to use symbolic links.

Replace all occurrences of

'Option -FollowSymLinks'
for

'Option +FollowSymLinks'
in

$IAS_ORACLE_HOME/Apache/Apache/conf/httpd.conf
and

$IAS_ORACLE_HOME/Apache/Apache/conf/httpd_pls.conf

and bounce apache. If this is the issue, you should then go back and implement the changes via autoconfig. The value from this is controllable from your Apps Context, see s_options_symlinks .
17. Clear the iAS cache.

To clear the jsp & modplsql caches either rename or delete the sub-directories below following directories and restart apache. This will clear out the compiled JSP classes and cached modplsql modules causing them to be automatically recompiled next time they are accessed.

rm -Rf $OA_HTML/_pages/*
rm -Rf $COMMON_TOP/_pages/*
rm -Rf $IAS_ORACLE_HOME/Apache/modplsql/cache/*

Note: Depending on your configuration and patch level all directories may not exist.

18. Check the sqlnet.ora setup in the database Oracle Home.

Edit the

$ORACLE_HOME/network/admin/sqlnet.ora

Is tcp.validnode_checking enabled (i.e. = yes)? If so, make sure the parameter tcp.invited_nodes contains an entry for all the nodes involved in your configuration. This parameter, in conjunction with tcp.validnode_checking determines which clients can connect to the database.

Or you can temporarily disable node checking by removing the tcp.validnode_checking and retest. This is enabled for security reasons.

19. Modify AppsLocalLogin.jsp to trap exceptions.

If you get java error while using the login page like NoClassDefFound or NullPointerException, we need to trap the context of the message.

Backup and edit your:

$OA_HTML/AppsLocalLogin.jsp.

On the line that reads


} catch(Exception e) {}
Change to:

} catch(Exception e) {
} catch(Throwable t) {
System.err.println("OSS: Caught throwable in AppsLocalLogin.jsp : " +t.toString()); t.printStackTrace();
}

Then bounce Apache and reload the page and more detail should show up in the OACoreGroup.0.stderr file.


< dbc="


20. 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.


21. Regenerate JAVA_TOP:

Ensure you have a full backup of your system! To run this process, run adadmin and select Maintain Applications Files then Copy Files to Destinations.

22. Generate database trace and SQL*Net traces.

To set-up SQL*Net Trace on the Web Server set

TRACE_LEVEL_CLIENT = 16
in the

$TNS_ADMIN/SQLNET.ORA

file. This should be in the iAS file system and not in the 8.0.6. Oracle Home. By default the SQL*Net trace file will be called 'sqlnet.trc'.