oracle application tech stack tips and queries for troubleshooting advanced topologies

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

In this Document

Purpose

Troubleshooting Steps

The Importance of Topology Diagrams

-

The FND_NODES query

Understanding the FND_NODES Query Results

-

The Trust Query

Understanding the Trust Query Results

Accidentally Locking Oneself Out

-

A Responsibility Query

Understanding the Responsibility Query Results

-

The Enabled Query

Understanding the Enabled Query Results

-

Understanding the Server and ServResp Hierarchy

Server Hierarchy

ServResp Hierarchy

-

The Profile Options Query

Understanding the Profiles Query

Setting ServResp Profile Options Manually

-

Orphaned Profile Options

Understanding the Orphans Query

-

Checking the Patch Requirements on each Appl_Top

Patch Query Caveats

-

Summary

References

APPLIES TO:

Oracle Applications 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 followingnotes 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

Document 364439.1 https://support.oracle.com/epmos/faces/DocumentDisplay?_adf.ctrl-state...

1 of 17 8/12/2014 6:54 PM

mpasha

Typewritten Text

mpasha

Text Box

mpasha

Text Box

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

The format in this revision of the paper will be to offer the troubleshooting query and then follow each query with an example outputand a substantial explanation of the meaning of the query results. As written, these queries can be expected to do as theaccompanying explanation explains, but these explanations and queries are offered with the full intention that the reader will embracethe 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 difficultwithout some sort of preconceived plan in writing. When requesting support for an advanced configuration, customers should expectthat a request for a topology diagram will be made before any advice beyond the generic can be offered. The DMZ and other advancedtopology configurations are becoming more popular each day and it seems that for each TAR worked we find that many customers havevery differing ideas on what constitutes a good DMZ. For this reason the first thing that I always ask for is a simple drawing thatdescribes 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.1with appropriate labels specific to the customer configuration. The problems most often faced are configuration related and it is notpossible to know from the traces and configuration files where the functionality goes awry without a previous understanding of whatit SHOULD look like. When troubleshooting, we start with the drawing and confirm that the various configuration parameters match thedrawing.

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 thequeries that follow. These drawings don't have to be a major effort created with expensive utilities; I simply created the above drawingwith Microsoft paint after scratching the icons directly out of Note:287176.1. The important thing is that the drawing describe evermajor component by its role, location in the topology, and its proper name.


2 of 17 8/12/2014 6:54 PM

In this drawing, dcollierbe is a single tier install of 11.5.10.2 and dcollieremt is a clone of the dcollierbe middle tier to be used as anexternal tier. The drawing shows the logical blocks; the internal tier is clearly indicated as a separate logical entity despite beingphysically on the same server as the database. Also note that the majority of the patch levels in this drawing are superceded at thisdate. 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. Anexample 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 theresults of the queries to a file, and upload the resulting, plain-text spool file. NEVER paste the results into the service request itselfas that results in an entry with a deluge of poorly formatted data.

FND_NODES Query

spool fnd_nodes set pagesize 50col node_name format a15col server_id format a8col server_address format a15col platform_code format a4col webhost format a12col domain format a20col virtual_ip format a12set linesize 132select 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_ipfrom fnd_nodesorder 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 DCOLLIERBE 102B7C50 10.1.2.3 us.oracle.com dcollierbe. 65BF2761 us.oracle.c E040018A om 439451E5 16313498 31994113 06835324 76801476

4067 46 N N N N AUTHENTICATION SECURE *

4068 46 N N N Y Y DCOLLIEREMT 11473885 100.1.2.3 us.oracle.com dcollieremt 65677803 .us.oracle. E040018A com 439450E9 29327317


3 of 17 8/12/2014 6:54 PM

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 makeup the release 11i and 12 instance and therefore the FND_NODES table is often the best, first place to look. You can quickly tell howmany servers a customer has and what they are configured to do and this should match their topology diagram. If it doesn't match thediagram, 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 inthis 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'shostname. 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 whennode_name is a valid server name. The exception to this rule is when node_name is the dummy row for authentication and then thevalue of server_id for the authentication row may be ON, OFF, or SECURE. If Authentication is set to SECURE then this string mustmatch the server_id string in each server's DBC files. The DBC file location is defined by the environment variable FND_SECURE and istypically $FND_TOP/secure.

