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

Friday, September 28, 2007

Use free DNS service to test extranet

Recently, I got an SOS from a team which was implementing iStore and had a sev 1 SR open with Oracle because they were getting redirected from extranet to internal servers. I checked for s_login_page and s_external_url and sure enough it had the internal server addresses. I asked the DBA to correct those entries in the extranet tiers and recheck the values of these variables in the context file:

set the webentry point, s_webentryhost, to the hostname justanexample
set the webentry domain, s_webentrydomain, to the domain name like example.com
set the active webport, s_active_webport, to the port where the reverse proxy server listen for client requests. For example port 80 for HTTP or 443 for HTTPS.
set the webentry protocol, s_webentryurlprotocol, to the protocol value the clients use to access the reverse proxy server.
set the login page, s_login_page, to webentryprotocol://webentrypoint.webentrydomain:active webport. Replace , , , and with their respective values.
set the external url, s_external_url to the full url like https://justanexample.example.com

After running autoconfig, the problem was solved and it was no longer directing to internal app tiers.

Peculiar thing was that, they were using numeric IP to reach the extranet web tier. I advsied them to find out from the client what name was reserved for the site and use that instead. It turned out that the name was being used for an existing web shop and iStore was supposed to replace it. I told them that they could use a free DNS provider and get a name for their IP in just a few seconds which would give them a proper URL to test instead of using a numeric IP. Furthermore, numeric IP is not supported as the URL by Oracle Apps. So I reserved a name for them through a free DNS service www.dns2go.com. I had used their services long back for a similar purpose.

In case you have a similar problem, you could also use a free DNS service like www.dns2go.com to get a name for your global IP. It would be name_given_by_you.dns2go.com

iStore extranet configuration

Access to iStore and other product pages from outside the DMZ server is controlled by entries in
$IAS_ORACLE_HOME/Apache/Apache/conf/url_fw.conf file. This file lists all the pages to which access is allowed from DMZ server.

You need to put the following line in the INITIAL section of url_fw.conf:

RewriteRule ^/$ /OA_HTML/ibeCZzpHome.jsp [R,L]

