Troubleshooting Oracle E-Business Suite AccessGate integration

Introduction
This document is intended to try and help troubleshoot the integration of Oracle E-Business Suite
(EBS) with Oracle E-Business Suite AccessGate (EAG)
It is based on the steps in "Integrating
Oracle E-Business Suite Release 12 with Oracle Access Manager 11gR2 (11.1.2) using Oracle EBusiness Suite AccessGate" (Doc ID 1484024.1) however most will apply to earlier versions
NOTE : at this stage this document is unstructured, so work with Oracle Support to identify which
parts are relevant to your situation
NOTE 2 : all scripts mentioned in this document should be downloaded from "Troubleshooting
the E-Business Suite Integration with Oracle Internet Directory (OID) and Oracle Access Manager
(OAM) - Part 2 OAM" (Doc ID 1466171.1)

Table of Contents
Introduction ........................................................................................................................................1
1. Collecting information to verify the integration..............................................................................2
1.1 Fusion Middleware Architecture..............................................................................................2
a. OID server..............................................................................................................................2
b. OAM server............................................................................................................................2
c. OHS and WebGate ................................................................................................................3
d. EBS AccessGate.....................................................................................................................4
1.2 EBS Architecture......................................................................................................................4
1.3 LDAP specific information.......................................................................................................4
1.4 Oracle Access Manager (OAM) specific information..............................................................5
1.5 Webgate and Web Tier specific information.............................................................................5
1.6 EAG specific information.........................................................................................................5
1.7 EBS specific information..........................................................................................................6
1.8 Operating System information.................................................................................................8
1.8.1 General operating system information..............................................................................8
1.8.2 WLS and RDBMS prereqs...............................................................................................8
2. Checks and tests to isolate the issue................................................................................................9
2.1 Gather HTTP headers from Client PC browser.........................................................................9
2.2 Test your OAM setup using the Oracle Access Manager tester tool.......................................9
2.3 Check JDBC connection on EAG Managed Server................................................................10
2.4 Test URLs...............................................................................................................................10
WebGate URLs.........................................................................................................................10
EBS AccessGate URLs.............................................................................................................10
3. Enabling additional debugging .....................................................................................................12
3.1 EAG Logging..........................................................................................................................12
3.2 Turning on Webgate11g tracing..............................................................................................12
3.3 OHS Web Server logging........................................................................................................13
3.4 OAM Debugging....................................................................................................................13
3.5 EBS Logging...........................................................................................................................14
Appendix A : Useful References........................................................................................................16

Page 1

Updated: 2nd April 2014

Troubleshooting Oracle E-Business Suite AccessGate integration

1. Collecting information to verify the integration

1.1 Fusion Middleware Architecture
It is important for us to understand the Fusion Middleware (FMW) setup.
questionaire to confirm this setup

Fill out the following

a. OID server
Database details
Database host name :
Database port :
Service name :
Middle tier details
Middleware Home directory:
WLS product install directory:
(e.g. wlsserver_10.3)
Domain Name:
(e.g. IDMDomain)
Oracle Home Directory :
Oracle Instance Name :
AdminServer Port:
(e.g. 7001)
OID Ports :
(e.g. ldap=3060, ldaps=3131)
wls_ods managed server port:
(e.g. 7005)
- Provide output from the following SQL
SELECT OWNER, VERSION, STATUS, UPGRADED FROM
SCHEMA_VERSION_REGISTRY;

b. OAM server
OAM Server hostname:
Database connect string for OAM repository :
(e.g. db1.example.com:1521/oid.example.com)
Middle tier details
Middleware Home directory:
WLS product install directory:
Page 2

Updated: 2nd April 2014

Troubleshooting Oracle E-Business Suite AccessGate integration

(e.g. wlsserver_10.3)
Oracle Home Directory:
Domain Name:
Domain Location:
(e.g.
/oracle/fmwhome/user_projects/domains)
Admin Server
Name :
(e.g. AdminServer)
Listen Port:
SSL Port : (if used)
Managed Servers
Name:
(e.g. oam_server1)
Listen Port:
SSL Port: (if used)
Node Manager
Listen port:
- Provide output from the following SQL
SELECT OWNER, VERSION, STATUS, UPGRADED FROM
SCHEMA_VERSION_REGISTRY;