Server_Address - the IP address of the server or, optionally, the IP address of the relevant reverse proxy server. This is normallydetermined 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) AutoConfigTemplate 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 veryreasonable uniqueness constraint on server_address, you cannot use "127.0.0.1" here. If another server tries to use 127.0.0.1, it willhave 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 IPaddress for both the internal server and the virtually external server. For more information of virtually external tiers, seeNote: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 tierusing the system profile option "Node Trust Level" (internal name NODE_TRUST_LEVEL). Similarly, you can set externally availableresponsibilities at the responsibility level using the system profile option "Responsibility Trust Level" (internal nameAPPL_SERVER_TRUST_LEVEL).

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

Trust Query


4 of 17 8/12/2014 6:54 PM

spool trustset linesize 132set pagesize 100col "Level Where Option Is Set" format a60col "Profile Name" format a30col value format a10break 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||')' fromfnd_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 vwhere 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:DCOLLIEREMTResponsibility 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 DCOLLIEREMT because the Node_Trust_Level is set toExternal at the server level for the server named DCOLLIEREMT. Similarly, the only responsibility that will be available to users logginginto DCOLLIEREMT 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 ispossible to restrict Administrative responsibilities to a specific tier. In this example, if a user logs in to one of the normal tiers, neitherthe responsibility "System Administration" nor "System Administrator" will appear in the responsibility list even if the user has thoseresponsibilities 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 adminresponsibility (such as System Administrator) will fail shortly after forms is launched with the error of "Sorry, no valid responsibilitiesare available [OK]".

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


5 of 17 8/12/2014 6:54 PM

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 theexternal tiers and leave all other tiers to default to the Site level value of Normal. The problem arises when accidentally setting theNode Trust level to External at the Site level when no servers are explicitly set to be normal since this results in ALL web nodesbecoming External nodes where only External responsibilities are allowed. If ALL nodes are External, then NONE of the nodes will allowSystem 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 toolate and you have locked yourself out of the instance, the only practical solution is to update the profile option via sqlplus as the appsuser.

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

set serveroutput onDECLARE 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 givenserver 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 doesnot 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 profileoption. Orphaned profile options are discussed later in this paper.

set serveroutput onDECLARE 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 numberof responsibilities, but when logging in to an external tier, that user will see ONLY those responsibilities that are declared external andthen 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 followingquery will list the overall responsibilities that have been assigned to a user that can be compared with the list of responsibilitiesdeclared as external as seen in the trust query.

Responsibility Query

undefine USER_NAME_IN_UPPER_CASEcol "Responsibility Name" format a40select urg.SECURITY_GROUP_ID SecGID,


6 of 17 8/12/2014 6:54 PM

urg.RESPONSIBILITY_ID RespID, urg.RESPONSIBILITY_APPLICATION_ID RespAppID, resp.RESPONSIBILITY_NAME "Responsibility Name"from fnd_user_resp_groups urg, fnd_responsibility_vl respwhere 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 = 0unionselect urg.SECURITY_GROUP_ID SecGID, urg.RESPONSIBILITY_ID RespID, urg.RESPONSIBILITY_APPLICATION_ID RespAppID, resp.RESPONSIBILITY_NAME || sec.security_group_name namefrom fnd_user_resp_groups urg, fnd_responsibility_vl resp, fnd_security_groups_vl secwhere 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 hasquite a few sensitive responsibilites that are available when logging into a "normal" tier, but when logging into an external tier this userhas "No Active Responsibilities" and therefore cannot use any of them. In this paper's example, there are only two responsibilities thathave been declared as external (iReceivables External Vision UK and iStore) and since MisterTester has neither of these he gets thefollowing screen when logging in from an external tier:

-

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.


7 of 17 8/12/2014 6:54 PM