As mentioned in Metalink Note 459501.1(How To Allow Access To Custom Istore JSP's From DMZ Server), to enable access to custom istore JSP's whose name does not start with ibe*, url_fw.conf file need to be updated. For example, If custom jsp's follow the naming convention of ABCibe*.jsp, need to add an entry such as this in url_fw.conf file.

#RewriteRule ^/OA_HTML/ABCibe.*\.jsp$ - [L]

Thursday, September 27, 2007

Bookmarks on Metalink

Recently I noticed that metalink has a bookmark link which appears on top right side of any metalink note. You can bookmark any good metalink notes you come across, through this link and refer to them later. This is a really cool feature and gives you access to "that metalink note I saw once, but can't find any more".

Wednesday, September 26, 2007

Oracle Coherence

I attended an Oracle session on its product called Coherence.

Just as Oracle Application Server is Oracle’s answer to the application grid, Oracle Coherence is the answer to the data grid. Coherence is a reliable in memory data management system. It acts both as a broker between application demand and data supply as well as a distributed computing platform to perforformance

What makes Oracle Coherence unique is that it was designed for reliability or to withstand system faults and outages. Additionally, it was built to scale out as needed and handle load gracefully. It is the leading solution in the market both in terms of production applications deployed (over 1500) and size of grids (over 400 servers in a single data grid)




Oracle Coherence is a JCache-compliant in memory distributed data grid solution for clustered applications and application servers. Oracle Coherence makes sharing and managing data in a cluster as simple as on a single server. It accomplishes this by coordinating updates to the data using cluster-wide concurrency control, replicating and distributing data modifications across the cluster using the highest performing clustered protocol available, and delivering notifications of data modifications to any servers that request them.

Developers can easily take advantage of Oracle Coherence features using the standard Java collections API to access and modify data, and use the standard JavaBean event model to receive data change notifications. Functionality such as HTTP Session Management is available out-of-the-box for applications.

Here are some points on Oracle coherence:

What is Coherence ?
Cluster-based Data Management Solution for Applications
Or… Distributed Memory Data Management Solution (aka: Data Grid)

What is Coherence ? Its clustering:

Coherence Clustering is special… It means…
• Collection of processes (members) that work together
• All members have equal responsibility*
• for the health of the cluster
• No static/tight coupling of responsibilities to hardware
• No masters and workers/slaves
• No centralized registries of data or services
• No single points of failure
• No single points of bottleneck

What is Coherence? Data
Data in Coherence…
• Any serializable* Object
• No proprietary classes to extend**
• Fully native Java & .NET interoperability (soon C++)
• No byte-code instruction manipulation or weaving
• Not forced to use Relational Models, Object-Relational-Mapping, SQL etc
• Just real POJOs and PONOs (soon POCOs)
• Domain objects are fine!
*serialization = writing to binary form
** implementing specialized interfaces improves performance!

What is Coherence? Management Solution
Management Solution…
• Responsible for Clustering, Data and Service management,
including partitioning
• Ideally engineers should not have to…
• design, specify and code how partitioning occurs in a solution
• handle Remote Exceptions
• manage the Cluster, either manually or in code
• shutdown the system to add new resources or repartition
• use “consoles” to recover or scale a system.
• These are impediments to scaling cost effectively
• As #members → ∞, management cost should be C
• Ideally log(#members)
• Clustering technology should invisible in your solution!

What is Coherence? For Applications
It’s for Applications!
• Coherence doesn’t require a container / server
• A single library*
• No external / open source dependencies!
• Won’t cause JAR / classloader hell (cf: DLL hell)
• Can be embedded or run standalone
• Runs where Java SE / EE, .NET runs
• Won’t impose architectural patterns**
* May require addition libraries depending on features required.
EG: spring, web, hibernate, jta integration are bundled as separate libraries.
** Though we do make some suggestions ☺

Clustered Hello World…
public static void main(String[] args)
throws IOException {
NamedCache nc = CacheFactory.getCache(“test”);
nc.put(“message”, “Hello World”);
System.out.println(nc.get(“message”));
System.in.read(); //may throw exception
}
• Joins / Establishes a cluster
• Places an Entry (key, value) into the Cache “test” (notice no configuration)
• Retrieves the Entry from the Cache.
• Displays it.
• “read” at the end to keep the application (and Cluster) from terminating.

Clustered Hello World…
public static void main(String[] args) throws
IOException {
NamedCache nc = CacheFactory.getCache(“test”);
System.out.println(nc.get(“message”));
}
• Joins / Establishes a cluster
• Retrieves the Entry from the Cache.
• Displays it
• Start as many applications as you like… they all cluster the are able to
share the values in the cache

What Coherence isn’t!
• It’s not an in-memory-database
• Though it’s often used for transactional state and as a
transient system-of-record
• Used for eXtreme Transaction Processing
• You can however:
• do queries – in a parallel – but not restricted to relationalstyle
(it’s not a RDBMS)
• use SQL-like queries
• perform indexing (like a DB)
• do things like Stored Procedures
• establish real-time views (like Materialized Views).

What Coherence isn’t!
• It’s not a messaging system
• You can use Events and Listeners for data inserts, updates,
deletes
• You can use Agents to handle data changes
• You can use Filters to filter events
• It’s not “just” a Cache!
• Caches expire data!
• Customers actually turn expiry off!
• Why do they take that risk? Why do we let them?
• Data management is based on reliable clustering technology. It’s stable, reliable and highly available.

What is Coherence?
Dependable resilient scalable cluster technology
that
enables developers to effortlessly
cluster stateful applications
so they can
dynamically and reliably share data,
provide services and respond to events
to
scale-out solutions to meet business demand.

You can read more about it in the Coherence knowledge base wiki.

File in patch is not a known Oracle Applications file

While applying 11.5.10.2 maintenance pack 3480000 on an 11.5.9 instance we got the following error:

AutoPatch error:
File in patch is not a known Oracle Applications file:
'bne conf example.txt'

Error occurred in version checking.
See above error messages or AutoPatch log file for details.
Exiting AutoPatch...

BNE.D was already applied on the 11.5.9 instance.

Patch 3480000 ships with bnefile.drv version 115.87.1012.5 which has this content:

# $Header: bnefile.drv 115.87.1012.5 2004/09/10 00:41:31 acleung noship $
# dbdrv: none
#==========================================================================
# (c) Copyright Oracle Corporation 1991
# All Rights Reserved
#
# =========================================================================
# FILENAME
# bnefile.drv
#
# PURPOSE
# Autoinstall file driver for Oracle Web ADI
#
#
# HISTORY
#
# 2001/06/22 DV Initial Creation.
# 2003/08/01 DG Added bne_processCheck.gif
# 3004/06/09 DG BNE.D
# ===========================================================================
#
#

if installation-type admin

bne admin/odf bneprim.odf
bne conf example.txt

Since we already applied BNE.D our bnefile.drv is already at a higher version: ppwms76@tsgsd1004 # adident Header $BNE_TOP/admin/driver/bnefile.drv
$BNE_TOP/admin/driver/bnefile.drv:
$Header bnefile.drv 115.96.1013.8 2007/02/16 03:34:35 dagroves noship $


# $Header: bnefile.drv 115.96.1013.8 2007/02/16 03:34:35 dagroves noship $
# dbdrv: none
#==========================================================================
# (c) Copyright Oracle Corporation 1991
# All Rights Reserved
#
# =========================================================================
# FILENAME
# bnefile.drv
#
# PURPOSE
# Autoinstall file driver for Oracle Web ADI
#
#
# HISTORY
#
# 2001/06/22 DV Initial Creation.
# 2003/08/01 DG Added bne_processCheck.gif
# 3004/06/09 DG BNE.D
# 2007/02/16 DG bug5886305
# ===========================================================================
#
#

if installation-type admin

bne admin/odf bneprim.odf

# SQL scripts for manual post-upgrade or post-install steps.
#

# Script for adding language rows to _TL tables for MLS
# bne admin/sql BNENLADD.sql
# Script for adding language rows to _TL tables for MLS, to be run via adpatch or 11i
bne sql BNENLINS.sql
#
#
# Added .lct files for the loaders
bne admin/import bnecomp.lct
bne admin/import bnecont.lct
bne admin/import bnefile.lct
bne admin/import bneint.lct
bne admin/import bnelay.lct
bne admin/import bnelaylob.lct
bne admin/import bnemap.lct
bne admin/import bneparamlist.lct
bne admin/import bness.lct
bne admin/import bneuserset.lct
bne admin/import bnevw.lct
bne admin/import bnequery.lct
bne admin/import bneperf.lct
bne admin/import bneflexoverride.lct
bne admin/import bnesecurity.lct
# Old Files pre 8.3... for back patching needs
# Added .lct files for the loaders
bne admin/import bnelang.lct
bne admin/import bnelangt.lct


Unfortunately Oracle removed the example file in the latest driver file. That’s why we are getting this issue. There are two workarounds:

Comment the example line in the 3480000/bne/admin/driver/bnefile.drv OR
Add the example line in the $BNE_TOP/admin/driver/bnefile.drv

I logged an SR with Oracle to validate my workaround. As expected, Oracle said: It is not supported to change or amend scripts, therefore please do not change the scripts unless Support advises to do so.

They gave a different workaround which we finally followed:

Please do not edit the bnefile.drv this is not a supported solution.

Instead please do the following:

1. Backup the existing file:
>cd $BNE_TOP/admin/driver
>mv bnefile.drv bnefile_bak.drv

2. Manually copy the bnefile.drv from patch 3480000 to $BNE_TOP/admin/driver

3. Run adpatch again.

4. Once 3480000 is applied successfully replace the backed up file.

Funny thing is that when we upgraded the 11.5.9 ASCP instance which also had BNE.D already applied, this error did not crop up. Still investigating, why it didn't happen in the ASCP instance.

DB invalids in 11.5.10.2 after upgrading from 9207 to 10203

In a test environment, we got more than 300 invalids after upgrading an 11.5.10.2 instance from DB 9.2.0.7 to 10.2.0.3. Metalink had a few notes on some of the objects:

458370.1: After Upgrade To Db 10.2.0.3, several Ctxsys Procedures/Packages ('WWSBR%) Are Invalid
380480.1: Invalid Objects After Upgrading Database From 9.2.0.4 To 10.2.0.2
386746.1: PORTAL Synonyms are Invalid After RDBMS Upgrade 9i to 10g

However, we had 250 invalids which were public synonyms referring to OLAPSYS, AMD and ODM. alter synonym compile resulted in ORA-00980: synonym translation is no longer valid. I checked and found that base objects for these synonyms did not exist. I went a step further and found that the schemas, OLAPSYS, AMD and ODM also did not exist. The conclusion was this:

1. In 2004, this instance was upgraded from 11.5.8 to 11.5.10.2 and DB upgraded from 8i to 9i. The components of OLAPSYS, AMD, and ODM which come preinstalled in a fresh 11.5.10.2 installation, were not installed separately during this upgrade. Hence these schemas were missing which resulted in invalid synonyms.

2. When we upgraded 9207 to 10203, it upgraded only the components which were present in 9207. It did not install OLAPSYS, AMD and ODM.

We followed some metalink notes to create the schemas for OLAPSYS, AMD and ODM.

420791.1 How To Manually Install Data Mining
296187.1 How To Manually Install Oracle OLAP into a 9i or 10g Database After the DB Has Been Created

Once we followed the steps in these notes, the OLAPSYS, AMD and ODM schemas were created and the 250 invalid synonyms became valid.

Monday, September 24, 2007

How to dissect a query to learn more about the optimizer

I recently came across this web presentation on Jonathan Lewis' blog in this post. It is a great presentation from my favorite expert on Oracle. I really admire the way Jonathan is able to see how the optimizer works, underneath the SQL, like Neo in the movie: The Matrix. As already noted in the title of this post, the presentation is about dissecting one query to see how much you can learn about how the optimizer works, and how you have to think when dealing with a difficult problem in SQL.

Oracle Jinitiator 1.1.8.16 decertified with 11i

In Metalink Note 459123.1 published on Sep 21, 2007 Oracle has stated that it is decertifying the use of Jinitiator 1.1.8.16 with 11i. This is because, on August 27th, CIAC reported a problem with versions of Oracle Jinitiator 1.1.8.16 and lower. Further details of this information bulletin is listed at :

http://www.ciac.org/ciac/bulletins/r-334.shtml

In nutshell, the Oracle JInitiator ActiveX control contains multiple stack buffer overflows, which could allow a remote, unauthenticated attacker to execute arbitrary code on a vulnerable system. Due to this security concern, Oracle has advised either to upgrade to Jinitiator 1.1.8.25 or 1.3.1.x or migrate to Sun JRE plugin.

OPT_PARAM hint in 10gr2

Metalink Note 377333.1 describes opt_param hint which was introduced in 10gR2 but remains undocumented in 10gr2 documentation. This hint behaves the same way as setting a parameter (e.g, using alter session) except that the effect is for the statement only.

Thankfully it is documented in 11g documentation here:

The OPT_PARAM hint lets you set an initialization parameter for the duration of the current query only. This hint is valid only for the following parameters: OPTIMIZER_DYNAMIC_SAMPLING, OPTIMIZER_INDEX_CACHING, OPTIMIZER_INDEX_COST_ADJ, OPTIMIZER_SECURE_VIEW_MERGING, and STAR_TRANSFORMATION_ENABLED. For example, the following hint sets the parameter STAR_TRANSFORMATION_ENABLED to TRUE for the statement to which it is added:

SELECT /*+ OPT_PARAM('star_transformation_enabled' 'true') */ * FROM ... ;

Parameter values that are strings are enclosed in single quotation marks. Numeric parameter values are specified without quotation marks.

Sunday, September 23, 2007

Developer 6i Patchset 18 on Apps should be applied on 806 home

A few hours back, Akhilesh called me for FRM-92100 being faced after upgrading an Apps Database to 10gR2 and applying Developer 6i patchset 18. There were 250 invalid synonyms in the DB after the upgrade which did not belong to APPS. Note 380480.1 advises to fix them using ALTER SYNONYM PUBLIC. COMPILE; and also mentions that Core Apps is not affected due to these invalid synonyms. However ALTER SYNONYM command doesn't work and the correct way is to generate the drop and create synonym commands from the source by using this script. However this had nothing to do with the FRM-92100 problem being faced.

On a hunch, I tried to relink f60webmx and it failed with an undefined symbol error.
Akhilesh told me that even though patchset 18 was applied it was reporting the version as 25.2. On checking the version of Developer 6i by running f60gen on command line, we found that it was 25.2 instead of 27.0 which is the version after applying patchset 18. The DBAs who had applied patchset 18 had dutifully followed the readme of patchset 18 (patch 4948577) which states this:

Installation Instructions
  1. Set your ORACLE_HOME.
  2. If you are going to apply this patch on top of an iAS install:
  3. Set ORACLE_HOME to /6iserver
    Prepend /6iserver/bin to the *beginning* of PATH
    Prepend /6iserver/lib to the *beginning* of LD_LIBRARY_PATH
  4. Copy the files in this patch to your $ORACLE_HOME.
  5. Unzip the patch using either WinZip on NT or unzip on Unix.
  6. Run the install script contained in the patch:
  7. cd $ORACLE_HOME/developer6i_patch18
    ./patch_install.sh 2>&1 | tee patch_install_p18.log (ksh)
    ./patch_install.sh |& tee patch_install_p18.log (csh)
  8. Check patch_install_p18.log for errors.
  9. Relink Procedure Builder, Forms, Graphics and Reports:
  10. cd $ORACLE_HOME/procbuilder60/lib; make -f ins_procbuilder.mk install
    cd $ORACLE_HOME/forms60/lib; make -f ins_forms60w.mk install
    cd $ORACLE_HOME/graphics60/lib; make -f ins_graphics60w.mk install
    Reports has both link-time and run-time dependency with libjava.so so you need to append $ORACLE_HOME/network/jre11/lib/sparc/native_threads in $LD_LIBRARY_PATH before linking Reports. The same $LD_LIBRARY_PATH should be used at run-time.
    cd $ORACLE_HOME/reports60/lib; make -f ins_reports60w.mk install


The ORACLE_HOME was set to IAS_ORACLE_HOME erroneously and patchset 18 was applied on IAS_ORACLE_HOME. This was confirmed by checking for the presence of the directory developer6i_patch18 in IAS_ORACLE_HOME. Akhilesh told me that he had tried to install patchset 18, by setting ORACLE_HOME to 806 home and it complained that patchset 18 was already present. I advised him that, since patchset 18 was applied to IAS_ORACLE_HOME it should be uninstalled when ORACLE_HOME was set to IAS_ORACLE_HOME.

# Run the de-install script contained in the patch:

cd $IAS_ORACLE_HOME/developer6i_patch18
./patch_deinstall.sh 2>&1 | tee patch_deinstall_p18.log (ksh)
./patch_deinstall.sh |& tee patch_deinstall_p18.log (csh)

Once he de-installed it from IAS_ORACLE_HOME, he was able to install it in 806 ORACLE_HOME. We were able to relink f60webmx without any symbol errors after this, and FRM-92100 error disappeared. The Jan 2007 CPU patches for Developer 6i needed to be applied correctly on the newly patched 806 home, as they were also applied incorrectly to IAS_ORACLE_HOME by the DBAs.

This is a very basic thing and you should be very clear about this. Developer 6i in Oracle Apps uses the 806 ORACLE_HOME. The IAS_ORACLE_HOME is used only for Apache Jserv and the 9i Application Server 1.0.2.2.2. Step 6 of the interop note for Dev6i also mentions clearly:

NOTE

All references to ORACLE_HOME refer to the 8.0.6-based Oracle Home.


I guess this is a good example of the adage: "Common sense is not so common ".

I'll repeat again, there are 3 different ORACLE_HOME in Apps 11i:

1. Tools ORACLE_HOME: Oracle 8.0.6 home used by Developer 6i
2. IAS ORACLE HOME: Oracle 8.1.7 home which contains, Apache, Jserv and 9i AS 1.0.2.2.2
3. Database ORACLE_HOME: Oracle 9.2.0 or 10.2.0 home which contains the binaries through which the Apps Database runs.

Friday, September 21, 2007

Oracle JDBC 9iR2 Drivers version 9.2.0.8 patch

Patch 4899697 provides JDBC drivers for 9.2.0.8. The pre-requisite for this patch is ATG_PF.H RUP5 ( patch 5473858). You are required to run adconfig after applying this patch. Metalink note 164317.1 has more details about this one.

As mentioned by Mike Shaw in Steven's blog :

You should note that the JDBC driver version has no relation to the Database version, as it is installed on the eBiz Middle Tier. For example, the latest version of JDBC drivers provided by patch 4899697 (9.2.0.8) is the same patch for all RDBMS versions.

Post ATG_PF.H RUP5 patches

On August 22, 2007, Oracle updated its ATG_PF.H RUP5 note with an additional patch described in this post.

Oracle released patch 6318531 on September, 7, 2007 which addresses bugs introduced after application of ATG_PF.H RUP5. Here's an excerpt from the patch readme:

This ARU delivers multiple code enhancements for the AdminAppServer.class. Some
of these enhancements were brought about by issues reported after the
application of 5473858 (11i.ATG_PF.H.RUP5) and is a prerequisite for this ARU.
The following issues are addressed in this ARU:

6351958: 11IRUP5:FND_JDBC SETTINGS IN DBC FILE CHANGED TO LOWERCASE
6318531: ORA-01403 FND_APP_SERVER_PKG IF USERENV('LANG') NOT AN INSTALLED LANG
5050617: ADMINAPPSERVER SHOULD RAISE ERROR IF THE GUEST_USER_PWD DOES NOT POINT
TO GUEST
4051516: CANNOT FIX CORRUPTED GUEST USER USING FNDCPASS OR ADMINAPPSERVER
Metalink Note 459072.1 has more details about the first bug 6351958.

Patch 6168660 (XML PUBLISHER DOES NOT START AS POST REQUEST SERVICE PROCESS ) is another post ATG_PF.H RUP5 patch. This patch modifies the way XML publisher is called making it possible for spawned programs, including FSG, to use the facility.

Thursday, September 20, 2007

First app tier on http, Second app tier on https

I am architecting a solution for an existing E-Biz instance which is going to talk to Kewill through Oracle's ITM (International Trade Management) adapter. For outbound traffice we'll be using a forward proxy. For inbound we need to give a URL which is accessible from internet. As a security policy, any external facing site needs to be on https. But if I implement https on all app tiers, there is going to be a performance hit of 35 - 40%. So instead, if I convert one of the existing app tiers to https, it would be a better solution overall. We have 6 app tiers. 4 app tiers will be on http and 2 app tiers will be on https. The http app tiers are being accessed through a Big IP load balancer URL which is reachable inside the intranet, and the 2 https app tiers will be accessed through a different URL which can be accessed through internet. I have raised an SR with Oracle to validate it. Here is what Oracle said in its response:

Hi Vikram.

I will be working with you on Service Request (SR) 6497953.993: IS IT POSSIBLE TO HAVE ONE APP TIER ON HTTPS, SECOND ON HTTP ?.

I am based in Melbourne, Australia. Our timezone is currently GMT+10 and our hours of operation are 7am - 3.30pm (local time). Should you wish for this SR to be repatriated to a different timezone, please let me know.

One thing I'll need to know is how you plan to access the apps tiers. Will you be going through a load balancer, for example, or connecting directly to them?

If you can give me an idea of your planned system architecture it will help me to find out your answer.


Thanks,
Gavin Stok

Oracle Support
Melbourne, Australia


20-SEP-07 23:40:05 GMT

Email Update button has been pressed: Sending email

21-SEP-07 03:35:02 GMT

New info :
The http app tiers will be accessed through a BigIP load balancer URL and the https app tiers will be accessed through a different BigIP load balancer URL.

- Vikram


21-SEP-07 04:52:24 GMT

UPDATE
======

Hi Vikram.

I write to you in regards to Service Request: IS IT POSSIBLE TO HAVE ONE APP TIER ON HTTPS, SECOND ON HTTP ?.

Thanks for the response.

From our understanding, yes this is possible and will mostly come down to the BigIP setup.

You should configure BigIP to have multiple entry points, with the access for the https nodes to run ssl acceleration. This will prevent you form needing to do ssl setup on the https middle tiers.

The people from BigIP may be better to explain the intricacies of what is needed.

Please let me know if you need any further clarification, or if this now resolves your
query.


Kind Regards,
Gavin Stok

Oracle Support
Melbourne, Australia


21-SEP-07 13:31:05 GMT

Hi Gavin,

Thank you for your answer. Our production environments have BigIP so we'll set it up there without issues. However I wanted to know if this is possible in dev environments where we don't have load balancers. Will this work if we configure https on one app tier and
http on second app tier in dev environment where we don't have BigIp load
balancers ?

- Vikram


25-SEP-07 00:46:43 GMT

UPDATE
======

Hi Vikram.

I write to you in regards to Service Request: IS IT POSSIBLE TO HAVE ONE APP TIER ON HTTPS, SECOND ON HTTP ?.

There should be no problem doing this. You set up one tier with HTTPS, and leave the
other with HTTP, then connect directly to whichever tier you need. The SSL setup
is between the middle tier and the browser, not the middle tier and the databas
e, so this is why there should be no problem.

Please refer to Note 123718.1 for implementing SSL with e-business suite.

Kind Regards,
Gavin Stok

Oracle Support
Melbourne, Australia

Wednesday, September 19, 2007

Can't locate object method "runPipedCmd" via package "ADX::util::Sysutil"

After applying TXK Rollup Q patch 5985992, when we ran adconfig, the following error appeared:

Can't locate object method "runPipedCmd" via package "ADX::util::Sysutil" at
$AD_TOP/bin/adconfig.pl

We checked for the presence of ADX directory in the unzipped patch directory and compared the version of Sysutil.pm in the patch directory and in the directories mentioned in $PERL5LIB.
The version in patch was 115.3 whereas the version in $IAS_ORACLE_HOME/Apache/perl/lib/5.00503/ADX was 115.2. We reapplied the patch, but the version remained 115.2. So we took a backup of $IAS_ORACLE_HOME/Apache/perl/lib/5.00503/ADX directory on the instance and copied the ADX directory from the patch. adconfig worked fine after that.

However, I felt this was not something Oracle could have screwed up on. So I checked other instances which belonged to different projects and found that ADX directory did not exist in $IAS_ORACLE_HOME/Apache/perl5.00503. ADX directory existed only in $AU_TOP/perl. When I checked for version of Sysutil.pm in $AU_TOP/perl/ADX, it was 115.3. So the patch did update the ADX directory. Since the $IAS_ORACLE_HOME/Apache/perl5.00503 directory came first in the PERL5LIB environment variable, adconfig was referencing the ADX in that directory. I have an SR open with Oracle to find out whether ADX directory needs to be present in $IAS_ORACLE_HOME/Apache/perl5.00503 at all and did a patch place it there. If Oracle says it should not be there, then most probably one of the DBAs put it in that directory manually.

My guess was correct, this is what Oracle said:

UPDATE
=======

ATG/ICM Advanced Resolution Engineer Review

Vikram,

I took a look at several 11i internal instances and none of them have an $IAS_ORACLE_HOME/Apache/perl/lib/5.00503/ADX subdirectory. This shouldn't exist, because we patch up the files under $AU_TOP/perl/ADX with the TXK/ADX patches, which wouldn't touch anything under any of the ORACLE_HOMEs (only APPL_TOP). I can't explain how you got an ADX subdirectory under the $IAS_ORACLE_HOME My speculation is that
maybe sometime in the past you had a PERL5LIB issue on this instance and you were instructed to copy the $AU_TOP/perl/ADX subdirectory (and contents) to $IAS_ORACLE_HOME/Apache/perl/lib/5.00503 instead of fixing. I would suggest that you rename $IAS_ORACLE_HOME/Apache/perl/site_per/5.00503/ADX first and ensure that you
have no issues with bouncing the services, running adconfig, running the applications, etc. Then after things have been tested out thoroughly you can remove the ADX backup directory entirely.

Regards,
Lynne

Tuesday, September 18, 2007

Steps to verify IMAP and SMTP setup on Apps

Updated: August 16, 2017

Here's the way to test it with IMAPS on port 993:

openssl s_client -crlf -connect imapserver.example.com:993
* OK The Microsoft Exchange IMAP4 service is ready.
a1 login workflow.mailer@example.com wfmailerpassword

a1 LIST "" "*"

a1 select INBOX

a1 status INBOX

a1 logout

Metalink note 242941.1 gives the steps as follows in point 8 :

8. Verify the SMTP and IMAP Server details are correct and these components are working.
Make sure customer has set up a Mail Client that connects to the same IMAP account that the Java Mailer is using, and it is configured to send outbound emails using the same SMTP Server. Verify that you can send an email from this account and have it be received by the receiver. Verify that you can send an email to this IMAP account and that the email is received by this IMAP account.
· Following are the manual steps to verify SMTP and IMAP server configuration. The lines marked with ‘%%’ are the commands to enter. Please exclude ‘%%’ when entering the commands, ‘%%’ is there for clarification purposes. The following examples were run on a Sun Solaris platform, on other UNIX platforms, the return result may look slightly different, in any case, you will get the general idea.
Important: These tests must be run from the concurrent processing tier where the Java Mailer is running.
a) Verify SMTP Server: In this example, crmops02.us.oracle.com is the SMTP server running on the default port 25, and ap102ses.us.oracle.com is the server where the Java Mailer(concurrent processing tier) runs. Wfuser is the mail account used by the Java Mailer. myemail@oracle.com is a valid email address to receive email from the Java Mailer.
%% telnet crmops02.us.oracle.com 25
Trying 144.25.76.117...
Connected to crmops02.us.oracle.com.
Escape character is '^]'.
220 crmops02.us.oracle.com ESMTP Sendmail 8.9.3 (PHNE_18546)/8.7.1; Fri, 31 Jan 2003 05:56:18 -0800 (PST)
%% EHLO ap102ses.us.oracle.com
250-crmops02.us.oracle.com Hello ap102ses.us.oracle.com [144.25.76.44] (may be forged), pleased to meet you
250-EXPN
250-VERB
250-8BITMIME
250-SIZE
250-DSN
250-ONEX
250-ETRN
250-XUSR
250 HELP
%% MAIL FROM: wfuser@crmops02.us.oracle.com
250 wfuser@crmops02.us.oracle.com... Sender ok
%% RCPT TO: myemail@oracle.com
250 myemail@oracle.com... Recipient ok
%% DATA
354 Enter mail, end with "." on a line by itself
%% Subject: Test message via CRMOPS2
%%
%% Test message body
%% .
250 FAA17833 Message accepted for delivery
%% quit
221 crmops02.us.oracle.com closing connection
Connection closed by foreign host.
Verify that myemail@oracle.com receives an email from wfuser01@crmops02.us.oracle.com. If not, then the SMTP server is not configured properly. Please contact the SMTP System Administrator to correct the problem.
b) Verify IMAP server: In this example, ap700lts.us.oracle.com is the IMAP server running on the default port 143. Each IMAP4 command must start with a number/character, and it does not matter if you use the same number/character for all the commands. Spaces are important and multiple spaces/tabs are not allowed. For each successful command completion the server ends the response by "n OK ....". If a command fails then the failure reason is printed.
%% telnet ap700lts.us.oracle.com 143
Trying 130.35.81.214...
Connected to ap700lts.us.oracle.com.
Escape character is '^]'.
* OK Domino IMAP4 Server Release 5.0.11 ready Sun, 23 Nov 2003 10:26:49 -0800
%% 1 login wfuser welcome
1 OK LOGIN completed
%% 1 select "INBOX"
* 23 EXISTS
* 0 RECENT
* OK [UIDVALIDITY 0] UIDs valid
* FLAGS (\Flagged \Seen \Answered \Deleted \Draft)
* OK [PERMANENTFLAGS (\Flagged \Seen \Answered \Deleted \Draft)] Permanent flags
1 OK [READ-WRITE] SELECT completed
%% 1 select "DISCARD"
* 54 EXISTS
* 0 RECENT
* OK [UIDVALIDITY 6] UIDs valid
* FLAGS (\Flagged \Seen \Answered \Deleted \Draft)
* OK [PERMANENTFLAGS (\Flagged \Seen \Answered \Deleted \Draft)] Permanent flags
1 OK [READ-WRITE] SELECT completed
%% 1 select "PROCESSED"
* 58 EXISTS
* 0 RECENT
* OK [UIDVALIDITY 4] UIDs valid
* FLAGS (\Flagged \Seen \Answered \Deleted \Draft)
* OK [PERMANENTFLAGS (\Flagged \Seen \Answered \Deleted \Draft)] Permanent flags
1 OK [READ-WRITE] SELECT completed
%% 1 logout
* BYE ap700lts.us.oracle.com IMAP4rev1 server terminating connection
1 OK LOGOUT completed
Connection closed by foreign host.
c) Patch 3265133 (included in Rollup 5.1 - patch 3409889) provides a simple commandline interface that uses native JavaMail APIs. This interface can be used to validate IMAP / SMTP connectivity from the concurrent-tier where mailer runs. Syntax can be found by running the following on the concurrent-tier after initializing the APPS environment:
Values in <...> are the values defined (or to be defined) for mailer configuration using OAM.
IMAP Server:
$AFJVAPRG -classpath $AF_CLASSPATH -Dprotocol=imap -Dserver= -Dport=143
-Daccount= -Dpassword= -Dconnect_timeout=120
-Dfolder=INBOX oracle.apps.fnd.wf.mailer.Mailer
Example output:
----------------
bash-2.05$ $AFJVAPRG -classpath $AF_CLASSPATH -Dprotocol=imap -Dserver=ap700lts.us.oracle.com -Dport=143
-Daccount=notesuser2@ap700lts.us.oracle.com -Dpassword=XXXXX -Dconnect_timeout=120 -Dfolder=INBOX oracle.apps.fnd.wf.mailer.Mailer
Server ap700lts.us.oracle.com at port 143 is reachable
Successfully connected to the IMAP account
Testing existense of folder:INBOX
Folder [INBOX] exists
Note: Special folders like Inbox/Trash may not get listed on some IMAP servers
Folders defined are:
INBOX
Drafts
Sent
PROCESS
DISCARD
------------------------------------------
Note: Email Processing (PROCESS and DISCARD Folder) should get listed like "PROCESS" and "DISCARD" in output above. If not, these folders are required to be created by connecting to the IMAP / Inbound Email account with any email client (e.g. Netscape Messenger or Microsoft Outlook). These folders should be created before they are specified in the mailer configuration within OAM. The notification mailer may not be able to access folders that were created using command line tools outside the e-mail client.
SMTP Server:
$AFJVAPRG -classpath $AF_CLASSPATH -Dprotocol=smtp -Dserver=
-Dport=25 -Daccount= -Dconnect_timeout=120
oracle.apps.fnd.wf.mailer.Mailer
Example Output:
-----------------
bash-2.05$ $AFJVAPRG -classpath $AF_CLASSPATH -Dprotocol=smtp -Dserver=ap6120rt.us.oracle.com -Dport=25
-Daccount=notesuser6@ap700lts.us.oracle.com -Dconnect_timeout=120 oracle.apps.fnd.wf.mailer.Mailer
Server ap6120rt.us.oracle.com at port 25 is reachable
Successfully connected to the SMTP account
------------------------------------------
If above tests are successful from commandline, mailer should also be able to connect to IMAP / SMTP server. If these tests are not successful then mailer will also not be able to connect. Then it is IMAP / SMTP server issue that administrators need to take a look at.


