Oracle Apps Technology

Blog dedicated to Oracle Applications (E-Business Suite) Technology; covers Apps Architecture, Administration and third party bolt-ons to Apps

Thursday, January 14, 2016

Extranet login redirects to intranet URL

For an old 11.5.10.2 ERP, we are moving from the architecture of "EBS application server in DMZ" to the architecture of "Reverse Proxy in DMZ and EBS application server in intranet".  After doing all configurations, we hit the classic issue where, you login through extranet url visible on public internet which redirects to intranet url.

So https://extranet.example.com asks for SSO details and after keying in SSO username and password goes to http://intranet.example.com.

The support.oracle.com article DMZ Configuration with Oracle E-Business Suite 11i (Doc ID 287176.1) has listed 4 checks which could be the reason for this issue:

H6: Redirection to an Incorrect Server During Login

If you are getting redirected to an incorrect server during the login process, check the following:
  • Whether the hirearchy type of the profile options mentioned in Section 5.1 is set to SERVRESP .
  • select PROFILE_OPTION_NAME,HIERARCHY_TYPE 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');
    PROFILE_OPTION_NAME                               HIERARCHY_TYPE
    ----------------------------------------                               --------------------------------
    APPS_FRAMEWORK_AGENT                         SERVRESP
    APPS_JSP_AGENT                                         SERVRESP
    APPS_PORTAL                                         SERVRESP
    APPS_SERVLET_AGENT                                 SERVRESP
    APPS_WEB_AGENT                                         SERVRESP
    ASO_CONFIGURATOR_URL                         SERVRESP
    CZ_UIMGR_URL                                         SERVRESP
    HELP_WEB_AGENT                                         SERVRESP
    ICX_DISCOVERER_LAUNCHER                 SERVRESP
    ICX_DISCOVERER_VIEWER_LAUNCHER SERVRESP
    ICX_FORMS_LAUNCHER                         SERVRESP
    QP_PRICING_ENGINE_URL                         SERVRESP
    TCF:HOST                                                 SERVRESP

    All good on this point

  • Whether the profile option values for the fnd profile options (APPS_FRAMEWORK_AGENT, APPS_WEB_AGENT, APPS_JSP_AGENT, APPS_SERVLET_AGENT) are pointing to the correct node. Replace the node_id with the node_id of the external and internal web tier. For example:
  • select fnd_profile.value_specific('APPS_FRAMEWORK_AGENT',null,null,null,null,) from dual;
    This query returned https://extranet.example.com

  • Whether the dbc file pointed to by the JVM parameter (JTFDBCFILE) in jserv.properties exists.
  • wrapper.bin.parameters=-DJTFDBCFILE=
    This was incorrect.  It was pointing to the intranet jdbc file location.

  • Whether the value of the parameter APPL_SERVER_ID set in the dbc file for the node is the same as the value of the server_id in the fnd_nodes table.
    select node_name,node_id,server_id from fnd_nodes;
    This was overwritten in the dbc file, with appl_server_id of intranet when autoconfig was done on intranet and overwritten with appl_server_id of extranet when autoconfig was done on extranet, as the DBC file location and name were same for both intranet and extranet.
I asked the DBA team to manually correct the dbc file name inside $IAS_CONFIG_HOME/Apache/Apache/Jserv/etc/jserv.properties
and create a file of that name in $FND_SECURE/$CONTEXT_NAME.dbc on the extranet node and bounce services.  Once that was done, we tested and it worked. No more redirection to intranet URL.

Then I asked them to correct the s_dbc_file_name variable in the context file of extranet node. Run autoconfig on extranet, verify the value of dbcfile in jserv.properties DJTFDBCFILE parameter, verify that the DBC file had the server_id of the extranet node.  Restart all services.
Checked again, and it worked again.

So apart from checking the values of context file variables like s_webentryhost, s_webentrydomain, s_active_port, you also need to check the value of s_dbc_file while verifying the setups for extranet configuration. This can happen in 11i , R12.1 and R12.2 also.

Tuesday, December 22, 2015

Calling all Apps DBAs doing 11i to R12.x upgrades

At this time of the year during holidays, the Apps DBA community is busy doing upgrades as longer downtimes are possible.  In case you are facing any issues, please feel free to write to me at my email: oracleappstechnology@gmail.com .  I will be glad to hear from you and help you.

Wednesday, December 16, 2015

11i pre-upgrade data fix script ap_wrg_11i_chrg_alloc_fix.sql runs very slow

