Routine CALL_STORED_PROCEDURE cannot initialize concurrent request information for

Problem:

Concurrent requests are failing. Only a fraction of Standard managers are running as against expected 50. No error messages in the ICM Log. Concurrent requests are failing with following errors:

Routine CALL_STORED_PROCEDURE cannot initialize concurrent request information for

Routine AFPEOT cannot initialize concurrent request information for .

Solution:

The mount point containing the $APPLLOG and $APPLOUT was full. Clean up some space.

- Aravind Kamath

Cisco Load Director to Cisco Content Switching Module Migration in ERP Environment

In the recent past, we had to move from our old desupported load balancer to a newer certified version of load balancer in our ERP environment. The old desupported load balancer was Cisco Load Director. The new supported version is Cisco Content Switching Module.

Actually, we (DBAs) do not handle the configuration and setup of the load balancer. However, we are responsible to ensure that the load balanced ERP environment meets our requirements and the ERP environment is available to the users.

One of the "simple" requirements for CSM was that the Servers which would be defined as "Real Servers" in CSM needed to be in a specific VLAN segment. This would mean that the IP addresses for the real servers (in our case online servers - apache / forms) had to be changed. We started with this background and had to take care of the following steps to complete the migration successfully:

1. Change the IP address of the ERP Mid-tier servers.
2. Update the host DNS records to reflect the new IP.
3. Configure the CSM with our load balancing requirements.
4. Update the DNS records for the virtual URL for ERP.
5. Update Oracle Applications (fnd_nodes) with the new ip address information.
6. Update the firewall rules to allow access to the new IP addresses.

By the way, our load balancing requirements were very simple. We have three online mid-tier servers and we wanted the requests to be routed in a round robin fashion on Apache, Forms and Mobile Supply Chain Application ports. We also wanted buddy enabled between Apache and Forms. This will ensure that the apache and forms sessions from a same IP will be directed to the same mid-tier. Also, we wanted "sticky" to be at 60 minutes. This ensures that one IP address will stay on one midtier with an idle time of upto 60 minutes.

- Aravind Kamath

Musings of the Mind: Of Choices and Right Decisions

You might wonder as to what this post is all about, because you were expecting a Apps-DBA-Tech-Stuff post and this one is far from being a tech post.

Nevertheless, I am sure life presents each one of us with different choices almost at every step in our lives. However, not often we get into the mode of sweating it out to make the right choices out of the convoluted ones and there by making the right decisions. This is so, I believe, because the choices presented in front of us make that task easy. In other words, it is easy to pick the 'obvious' one. What really puts one under tremendous stress and pressure is when all the available choices have their own significant merits and significant demerits, that it becomes difficult to choose one. It is like having to choose between my engineering exam questions 1a and 1b OR 2a and 2b, each pieces carrying 10 marks, where albeit my wish that i would love to answer 1a AND 2b, that would not be allowed. This is especially true when the choices pertain to ones life and will chart the course of ones future - with no option of returning back - kind of one way street.

I think this is when one has to start analysing the choices against the backdrop of the knowledge of the consequences of making a choice. Let go of things over which we have no control. Focus on things which are under our control. Seek answers from the innerself. Apply past experiences. Seek expert assistance. Confide in your friend, partner. Then go ahead, make a decision, because there are no right or wrong decisions - only consequences. Embrace the consequences with open arms, for that was the best you could do, under "those" circumstances. After all, we are human and no Oracle!

- Aravind Kamath

Killing a Jserv in a Load Balanced Environment

Some time back, we came across a very interesting situation where the requirement was to kill a jserv. Here is the background : In our environment (11.5.8 + JDK 1.3.1_19), we have multiple Jservs configured to run under apache. These Jservs are running in "Automatic" mode - in other words not in manual mode. One of the jservs was throwing noclassdefinitionfound error and we wanted to kill that jserv so that we could get rid of this issue. The problem in this approach is that all the jservs write to one single jserv.log file and there is no way to identify which jserv is hitting this error based on the log file.

I came up with the following approach to overcome this issue:

1. Time spent by each jservs in user mode and kernel mode
2. IO performed by each jservs
3. Virtual Memory consumed by each of the jserv processes.

Since the load balancing happens in a round robin fashion, in other words apache distributes the load in a round robin fashion between the jservs started automatically, it is fair to assume that each of the jservs should see equal amount of load in terms of user requests. Based on this assumption and the facts collated as above, I was able to zero down on a single jserv which had spent less time, less io and least vm amongst the four jservs to be the culprit.

