OracleAppsDBAKK1: Tips and Queries for Troubleshooting Advanced Topologies (Doc ID 364439.1)

In this Document

APPLIES TO:

Oracle E-Business Suite Technology Stack - Version 11.5.10.2 to 12.2.2 [Release 11.5.10 to 12.2]
Information in this document applies to any platform.

PURPOSE

The Troubleshooting Guide is provided to assist in debugging advanced topologies and configurations as described in the following notes that are recommended prerequisites for this note:

   Note:287176.1-DMZ Configuration with Oracle E-Business Suite 11i
   Note:217368.1-Advanced Configurations and Topologies for Enterprise Deployments of E-Business Suite 11i
   Note:380489.1-Using Load-Balancers with Oracle E-Business Suite Release 12
   Note:380490.1-Oracle E-Business Suite Release 12 Configuration in a DMZ

This is version 3.0 of this document which will always be a work in progress. Given the complexity of these issues, it is nearly impossible to be comprehensive enough to cover every situation. Instead, the approach here is to provide well documented analysis tools along with some example applications of these tools to illustrate the fundamental concepts that can then be applied to any real-world instance. While this note was written primarily with 11i in mind, the concepts and queries do apply to release 12 instances as well.

The format in this revision of the paper will be to offer the troubleshooting query and then follow each query with an example output and a substantial explanation of the meaning of the query results. As written, these queries can be expected to do as the accompanying explanation explains, but these explanations and queries are offered with the full intention that the reader will embrace the concept as illustrated and will then modify the queries to suit their individual purposes.

TROUBLESHOOTING STEPS

The Importance of Topology Diagrams

Upgrading from a simple single-tier or even multiple tier environment to a DMZ or other advanced topology is extremely difficult without some sort of preconceived plan in writing. When requesting support for an advanced configuration, customers should expect that a request for a topology diagram will be made before any advice beyond the generic can be offered. The DMZ and other advanced topology configurations are becoming more popular each day and it seems that for each TAR worked we find that many customers have very differing ideas on what constitutes a good DMZ. For this reason the first thing that I always ask for is a simple drawing that describes the proposed network architecture.

The logical drawing should show each machine that is part of the instance, to include each server, firewall, and hardware load balancer (if any) with each described by their name, alias (if any), IP address, and the type of node installed (database, apache, forms, web, etc.) with appropriate version information. In general, the drawing should look something like those featured in Note:287176.1 with appropriate labels specific to the customer configuration. The problems most often faced are configuration related and it is not possible to know from the traces and configuration files where the functionality goes awry without a previous understanding of what it SHOULD look like. When troubleshooting, we start with the drawing and confirm that the various configuration parameters match the drawing.

The figure above is an example drawing based upon figure F4 of Note:287176.1 and will be used as the example testcase for all of the queries that follow. These drawings don't have to be a major effort created with expensive utilities; I simply created the above drawing with Microsoft paint after scratching the icons directly out of Note:287176.1. The important thing is that the drawing describe ever major component by its role, location in the topology, and its proper name.

In this drawing, hostbe is a single tier install of 11.5.10.2 and hostemt is a clone of the hostbe middle tier to be used as an external tier. The drawing shows the logical blocks; the internal tier is clearly indicated as a separate logical entity despite being physically on the same server as the database. Also note that the majority of the patch levels in this drawing are superceded at this date. This DMZ instance is therefore not an ideal example, just a working example.

The FND_NODES query

The FND_NODES query is usually the logical first place to start when troubleshooting an advanced topology such as a DMZ. An example query that cleanly extracts important FND_NODES information follows and includes some sample output for discussion.

When uploading results of the queries in this note to a service request, ALWAYS run them from sqlplus as the apps user, spool the results of the queries to a file, and upload the resulting, plain-text spool file. NEVER paste the results into the service request itself as that results in an entry with a deluge of poorly formatted data.

FND_NODES Query

spool fnd_nodes 
set pagesize 50
col node_name format a15
col server_id format a8
col server_address format a15
col platform_code format a4
col webhost format a12
col domain format a20
col virtual_ip format a12
set linesize 132
select
  node_id,
  platform_code,
  support_db D,
  support_cp C,
  support_admin A,
  support_forms F,
  support_web W,
  node_name,
  server_id,
  server_address,
  domain,
  webhost,
  virtual_ip
from
  fnd_nodes
order by node_id;

Example Output of FND_NODES Query

NODE_ID PLAT D C A F W NODE_NAME     SERVER_ID SERVER_ADDRESS DOMAIN        WEBHOST
------- ---- - - - - - ------------- --------- -------------- ------------- -----------
   4066 46   Y Y Y Y Y NODEBE    102B7C50  10.1.2.3       domain.com host. 
                                     65BF2761                               domain.c
                                     E040018A                               om
                                     439451E5
                                     16313498
                                     31994113
                                     06835324
                                     76801476

   4067 46     N N N N AUTHENTICATION SECURE   *

   4068 46   N N N Y Y NODEEMT   11473885 100.1.2.3       domain.com host
                                     65677803                               .domain.
                                     E040018A                               com
                                     439450E9
                                     29327317
                                     51161446
                                     91712165
                                     64996016

Understanding the FND_NODES Query Results

The FND_NODES table is the primary source of information used by AutoConfig in determining the names and types of nodes that make up the release 11i and 12 instance and therefore the FND_NODES table is often the best, first place to look. You can quickly tell how many servers a customer has and what they are configured to do and this should match their topology diagram. If it doesn't match the diagram, this is the first hint at a configuration error.

Important Columns