c. OHS and WebGate

OHS Hostname:
Webgate Hostname:
OHS installation
Middleware Home directory:
Oracle Home Directory:
Instance Home Location:
Instance Name:
OHS Component name:
Listen ports:
(e.g. http:7777, https:4443)
WebGate installation
Middleware Home directory:
Oracle Home Directory:

Page 3

Updated: 2nd April 2014

Troubleshooting Oracle E-Business Suite AccessGate integration

d. EBS AccessGate
Webgate Hostname:
Middle tier details
Middleware Home directory:
WLS product install directory:
(e.g. wlsserver_10.3)
Domain Name:
Domain Location:
(e.g.
/oracle/fmwhome/user_projects/domains)
Admin Server
Name :
(e.g. AdminServer)
Listen Port:
SSL Port : (if used)
Managed Servers
Name:
(e.g. eag_server1)
Listen Port:
SSL Port: (if used)
Node Manager
Listen port:

1.2 EBS Architecture

We also need to understand the EBS architecture
a. Provide system architecture diagram for the instance concerned. It should include network
devices such as Load Balancers, Proxies and firewalls, as well as showing the servers used for EBS
and port numbers. If no diagram is available, provide a description with these details
b. Provide the output from the SQL script "mzFndNodes.sql"

1.3 LDAP specific information

a. Provide output from the script "ldapsearchOAM.sh" You should run this script from your OID
server
NOTE : you need to first edit this script and change the Instance Specific variables as appropriate
for your instance

Page 4

Updated: 2nd April 2014

Troubleshooting Oracle E-Business Suite AccessGate integration

1.4 Oracle Access Manager (OAM) specific information

a. Provide the output from running the script "mzCollectOAM.sh config" This script will
generate a ".txt" file and also a ".zip" file which should both be provided
This captures just the configuration details. The same script can be run without any arguments to
collect log file information as well, or "mzCollectOAM.sh log" for just the log files
NOTE : you need to first edit this script and change the Instance Specific variables as appropriate
for your instance
b. Provide export of the OAM policy data by following these steps from your OAM server:
cd $OAM_ORACLE_HOME/common/bin
./wlst.sh
wls:/offline>> connect() -- connect to OAM AdminServer port with OAM
Administrator credentials
wls> domainRuntime()
wls> exportPolicy('/tmp/policy_export.xml') - This can be any valid path
wls> exit()

Provide the "/tmp/policy_export.xml" file

c. Provide the output from the following SQL against the OAM repository
SELECT OWNER, VERSION, STATUS, UPGRADED FROM SCHEMA_VERSION_REGISTRY;

1.5 Webgate and Web Tier specific information

a. Provide the output from "mzCollectWG.sh config" This script will generate a ".txt" file and also
a ".zip" file which should both be provided
This captures just the configuration details. The same script can be run without any arguments to
collect log file information as well, or "mzCollectWG.sh log" for just the log files
NOTE : you need to first edit this script and change the Instance Specific variables as appropriate
for your instance

1.6 EAG specific information

a. Provide the output from "mzCollectEAG.sh config" This script will generate a ".txt" file and
also a ".zip" file which should both be provided
This captures just the configuration details. The same script can be run without any arguments to
collect log file information as well, or "mzCollectEAG.sh log" for just the log files
NOTE : you need to first edit this script and change the Instance Specific variables as appropriate
for your instance
b. Provide the log file from the "ant -f txkEBSAuth.xml " run. If this log file is no longer
available, re-deploy EAG using the "mzRunAnt.sh"script and provide the log file and also the script
output files:
Page 5

Updated: 2nd April 2014

Troubleshooting Oracle E-Business Suite AccessGate integration

mzRunAnt*.sh
mzRunAnt*.log
ant*.log

1.7 EBS specific information