I killed that jserv and the issue was fixed.

- Aravind Kamath

Oracle Apps DBA: Workflow Notification Mailer - Mails Sitting in Inbox

This is the other situation that i talked about in my earlier post with Notification Mailer.

Situation:

Mails are sitting in the inbox and Production Workflow Notification Mailer is not processing them.

Symptoms:

1. Most of the mails are getting processed without any issues.
2. Some of the mails are not getting processed. They are not moved to Discard folder either.
3. Out going emails are working fine.


Troubleshooting:

The starting point in this case too, was to pick up a NID from the one of the mails sitting in the inbox. This was specially puzzling because there were no errors logged in the workflow notification mailer logs. The log level was set to error. There were no obvious discrepancies in the wf_notification or wf_notification_out or wf_notification_in queues.

After some desperate troubleshooting techniques like bouncing the mailer failed, I changed the log level to 'statement'. This is when the first clue emerged. I saw some entries in the mailer log something like 'mailer will not process these NIDs'. I cant recollect the exact error message, but it sounded something like that. Then i decided to take a closer look as to why the mailer refused to process those notification.

The answer lied in the previous week end patching that was performed in production environment. I realized that autoconfig was run as part of patching and this was the key to the issue. In our environment, we have modified the node names to match wf. The default value that oracle brings in WFMAIL. We have two production instances and hence we have renamed this to read WFOIAP and WFOGAP. This was reset to WFMAIL after autoconfig was run.

The notification ids contain the following section at the bottom of the notification:

NID[5369273/218241628447419580901891042449889261398@WFOIAP]

In the above example, WFOIAP is the mailer that would process this notification. Since the mailer name got changed to WFMAIL in production, it would not process.

Hence, to sort out the issue, we changed the node name back to WFOIAP and the mails were cleared in no time.

But this also led to another issue - the mails that were generated with node name as WFMAIL in the problem window started piling up in inbox as WFOIAP would not process them.

This situation was handled manually to get past the issue.

- Aravind Kamath

Oracle Apps DBA: Workflow Notification Mailer - Mails incorrectly being moved to Discard Folder

In the recent past, we were faced with two separate issues pertaining to Workflow Notification Mailer in our production environment. We have OWF.H in our environment and of course, it is a java mailer. I will write about one situation here and the other in a separate post.

Here is the situation:

Inbound mail processing is erratic. Some mails are processed and some are not.

Symptoms:

1. Outbound notifications are working fine.
2. Inbound notifications are not being processed correctly. Some mails are being processed and some legitimate mails are directly moved from "Inbox" to "Discard" folder.

Troubleshooting Tips:

I first started with a legitimate email which was incorrectly moved to discard folder directly from inbox. I noted the NID of the notification which is available in the email response. The next step was to see if something was obviously wrong with the notification.. like for example the requisition was approved from within application and hence the notification was discarded. This was achieved by querying up the records for this NID from wf_notifications table. Everything seemed OK there.

The next step was to scan through the log files for the workflow notification mailer in the production environment by searching for this specific NID. I was able to locate log entries which relating to sending out the mail with this NID. However, there was no record of any inbound mail with this specific NID. This enabled me to conclude that the production workflow notification mailer never was aware of this email which came to the inbox.

This can happen in following situation amongst others:

1. Some rules are defined at the mailbox level that move the specific mails automatically
2. Some other mailer is also working on the same mailbox
3. Human intervention

In our case, I verified that rules were not defined at the mailbox level and there was no human intervention.

The remaining candidate was some other notification mailer accessing the same mailbox.

That pointed out to the latest refresh that happened from this production environment. I verified in the target instance and i was able to locate these NIDs in the mailer logs. That suggests that the portion of the refresh process which addresses the Mailer related settings was overlooked.

Now, as far as the mystery of why some legitimate notifications were being sent to discard folder is concerned, here is what i discovered:

There are two scenarios possible:

1. Set of notifications (or the originating processes) in production, that were created before the target database was refreshed.
2. Set of notifications (or the originating processes) in production, that were created after the target database copy was made.

In the first case, since the target database already had the relevant data about the notifications and hence it actioned (i.e., either approved or rejected) in the target (dev) database.

In the second case, the dev database did not have the entries for those NIDs and hence was moved to discard as "bad" responses.

Finally, there was a third category which were processed by the legitimate production mailer which was all ok.

Hence, the mailes in "process" folder does not essentially mean that notification was updated appropriately in the production database. This needs to be addressed on a case to case basis and that is where my developer friends can contribute.

- Aravind Kamath