Node_Id - a unique number for identifying the node.
This is used by the profile options tables to identify the server when using Server and/or ServResp profile options as discussed later in this note.

Platform_Code - 2=HPUX, 46=Linux, 319=AIX 32-bit, 453=Solaris, 912-Windows, etc.

The Node type columns - each node can be a combination of these
  D support_db -- this node has a database
  C support_cp -- this node hosts the concurrent managers
  A support_admin -- this is an administration tier
  F support_forms -- this is a forms server tier (and likely a web tier as well)
  W support_web -- this is a web tier (Apache)

Node_Name - Node/Machine Name.
This may also be "Authentication" which is a dummy row to support server_id authentication, but typically this is just the server's hostname. This should not be an IP address.

Server_ID - this is a semi-randomly generated string created by the AdminAppServer utility to uniquely identify the server when node_name is a valid server name. The exception to this rule is when node_name is the dummy row for authentication and then the value of server_id for the authentication row may be ON, OFF, or SECURE. If Authentication is set to SECURE then this string must match the server_id string in each server's DBC files. The DBC file location is defined by the environment variable FND_SECURE and is typically $FND_TOP/secure.

Server_Address - the IP address of the server or, optionally, the IP address of the relevant reverse proxy server. This is normally determined automatically by AutoConfig, but can be overridden and specified manually by the AutoConfig parameter "s_server_ip_address" in the AutoConfig XML context file. This new variable first appeared in patch "4709948 - TXK (FND) AutoConfig Template Rollup Patch M (April 2006)".

A common error is to have an incorrectly formatted /etc/hosts file that has only the loopback address. Since this table has the very reasonable uniqueness constraint on server_address, you cannot use "127.0.0.1" here. If another server tries to use 127.0.0.1, it will have only a partially formed (invalid) entry in this table.

Similarly, only a partially formed (invalid) entry will be formed when creating a virtually external tier and attempting to use the same IP address for both the internal server and the virtually external server. For more information of virtually external tiers, see Note:438744.1-Case History: Implementing a Reverse Proxy Alone in a DMZ Configuration

Domain - the TCP/IP domain of the server

Webhost - the fully qualified domain name of the server. Required for iRecruitment (Bug:3725573) and others.

The Trust Query

As discussed in Note:287176.1, each node listed in the fnd_nodes table can be set to be an External, Normal, or Administrative tier using the system profile option "Node Trust Level" (internal name NODE_TRUST_LEVEL). Similarly, you can set externally available responsibilities at the responsibility level using the system profile option "Responsibility Trust Level" (internal name APPL_SERVER_TRUST_LEVEL).

The following query provides a handy dump of these "trust" profile options (run from sqlplus as apps):

Trust Query

spool trust
set linesize 132
set pagesize 100
col "Level Where Option Is Set" format a60
col "Profile Name" format a30
col value format a10
break on "Profile Name";
select
  p.user_profile_option_name "Profile Name",  
  decode(v.profile_option_value,
    1, 'Admin',
    2, 'Normal',
    3, 'External',
    'Unknown') Value,
  decode(v.level_id,
    10001, 'SITE',
    10002, (select 'App:'||a.application_short_name from fnd_application a
            where a.application_id = v.level_value),
    10003, (select 'Resp:'||f.RESPONSIBILITY_name||' ('||responsibility_key||')' from fnd_responsibility_vl f
            where f.responsibility_id = v.level_value),
    10004, (select 'User:'||u.user_name from fnd_user u
            where u.user_id = v.level_value),
    10005, (select 'Server:'||n.node_name from fnd_nodes n
            where n.node_id = v.level_value),
    10006, (select 'Org:'||org.name from hr_operating_units org
            where org.name = v.level_value),
    'NOT SET') "Level Where Option Is Set"
from
  fnd_profile_options_vl p,
  fnd_profile_option_values v
where
  p.profile_option_id = v.profile_option_id (+)
  and p.application_id = v.application_id (+)
  and p.profile_option_name like upper('%TRUST%')
order by 1,2,3 desc;

Example Output of Trust Query

Profile Name                VALUE      Level Where Option Is Set
--------------------------- ---------- --------------------------------------------------------
Node Trust Level            Normal     SITE
                            Admin      Server:OTHERMT
                            External   Server:NODEEMT
Responsibility Trust Level  Normal     SITE
                            External   Resp:iRecruitment External Candidate (IRC_EXT_CANDIDATE)
                            Admin      Resp:System Administration (SYSTEM_ADMINISTRATION)
                            Admin      Resp:System Administrator (SYSTEM_ADMINISTRATOR)

Understanding the Trust Query Results

In the above example, the external server sitting in the DMZ is defined to be NODEEMT because the Node_Trust_Level is set to External at the server level for the server named NODEEMT. Similarly, the only responsibility that will be available to users logging into NODEEMT is iRecruitment External Candidate.

On a new tier identified as "OTHERMT", which didn't make it to the topology diagram yet, is an example of an Administrative tier. It is possible to restrict Administrative responsibilities to a specific tier. In this example, if a user logs in to one of the normal tiers, neither the responsibility "System Administration" nor "System Administrator" will appear in the responsibility list even if the user has those responsibilities assigned. As setup in this example, the user must have these responsibilities AND be logged in on the server named "OtherMT" to see and use them.

There is currently a bug open on the ADMIN trust level. While it is proper that the Admin responsibilities (such as in this example) will not appear on a normal or external tier, they will appear on the admin tier, but an attempt to make use of a forms-based admin responsibility (such as System Administrator) will fail shortly after forms is launched with the error of "Sorry, no valid responsibilities are available [OK]".