a. Provide the output from the script "EAG_PatchesApplied.sql" This confirms the mandatory
patches have been applied
b. Provide the output from "AS10g_profile_options.sql" This provides the OID/OAM integration
profile options to verify
c. Check the eBiz user
i. When prompted enter the username you setup in step "2.1.3. Set Up Necessary Oracle EBusiness Suite Users" from "Oracle E-Business Suite Software Development Kit for Java (includes
AppsDataSource, Java Authentication and Authorization Service) Readme - Patch 9863609" (Doc
ID 974949.1)
REM
REM
START OF SQL
REM
set pagesize 132
set linesize 100
col user_id form 9999999999
col user_name form a30
col start_date form a12
col end_date form a12
col user_guid form a90
col encrypted_foundation_password form a12
col encrypted_user_password form a12
col fax form a15
col description form a20
--Basic user details
-select
user_id,
user_name,
to_char(start_date, 'DD-MON-RRRR') start_date,
to_char(end_date, 'DD-MON-RRRR') end_date,
decode(encrypted_foundation_password, 'EXTERNAL', 'EXTERNAL',
'LOCAL_LOGIN') encrypted_foundation_password,
decode(encrypted_user_password,'EXTERNAL', 'EXTERNAL', 'LOCAL_LOGIN')
encrypted_user_password,
fax,
description,
decode(user_guid,NULL, '*** NULL ***',user_guid) user_guid
from fnd_user
where user_name like upper('&enter_username')
order by user_name
/
--- Check the roles

Page 6

Updated: 2nd April 2014

Troubleshooting Oracle E-Business Suite AccessGate integration

-select user_name, role_name, assigning_role, to_char(start_date,'DD-MON-RR')
start_date, to_char(end_date,'DD-MON-RR') end_date, user_orig_system,
role_orig_system, assignment_reason
from WF_USER_ROLE_ASSIGNMENTS
where user_name = upper('&enter_username')
/
REM
REM
END OF SQL
REM

ii. Use the following command to ensure the specified eBiz user can login succesfully
REM
REM
START OF SQL
REM
-select FND_WEB_SEC.VALIDATE_LOGIN('&enter_username','&enter_password') from
dual
/
REM
REM
END OF SQL
REM

iii. Use the following SQL to gather information about the WLS server entries and compare to the
DBC file
REM
REM
START OF SQL
REM
set echo on
set timing on
set feedback on
set pagesize 132
set linesize 80
col PLATFORM_CODE form a5
col HOST form a20
col DOMAIN form a30
col WEBHOST form a30
col VIRTUAL_IP form a20
col status form a20
col ConcMgr form a8
col Forms form a8
col WebServer form a8
col Admin form a8
col Database form a8
col last_monitored form a40
-select
NODE_NAME,
to_char(CREATION_DATE, 'DD-MON-RR HH24:MI') creation_date,
PLATFORM_CODE,
DESCRIPTION,
decode(STATUS,'Y','ACTIVE','INACTIVE') Status,
decode(SUPPORT_CP,'Y', 'ConcMgr','No') ConcMgr,
decode(SUPPORT_FORMS,'Y','Forms', 'No') Forms,
decode(SUPPORT_WEB,'Y','Web', 'No') WebServer,
decode(SUPPORT_ADMIN, 'Y','Admin', 'No') Admin,

Page 7

Updated: 2nd April 2014

Troubleshooting Oracle E-Business Suite AccessGate integration

decode(SUPPORT_DB, 'Y','Rdbms', 'No') Database,
to_char(LAST_MONITORED_TIME, 'DD-MON-RR HH24:MI:SS') last_monitored,
NODE_MODE,
SERVER_ADDRESS,
HOST,
DOMAIN,
WEBHOST,
VIRTUAL_IP,
SERVER_ID
from fnd_nodes
where NODE_NAME != 'AUTHENTICATION'
order by STATUS, NODE_NAME;
/
REM
REM
END OF SQL
REM

iv. Provide the DBC file generated in "2.1. Configuring AppsDataSource" from "Oracle EBusiness Suite Software Development Kit for Java (includes AppsDataSource, Java Authentication
and Authorization Service) Readme - Patch 9863609" (Doc ID 974949.1)