The following query is a very good check to make certain that the key profile options have been set to the appropriate hierarchy andthat the flags are correctly set to allow updates.

Enabled Query

col profile_option_name format a32col SITE format a4col APPL format a4col RESP format a4col USER format a4col SRVR format a4col ORG format a3col svrp format a4set pagesize 100select 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_optionswhere 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 NNFND_VALIDATION_LEVEL SERVER Y Y YY NN YY NN NN NN NNNODE_TRUST_LEVEL SERVER Y Y YY NN YY NN NN NN NNOKS_SMTP_DOMAIN SERVER Y Y YY NN YY YN NN NN NNUMX_REGISTER_HERE_HTMLPARAMS SERVER Y Y NN NN YY NN NN NN NNUMX_REGISTER_HERE_REGPARAMS SERVER Y Y NN NN YY NN NN NN NNUMX_REGISTER_HERE_REG_SRV SERVER Y Y NN NN YY NN NN NN NNAPPS_FRAMEWORK_AGENT SERVRESP Y Y YY YY NN YY NN NN NNAPPS_JSP_AGENT SERVRESP Y Y YY YY NN YY NN NN NNAPPS_PORTAL SERVRESP Y Y YY YY NN YY NN NN NNAPPS_SERVLET_AGENT SERVRESP Y Y YY YY NN YY NN NN NNAPPS_WEB_AGENT SERVRESP Y Y YY YY NN YY NN NN NNASO_CONFIGURATOR_URL SERVRESP Y Y YY YY NN YY NN NN NNCZ_UIMGR_URL SERVRESP Y Y YY YY NN YY NN NN NNHELP_WEB_AGENT SERVRESP Y Y YY YY NN YY NN NN NNICX_DISCOVERER_LAUNCHER SERVRESP Y Y YY YY NN YY NN NN NNICX_DISCOVERER_VIEWER_LAUNCHER SERVRESP Y Y YY YY NN YY NN NN NNICX_FORMS_LAUNCHER SERVRESP Y Y YY YY NN YY NN NN NNQP_PRICING_ENGINE_URL SERVRESP Y Y YY YY NN YY NN NN NNTCF: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 enabledand updateable. This version of the note expands that query with a more complex appearance. The resulting matrix of Y's and N'sfrom a customer's instance should be similar to what is posted above. The meaning of the columns can easily be determined from thequery 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


8 of 17 8/12/2014 6:54 PM

hierarchies should be enabled and updateable only at certain levels. When troubleshooting, it is best to confirm that ONLY these validlevels (as shown in the example output) are available for the key profile options used in the more advanced topologies and that theenabled and updateable flags haven't been changed by some errant twiddling. Similarly, since this configuration is sensitive to whatserver a user logs into it is useful to know what profile options are under the server hierarchy and if those are also properly accessibleto AutoConfig. This should always be the case, but behind the scenes there is often someone trying to work beyond the supportednotes 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 serverlevel 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, butAutoConfig will not set these values if your web related profile options are not using ServResp hierarchy and then your DMZ instancewill 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 undereither the SERVER level hierarchy or the SERVRESP level hierarchy. Before providing a script to display the settings of every profileoption 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:

SiteServerUser

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 servera 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 maybe 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 interpretwhen running the Profiles Query, below, for listing all web like profile options and where they are set. When evaluating the value of theprofile option, the user level takes precedence over all other levels and the site level is only used when the settings at the other levelsdo 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 typeis 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 responsibilitylevel and is especially useful when you want to specify a specific responsibility to be available only on a specific server (a functionallydirected 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 aDMZ 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 anddiscussed below. When a profile option is setup to have the ServResp hierarchy, it can only (legally) be set at one of the following threelevels:

SiteServer/Responsibility (ServResp)User

The middle "Server/Responsibility" level is a combination of responsibility and server. Either or both of the responsibility or servercomponents may be specific values, or may be the "default" value. For purposes of evaluating "default" matches, the server isconsidered to be at a higher level (and less specific) than the responsibility so values specified at the responsibility level will overridevalues specified only at the server level. When evaluating profile values at this "Server/Responsibility" level, the values of both thelevel_value/level_application_value pair and level_value2 columns are considered together. If no overriding value is specified at theuser level, the algorithm will first look for a specific match for both responsibility and server level values. If no such match is found, itwill 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


9 of 17 8/12/2014 6:54 PM

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 areset and what they are set to. Seeing these profile option values at once in a report format is much easier than picking through themone by one from within forms (System Administrator/Profiles/System). As always, it is best to cut and paste this into sqlplus as appsand 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, simplyomit the boldfaced section of the where clause. This is sometimes very useful, but will produce a very large spool file.

Profiles Options Query

set linesize 132set pagesize 132col NAME format A40col LEVEL_SET format a15col CONTEXT format a20col VALUE format A20 wrapcol Server format a10col resp format a8 wrapcol 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,


10 of 17 8/12/2014 6:54 PM

'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') 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_valueorder by name, v.level_id;

Example Output of Profiles Query (small excerpt)

NAME LEVEL_SET CONTEXT VALUE----------------------------------- ------------- -------------------- --------------------(US)-Application Framework Agent Site http://dcollieremt.u s.oracle.com:8006

ServResp DCOLLIEREMT/Default http://dcollieremt.u s.oracle.com:8005

ServResp DCOLLIERBE/Default htp://dcollierbe.us .oracle.com:8005

(US)-Node Trust Level Site 2 Server DCOLLIEREMT 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 eachof the four columns:

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

LANG NLS_LANGUAGE---- ------------------------AR ARABICD GERMANE SPANISHEG EGYPTIANESA LATIN AMERICAN SPANISHF FRENCHFRC CANADIAN FRENCHGB ENGLISH


11 of 17 8/12/2014 6:54 PM

IW HEBREWJA JAPANESEKO KOREANUS AMERICANZHS SIMPLIFIED CHINESEZHT 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 currentlyshows 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 responsibilityand 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 plusthe 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 throughsection five of the note, you find that section 5.2 directs you to declare which servers are internal and which are external. AutoConfigwill 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 inthe forms unless you have applied the updated forms patch. The one-off patch that updates the forms so that you can see andmanipulate SERVRESP hierarchy based profile options is "4733943-One-off ARU for 4240917: SERVRESP UI ENHANCEMENTS FORFNDPOMPV, FNDPOMPO, FNDPOMSV".

Example screenshot after applying 4733943:

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


12 of 17 8/12/2014 6:54 PM

-

Orphaned Profile Options

As another troubleshooting point, it is often helpful to consider orphaned profile options as described in Bug:4858843. As discussedearlier, 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 toimplement a DMZ (Note:287176.1). The current revision of Note 287176.1 sets the appropriate profile options using the scriptafwebprf.sql via AutoConfig. With the original instance working well, they now clone the instance, runFND_CONC_CLONE.SETUP_CLEAN, and finally run AutoConfig on the new instance with the reasonable expectation that the newinstance's DMZ configuration will work well. At this point they have new nodes defined in the fnd_nodes table and new Server/ServResplevel profile options with the names of the new nodes that make up the cloned instance. Unfortunately, they also have the original, butnow orphaned, profile option values for the nodes of the original instance. These orphaned profile options are Server/ServResp levelprofile options that refer to node_id's that were removed from the fnd_nodes table by the FND_CONC_CLONE.SETUP_CLEAN and stilllinger, 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 66set linesize 132col "Effected Profile Name" format a40col "Orphaned ID(s)" format a14Break 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 nwhere 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