Bug:7699618-ISSUE WHEN SETTING RESPONSIBILITY TRUST LEVEL (FORMS BASED)

Accidentally Locking Oneself Out

Note in the above example, that the Node Trust Level is set to Normal at the Site level. This is ideal, because all responsibilities are "Normal" by default. In many customer instances, it is entirely common to have the Node Trust Level set to external only for the external tiers and leave all other tiers to default to the Site level value of Normal. The problem arises when accidentally setting the Node Trust level to External at the Site level when no servers are explicitly set to be normal since this results in ALL web nodes becoming External nodes where only External responsibilities are allowed. If ALL nodes are External, then NONE of the nodes will allow System Administration tasks, including changing profile options back.

To avoid this problem, it is a good practice to have at least one server explicitly marked as either Admin or Normal. If it is already too late and you have locked yourself out of the instance, the only practical solution is to update the profile option via sqlplus as the apps user.

To reset the Node Trust Level back to Normal at the Site level, use the following PL/SQL:

set serveroutput on
DECLARE
  stat boolean;
BEGIN
  stat := FND_PROFILE.SAVE('NODE_TRUST_LEVEL', '2', 'SITE');
  IF stat THEN
    dbms_output.put_line( 'Stat = TRUE - profile updated' );
  ELSE
    dbms_output.put_line( 'Stat = FALSE - profile NOT updated' );
  END IF;
commit;
END;
/

Accidentally setting ALL servers, one by one, to External will cause a similar lockout situation. The following PL/SQL will set the given server back to Normal. The Node_ID can be obtained from the fnd_nodes query at the top of this note. Note that, as written, this does not check that the Node_ID entered is a valid Node_ID. If you enter a Node_ID that is not valid, you will create an orphaned profile option. Orphaned profile options are discussed later in this paper.

set serveroutput on
DECLARE
  stat boolean;
BEGIN
  stat := FND_PROFILE.SAVE('NODE_TRUST_LEVEL', '2', 'SERVER', &Node_ID);
  IF stat THEN
    dbms_output.put_line( 'Stat = TRUE - profile updated' );
  ELSE
    dbms_output.put_line( 'Stat = FALSE - profile NOT updated' );
END IF;
commit;
END;
/

A Responsibility Query

Tailing off the previous discussion of the Trust Query, above, an important point to reiterate is that a user can be assigned any number of responsibilities, but when logging in to an external tier, that user will see ONLY those responsibilities that are declared external and then only if that user has been assigned one of those external responsibilities.

A common problem is to miss the forest for the trees and not understand why a user has no responsibilities available. The following query will list the overall responsibilities that have been assigned to a user that can be compared with the list of responsibilities declared as external as seen in the trust query.

Responsibility Query

undefine USER_NAME_IN_UPPER_CASE
col "Responsibility Name" format a40
select
  urg.SECURITY_GROUP_ID SecGID,
  urg.RESPONSIBILITY_ID RespID,
  urg.RESPONSIBILITY_APPLICATION_ID RespAppID,
  resp.RESPONSIBILITY_NAME "Responsibility Name"
from
  fnd_user_resp_groups urg, fnd_responsibility_vl resp
where
  urg.responsibility_id = resp.responsibility_id
  and urg.responsibility_application_id = resp.application_id
  and urg.user_id = (select user_id from fnd_user where user_name = '&&USER_NAME_IN_UPPER_CASE')
  and urg.security_group_id = 0
union
select
  urg.SECURITY_GROUP_ID SecGID,
  urg.RESPONSIBILITY_ID RespID,
  urg.RESPONSIBILITY_APPLICATION_ID RespAppID,
  resp.RESPONSIBILITY_NAME || sec.security_group_name name
from fnd_user_resp_groups urg, fnd_responsibility_vl resp,
  fnd_security_groups_vl sec
where
  urg.responsibility_id = resp.responsibility_id
  and urg.responsibility_application_id = resp.application_id
  and urg.security_group_id = sec.security_group_id
  and urg.user_id = (select user_id from fnd_user where user_name = '&USER_NAME_IN_UPPER_CASE')
  and urg.security_group_id != 0;

Example Output of Responsibility Query

Enter value for user_name_in_upper_case: MISTERTESTER

    SECGID     RESPID  RESPAPPID Responsibility Name
---------- ---------- ---------- ----------------------------------------
         0      20419          0 Application Developer
         0      20420          1 System Administrator
         0      20872        178 System Administration
         0      53981          1 Applications Administration  

Understanding the Responsibility Query Results

The query simply lists, in this case, the responsibilities that are assigned to the user named "MisterTester". Notice that this user has quite a few sensitive responsibilites that are available when logging into a "normal" tier, but when logging into an external tier this user has "No Active Responsibilities" and therefore cannot use any of them. In this paper's example, there are only two responsibilities that have been declared as external (iReceivables External Vision UK and iStore) and since MisterTester has neither of these he gets the following message in the Applications Home Page when logging in from an external tier:

"There are no active responsibilities available for this user"

The Enabled Query

Note:287176.1 requires that certain specific profile options need to be enabled at the server level and some at the new ServResp level. The following query is a very good check to make certain that the key profile options have been set to the appropriate hierarchy and that the flags are correctly set to allow updates.

Enabled Query