1.8 Operating System information

1.8.1 General operating system information
For each of your FMW and EBS servers, provide the output from the following scripts
os_stats_<PLATFORM>.sh
mzNodeInfo.sh
i.e. Run on your EBS Middle Tier and Database servers Also run for your FMW OAM,
OHS/Webgate, EAG and OID servers

1.8.2 WLS and RDBMS prereqs

Run the following script on each of your FMW OAM, OHS/Webgate, EAG and OID servers
mzCheckPrereqs.sh

Page 8

Updated: 2nd April 2014

Troubleshooting Oracle E-Business Suite AccessGate integration

2. Checks and tests to isolate the issue

2.1 Gather HTTP headers from Client PC browser
For most issue, it will be required to reproduice the problem whilst gathering HTTP header output
from the Client PC browser session
I now prefer using Fiddler to collect HTTP (works with Internet Explorer and Firefox) information.
You can follow these steps to obtain and use this tool
NOTE: Oracle is not responsible in any way for supporting this tool or for any consequences of
downloading or using it
a. Navigate to the Fiddler home page at http://www.telerik.com/download/fiddler/
b. If you are happy to install Fiddler, then proceed to the download page and install on the PC you
will be using for testing
c. When running the test case, you will need to launch Fiddler to capture the HTTP traffic. This can
be done from the Internet Explorer or Firefox tool menu to launch the program and capture traffic
d. Once the test case is completed, save the captured data from the Fiddler window, by selecting
File--> Save--> Sessions--> In ArchiveZIP Give the archive file a name, then click "Yes" when
prompted "Would you like to archive ALL of the sessions instead" This creates a file with a .saz
extension which you can upload to Oracle Support

2.2 Test your OAM setup using the Oracle Access Manager tester tool
Review chapter 21 "Validating Connectivity and Policies Using the Access Tester" of "Oracle
Fusion Middleware Administrator's Guide for Oracle Access Management 11g Release 2 (11.1.2.2)
for All Platforms" (
http://docs.oracle.com/cd/E40329_01/admin.1112/e27239/tester.htm#AIAAG1954 ) for details of
installing and using this tool, or "Using Access Tester in Oracle Access Manager (OAM) 11g" (Doc
ID 1243086.1)
a. Test the protected resource URL
/ebsauth_[instance]/
where [instance] is the name of your Oracle E-Business Suite instance/
Enter Username and Password and select both the "Authenticate" and "Authorize" buttons
b. Save the status messages to a file and upload this (Disk icon at the bottom of the screen)
Page 9

Updated: 2nd April 2014

Troubleshooting Oracle E-Business Suite AccessGate integration

c. Also use the "save configuration" button (at the top of the screen) and upload the resulting
configuration XML file (Remove the password from this file before uploading)

2.3 Check JDBC connection on EAG Managed Server

Go into WLS Admin Console

http://host.domain.com:7001/console
Services-->JDBC--> Data Sources
Select the JDBC Data Source (the "dataSourceName" when you ran the ant script)
Select the "Monitoring" tab to see the current information and also use the "Testing" option
to confirm connectivity

Provide a screen shot to show the results of this test

2.4 Test URLs

Note 1484024.1 provides certain URLs to test your OAM/Webgate setup at certain points.
Provide HTTP header traces for these URLs, shown below for your convinience

WebGate URLs
Replace the following variables for your WebGate Installation in the URL below:
{WG_PROTOCOL}
{WG_HOST}
{WG_DOMAIN}
{WG_PORT}
No authentication needed
{WG_PROTOCOL}://{WG_HOST}.{WG_DOMAIN}:{WG_PORT}/public/index.html
Authentication required
{WG_PROTOCOL}://{WG_HOST}.{WG_DOMAIN}:{WG_PORT}/index.html
Authentication required
{WG_PROTOCOL}://{WG_HOST}.{WG_DOMAIN}:{WG_PORT}/cgi-bin/printenv

EBS AccessGate URLs