We are currently upgrading one of our ERP instances from 11.5.10.2 to R12.2.5.  One of the pre-upgrade steps is to execute the data fix script ap_wrg_11i_chrg_alloc_fix.sql.  However, this script has been running very very slow. After 4 weeks of monitoring, logging SRs with Oracle, escalating etc., we started a group chat today with our internal experts.  We had Ali, Germaine, Aditya, Mukhtiar, Martha Gomez and Zoltan.  I also invited our top notch EBS Techstack expert John Felix. After doing explain plan on the sql, Based on the updates being done by the query I predicted that it will take 65 days to complete.

John pointed out that the query was using the index AP_INVOICE_DISTRIBUTIONS_N4  that had a very high cost.  We used an sql profile that replaced AP_INVOICE_DISTRIBUTIONS_N4  with AP_INVOICE_DISTRIBUTIONS_U1.  The query started running faster and my new prediction was that it would complete in 5.45 days.

John mentioned that now another select statement was using the same index AP_INVOICE_DISTRIBUTIONS_N4 that had a very high cost.

After discussing among ourselves, we decided to drop the index, run the script and re-create the index. Aditya saved the definition of the index and dropped it.

DBMS_METADATA.GET_DDL('INDEX','AP_INVOICE_DISTRIBUTIONS_N4','AP')
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

  CREATE INDEX "AP"."AP_INVOICE_DISTRIBUTIONS_N4" ON "AP"."AP_INVOICE_DISTRIBUTIONS_ALL" ("ACCOUNTING_DATE")
  PCTFREE 10 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "APPS_TS_TX_IDX"

1 row selected.

SQL> drop index AP.AP_INVOICE_DISTRIBUTIONS_N4;

Index dropped.

The updates started happening blazing fast.  The whole thing got done in 39 minutes and we saw the much awaited:

SQL> set time on
16:34:16 SQL> @ap_wrg_11i_chrg_alloc_fix.sql
Enter value for resp_name: Payables Manager
Enter value for usr_name: 123456
-------------------------------------------------------------------------------
/erp11i/applcsf/temp/9570496-fix-16:34:40.html is the log file created
-------------------------------------------------------------------------------

PL/SQL procedure successfully completed.

17:13:36 SQL>

From 65 days to 5.45 days to 39 minutes.  Remarkable.  Thank you John for your correct diagnosis and solution.

Monday, November 16, 2015

sqlplus core dumps with segmentation fault error in OEL 6.6 when you connect to DB

We have used OEL 6.6 image in our latest build.  When we cloned an EBS R12.2 instance that was on OEL 5.7 to this new server that has OEL 6.6, During the clone, adcfgclone.pl was failing. On further checks, we discovered that sqlplus is crashing with segmentation fault error whenever we tried to connect to database:

sqlplus /nolog
conn apps/apps
Segmentation Fault

So, I suggested the DBAs to do strace sqlplus apps/apps.  The strace revealed many missing libraries:

We had another working OEL 6.4 instance where we checked for these libraries, and all of them were present.

The locate command was used to locate the full directory paths of the missing libraries

locate libnss_sss.so.2
/lib/libnss_sss.so.2

/lib/libnss_sss.so.2
/lib/libnss_files.so.2
/lib/libociei.so
/lib/libc.so.6
/lib/libgcc_s.so.1
/lib/libnsl.so.1
/lib/libpthread.so.0

Then rpm -qf command was used to find out the rpm that would have the library:

$ rpm -qf /lib/libnss_sss.so.2
sssd-client-1.11.6-30.el6_6.3.i686
$ rpm -qf /lib/libnss_files.so.2
glibc-2.12-1.149.el6_6.9.i686
$ rpm -qf /lib/libociei.so
error: file /lib/libociei.so: No such file or directory
$ rpm -qf /lib/libc.so.6
glibc-2.12-1.149.el6_6.9.i686
$ rpm -qf /lib/libgcc_s.so.1
libgcc-4.4.7-3.el6.i686
$ rpm -qf /lib/libnsl.so.1
glibc-2.12-1.149.el6_6.9.i686
$ rpm -qf /lib/libpthread.so.0
glibc-2.12-1.149.el6_6.9.i686
$ rpm -qf /lib/libm.so.6
glibc-2.12-1.149.el6_6.9.i686
$ rpm -qf /lib/libdl.so.2
glibc-2.12-1.149.el6_6.9.i686

Since 10.1.2 home is 32-bit in EBS R12.1 and 12.2, all the libraries needed to be 32-bit.