col profile_option_name format a32
col SITE format a4
col APPL format a4
col RESP format a4
col USER format a4
col SRVR format a4
col ORG format a3
col svrp format a4
set pagesize 100
select unique profile_option_name,
  hierarchy_type "Type",
  WRITE_ALLOWED_FLAG,
  READ_ALLOWED_FLAG,
  SITE_ENABLED_FLAG || SITE_UPDATE_ALLOWED_FLAG "Site",
  SERVERRESP_ENABLED_FLAG || SERVERRESP_UPDATE_ALLOWED_FLAG "SvRp",
  SERVER_ENABLED_FLAG || SERVER_UPDATE_ALLOWED_FLAG "Srvr",
  USER_ENABLED_FLAG || USER_UPDATE_ALLOWED_FLAG "User",
  RESP_ENABLED_FLAG || RESP_UPDATE_ALLOWED_FLAG "Resp",
  ORG_ENABLED_FLAG || ORG_UPDATE_ALLOWED_FLAG "Org",
  APP_ENABLED_FLAG ||APP_UPDATE_ALLOWED_FLAG "Appl"
from
  fnd_profile_options
where
  profile_option_name in
  ('APPS_WEB_AGENT','APPS_SERVLET_AGENT','APPS_JSP_AGENT','APPS_FRAMEWORK_AGENT',
   'ICX_FORMS_LAUNCHER','ICX_DISCOVERER_LAUNCHER','ICX_DISCOVERER_VIEWER_LAUNCHER',
   'HELP_WEB_AGENT','APPS_PORTAL','CZ_UIMGR_URL','ASO_CONFIGURATOR_URL',
   'QP_PRICING_ENGINE_URL','TCF:HOST')
   or hierarchy_type='SERVER'
   or hierarchy_type='SERVRESP'
order by hierarchy_type,profile_option_name; 

Example Output of Enabled Query

PROFILE_OPTION_NAME              Type     W R Site SvRp Srvr User Resp Org Appl
-------------------------------- -------- - - ---- ---- ---- ---- ---- --- ----
FND_FUNCTION_VALIDATION_LEVEL    SERVER   Y Y YY   NN   YY   NN   NN   NN  NN
FND_VALIDATION_LEVEL             SERVER   Y Y YY   NN   YY   NN   NN   NN  NN
NODE_TRUST_LEVEL                 SERVER   Y Y YY   NN   YY   NN   NN   NN  NN
OKS_SMTP_DOMAIN                  SERVER   Y Y YY   NN   YY   YN   NN   NN  NN
UMX_REGISTER_HERE_HTMLPARAMS     SERVER   Y Y NN   NN   YY   NN   NN   NN  NN
UMX_REGISTER_HERE_REGPARAMS      SERVER   Y Y NN   NN   YY   NN   NN   NN  NN
UMX_REGISTER_HERE_REG_SRV        SERVER   Y Y NN   NN   YY   NN   NN   NN  NN
APPS_FRAMEWORK_AGENT             SERVRESP Y Y YY   YY   NN   YY   NN   NN  NN
APPS_JSP_AGENT                   SERVRESP Y Y YY   YY   NN   YY   NN   NN  NN
APPS_PORTAL                      SERVRESP Y Y YY   YY   NN   YY   NN   NN  NN
APPS_SERVLET_AGENT               SERVRESP Y Y YY   YY   NN   YY   NN   NN  NN
APPS_WEB_AGENT                   SERVRESP Y Y YY   YY   NN   YY   NN   NN  NN
ASO_CONFIGURATOR_URL             SERVRESP Y Y YY   YY   NN   YY   NN   NN  NN
CZ_UIMGR_URL                     SERVRESP Y Y YY   YY   NN   YY   NN   NN  NN
HELP_WEB_AGENT                   SERVRESP Y Y YY   YY   NN   YY   NN   NN  NN
ICX_DISCOVERER_LAUNCHER          SERVRESP Y Y YY   YY   NN   YY   NN   NN  NN
ICX_DISCOVERER_VIEWER_LAUNCHER   SERVRESP Y Y YY   YY   NN   YY   NN   NN  NN
ICX_FORMS_LAUNCHER               SERVRESP Y Y YY   YY   NN   YY   NN   NN  NN
QP_PRICING_ENGINE_URL            SERVRESP Y Y YY   YY   NN   YY   NN   NN  NN
TCF:HOST                         SERVRESP Y Y YY   YY   NN   YY   NN   NN  NN

20 rows selected.

Understanding the Enabled Query Results

Version 1.0 of this note had this query showing just the hierarchy type plus whether or not the server and ServResp levels were enabled and updatable. This version of the note expands that query with a more complex appearance. The resulting matrix of Y's and N's from a customer's instance should be similar to what is posted above. The meaning of the columns can easily be determined from the query above, but the main goal here is to show the pattern of Y's versus N's as an example for comparison.

As described in the long discussion of profile options at the server versus ServResp hierarchy section below, the server and ServResp hierarchies should be enabled and updatable only at certain levels. When troubleshooting, it is best to confirm that ONLY these valid levels (as shown in the example output) are available for the key profile options used in the more advanced topologies and that the enabled and updatable flags haven't been changed by some errant twiddling. Similarly, since this configuration is sensitive to what server a user logs into it is useful to know what profile options are under the server hierarchy and if those are also properly accessible to AutoConfig. This should always be the case, but behind the scenes there is often someone trying to work beyond the supported notes in an effort to force a configuration to work in an unnatural way.

A key reason why ServResp hierarchy is required for advanced topologies such as DMZ is that profile option values set at the server level are NOT controlled by AutoConfig, but ServResp values are. As you work through Note:287176.1, settings for s_webentry_url, s_webentry_host, etc. in the AutoConfig context file SHOULD propagate to the web related profile options after running AutoConfig, but AutoConfig will not set these values if your web related profile options are not using ServResp hierarchy and then your DMZ instance will not work unless you take on the burden of setting the profile options manually (errant twiddling).

