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.
Friday
Tuesday
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...
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:
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.)
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.
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.
Monday
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...
Tuesday
ORA 24323 - Report Builder
Yesterday we encountered a weird issue with Report Builder. One of the developers, while working on a report, encountered the following error:
The developer proceeded to make several changes but received the same error. Then they pulled up other reports and the error occured there as well. They contacted us thinking that there may be something wrong with the environment.
I have never used Report Builder but a quick search turned up 2 possible solutions:
1. A mandatory value received incorrect or null data.
2. Your not connected to the database in report builder.
I asked the developer to verify that the SQL they were working on was valid by pasting it into sqlplus and executing it and to verify that they are connected to the database.
A short while later they let me know that once you receive the error above, it seems Report Builder loses connection to the database. At that point they have to reconnect and if the query is fine they no longer receive the error.
ORA-24323: value not allowed
==> select * from mytable
Report Builder: Release 6.0.8.27.0 - Production on Thu Jun 12 10:10:05 2008
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Enter Username:
Program exited with status 1
Concurrent Manager encountered an error while running Oracle*Report for your concurrent request 4792266.
Review your concurrent request log and/or report output file for more detailed information.
The developer proceeded to make several changes but received the same error. Then they pulled up other reports and the error occured there as well. They contacted us thinking that there may be something wrong with the environment.
I have never used Report Builder but a quick search turned up 2 possible solutions:
1. A mandatory value received incorrect or null data.
2. Your not connected to the database in report builder.
I asked the developer to verify that the SQL they were working on was valid by pasting it into sqlplus and executing it and to verify that they are connected to the database.
A short while later they let me know that once you receive the error above, it seems Report Builder loses connection to the database. At that point they have to reconnect and if the query is fine they no longer receive the error.
Subscribe to:
Posts (Atom)