13 of 17 8/12/2014 6:54 PM

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 ServRespAPPS_JSP_AGENT 5065 ServResp 5067 ServRespAPPS_PORTAL 5065 ServResp 5067 ServRespAPPS_SERVLET_AGENT 5065 ServResp 5067 ServRespAPPS_WEB_AGENT 5065 ServResp 5067 ServRespASO_CONFIGURATOR_URL 5065 ServResp 5067 ServRespCZ_UIMGR_URL 5065 ServResp 5067 ServRespFND_FUNCTION_VALIDATION_LEVEL 5067 ServerHELP_WEB_AGENT 5067 ServResp 5065 ServRespICX_DISCOVERER_LAUNCHER 5065 ServResp 5067 ServRespICX_DISCOVERER_VIEWER_LAUNCHER 5065 ServResp 5067 ServRespICX_FORMS_LAUNCHER 5067 ServResp 5065 ServRespNODE_TRUST_LEVEL 5067 ServerQP_PRICING_ENGINE_URL 5067 ServResp 5065 ServRespTCF: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 willshow 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 theabove example, we see that two nodes were orphaned and that this effected 14 different profile options. These profile option valuescannot be removed via the forms interface because the node_id is already missing from FND_NODES. The only way to remove it is viaSQL. In many instances, orphaned profile options can simply be left alone, but there are a few cases where they cause problems. Forexample, 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 alreadyexisting from a previous instance, that new node would inherit all of the profile option values of that previously orphaned node. Usingthe above query as a guide, these orphaned node_ids can be removed via sqlplus as the apps user using the following deletestatement:

delete from fnd_profile_option_valueswhere (level_id = 10005 and level_value > 0 and level_value not in (select node_id from fnd_nodes)) or


14 of 17 8/12/2014 6:54 PM

(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 in5107107-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 tosee 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'trequired by the note like '4733943'. This is misleading, because the instance is NOT compliant. For this illustration, I didn't apply thesepatches to every tier and therefore my DMZ instance will not work. A better approach is to use the "ad_patch.is_patch_applied" functionwhich considers each APPL_TOP such as in the following PL/SQL example:

Patch Query

set serveroutput onDECLARE 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; --


15 of 17 8/12/2014 6:54 PM

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

dcollierbe: ..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 . dcollieremt: ..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 dcollieremt (my external middletier), but AD_BUGS implied that I had applied everything that I needed to! In a DMZ environment, this is especially important to checksince 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 andexplanation of its limitations. The philosophy behind this whitepaper is to offer these queries as troubleshooting tools with the fullexpectation that the reader, after understanding how the query works as written, will modify them to suit their particular need. To dothis 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 currentlylisted above is a list of patches as mentioned in an early revision of Note:287176.1. Most of these patches are now obsoleted andtherefore p_patchlist lists the realistic minimum patches and not necessarily the current recommended list. It is fully expected that theuser 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 problemthat a patch was NOT_APPLIED because Note:287176.1 states that if a certain patch was applied, then some certain other patch is notrequired. It is the responsibility of the reader to have a current copy of Note:287176.1 in front of them to determine what patches stillneed to be applied.

3. This query will not find patches that were not applied with adpatch. For example: iAS rollup patches, forms patchsets, and RDBMSspecific 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 listAPPL_TOPs that, while still in the database, are NOT a part of the current instance ("p_appltop_name NOT IN ..." can addressthis). The reader should also understand what servers in their instances are sharing APPL_TOPs with other servers so as to understandthat 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 andget behind on this. For best results, run adadmin "Maintain Snapshot Info" before running this query (or if you suspect the results areamiss) or newer patches may not be seen in the query results.

-


16 of 17 8/12/2014 6:54 PM

Summary

This note is the first of a currently four part series of advanced topology troubleshooting notes and provides some explanations offundamental concepts and queries useful in diagnosing such instances. Readers wanting to increase their knowledge in this subjectarea 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:460564.1-Hints and Tips for Troubleshooting the URL FirewallNote:438744.1-Case History: Implementing a Reverse Proxy Alone in a DMZ Configuration - 11iNote.726953.1-Case History: Implementing a Reverse Proxy Alone in the DMZ Configuration - R12

REFERENCES

NOTE:287176.1 - DMZ Configuration with Oracle E-Business Suite 11i

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


17 of 17 8/12/2014 6:54 PM

oracle application tech stack tips and queries for troubleshooting advanced topologies

Software