Understanding the Server and ServResp Hierarchy

As shown above, the web like profile options (options with values like "http://...") in a DMZ configuration tend to be defined under either the SERVER level hierarchy or the SERVRESP level hierarchy. Before providing a script to display the settings of every profile option at each level, a discussion of how to interpret these profile option levels is required for understanding.

Server Hierarchy

Under the server level hierarchy, the profile options can be set at only the following levels:

Site
Server
User

The SITE level is essentially the default value if no other level applies.

The SERVER level will override the value set at the SITE level. This new feature is particularly useful in that it now matters what server a user logs into. If a user logs into Server-A, his activities can be controlled by the profile options that are specific to Server-A and may be very different from those of Server-B. This is ideal in a DMZ configuration.

The USER level overrides both the SITE and SERVER level.

Based upon this simple, outline explanation the site, responsibility, user, and server profile options are fairly straightforward to interpret when running the Profiles Query, below, for listing all web like profile options and where they are set. When evaluating the value of the profile option, the user level takes precedence over all other levels and the site level is only used when the settings at the other levels do not apply to the current user's username or server. In contrast, the ServResp profile option algorithm is a bit more complex.

Note that if you are somehow (refer to the enabled query, above) able to set a value at the responsibility level, but the hierarchy type is set to server, that responsibility level value will be ignored. Server hierarchy is only meant to look at the user, server, and site level.

ServResp Hierarchy

In contrast to server hierarchy, the ServResp profile option hierarchy is a hybrid combination of the server level and the responsibility level and is especially useful when you want to specify a specific responsibility to be available only on a specific server (a functionally directed load as described in Note:287176.1) or when you simply want a specific server to behave in a specific way. This is typical in a DMZ configuration with administrative, normal, and external servers which need to act differently.

The value of the profile option under the ServResp hierarchy is derived by a specific set of rules as defined in internal Bug:3824790 and discussed below. When a profile option is setup to have the ServResp hierarchy, it can only (legally) be set at one of the following three levels:

Site
Server/Responsibility (ServResp)
User

The middle "Server/Responsibility" level is a combination of responsibility and server. Either or both of the responsibility or server components may be specific values, or may be the "default" value. For purposes of evaluating "default" matches, the server is considered to be at a higher level (and less specific) than the responsibility so values specified at the responsibility level will override values specified only at the server level. When evaluating profile values at this "Server/Responsibility" level, the values of both the level_value/level_application_value pair and level_value2 columns are considered together. If no overriding value is specified at the user level, the algorithm will first look for a specific match for both responsibility and server level values. If no such match is found, it will next look for a row matching the responsibility and with "default" for the server level. If no such match is found in that comparison, it will next look for a row matching the server with "default" for the responsibility level. If no such match is found there either, it will continue up the hierarchy to the Site level.

For example:
Suppose you have a profile set to the values A, B, C, D, E, F, G, and H at the various levels described in the following table:

With the above profile option set as above, the following combinations would be interpreted as
follows for the reasons stated:

The Profile Options Query

Based upon the above discussion, the following script will produce a nice report detailing where all of the URL type profile options are set and what they are set to. Seeing these profile option values at once in a report format is much easier than picking through them one by one from within forms (System Administrator/Profiles/System). As always, it is best to cut and paste this into sqlplus as apps and create a simple text based spool file.