Replace the following variables for your WebGate Installation in the URL below:
{EAG_PROTOCOL}
{EAG_HOST}
{EAG_DOMAIN}
{EAG_MANAGED_SERVER_PORT}
{EBS_CONTEXT}
Page 10

Updated: 2nd April 2014

Troubleshooting Oracle E-Business Suite AccessGate integration

No authentication needed
{EAG_PROTOCOL}://{EAG_HOST}.{EAG_DOMAIN}:
{EAG_MANAGED_SERVER_PORT}/ebsauth_{EBS_CONTEXT}/ssologout_callback
You should see an empty page at this point

Page 11

Updated: 2nd April 2014

Troubleshooting Oracle E-Business Suite AccessGate integration

3. Enabling additional debugging

3.1 EAG Logging
a. In your $MW_HOME/appsutil/accessgate/{EBS_INST} directory copy the
"samples/sampleLogConfig.properties" file to "LogConfig.properties"
b. Edit "LogConfig.properties" file FileHandler.pattern to a suitable directory name
e.g. change from
java.util.logging.FileHandler.pattern = /tmp/mywork/fndauth%u.log

to be
java.util.logging.FileHandler.pattern =
/oracle/fmwhome/appsutil/accessgate/r121prod/logs/fndauth%u.log

NOTE: Use an explicit path rather than using shell variable

Where "/oracle/fmwhome/appsutil/accessgate/r121prod" is the directory you created in step
5.2.1.1 of note 1484024.1
Also make sure the {MW_HOME}/appsutil/accessgate/{EBS_INSTANCE}/logs directory exists
and has write permissions for the user
c. Redeploy your EAG, using the "mzRunAnt.sh" script.
NOTE : you need to first edit this script and change the Instance Specific variables as appropriate
for your instance

3.2 Turning on Webgate11g tracing

This is described in Chapter 10 "Logging WebGate Event Messages" in the "Oracle Fusion
Middleware Administrator's Guide for Oracle Access Management 11g Release 2 (11.1.2.2) for All
Platforms" ( http://docs.oracle.com/cd/E40329_01/admin.1112/e27239/log_wg.htm )
You can also refer "How To Turn On Trace/Logging Oracle Access Manager (OAM)/COREid"
(Doc ID 403118.1)

Stop the Web server the Webgate is running on

Locate the oblog_config_wg.xml for the 11g WG $ORACLE_INSTANCE/config/$
{COMPONENT_TYPE}/$
{COMPONENT_NAME}/webgate/config/oblog_config_wg.xml
NOTE: do not change the oblog_config_wg.xml file located in
$WG_HOME/webgate/ohs/config as this file is not being used
Make a copy of the WG's oblog_config_wg.xml
Turn on tracing for the Webgate
Edit oblog_config_wg.xml
Change the "LOG_THRESHOLD_LEVEL" from WARNING to TRACE

Example:

Change from ParamName ="LOG_THRESHOLD_LEVEL"

Value="LOGLEVEL_WARNING" />

Page 12

Updated: 2nd April 2014

Troubleshooting Oracle E-Business Suite AccessGate integration

Change to ParamName ="LOG_THRESHOLD_LEVEL"
Value="LOGLEVEL_TRACE" />
Change the "BUFFER_SIZE" from 65535 to 1

Example:

Change from ParamName ="BUFFER_SIZE" Value="65535" />

Change to ParamName ="BUFFER_SIZE" Value="1" />

Rename the current oblog.log
Start the Web server

The detailed logging information will go into the oblog.log file

3.3 OHS Web Server logging

a. OHS logging by default is set in httpd.conf to "OraLogSeverity WARNING:32" so this can be
changed if needed
Review the " Managing Oracle HTTP Server Logs" chapter in "Oracle Fusion Middleware
Administrator's Guide for Oracle HTTP Server 11g Release 1 (11.1.1)" which is at
http://download.oracle.com/docs/cd/E15523_01/web.1111/e10144/man_logs.htm#HSADM890
b. Additional logging can be enabled for the WLS calls by editing the file
"$ORACLE_INSTANCE/config/${COMPONENT_TYPE}/$
{COMPONENT_NAME}/mod_wl_ohs.conf"

