Everyone Has One

Everyone has an environment that they are tasked with supporting but dread the day a call comes in. For me is an old 10.7 environment which we have for historical purposes. I'm not exactly sure how often users need to run reports in there but i'd say a few times a month. I dread supporting this environment because I have never used 10.7... When I joined this group we had been using 11i for a few years. Since then, the only people who knew anything about 10.7 have left the company.

Recently we received a call that users could not connect. To make a long story short we had to restore the application server from a backup. I wish I could tell the story because its hilarious but suffice to say i've seen a few bloggers lose their jobs for posting too many environment specific details. Better safe than sorry.

Luckily I was going on vacation the day after we requested the restore and I was hoping it would all be resolved by the time I returned. No such luck. Once the server was ready for us I logged in to restart the services expecting no issues. No suck luck. Executing owsctl produced the following error:

> Error initializing CORE
> Please check if the ORACLE_HOME is set correctly.

Great. Luckily there is a 7 year old, archived document on Metalink which pointed me in the right location: Note:1015720.101 The note refers to changing
the location of your ORACLE_HOME but symbolic links under $ORACLE_HOME/ows/mesg were not updated correctly to the new location.

Well, in this case everything should be identical but with no other hits on Metalink its was my only option. Indeed the symbolic links were pointing to an invalid location. When the unix admin restored the files he had to put them in a temporary location first and copy them over. The restore process must have recreated the symbolic links because they were still pointing to that temp location.

Sweet, I found the solution so I fired up the startup scripts again.. No suck luck. Now I faced the following error:

OMN-2001, could not contact address server
Information: Application init function oracle_adp_init returned ADI_FATALINIT
Error: The server could not initialize
Information: The server is exiting
OWS-08811: Unable to startup Oracle Web Listener `xyz01'.

This time Note:1016600.102 came to my rescue. OMN-2001 means the admin process was started before the WRB (web request broker). Solution, start WRB before admin. Then I could process to starting the listener.

While the first problem was as a result of the restore, the second could have been avoided with good documentation. Now that I know the proper order to start the services (wrb, admin, listener) I found the correct startup scripts.

DB2 No More

A couple of posts ago I mentioned that the majority of my time was going to be spend learning DB2. I also mentioned I was going to put some feelers out internally to see if any Oracle Apps jobs were out there. So over the past few weeks I networked quite a bit and with luck I managed to find an opening. This week I start part time in an Oracle Applications DBA group and hopefully as integration work slows down i'll spend more and more time over there.

So now I can dedicate my free time to learning more about Oracle Apps (which hopefully means more blog posts) instead of ramping up on DB2.

Security Patch Woes

A few weeks ago we applied a number of security patches. Due to various reasons we were a bit behind schedule and had to push a couple of releases out to production. Since then we have encountered 3 bugs, one of which crashed production just before month end. 2 of the bugs were the result of upgrading to 10.2.0.3 (a requirement for the security patches). The other was a bug on top of ATG_PF.H.5

Problem 1:

After cloning, Concurrent Managers fail to start. As per Note:434613.1, this problem exists on top of ATG_PF.H delta 5 and delta 6, as well as R12. During cloning neither the service manager or the internal monitor are created thus the concurrent managers will not start. A patch has been published to resolve this issue and there is a very easy workaround. We have added the patch to the next release cycle and modified our cloning scripts to incorporate the workaround.

The interesting part about this problem is that we have been on ATG_PF.5 since Dec when we upgraded to 11.5.10.2. Since then we have cloned a test environment over 200 times (its rebuilt nightly) and did not encounter this bug. We did hit it once back in March for a one off clone, but since then we have recloned that environment plus many others multiple times and it didn't resurface. I find it interesting that now after upgraded to 10.2.0.3 (from 10.2.0.2) and applying security patches that we can reproduce the problem consistently.

In retrospect we should have added this patch to the release cycle but we tend not to recommend that unless a problem can be consistently reproduced.


Problem 2:

Users stopped receiving email notifications from workflow. The following error could been seen in the logs:

ORA-06502:

PL/SQL: numeric or value error: associative array shape is not consistent with session parameters

A quick search turned up bug 5890966 which mentions this problem could occur during periods of high activity. Once we encountered this bug emails ceased to be sent. Oracle confirmed that this is a mandatory patch for 10.2.0.3 but has not been published as such yet.

Thanks to the next problem tho, we had to restart our environment and the problem hasn't reoccurred yet. We have added the patch to the next release cycle and hopefully it won't reoccur before then.

Problem 3:

On 10.2.0.3 bug 5907779 can cause sessions to self hang if dbms_stats is executing... I recall reading a few blog posts about this particular error but since it wasn't recorded as a mandatory patch we didn't apply it. At least the blog posts helped me identify the problem quickly.

Our statistics gathering jobs are scheduled on weekends and in this particular case only 1 type of session was hanging as a result of this bug. This session was spawned by an integration which is scheduled to execute once every few minutes. Unfortunately it wasn't smart enough to detect previous instances were still running and of course isn't monitored on weekends.

So as the weekend wore on, more and more sessions consuming more and more resources accumulated in the database. We didn't realize there was a problem until Sunday night when APO users came online. Unfortunately by then it had progressed to the point where the system ran out of resources, sessions couldn't be killed and we had to reboot the server. Luckily I was able to capture enough information (hang analyze and system state dumps) to confirm that bug 5907779 was the culprit. Everything came back up properly and as a bonus temporarily fixed our workflow email issues.


Unfortunately these types of problems (at least number 2 and 3) are not likely to surface in a test/dev environment. We have some patch review meetings coming up over the next few days to re-examine our processes but i'm not sure how we can prevent these types of problems in the future. Note:401435.1 lists a number of issues specific to 10.2.0.3. I guess I could have analyzed each of those patches to determine if they were applicable to our environment but whats to say they wouldn't have introduced additional bugs? Even then, I would have only prevented one issue since the workflow patch isn't listed in that note. Normally I just review Note:285267.1 which is the EBS 11i and Database FAQ to make sure there are no known issues.

Feel free to leave a comment describing how you analyze patchsets and full releases...

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:

{true,/apps/appsvis/visappl/fnd/11.5.0/secure/myserver_vis.dbc,1,1,0}]
https://myappserver.cognos.com:8005/OA_CGI/FNDWRR.exe?temp_id=2964546434
null
oracle.apps.fnd.cp.request.FileAccessException
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 10.69.92.24) (OpenSSL library error
follows)
[Wed Jul 2 13:10:34 2008] [error] OpenSSL: error:14094416:SSL routines:SSL3_READ_BYTES:sslv3 alert certificate unknown
Terminated

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...