As written, this query will only show the profile options of general interest to a DMZ configuration. To show ALL profile options, simply omit the boldfaced section of the where clause. This is sometimes very useful, but will produce a very large spool file. The last line of the query (language='US) reduces the result set by showing the profile options only in US English. Removing this line will show the profile option values in all languages available on the system which may be beneficial for those who prefer to read the profile option names in a language other than English. The profile option values, given the choice of profile options, are language neutral and are mostly URLs.

Profiles Options Query

set linesize 132
set pagesize 132
col NAME format A40
col LEVEL_SET format a15
col CONTEXT format a20
col VALUE format A20 wrap
col Server format a10
col resp format a8 wrap
col application format a10

break on NAME

select
  '('||language||')-'||n.user_profile_option_name NAME,
    decode(v.level_id,
           10001, 'Site',
           10002, 'Application',
           10003, 'Responsibility',
           10004, 'User',
           10005, 'Server',
           10006, 'Organization',
           10007, 'ServResp',
                  'Undefined') LEVEL_SET,
    decode(to_char(v.level_id),
           '10001', '',
           '10002', app.application_short_name,
           '10003', rsp.responsibility_key,
           '10004', usr.user_name,
           '10005', svr.node_name,
           '10006', org.name,
           '10007', (select n.node_name
                     from fnd_nodes n
                     where n.node_id=level_value2)
                     ||'/'||
                     (decode(v.level_value,
                      -1,'Default',
                      (select responsibility_key
                       from fnd_responsibility
                       where responsibility_id=level_value))),
                  v.level_id) "CONTEXT",
     v.profile_option_value VALUE
  from
    fnd_profile_options p,
    fnd_profile_option_values v,
    fnd_profile_options_tl n,
    fnd_user usr,
    fnd_application app,
    fnd_responsibility rsp,
    fnd_nodes svr,
    hr_operating_units org
  where
    p.profile_option_id = v.profile_option_id (+)
    and p.profile_option_name = n.profile_option_name
    and ((upper(v.profile_option_value) like '%HTTP%')
        or p.profile_option_name in
             ('APPS_WEB_AGENT','APPS_SERVLET_AGENT','APPS_JSP_AGENT','APPS_FRAMEWORK_AGENT',
              'ICX_FORMS_LAUNCHER','ICX_DISCOVERER_LAUNCHER','ICX_DISCOVERER_VIEWER_LAUNCHER',
              'HELP_WEB_AGENT','APPS_PORTAL','CZ_UIMGR_URL','ASO_CONFIGURATOR_URL',
              'QP_PRICING_ENGINE_URL','TCF:HOST','NODE_TRUST_LEVEL','APPL_SERVER_TRUST_LEVEL','APPS_SSO')
        or hierarchy_type='SERVER'
        or hierarchy_type='SERVRESP')    and usr.user_id (+) = v.level_value
    and rsp.application_id (+) = v.level_value_application_id
    and rsp.responsibility_id (+) = v.level_value
    and app.application_id (+) = v.level_value
    and svr.node_id (+) = v.level_value
    and org.organization_id (+) = v.level_value
    and language = 'US'
order by name, v.level_id;

Example Output of Profiles Query (small excerpt)

NAME                                LEVEL_SET     CONTEXT              VALUE
----------------------------------- ------------- -------------------- --------------------
(US)-Application Framework Agent    Site                               http://host
                                                                       .domain.com:8006

                                    ServResp      NODEEMT/Default  http://host.
                                                                       domain.com:8005

                                    ServResp      NODEBE/Default   htp://host
                                                                       .domain.com:8005

(US)-Node Trust Level               Site                               2
                                    Server        NODEEMT          3

Understanding the Profiles Query

In the above profile options query example, each row is a specific value of a specific profile option at a specific level explained by each of the four columns:

NAME - The name of the profile option prefixed with the NLS language code.

LANG NLS_LANGUAGE
---- ------------------------
AR   ARABIC
D    GERMAN
E    SPANISH
EG   EGYPTIAN
ESA  LATIN AMERICAN SPANISH
F    FRENCH
FRC  CANADIAN FRENCH
GB   ENGLISH
IW   HEBREW
JA   JAPANESE
KO   KOREAN
US   AMERICAN
ZHS  SIMPLIFIED CHINESE
ZHT  TRADITIONAL CHINESE

(This is a short version of select language_code, nls_language from fnd_languages;)

As written, the profiles query grabs all that is readily apparent to be important for DMZ and hardware load balancing. It currently shows all of the profile values of the profiles mentioned by Note:287176.1 and every profile option set at a server and ServResp level.

LEVEL_SET - The level where the specific value of the specific profile option comes from i.e.; site, application, responsibility, user, server, org, ServResp.

CONTEXT - The context of the level_set. For example, if the level_set is responsibility, this column will show the specific responsibility and for server it will show the server's name. In the more complex case of ServResp, this column will show the name of the server plus the responsibility it is tied to for the ServResp evaluation explained above.

VALUE - The raw value of the profile option. As an example, the query result shows "Node Trust Level", which can be a value of 1, 2, or 3. The meaningful value of "Node Trust Level" is attainable from the above Trust query which translates the 1, 2, 3 to admin, normal, external.

Setting ServResp Profile Options Manually

The way Note:287176.1 is setup, you should not need to manually set the ServResp level profile options. As you work your way through section five of the note, you find that section 5.2 directs you to declare which servers are internal and which are external. AutoConfig will handle setting the profile options from there using a script such as afwebprf.sql (buried under a stack of calls).

After running the txkChangeProf.sql from Note:287176.1 with the SERVRESP argument, you won't be able to see the profile options in the forms unless you have applied the updated forms patch. The one-off patch that updates the forms so that you can see and manipulate SERVRESP hierarchy based profile options is "4733943-One-off ARU for 4240917: SERVRESP UI ENHANCEMENTS FOR FNDPOMPV, FNDPOMPO, FNDPOMSV".

To see the ServResp column in the successive form you must specify both a Responsibility and a Server.

Orphaned Profile Options

As another troubleshooting point, it is often helpful to consider orphaned profile options as described in Bug:4858843. As discussed earlier, the server (and therefore ServResp) profile option hierarchies do not store the server name in the profile option tables. Instead, the server is identified by its node_id as listed in the fnd_nodes table. This has the chance to cause problems in cloned instances.

Per Bug:4858843, a customer starts with an original instance and sets up the various profile options at the Server/ServResp level to implement a DMZ (Note:287176.1). The current revision of Note 287176.1 sets the appropriate profile options using the script afwebprf.sql via AutoConfig. With the original instance working well, they now clone the instance, run FND_CONC_CLONE.SETUP_CLEAN, and finally run AutoConfig on the new instance with the reasonable expectation that the new instance's DMZ configuration will work well. At this point they have new nodes defined in the fnd_nodes table and new Server/ServResp level profile options with the names of the new nodes that make up the cloned instance. Unfortunately, they also have the original, but now orphaned, profile option values for the nodes of the original instance. These orphaned profile options are Server/ServResp level profile options that refer to node_id's that were removed from the fnd_nodes table by the FND_CONC_CLONE.SETUP_CLEAN and still linger, somewhat inaccessible, in the profile option value tables.

The query below is meant only to show if the instance has orphaned profile options.

Orphans Query

set pagesize 66
set linesize 132
col "Effected Profile Name" format a40
col "Orphaned ID(s)" format a14
Break on "Effected Profile Name"
select
  p.Profile_option_name "Effected Profile Name",
  decode(v.level_value, -1,'',v.level_value)||v.level_value2 "Orphaned ID(s)",
  decode(v.level_id,
  10005, 'Server',
  10007, 'ServResp',
  'Other') "Level"
from
  fnd_profile_options p,
  fnd_profile_option_values v,
  fnd_profile_options_tl n
where
  p.profile_option_name = n.profile_option_name
  and p.profile_option_id = v.profile_option_id (+)
  and ( /* check Server level */
  (
    v.level_id=10005
    and v.level_value > 0
    and v.level_value
    not in ( select f.node_id from fnd_nodes f )
  )
  or /* check ServResp level */
  (
    v.level_id=10007 and (v.level_value2 is not null)
    and (v.level_value2 > 0)
    and v.level_value2
    not in
    (
      select
        f.node_id
      from
       fnd_nodes f
    )
  )
)
order by p.Profile_option_name;

Example Output of Orphans Query

Effected Profile Name                    Orphaned ID(s) Level
---------------------------------------- -------------- --------
APPS_FRAMEWORK_AGENT                     5067           ServResp
                                         5065           ServResp
APPS_JSP_AGENT                           5065           ServResp
                                         5067           ServResp
APPS_PORTAL                              5065           ServResp
                                         5067           ServResp
APPS_SERVLET_AGENT                       5065           ServResp
                                         5067           ServResp
APPS_WEB_AGENT                           5065           ServResp
                                         5067           ServResp
ASO_CONFIGURATOR_URL                     5065           ServResp
                                         5067           ServResp
CZ_UIMGR_URL                             5065           ServResp
                                         5067           ServResp
FND_FUNCTION_VALIDATION_LEVEL            5067           Server
HELP_WEB_AGENT                           5067           ServResp
                                         5065           ServResp
ICX_DISCOVERER_LAUNCHER                  5065           ServResp
                                         5067           ServResp
ICX_DISCOVERER_VIEWER_LAUNCHER           5065           ServResp                                         
                                         5067           ServResp
ICX_FORMS_LAUNCHER                       5067           ServResp
                                         5065           ServResp
NODE_TRUST_LEVEL                         5067           Server
QP_PRICING_ENGINE_URL                    5067           ServResp
                                         5065           ServResp
TCF:HOST                                 5065           ServResp
                                         5067           ServResp

28 rows selected.

Ideally this will return "no rows selected".

Understanding the Orphans Query

Ideally the above query will return "no rows selected" and indicate that the instance has no orphaned profile options. Otherwise, it will show the node_id that is present at the Server level_value or ServResp level_value2 that is NOT tied to a value in FND_NODES. In the above example, we see that two nodes were orphaned and that this effected 14 different profile options. These profile option values cannot be removed via the forms interface because the node_id is already missing from FND_NODES. The only way to remove it is via SQL. In many instances, orphaned profile options can simply be left alone, but there are a few cases where they cause problems. For example, if a new node were added to the instance or if the post-clone run of AutoConfig happened to assign a node_id that is already existing from a previous instance, that new node would inherit all of the profile option values of that previously orphaned node. Using the above query as a guide, these orphaned node_ids can be removed via sqlplus as the apps user using the following delete statement:

delete from fnd_profile_option_values
where
  (level_id = 10005
   and level_value > 0
   and level_value not in (select node_id from fnd_nodes))
  or
  (level_id = 10007
   and level_value2 > 0
   and level_value_application_id=-1
   and level_value2 not in (select node_id from fnd_nodes));  

28 rows deleted.

The official fix for this comes from a modification of afcpclean.sql to delete the orphaned profile options. This fix was first delivered in 5107107-TXK (FND) AUTOCONFIG TEMPLATE ROLLUP PATCH N (July 2006).

Checking the Patch Requirements on each Appl_Top

A number of customers use the AD_BUGS table to see if patches are applied. This really isn't accurate. Take, for example, the need to see if your 11.5.10.2 instance has all of the patches required by the DMZ note 287176.1:

Example of a bad way to check for patch application

select 
  creation_date, 
  bug_number 
from 
  ad_bugs 
where 
  bug_number in ('3240000','3460000','4204335','4125550','3942483','4733943');

Example output of a bad way to check for patch application

CREATION_DATE    BUG_NUMBER 
---------------  --------------- 
      13-JUL-05  3460000 
      29-APR-06  3942483 
      13-JUL-05  4125550 
      29-APR-06  4733943 

The above example output shows that this instance is fully compliant, even with the additional "nice to have" patches that aren't required by the note like '4733943'. This is misleading, because the instance is NOT compliant. For this illustration, I didn't apply these patches to every tier and therefore my DMZ instance will not work. A better approach is to use the "ad_patch.is_patch_applied" function which considers each APPL_TOP such as in the following PL/SQL example:

Patch Query

set serveroutput on
DECLARE
  TYPE p_patch_array_type is varray(10) of varchar2(10);
  --
  p_patchlist p_patch_array_type;
  p_appltop_name        varchar2(50);
  p_patch_status        varchar2(15);
  p_appl_top_id         number;
  --
  CURSOR alist IS
    select appl_top_id, name
    from ad_appl_tops;
  --
  procedure println(msg in varchar2)
  is
  begin
    dbms_output.enable;
    dbms_output.put_line(msg);
  end;
  --
BEGIN
  open alist;
  --
  p_patchlist:= p_patch_array_type('3240000','3460000','4204335','4125550','3942483','4733943');
  --
  LOOP
    FETCH alist INTO p_appl_top_id,p_appltop_name;
    EXIT WHEN alist%NOTFOUND;
    --
    IF p_appltop_name NOT IN ('GLOBAL','*PRESEEDED*')
    THEN
      println(p_appltop_name || ':');
      for i in 1..p_patchlist.count
      loop
        p_patch_status := ad_patch.is_patch_applied('11i',p_appl_top_id,p_patchlist(i));
        println('..Patch ' || p_patchlist(i) || ' was ' || p_patch_status);
      end loop;
    END if;
    println('.');
  END LOOP;
  --
  close alist;
END;
/

Example Output of Patch Query

hostbe: 
..Patch 3240000 was NOT_APPLIED 
..Patch 3460000 was EXPLICIT 
..Patch 4204335 was NOT_APPLIED 
..Patch 4125550 was EXPLICIT 
..Patch 3942483 was EXPLICIT 
..Patch 4733943 was EXPLICIT 
. 
hostemt: 
..Patch 3240000 was NOT_APPLIED 
..Patch 3460000 was EXPLICIT 
..Patch 4204335 was NOT_APPLIED 
..Patch 4125550 was EXPLICIT 
..Patch 3942483 was NOT_APPLIED 
..Patch 4733943 was NOT_APPLIED

Notice in the above example output that patches 3942483 and 4733943 HAVE NOT BEEN APPLIED to hostemt (my external middle tier), but AD_BUGS implied that I had applied everything that I needed to! In a DMZ environment, this is especially important to check since the DMZ middle tiers shouldn't share an APPL_TOP with the internal tiers.

Patch Query Caveats

This patch query is becoming very popular and is often copy/pasted into other notes and TARs without its proper context and explanation of its limitations. The philosophy behind this whitepaper is to offer these queries as troubleshooting tools with the full expectation that the reader, after understanding how the query works as written, will modify them to suit their particular need. To do this in a meaningful way, the existing limitations of the query must be understood.

1. The p_patchlist can any single-quoted, comma-separated list of patch numbers of patches applied using adpatch. What is currently listed above is a list of patches as mentioned in an early revision of Note:287176.1. Most of these patches are now obsoleted and therefore p_patchlist lists the realistic minimum patches and not necessarily the current recommended list. It is fully expected that the user of this script update the p_patchlist to suit their need.

2. As written, p_patchlist looks for all of the patches listed in the early revision of Note:287176.1. In many cases, it is NOT a problem that a patch was NOT_APPLIED because Note:287176.1 states that if a certain patch was applied, then some certain other patch is not required. It is the responsibility of the reader to have a current copy of Note:287176.1 in front of them to determine what patches still need to be applied.

3. This query will not find patches that were not applied with adpatch. For example: iAS rollup patches, forms patchsets, and RDBMS specific patches.

4. It is important for the reader to understand what APPL_TOPs are valid for their instance before interpreting the query results.
Many customers will run this script on cloned systems that haven't had the old patch histories cleaned and therefore the script will list APPL_TOPs that, while still in the database, are NOT a part of the current instance ("p_appltop_name NOT IN ..." can address this). The reader should also understand what servers in their instances are sharing APPL_TOPs with other servers so as to understand that the p_applop_name doesn't always refer to a specific server.

5. Most of the newer patches run by adpatch will finish by running "Maintain Snapshot Info", but some customer's take shortcuts and get behind on this. For best results, run adadmin "Maintain Snapshot Info" before running this query (or if you suspect the results are amiss) or newer patches may not be seen in the query results.

What Server Did that User Log Into

A handy way to tell what server a user is logged into is to use the following query from sqlplus as apps:

col server_name format a12
col user_name format a30
select
  to_char(first_connect, 'HH24:MI:SS') "TIME",
  user_name,
  decode
   (a.node_id,
   a.node_id,
   (select node_name 
   from fnd_nodes n 
   where a.node_id=n.node_id),a.node_id) server_name
from
  icx_sessions a,
  fnd_user b,
  fnd_nodes svr
where
  first_connect > (sysdate-1/24)
  and (a.user_id=b.user_id
       and a.node_id=svr.node_id)
  and disabled_flag='N'
order by first_connect;

For example, as I login to various tiers as various users on my DMZ instance, I see:

TIME     USER_NAME         SERVER_NAME
-------- ----------------- ------------
20:11:21 SYSADMIN          HOSTBE
20:17:53 USER              HOSTINT
20:19:16 OPERATIONS        HOSTEMT
20:39:21 GUEST             HOSTPC8  (did not login, hit "forgot password")

Topology
  HOSTBE - Single tier install of EBS
  HOSTINT - additional internal web node
  HOSTEMT - external web node for DMZ testing

Summary

This note is the first of a currently four part series of advanced topology troubleshooting notes and provides some explanations of fundamental concepts and queries useful in diagnosing such instances. Readers wanting to increase their knowledge in this subject area are encouraged to read the rest of the series.

Advanced Topology/DMZ Troubleshooting Series

Note:364439.1-Tips and Queries for Troubleshooting Advanced Topologies (this document)
Note: 2194374.1-Utility/Script for DMZ Setup Validation
Note:460564.1-Hints and Tips for Troubleshooting the URL Firewall
Note:438744.1-Case History: Implementing a Reverse Proxy Alone in a DMZ Configuration - 11i
Note.726953.1-Case History: Implementing a Reverse Proxy Alone in the DMZ Configuration - R12

REFERENCES

NOTE:2194374.1 - Utility/script for DMZ setup validation
NOTE:438744.1 - Case History: Implementing a Reverse Proxy Alone in a DMZ Configuration - 11i
BUG:7699618 - ISSUE WHEN SETTING RESPONSIBILITY TRUST LEVEL
NOTE:217368.1 - Advanced Configurations and Topologies for Enterprise Deployments of E-Business Suite 11i
NOTE:460564.1 - Hints and Tips for Troubleshooting the URL Firewall (410-Gone on DMZ External Tiers)

OracleAppsDBAKK1

Monday, August 26, 2019

Tips and Queries for Troubleshooting Advanced Topologies (Doc ID 364439.1)