iProcurement: Catalog Bulk Load

Just over a year ago we implemented Shared Application Filesystem in our EBS environment. This reduced maintenance time by my guesstimate of 25-50%. Its hard to say exactly how much time we have saved but previously we would have to apply a patch to two application tiers (web, forms) and a database tier (cm, db). If patches took an equal amount of time per tier it would be easy to say how much time we have saved but typically patches on the database tier take longer to apply.

One of the modules we have implement is iProcurement and as part of that, periodically catalog data has to be uploaded. Catalog data consists of items available and their prices. To peform this task a user with the "Internet Procurement Catalog Administration/eContent Manager" responsibility has to select a file on their system and load it via the "Bulk Load Items & Price Lists" screen.

Behind the scenes the catalog data file is stored as a temporary file on the application tier. The POXCDXBL concurrent request starts and if it detects the node names for the cm tier and application tier are different it executes a file transfer, regardless of whether or not you are using a shared filesystem. In order for you to take advantage of the shared file system you need to apply patch 4656509 which upgrades RemoteFile.java to version 115.4 and set the profile option "POR: Catalog bulkload Directory" to a location on your share.

At the time we implemented Shared Application Filesystem we investigated this but it would have required alot of patching. At least 11 patches, with some of them being family packs. Even tho our POR profile option (listed above) was set to a local directory, catalog bulk load was working fine. So we decided not to change the configuration as it would require alot of testing.

Skip ahead almost two years, with alot of changes in between, and this configuration was still working until we applied the Oct 07, Jan 08 and Apr 08 security patches. During testing users reported that catalog bulk load failed:

POXCDXBL module: Catalog Bulk Load - Items & Price Lists failing with:

at oracle.apps.fnd.cp.request.RemoteFile.getFile(RemoteFile.java:441)
at oracle.apps.icx.loader.LoadRequest.runProgram(LoadRequest.java:51)
at oracle.apps.fnd.cp.request.Run.main(Run.java:161)
[6/12/08 3:11:02 PM] ********** Loader stopped **********

At first I assumed it was a cloning issue so I checked some typical problems we have experienced in the past. I noticed a few things that were wrong such as adovars.env settings, an error in our apache error_log file (below) and a few others but fixing them didn't resolve the error. I spent a fair bit of time on the apache SSL error because I had seen that in the past and it usually meant the txkrun.pl script to enable SSL failed for some reason. I re-executed it but the error did not go away (although, it did fix another problem). (As an aside, I have to say, debugging someone elses clones is a nightmare, especially if they didn't log everything.)

[Wed Jul 2 13:07:13 2008] [notice] caught SIGTERM, shutting down
[Wed Jul 2 13:07:24 2008] [notice] FastCGI: process manager initialized (pid 17232)
[Wed Jul 2 13:07:25 2008] [notice] Oracle HTTP Server Powered by Apache/1.3.19 configured -- resuming normal operations
[Wed Jul 2 13:10:34 2008] [error] mod_ssl: SSL handshake failed (server
myappserver.cognos.com:8005, client (OpenSSL library error
[Wed Jul 2 13:10:34 2008] [error] OpenSSL: error:14094416:SSL routines:SSL3_READ_BYTES:sslv3 alert certificate unknown

Moving on, Note:281530.1 talks about this feature and how it works behind the scenes in great detail. It also includes typical problems that may be encountered, workarounds and potential fixes. Using this note I narrowed down our problem to be at the point where the CM requests the catalog temp file from the application tier and places it in the CM tiers $APPLTMP directory. However, this file was of zero size.

At this point we decided to change the "POR: Catalog bulkload Directory" profile option to a directory on our shared filesystem and retest. This solved the issue. An SR with Oracle couldn't confirm whether or not the problem was caused by the security patches. So for now we will put this change into our release instructions since it is a better configuration (why transfer the file if can be available locally?). We have to apply the patches to one more environment before we go live and I will test catalog bulk loads before and after we apply the patches to see if they were the cause or it was indeed a cloning issue.


Branching out?


A few months ago our company was acquired.... Since I support our Oracle EBS environment it was a pretty safe assumption that at some point it would be decommissioned.  As with all acquisitions, redundancies are the first to disappear.

Add to the fact that they use SAP and DB2, I have some choices to make.    However, there are a couple of areas where I may be able to fit in, mostly the consulting branch but at least i'd still be able to focus on Oracle.

The majority of my time is now spent ramping up on DB2, while the remainder is supporting our EBS environment until the transition is over.   This makes it much more difficult to find topics to write about and its pretty much the reason why I haven't posted very much lately.

I have to say tho, learning another rdbms is a great way to refresh my knowledge of Oracle.    The book I am using to learn DB2 is aimed at Oracle DBA's, so as it describes a feature in DB2 it relates it to Oracle.  This makes it much easier to learn while at the same time refreshing my memory.

For the time being i'll be focusing on DB2 and I decided to start a blog related to that.  I'm also putting some feelers out internally hoping an Oracle Apps DBA job opens up...