Add or uncomment the following entries:

Debug ALL

WLLogFile ${ORACLE_INSTANCE}/diagnostics/logs/$
{COMPONENT_TYPE}/${COMPONENT_NAME}/mod_weblogic.log
Restart Apache for this change to take effect
The weblogic.log file will exist and be 0 bytes once apache has restarted
Be aware this log file will get very big if left enabled for any length of time

3.4 OAM Debugging

As described in "Oracle Fusion Middleware Administrator's Guide 11g Release 2 (11.1.2.2)"
( http://docs.oracle.com/cd/E40329_01/core.1112/e28516/logs.htm#ASADM576 ) you can change
the logging levels for OAM using "Fusion Middleware Control" or "WLST"
The wlst method is shown as an example below:
a. Execute wlst.sh script on your OAM server
# cd $ORACLE_HOME/common/bin
# ./wlst.sh

b. Connect to the OAM running server

Page 13

Updated: 2nd April 2014

Troubleshooting Oracle E-Business Suite AccessGate integration

wls:/offline> connect ()
Enter your username :weblogic
Enter your password :<enter password>
Enter your server URL [t3://localhost:7001] :

c. Location changed to domainRuntime tree

wls:/oam_domain/serverConfig>
domainRuntime()

d. Set logger level to TRACE:32

wls:/oam_domain/serverConfig>
setLogLevel(logger="oracle.oam",level="TRACE:32",
persist="0",target="oam_server1")

e. Review the changes

wls:/oam_domain/serverConfig>
listLoggers(pattern="oracle.oam.*",target="oam_server1")

f. Reproduce the issue and capture the logs. The diagnostics logs will be located at:
$DOMAIN_HOME/servers/oam_server1/logs/oam_server1-diagnostic.log
g. Change the log level back to default level after you you have reproduce the issue and collected
the diagnostic log
wls:/oam_domain/serverConfig>
setLogLevel(logger="oracle.oam",level="NOTIFICATION:1",
persist="1",target="oam_server1")

3.5 EBS Logging

a. Shutdown EBS Middle tier services
$ADMIN_SCRIPTS_HOME/adstpall.sh
b. Edit $ORA_CONFIG_HOME/10.1.3/j2ee/oacore/config/oc4j.properties and add the following
entries:
AFLOG_ENABLED=true
AFLOG_LEVEL=statement
AFLOG_MODULE=%
AFLOG_FILENAME=/tmp/FND_statementDebug.log

c. Start just Apache and the OACore JVM services

$ADMIN_SCRIPTS_HOME/adapcctl.sh start
$ADMIN_SCRIPTS_HOME/adoacorectl.sh start
d. Run the testcase to reproduce the issue. (You should normally also capture the HTTP headers
for this testcase)
e. Shutdown OACore and Apache, revert the change to oc4j.properties and then re-start EBS
services normally

Page 14

Updated: 2nd April 2014

Troubleshooting Oracle E-Business Suite AccessGate integration

NOTE: this type of FND logging will generate huge amount of logging for all activities. It is
therefore only suitable for short term diagnostic gathering with a single user performing activities.
Disable this logging as soon as diagnostics have been gathered

Page 15

Updated: 2nd April 2014

Troubleshooting Oracle E-Business Suite AccessGate integration

Appendix A : Useful References

Overview of Single Sign-On Integration Options for Oracle E-Business Suite (Doc ID
1388152.1)

Integrating Oracle E-Business Suite Release 12 with Oracle Access Manager 11gR2 (11.1.2)
using Oracle E-Business Suite AccessGate (Doc ID 1484024.1)

Troubleshooting the E-Business Suite Integration with Oracle Internet Directory (OID) and
Oracle Access Manager (OAM) - Part 1 OID (Doc ID 1450231.1)

How To Turn On Trace/Logging Oracle Access Manager (OAM)/COREid (Doc ID

403118.1)

Using Access Tester in Oracle Access Manager (OAM) 11g (Doc ID 1243086.1)

Page 16

Updated: 2nd April 2014