Except for sssd-client, the other rpms were present.  64-bit version of sssd-client was present and whenver we tried to install the 32-bit rpm it would give this error, as the operating system thinks that it is already installed:

# yum install sssd-client.i686
Loaded plugins: security
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package sssd-client.i686 0:1.12.4-47.el6 will be installed
--> Finished Dependency Resolution
Error:  Multilib version problems found. This often means that the root
       cause is something else and multilib version checking is just
       pointing out that there is a problem. Eg.:

         1. You have an upgrade for sssd-client which is missing some
            dependency that another package requires. Yum is trying to
            solve this by installing an older version of sssd-client of the
            different architecture. If you exclude the bad architecture
            yum will tell you what the root cause is (which package
            requires what). You can try redoing the upgrade with
            --exclude sssd-client.otherarch ... this should give you an error
            message showing the root cause of the problem.

         2. You have multiple architectures of sssd-client installed, but
            yum can only see an upgrade for one of those arcitectures.
            If you don't want/need both architectures anymore then you
            can remove the one with the missing update and everything
            will work.

         3. You have duplicate versions of sssd-client installed already.
            You can use "yum check" to get yum show these errors.

       ...you can also use --setopt=protected_multilib=false to remove
       this checking, however this is almost never the correct thing to
       do as something else is very likely to go wrong (often causing
       much more problems).

       Protected multilib versions: sssd-client-1.12.4-47.el6.i686 != sssd-client-1.11.6-30.el6_6.4.x86_64


# rpm -qa | grep sssd-client
sssd-client-1.11.6-30.el6_6.4.x86_64

Eventually we installed it with force option

# rpm -Uvh --force /tmp/sssd-client-1.11.6-30.el6_6.3.i686.rpm

# rpm -qa | grep sssd-client
sssd-client-1.11.6-30.el6_6.3.i686
sssd-client-1.11.6-30.el6_6.4.x86_64

pam-ldap was one of the other rpms that was installed for other missing libraries.  Surprisingly, sssd-client and pam-ldap rpms are not mentioned as pre-requisites in support.oracle.com article:
Oracle E-Business Suite Installation and Upgrade Notes Release 12 (12.2) for Linux x86-64 (Doc ID 1330701.1) 

twm: unable to open fontset "-adobe-helvetica-bold-r-normal--*-120-*-*-*-*-*-*"

While launching twm, it gives this error and exits to unix prompt:

twm: unable to open fontset "-adobe-helvetica-bold-r-normal--*-120-*-*-*-*-*-*"

I found a solution on http://ubuntuforums.org/archive/index.php/t-1596636.html :

It was reported here for fedora: https://bugzilla.redhat.com/show_bug.cgi?id=509639. The workaround is to execute it with a specific shell variable:

$ LANG=C
$ export LANG
twm &

twm launches fine after this.

Saturday, November 14, 2015

Oracle SSO Failure - Unable to process request Either the requested URL was not specified in terms of a fully-qualified host name or OHS single sign-on is incorrectly configured

Today, during a cutover when we were moving one of our ERP instance on Cisco UCS VMware VMs to Exalogic and Exadata, I got a call from Bimal.  The extranet iSupplier URL had been configured, but whenever any user logged in, they were seeing the following error instead of the iSupplier OAF Home page:

Oracle SSO Failure - Unable to process request Either the requested URL was not specified in terms of a fully-qualified host name or OHS single sign-on is incorrectly configured

A search on support.oracle.com showed many hits.  I went through a few of them and ruled out the solutions given. This article sounded promising: Oracle SSO Failure - Unable to process request Either the requested URL was not specified in terms of a fully-qualified host name or OHS single sign-on is incorrectly configured (Doc ID 1474474.1).

The solution suggested:

There is  a hardware load-balancer for a multi-tier environment on place, as well as an SSL accelerator.

     For R12, there is a context variable, s_enable_sslterminator, that was set to "#".

     This should be null for e-Business R12 using specific hardwarementioned before.


1. Set  context variable, s_enable_sslterminator to null,

2. Re-ran autoconfig,

3. Re-test Single sign-ons via IE and Firefox now works as expected.

I asked the DBAs to check the value of s_enable_sslterminator:

grep s_enable_sslterminator

and sure enough the value was #

As per article Enabling SSL or TLS in Oracle E-Business Suite Release 12 (Doc ID 376700.1), the value of s_enable_sslterminator should be made null if you are using an SSL accelerator.  In our case we use SSL certificate on the Load Balancer and never on Web servers.

The DBAs removed the #
Ran autoconfig
Deregistered SSO
Registered SSO

The user was able to login after that.