Monday, September 17, 2007

BBED: Block Browser Editor

BBED is a character based utility to browse (and if necessary edit) the contents of Oracle data blocks symbolically. Because the data structures involved are fairly complex and inter-related, it would be very difficult to diagnose and fix corruptions using just a hex editor. So BBED is a potentially significant recovery utility.

Of course, it is not intended for users to play with as a way of learning more about Oracle data block internals. To that end it is protected by a password known only to trained Oracle support people. It does have a HELP facility, but that is only enough to remind users of the syntax. Anyone who does not already understand the data structures and their relationships, and who has not been trained in the use of BBED would hard pressed to make any use of it.

Hackers might use BBED to break into an Oracle database. Tools like BBED can be used to view data directly within their data block (bypassing the Oracle later), and because BBED writes directly the data block, BBED could be used by hackers to update a database without logging and auditing.

This paper titled "disassembling the Oracle data block" has complete instructions for installing and using BBED. This make command tells how to linkedit BBED:

make -f ins_rdbms.mk BBED=$ORACLE_HOME/bin/bbed $ORACLE_HOME/bin/bbed

Orafaq.com's Miladin Modrakovic has an article on Fixing" SYS for hacking purposes which uses bbed

Solaris 10 IMAP

There's a very good post by Joe Mocke on his blog on compling IMAPD with SSL on Solaris 10

Solaris 10 IMAP requires SSL certificate which needs to be created (as mentioned in Joe Mocke's blog):

Configuring the imapd certificate -- Thanks to Rama on the magic OpenSSL command. All that you really do is create a PEM certificate called imapd.pem in the OpenSSL certs folder:

cd /opt/sfw/openssl/certs
openssl req -new -x509 -nodes -out imapd.pem -keyout imapd.pem -days 3650
Most of the times, this is not done and we get authentication errors while using IMAP. Something like this:

NO Invalid login credentials

If you get this error while testing IMAP, then check whether your SSL certificate is installed.

Sunfreeware.com has a Quick Guide to setting up IMAP on Solaris 10. Be sure to go through that it has all the steps for doing it correctly.

Friday, September 14, 2007

Good post clone procedure

Once you complete your clone, it is a good idea to run this to clean up fnd tables which contain information about source (Production) nodes:

EXEC FND_CONC_CLONE.SETUP_CLEAN;
COMMIT;
EXIT;

This step will delete all data from system tables such as FND_NODES, FND_OAM_CONTEXT_FILES etc. The correct information for the current instance will be repopulated once autoconfig is run.

So. Run Autoconfig.

Lets go a little deeper and go through the source code of this package:

SQL> desc apps.fnd_conc_clone
PROCEDURE SETUP_CLEAN
PROCEDURE TARGET_CLEAN

We'll take a look at setup_clean procedure for now:

351 procedure setup_clean is
352 begin
353 -- Delete info from FND_CONCURRENT_QUEUE_SIZE table
354
355 Delete From fnd_Concurrent_Queue_Size
356 where concurrent_queue_id in
357 (Select concurrent_queue_id
358 from fnd_concurrent_queues
359 where manager_type in (2,6));
360
361 Delete from fnd_concurrent_queue_size
362 where concurrent_queue_id in
363 (select concurrent_queue_id
364 from fnd_concurrent_queues
365 where manager_type in
366 ( select service_id
367 from fnd_cp_services
368 where upper(service_handle) in
369 ('FORMSL', 'FORMSMS', 'FORMSMC',
370 'REPSERV', 'TCF', 'APACHE',
371 'JSERV', 'OAMGCS')));
372
373 -- Delete from FND_CONCURRENT_QUEUES_TL table
374 Delete From fnd_Concurrent_Queues_tl
375 where concurrent_queue_id in
376 (Select concurrent_queue_id
377 from fnd_concurrent_queues
378 where manager_type in (2,6));
379
380 Delete from fnd_concurrent_queues_tl
381 where concurrent_queue_id in
382 (select concurrent_queue_id
383 from fnd_concurrent_queues
384 where manager_type in
385 (select service_id
386 from fnd_cp_services
387 where upper(service_handle) in
388 ('FORMSL', 'FORMSMS', 'FORMSMC',
389 'REPSERV', 'TCF', 'APACHE',
390 'JSERV', 'OAMGCS')));
391
392 -- Delete from FND_CONCURRENT_QUEUES table
393 Delete from fnd_concurrent_queues
394 where manager_type in (2,6);
395
396 Delete from fnd_concurrent_queues
397 where manager_type in
398 (select service_id
399 from fnd_cp_services
400 where upper(service_handle) in
401 ('FORMSL', 'FORMSMS', 'FORMSMC',
402 'REPSERV', 'TCF', 'APACHE',
403 'JSERV', 'OAMGCS'));
404
405 -- Delete from FND_NODES table
406 Delete from fnd_nodes;
407
408 --
409 -- TRUNCATE TABLES
410 --
411 -- Find out the database user for FND and JTF objects
412 get_database_user;
413
414 truncate_table(OracleUserFND , 'FND_OAM_CONTEXT_FILES');
415 truncate_table(OracleUserFND , 'FND_OAM_APP_SYS_STATUS');
416
417 -- Added following JTF tables based on bug 2949216
418 truncate_table(OracleUserJTF , 'JTF_PREFAB_HA_COMPS');
419 truncate_table(OracleUserJTF , 'JTF_PREFAB_HA_FILTERS');
420 truncate_table(OracleUserJTF , 'JTF_PREFAB_HOST_APPS');
421 truncate_table(OracleUserJTF , 'JTF_PREFAB_WSH_POES_B');
422 truncate_table(OracleUserJTF , 'JTF_PREFAB_WSH_POES_TL');
423 truncate_table(OracleUserJTF , 'JTF_PREFAB_WSHP_POLICIES');
424 truncate_table(OracleUserJTF , 'JTF_PREFAB_CACHE_STATS');
425
426
427 -- TRUNCATE TABLES RELATED TO TOPOLOGY
428 truncate_table_topology;
429 end;
430
431 end;

Thursday, September 13, 2007

How to start sendmail(SMTP) and IMAP on Solaris 10

Short answer:

For SMTP(Sendmail):

Login as root
/lib/svc/method/smtp-sendmail start

or

svcadm restart sendmail

For activating IMAP:

1. Download imap package from ftp://ftp.sunfreeware.com/pub/freeware/sparc/10/imap-2006e-sol10-sparc-local.gz

2. # gunzip imap-2006e-sol10-sparc-local.gz
3. # pkgadd -d imap-2006e-sol10-sparc-local
4. # update file /etc/inetd.conf with the proper lines pointing to imapd and ipop3d:

imap stream tcp nowait root /usr/local/sbin/imapd imapd
pop2 stream tcp nowait root /usr/local/sbin/ipop2d ipop2d
pop3 stream tcp nowait root /usr/local/sbin/ipop3d ipop3d

5. # inetconv
6. # inetadm (to verify the services)

Commands to restart IMAP on Solaris 10

Login as root

# svcs -a |grep imap
online Feb_15 svc:/network/imap/tcp:default
# svcadm restart svc:/network/imap/tcp:default
# svcs -a |grep imap
online 22:07:24 svc:/network/imap/tcp:default
#

Forms doesn't launch from self service

I am sure you must have faced this so many times and spent frustating hours debugging it. I'll update this thread with all the possible solutions I have found:

Solution I

Do a ping -s
It should return the correct hostname.domainname
Check whether the hostname.domainname is the value of the context file variable "s_oacore_trusted_oproc_nodes"

grep s_oacore_trusted_oproc_nodes $CONTEXT_FILE

If it is not present, add it in the variable value as ,hostname

Run autoconfig.

Check for the string Allow in Apache configuration files:

grep Allow $IAS_ORACLE_HOME/Apache/Apache/conf/*.conf

Look for the phrase Allow from hostname.domainname

If its not present, add it in all the files.

Bounce Apache

Retest issue

Solution II

vi $IAS_ORACLE_HOME/Apache/Apache/conf/security.conf
Go to end of the file with the vi command: Shift+G
Comment these two lines which begin with SecFilterSelective by placing # before them:

SecFilterSelective "REQUEST_URI" !^/pls/[-a-z0-9_]+/!?[a-z](([a-z0-9_\$#]){0,29})(\.[a-z](([a-z0-9_\$#]){0,29})){0,2}(\?.*
){0,1}$

# Make sure the parameter names contains only valid subset of 7-bit ascii
SecFilterSelective "ARGS_NAMES" !^(([a-z0-9_]){1,30})((\.[a-z0-9_]{1,30}){0,1})$

These lines appear in security.conf after applying ATG_PF.H RUP4 or TXK Rollup O.

Save the file security.conf with vi command :wq

Bounce Apache

Retest issue.

Solution III

Concurrent Managers Fail to Start After Cloning with ATG RUP 5

We faced this issue in one of our first clones after ATG_PF.H RUP5. This issue is well documented in metalink note id 434613.1. A workaround has been given for altering the trigger code causing the issue. Just ensure that the value of support_cp is set to Y in FND_NODES table for the node which is erroring out in logs. A permanent fix is being made available in ATG_PF.H RUP6.

Copy paste from metalink:

Applies to:

Oracle Application Object Library - Version: 11.5.10.2
This problem can occur on any platform.
11i with ATG RUP 5
Release 12

Symptoms

After cloning, concurrent managers fail to start.

The logfile for Internal Manager shows following error:

Could not initialize the Service Manager FNDSM__. Verify that has been registered for concurrent processing.

Output of FNDCCMDiagnostics.sh shows that Service manager (FNDSM) and Internal Monitor (FNDIM) for concurrent node do not exist. Absence of Service Manager causes concurrent managers not to start.

Changes

Application of ATG RUP 5 on 11i

Cause

The cause of this problem has been identified in Bug 6085070. It is caused by change in FNDSM trigger body.

FNDSM trigger body is changed to:

CREATE OR REPLACE TRIGGER fndsm
AFTER INSERT ON FND_NODES
FOR EACH ROW

BEGIN

if ( :new.NODE_NAME <> 'AUTHENTICATION' ) then
if ( (:new.SUPPORT_CP='Y')
or (:new.SUPPORT_FORMS='Y')
or (:new.SUPPORT_WEB='Y') ) then
fnd_cp_fndsm.register_fndsm_fcq(:new.NODE_NAME);

end if;

if (:new.SUPPORT_CP = 'Y') then
fnd_cp_fndsm.register_fndim_fcq(:new.NODE_NAME);
end if;

end if;

END;


This causes the problem if database tier and concurrent tier are on the same node in cloned environment.

When autoconfig is run on db tier, it creates the node but FNDSM and FNDIM are not created
because support_CP, SUPPRT_FORMS and SUPPORT_WEB are set to 'N'. When autoconfig is run next for apps tier, these column as updated to 'Y' but the trigger is NOT fired as it in 'ON INSERT' trigger and not 'ON UPDATE' trigger.

This causes FNDSM and FNDIM not to be created for the node which results in concurrent managers
not to start after cloning.

Solution

To implement the solution, please execute the following steps:

1. Connect via sqlplus as APPS user

2. Execute the following to alter trigger FNDSM:

CREATE OR REPLACE TRIGGER fndsm
AFTER INSERT OR UPDATE ON FND_NODES
FOR EACH ROW
BEGIN
if ( :new.NODE_NAME <> 'AUTHENTICATION' ) then
if ( (:new.SUPPORT_CP='Y')
or (:new.SUPPORT_FORMS='Y')
or (:new.SUPPORT_WEB='Y') ) then
fnd_cp_fndsm.register_fndsm_fcq(:new.NODE_NAME);
end if;
if (:new.SUPPORT_CP = 'Y') then
fnd_cp_fndsm.register_fndim_fcq(:new.NODE_NAME);
end if;
end if;
END;
/
commit;

3. Clean FND_NODES by executing the following:

exec FND_CONC_CLONE.SETUP_CLEAN;

4. Run autoconfig.

5. Restart concurrent managers and retest.

6. To avoid the same problem for future cloning, execute step #2 on source environment to alter FNDSM trigger

OR

When available, apply Patch 5903765 (ATG RUP 6) which delivers the above code in aftcm046.sql version 115.20 to alter FNDSM trigger. Note that this patch was not available at the time of writing this document (June 2007).

References

Bug 6085070 - FNDSM TRIGGER CAUSES SERVICE MANAGER NOT TO BE CREATED AFTER CLONING SINGLE NODE

oracle.apps.fnd.common.PoolException: Exception creating new Poolable object.

Following error may appear when you click on AppsLocalLogin.jsp link while doing adconfig on DB tier:

JSP error
oracle.apps.fnd.common.PoolException: Exception creating new Poolable object.

It is best to have the services down while doing autoconfig, to avoid such errors.

I saw the same error when the database server had gone down and was not available due to hardware issues.  After the server came up and DB was started up, the error disappeared.

Query for datafile growth in a database

select to_char(creation_time, 'RRRR Month') "Month",
sum(bytes)/1024/1024 "Growth in Meg"
from sys.v_$datafile
where to_char(creation_time, 'RRRR Month') like '2007%'
group by to_char(creation_time, 'RRRR Month')
/

Wednesday, September 12, 2007

AppsLocallogin shows blank page or HTTP-403

An instance which had a new virtual name was recently cloned. Whenever the E-Business Home Page link (which points to http://www.justanexample.com/OA_HTML/AppsLocallogin.jsp) was clicked it showed up a blank page or HTTP-403 (If you go to Internet Explorer -> Tools Menu -> Advanced -> uncheck Show friendly HTTP error messages).

mod_jserv.log showed a lot of errors like this one:
..] [error] OPM:Can not find one alive process
These errors indicate that Jserv is not up-and-running. This is confirmed by the following entry in the error_log which indicates that oprocmgr is not able to find a active Jserv JVM.

If you check the Apache access log :

grep 403 $IAS_ORACLE_HOME/Apache/Apache/access_log

It would return a lot of lines similar to this one:

physicalservername.com - - [12/Sep/2007:17:25:55 -0400] "GET /oprocmgr-service?cmd=Register&index=0&modName=JServ&grpName=DiscoGroup&port=17140 HTTP/1.1" 403 226

This means that oprocmgr-service doesn't trust physicalservername.com

I checked the "s_oacore_trusted_oproc_nodes" variable in xml file and it pointed to the new virtual name only. I added the physicalservername.com also

"s_oacore_trusted_oproc_nodes=justanexample.com,physicalservername.com

and ran autoconfig. The problem was solved. If you do not want to run autoconfig, the file to modify is $IAS_ORACLE_HOME/Apache/Apache/conf/oprocmgr.conf

======================================
Port 1234

Order Deny,Allow
Deny from all
Allow from localhost
Allow from justanexample
Allow from justanexample.com
Allow from physicalservername.com -- Added this line
======================================

This problem and its solution is well documented in metalink note 372096.1.

You should add this line in these files present in $IAS_ORACLE_HOME/Apache/Apache/conf/

trusted.conf
apps.conf
oprocmgr.conf
httpd_pls.conf

Allow from physicalservername.com

Notes from the field: DB doesnt start after changing spfile on ASM

If you changed your spfile with

alter system set =.... scope=spfile;

shutdown immediate;

startup;

The database refuses to startup. The spfile is on ASM storage, so you don't have a filesystem to visit and modify it.

Steps to follow to revert spfile to original:

In $ORACLE_HOME/dbs

. oraenv

sqlplus "/ as sysdba"

startup nomount

create pfile='recoverinit.ora' from spfile
/
shutdown immediate
exit

Edit the newly created pfile to change the parameter which caused trouble, to its original value

Save the pfile

sqlplus "/ as sysdba"

startup pfile='recoverinit.ora' (or whatever you called it in step one).

create spfile='+DATA/ORACLE_SID/spfileORACLE_SID.ora' from pfile='recoversp'
/
Note:The name of the spfile is in your original init.ora so adjust to suit

shutdown immediate
startup
exit

Query to get current session timezone from FND views

I got this query from Vasu. This query gives you the current session timezone from Oracle Apps FND views:

SELECT TIMEZONE_CODE
FROM FND_TIMEZONES_VL WHERE UPGRADE_TZ_ID = (SELECT fnd_profile.VALUE('SERVER_TIMEZONE_ID') FROM DUAL)
/

TIMEZONE_CODE
--------------------------------------------------
America/New_York

Tuesday, September 11, 2007

MWA_PH_DEVCFG_NOTFOUND

We got a problem in one of our environments where Mobile services hang. We have raised an SR with Oracle for this one, and here's the SR text:

Mobile services are not responding intermittently when logging in as a mobile user. Telnet to the service works fine, but when we try to login to the mobile application with the mobile user and password, it hangs. This is happening every 2 - 3 hours. We have to bounce the mobile services as a workaround. Error shown in logs is:

MWA_PH_DEVCFG_NOTFOUND: 192.168.3.60
configuration file is not found. Will use default configuration for now
[Sun Sep 09 07:12:40 EDT 2007] (Thread-9328) MWA_PH_DEVCFG_NOTFOUND: 192.168.3.58
configuration file is not found. Will use default configuration for now
[Sun Sep 09 07:12:41 EDT 2007] (Thread-9329) MWA_PH_DEVCFG_NOTFOUND: 192.168.3.61
configuration file is not found. Will use default configuration for now
[Sun Sep 09 07:12:41 EDT 2007] (Thread-9327) PH: User null got disconnected...
[Sun Sep 09 07:12:41 EDT 2007] (Thread-9327) PH: caught IOException

### Steps to Reproduce ###
telnet 11111
This shows the following screen:


Device List
1 Default
2 Symbol Device
3 Intermec Device
4 GUI Client

Press Enter for 1

It shows the following

Login
--------------------
Oracle
Mobile
Applications
--------------------
User Name:

Password :

Database :production
--------------------

Once we key in the username and password, it hangs/doesn't respond.

Oracle responded with 34 questions:

1. What is the version of JDK?
We would like to know the full version of JAVA/JDK that you are using.
Please go to your mwactl.sh file.
Find the "JAVA=..." parameter in this file.
cd to the director that is referenced by this JAVA parameter.
do a java -version and it should return something like this:
$java -version
java version "1.4.2_04"

Please ensure that the mwactl.sh using the jdk 1.4.2_20 or higher
With the Latest JDK 1.4.2_x and 1.5.x the overall Performance of the MWA Server increases
. We were able to UNOFFICALLY test the number of User Connection with 80 users
on one Telnet Server for a week without any issues. Basically the newer the JDK version is, the better MWA performed with each and every JDK signal version release.

2. Make sure Patch 4734840 - Oracle Inventory, Warehouse Management, Mobile Application Server (MWA), and Receiving (PO): Release 11.5.10, Rollup Patch 3
(INV/WMS/MWA/RCV 11.5.10 RUP3), has been applied.

OR

Patch 5855276 - Oracle Warehouse Management System : Release 11.5.10, Rollup Patch 4 ( WMS 11.5.10 RUP4)


This is the single most important Patch to have applied to a MWA Server. This Patch provided numberous fixes to the MWA Server, Dispatcher and all the Products that use the MWA Server all in one shot. This Patch will place the MWA Server on the latest Core Server file system versions.


If the above two suggestions do not help with this issue, then please proceed with the action plan below.

ACTION PLAN
----------------------

1. When did this problem start?

2. How long have you been using MWA?
Have there been any changes that might have brought on this issue - patching, cloning, upgrades, ...?

3. Does this issue happen everytime for the same transactions?

4. What are the particular transactions from Data Warehouse that have this issue?

5. What are the specific steps to reproduce this issue?

6. Does this issue happen everytime for the same transactions?

7. Are there some MWA transactions that do not have this issue?

8. Are you using WMS, INV, or other Applications that use MWA?

9. How many MWA users do you have connected during the peak load?

10. Does this issue occur with telnet sessions as well as the hand-held devices?

11. What is the version of JDK?
We would like to know the full version of JAVA/JDK that you are using.
Please go to your mwactl.sh file.
Find the "JAVA=..." parameter in this file.
cd to the director that is referenced by this JAVA parameter.
do a java -version and it should return something like this:
$java -version
java version "1.4.2_04"

Please ensure that the mwactl.sh using the jdk 1.4.2_20 or higher
With the Latest JDK 1.4.2_x and 1.5.x the overall Performance of the MWA Server increases. We were able to UNOFFICALLY test the number of User Connection with 80 users on one Telnet Server for a week without any issues. Basically the newer the JDK version is, the better MWA performed with each and every JDK signal version release.

12. What are the hardware and network configurations?

13. How is your instance configured - how many nodes, what is on each node, ....?
Are the database server and the server hosting the MWA servers on different machines?
Are there any 3rd party Load Balancers configured?

14. Is there a firewall between the database server and the server hosting the MWA servers?
If firewall is used, what is the firewall timeout value?

15. Are you using Oracles Dispatcher or are you using a third party product to dispatch the request?

16. Does the issue reproduces the issue if the MWA server is bounced daily? If not checked, then as documented in the MWA Quick Reference Guide:
The MWA server should be stopped and started (bounced) at least daily so that memory can be flushed.
http://logistics.us.oracle.com/collat/repository/docs/MwaQuickRef.pdf

If you haven't already done so, please review the following note for some good information about bouncing your MWA and Dispatcher:
Note 198543.1 How To Rebounce the Mobile Application Server for Industrial Applications v1.0.8


17. Please upload the mwa.cfg file located under $MWA_TOP/secure and the $MWA_TOP/bin/mwactl.sh file.

18. How many MWA Telnet Servers are running?

19. If using Oracle Dispatcher:
How many Dispatchers are you running?
Double check the MWA Quick Start Guide for Dispatcher setup info:
MWA Quick Start Guide can also be found in this note:
72450.1 Ext/Pub Oracle Inventory White Papers Template:
Click on the link: MWA Server and Dispatcher Configuration Quick Start Reference

20. What are the ports that your MWA Listeners and Dispatcher are running on?

21. What command are you using to start and stop the MWA Server?

22. Make sure that the jserv.properties file is edited correctly for MWA:
wrapper.bin.parameters=-Doracle.apps.mwa=[full path to $MWA_TOP]

23. Make sure Patch 4734840 - Oracle Inventory, Warehouse Management, Mobile Application Server (MWA), and Receiving (PO): Release 11.5.10, Rollup Patch 3 (INV/WMS/MWA/RCV 11.5.10 RUP3), has been applied.

OR

Patch 5855276 - Oracle Warehouse Management System : Release 11.5.10, Rollup Patch 4 ( WMS 11.5.10 RUP4)

This is the single most important Patch to have applied to a MWA Server. This Patch provided numberous fixes to the MWA Server, Dispatcher and all the Products that use the MWA Serv
er all in one shot. This Patch will place the MWA Server on the latest Core Serv
er file system versions.

24. You can see the MWA DBsession-id for a particular user using the Server Manager.
Server Manager will shows all the details of the users logged into the MWA server.
Please make sure that you download review the readme and pre-requisites, and apply Patch.5166627
Then navigate: MWA Server Manager -->Supply chain --> Monitor to monitor the users connection.

25. Please make sure that you have the latest Autoconfig patch and that Autoconfig has been
run. Ref. 5478710 TXK-O

26. Please provide a Performance trace and upload fresh set of logs

1. Clear the mobile log files
2. To enable trace-level logging, go to "$MWA_TOP/secure/" directory and in the file "mwa.cfg" edit the line starting with "mwa.LogLevel=" to contain "mwa.LogLevel=perform".
3. Search for mwa.SystemLog and note the string
4. Search for mwa.logdir and note the directory
5. Clear the existing *.INV.log and *.system.log files
6. After this you have to restart the server and reproduce the problem.
7. Upoad the logs specified in the above parameters as soon as the issue is reproduced.
· system.log
· dispatcher.log


In mwa.cfg, the values available for mwa.LogLevel are, fatal, error, warning, debug, trace, and 'Perform' is a valid, although undocumented value for this parameter. There should be a trace file generated where ever customer's normal trace dumps are written to. The 'Perform' value should create the trace with performance statistics for the session. Bounce the MWA telnet server after making this change. Then recrerate the issue and upload the system.log file

27. How much memory is allocated to the MWA server? (Hint: check the $MWA_TOP/bin/mwactl.sh script VM_CONFIG settings)

28. The INV_MOBILE_LOGIN_INFO_PVT.LOG_USER_INFO is called and should add a new record in the MTL_MOBILE_LOGIN_HIST table. Check the system.log to verify that INV_MOBILE_LOGIN_INFO_PVT.LOG_USER_INFO was called. Also, check the MTL_MOBILE_LOGIN_HIST table and verify that a row was added to the table for the users connection - query the table by USER_ID.

29. What version of apache are you running? If you are not sure, you can run the following command from your iAS_ORACLE_HOME/Apache/bin directory
httpd -v

30. What is your system profile option "Self Service Personal Home Page Mode' set to?

31. What version of Forms are you on - 6.0.8.x - what is the 'x' version? Use Help > About Oracle Applications to find this version.
Or, from sqlplus you can run the following command:
For 6i forms:
!f60gen \? | grep Forms | grep Version | awk '{print $6}'

For 10g forms:
!frmbld \? | grep Forms | grep Version | awk '{print $6}'

32. What is the version of the following files:

ident $MWA_TOP/bin/MWADIS
mwacfg.lc
mwadis.oc

$MWA_TOP/admin/template/mwactl.sh
$MWA_TOP/admin/template/mwactl.cmd
$MWA_TOP/bin/mwactl.sh
$MWA_TOP/bin/mwactl.cmd
$MWA_TOP/oracle/apps/mwa/presentation/telnet/ProtocolHandler.class
$JAVA_TOP/oracle/apps/fnd/security/SessionManager.class
$JAVA_TOP/oracle/apps/mwa/container/ApplicationsObjectLibrary.class


33. In the mwa.cfg, what are the values of
mwa.DispatcherWorkerThreadCount
mwa.DispatcherClientsPerWorker
set to?

34. It is important that the file descriptors are set before starting MWA on a Unix Instance. Use the command "ulimit -n 1024" before using mwactl.sh to start MWA Server.

Reference the following note for configuration tips and latest MWA patches: 269991.1 MWA Tips for Troubleshooting
Please make sure that you have the latest MWA patchset as noted in the 11.5. 9 section of this note. Also, this note has a section on Performance and configuration settings, so make sure to review these sections as well

The DBAs filled up this questionnaire. Oracle support studied the logs sent :

The system.log is still full of the broken pipe messages

[Fri Sep 21 04:05:34 EDT 2007] (Thread-3102) MWA_PH_DEVCFG_NOTFOUND: 192.168.1.1 configuration file is not found. Will use default
configuration for now
[Fri Sep 21 04:05:34 EDT 2007] (Thread-3103) MWA_PH_DEVCFG_NOTFOUND: 192.168.2.1 configuration file is not found. Will use default configu
ration for now
[Fri Sep 21 04:05:35 EDT 2007] (Thread-3102) PH: User null got disconnected...
[Fri Sep 21 04:05:35 EDT 2007] (Thread-3102) PH: caught IOException
java.net.SocketException: Broken pipe
at java.net.SocketOutputStream.socketWrite0(Native Method)
at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:92)
at java.net.SocketOutputStream.write(SocketOutputStream.java:136)
at sun.nio.cs.StreamEncoder$CharsetSE.writeBytes(StreamEncoder.java:336)
at sun.nio.cs.StreamEncoder$CharsetSE.implFlushBuffer(StreamEncoder.java:404)
at sun.nio.cs.StreamEncoder$CharsetSE.implFlush(StreamEncoder.java:408)
at sun.nio.cs.StreamEncoder.flush(StreamEncoder.java:152)
at java.io.OutputStreamWriter.flush(OutputStreamWriter.java:213)
at java.io.BufferedWriter.flush(BufferedWriter.java:230)
at oracle.apps.mwa.presentation.telnet.ProtocolHandler.enterHighlight(ProtocolHandler.java:1998)
at oracle.apps.mwa.presentation.telnet.ProtocolHandler.run(ProtocolHandler.java:731)
[Fri Sep 21 04:05:35 EDT 2007] (Thread-3103) PH: User null got disconnected...


The inv.log is showing an error, but it is not on the same date as the error in the system.log so these don't appear to be related:
[Thu Sep 20 12:34:35 EDT 2007] (Thread-26) RCV: RcptGenFListner.nextItemExited 190 throwing exception
java.sql.SQLException: Exception while calling java.sql.SQLException: No more data to read from socket
at oracle.apps.inv.utilities.server.UtilFns.process(UtilFns.java:567)
at oracle.apps.inv.utilities.server.UtilFns.paramsProcessAPI(UtilFns.java:341)
at oracle.apps.inv.rcv.server.RcptGenFListener.nextItemExited(RcptGenFListener.java:5051)
at oracle.apps.inv.rcv.server.RcptGenFListener.doneButExited(RcptGenFListener.java:6001)
at oracle.apps.inv.rcv.server.RcptGenFListener.fieldExited(RcptGenFListener.java:421)
at oracle.apps.mwa.container.StateMachine.callListeners(StateMachine.java:1641)
at oracle.apps.mwa.container.StateMachine.handleEvent(StateMachine.java:526)
at oracle.apps.mwa.presentation.telnet.PresentationManager.handle(PresentationManager.jav
a:690)
at oracle.apps.mwa.presentation.telnet.ProtocolHandler.run(ProtocolHandler.java:818)
[Thu Sep 20 12:37:45 EDT 2007] (Thread-26) RCV: RcvFListner - Could not clear globals
java.sql.SQLException: No more data to read from socket
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
at oracle.jdbc.dbaccess.DBError.check_error(DBError.java:1160)
at oracle.jdbc.ttc7.MAREngine.unmarshalUB1(MAREngine.java:961)
at oracle.jdbc.ttc7.MAREngine.unmarshalSB1(MAREngine.java:893)
at oracle.jdbc.ttc7.Oopen.receive(Oopen.java:109)
at oracle.jdbc.ttc7.TTC7Protocol.open(TTC7Protocol.java:584)
at oracle.jdbc.driver.OracleStatement.open(OracleStatement.java:584)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2905)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:656)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:734)
at oracle.apps.inv.rcv.server.RcvFListener.cancelButExited(RcvFListener.java:145)
at oracle.apps.inv.rcv.server.RcptInfoFListener.cancelButExited(RcptInfoFListener.java:235)
at oracle.apps.inv.rcv.server.RcptInfoFListener.fieldExited(RcptInfoFListener.java:
92)
at oracle.apps.mwa.container.StateMachine.callListeners(StateMachine.java:1641)
at oracle.apps.mwa.container.StateMachine.handleEvent(StateMachine.java:526)
at oracle.apps.mwa.presentation.telnet.PresentationManager.handle(PresentationManager.java:690)
at oracle.apps.mwa.presentation.telnet.ProtocolHandler.run(ProtocolHandler.java:818)

The RcvFListner is an INV listener and is separate from the MWA listener.
There are no errors in the inv.log for Sept 21st.

UPDATE
======
We are still seeing the Broken Pipe error in the system.log, which still suggest there is either a network or a java problem.

The problem turned out to be that they had switched on the dispatcher service even though they were on BigIP load balancer. Once they shut down the dispatcher service, the error disappeared.