R12 Payables: Vendor and vendor sites can be updated post merge

The My Oracle Support article "R12: Payables: Merged Vendor and Vendor Sites can be Updated in Supplier Entry Screen" via document ID 2107797.1 talks about what happens when vendor sites can be updated after being merged and an error is thrown when trying to merge it again.  Also, I learned about the table AP_DUPLICATE_VENDORS_ALL which will let you know if a vendor has ever been merged before and with the data pattern described in the document could give you a chance to build a proactive alert!

Weekend Learning: Whitepaper on Exadata monitoring with Enterprise Manager

Do not miss this whitepaper on Monitoring Exadata Storage Server Grids with Enterprise Manager from the Maximum Availability Architecture (MAA) team which has the entire step by step process and all the SQL you need to set up all of the conditions you need to alert on!

R12.1.3 Receivables: Cannot Close period with unposted adjustment

If you have had to deal with Closing a period in A/P or A/R, you are probably very cognizant about unposted transactions holding up the process!  The article "Unable to Close the Period Due to Unposted Adjustment" on My Oracle Support in document ID 2077446.1 gives us yet another reason how a transaction can be in such an incorrect state so it requires an SR to get a datafix, but with the script and information provided we can build a proactive alert against that situation!

Wait event "Library Cache Locks" seen during invalid logins

Of course the My Oracle Support article "High "Library Cache Locks" Wait Time Due to Invalid Login Attempts" has some straightforward value in note ID 1309738.1 but there are some scripts in here to allow you to design some proactive alerts around exclusive locks AND incorrect login attempts/attacks!

Using an array update statement may cause row or block corruption

While advanced PL/SQL programming structures like an array can be advantageous at times over row by row (Cary Millsap's slow by slow), the article "ALERT: Bug 7662491 - Array Update can corrupt a row. ORA-600 [kghstack_free1] ORA-600 [kddummy_blkchk][6110/6129]" found on My Oracle Support by way of note ID 861965.1 shows that not every decision we make is without consequences!  Think you do not need to know about this if you are in Support?  Think again, unless you already know what table holds data about blocks which get corrupted and in that case you probably already have an alert built looking at that table to see when corruption occurs right?

Assets: Not able to create assets via Workbench

Before we went to R12 I posted an alert I created for Fixed Assets, and it still was relevant on R12, so tonight when the 11.5.10.2 targeted article "Cannot Update Assets Using Asset Workbench form FAXASSET: Error: Depreciation Request Status S Is Not Known." was posted on My Oracle Support via note ID 2005126.1 I took notice.  Why did I take notice?  Because we may encounter the same issue on R12 and ANY chance to be proactive in a situation like this is important!

How the NTP service can be your weakest link

If you are not familiar with the Network Time Protocol and the services related to supporting it, do not feel bad; I did not know either until we had a problem with it!  Why could this be your weakest link?  I am glad you asked!  Recently we had our Exadata DBs start reporting different server times because it seemed that they were getting the server time from their switches but the switches were not correctly getting their time from our network time server.  That is obviously a problem, but imagine my surprise when I tried to log in and received "Permission denied, please try again" when trying to SSH into our server.  Why does this relate to NTP?  Well, it seems that if the time drifts too much on a server the SSH login attempt is rejected!

Why am I making a big deal out of this?  What happens when you are alerted that your CPU is starting to spike and you want to log in to take a look, but are rejected?  Do you have time to waste on getting a system administrator to restart the ntpd service so you can get into your machine?  I doubt it!  Now, it is on to making an alert to check our system time against the network time server!

In defense of regular alerting - Part 2

Following up on the first installment of this idea, today I bring you yet another reason to put in regular alerting on your system.

How many sessions do you have coming into your system?  Is your EBS system a spider in the middle of an ever increasingly complex web?  If you are not sure about what is coming into your system, can you swear that the system is working as intended?

In my newest alert to monitor the sessions coming in to our EBS from other servers in our ecosystem, a pleasant side effect is that we could see what server/user combinations had 0 connections.  What does that mean for us?  Well, in this instance we could actually tell when the other server had been bounced and the services did not come back up automatically.  This means we can get to another level of proactivity by seeing an issue before the business reports it AND help out our IT partners by alerting them to this condition if they were not already aware of it.

In defense of regular alerting

There have been many different alerts that I've designed, and for the most part they are setup to run many times a day and sometimes even several times an hour.  This means that you can depend on your alerts coming out at regular intervals, and even if they may have different content each time you are receiving information which in and of itself tells you something about your system.  It is up and working as expected.  In this way, the alert itself becomes an indicator of your system regardless of what information it holds.  Take what happened to us today, where we had a report of a problem by the business and the absence of any of our regular alerting during this time tells us there was a serious problem going on which is above and beyond any of the business reporting.  Looking at our system we see that anything going out to a remote database encountered issues including our alerts which help us keep track of several databases at the same time using database links.  In this case I was able to use the absence of the alert to start me down the right path of finding the root cause, instead of actually using any information in the alert.

R12: Invalidated workflow

After our upgrade, we kept a close eye on our monitoring alerts and noticed right away when one for workflow errors in the last day showed us this:

ORA-04061: existing state of  has been invalidated

Reaching out to the team responsible for validating the release of the workflow, they weren't quite sure if this was a one off issue or a complete outage type of issue but upon further examination it was determined that the resolution to this was a bounce of the application tier.  I was skeptical, but believed in what I was being told, and it just so happened we had an application tier bounce already lined up for another issue so the stars were aligned and the next day this error went away.  I'm not quite sure what object was invalidated, as you can see that between of and has in the error message, there are 2 spaces as if the object name was not able to be reported up in the error stack.

Keeping Exadata nodes maintained

From an interesting article I ran across on LinkedIn, Mike Chafin gives a step by step for how to create disk monitoring for Exadata Compute node disks with Enterprise Manager.  I need to get this over to our DBAs as they are the primary users of EM in our shop, since we could always use more alerting and Mike's article includes that as well for us!

Proactive DBAs and TEMP space

On the same day that we ran out of FLASH, we later saw our database running out of TEMP space and thought this might be related to the same archive job but in reality it was a user report which was run with invalid parameters so it was just sitting there consuming temp space trying to complete.  In addition to resolving the issue by terminating the session, the DBAs created an alert that day to check if any sessions are taking up more than 6 GB of TEMP space and if anything is found it e-mails the DBA and Support teams.  This is a critically important and proactive alert that has been put into place, which has helped us several times to identify potential issues before they could halt the database until the TEMP space issue was resolved.

What's the difference in a column?

This blog is filled with the times I'm just so brilliant, but this is not one of those times.  In our due-diligence in verifying all our items, objects, and processes will stand up to being upgraded by R12 an analyst started asking us some questions about an alert which just so happens to be one I created.  The statement was being made that "it does not pull up anything" which was curious to me, since I usually don't send alerts out unless I can test them successfully (often many many times) before putting my team on them.  Sure enough, I went back to see that I DID test the alert and it DID show information so now I'm really curious and digging into the statements being made in relation to actual_start_time being selected as the criteria for the alert.

Seems innocent enough right?  I had wanted to find out when a Journal Import ended within the first hour of it being in a non-normal status (Cancelled, Terminated, Error, you get the idea) but in fact that isn't what I asked the alert to do.  Because I used actual_start_time, and not actual_completion_time, I was asking the alert to tell me if a Journal Import ended in a non-normal state only if it started within the past hour.  How about that huh?  So I looked back in the history of when the alert should have come out, and sure enough there was a Journal Import that started at 8 AM and didn't end abnormally until after 10 AM so when the alert ran this wasn't picked up.  A good lesson learned that even with good intentions, you can do something completely wrong, and not find out about it until too late.

Getting alerts from OEM 12c

A through article detailing how to set up alerts, adding notifications and rules, with a link to a whitepaper PDF on how to implement this for an enterprise situation.  Well worth the read!

Batches? We don't need no stinking batches!

The error message of "Unable to create batch" was being presented to our users when they were either trying to manually create receipt batches or when the automated processes would run from their requests.  Digging into the SQL of the package started taking me down a rabbit hole from one custom package to another, until I finally found that even though the error message being returned to the users was custom but it was being raised as a result to a call to ARP_RW_BATCHES_PKG.INSERT_MANUAL_BATCH which is a completely canned Oracle procedure.  Tracing through this started making me suspect the call over to arp_rw_batches_check_pkg.check_unique_batch_name but that didn't make any sense since we're talking about receipt classes as defined in ar_batch_sources which are set to AUTOMATIC for their numbering scheme in the auto_batch_numbering field.  So I wind up taking a look at this piece of code a bit closer because nothing else looked possible if I was in the right spot:

SELECT  COUNT(*)
INTO    l_count
FROM    AR_BATCHES bat
WHERE   bat.batch_source_id = p_batch_source_id
AND     bat.name = p_batch_name
AND     (     p_row_id IS NULL
          OR  bat.rowid <> p_row_id );
IF ( l_count <> 0 ) THEN
     FND_MESSAGE.set_name( 'AR', 'AR_DUP_BATCH_NAME' );
     APP_EXCEPTION.raise_exception;

when I decide to see what the data in ar_batches shows me.  Sure enough, the last_batch_num in ar_batch_sources for the batch_source_id in question was currently 5792097 and we have a sequence of batches with a name defined in ar_batches ranging from 5791705 to 5792958 which is preventing the package from successfully getting an available batch name to use in the creation of the batch as shown in these pieces of ARP_RW_BATCHES_PKG.INSERT_MANUAL_BATCH:

IF ( l_auto_batch_numbering = 'AUTOMATIC' ) THEN
UPDATE ar_batch_sources
SET last_batch_num = NVL( LAST_BATCH_NUM, 0 ) + 1
WHERE batch_source_id = l_batch_rec.batch_source_id;

SELECT NVL( LAST_BATCH_NUM, 0 )
INTO   l_batch_rec.name
FROM   ar_batch_sources
WHERE  batch_source_id = l_batch_rec.batch_source_id;

This meant that the application was continuing to increment the last_batch_num value every time the users were trying to do their work, even though the batch names were already taken and it would continue to do so until it encountered a data value which did not already exist for the name field in ar_batches.  The only option I could see is that we would need to manually push out the last_batch_num to match the last item in the sequence, so that a unique value would be returned the next time it would try to get a batch name (see above UPDATE statement).  I didn't want to change the batch names of the items which were created months ago because then we’re changing historical data, and I can’t be 100% sure that it’s not used for reporting or other audit type functions by the business.  After the appropriate approvals, we ran the below script:

UPDATE ar_batch_sources_all
SET last_batch_num = 5792958
WHERE batch_source_id = &batch_source_id
and last_batch_num = 5792097

Service was restored to the batch source after this update was applied, and all was well with the world!  Then we were asked how we could make sure this doesn't happen in the future.  After thinking through the answer a bit, I've come up with the below script which needs to go into a daily alert for us:

select a.batch_source_id, a.last_batch_num, a.org_id from ar_batch_sources_all a, ar_batches_all b
where (a.status not in ('I') or a.status is null)
and a.end_date_active is null
and a.batch_source_id = b.batch_source_id
and a.org_id = b.org_id
and b.name > a.last_batch_num

This was one of those problems where it's incredibly challenging, but also rewarding when you get to the end of the day having solved a pretty big problem and learning a lot in the process.  Years ago I would've ran a trace and handed it over to development to figure out, but I've gained some seasoning since then and decided that I'd do as much "dirty work" as possible before I had to escalate up so I ended up digging down through 7 layers of code to find the actual root cause!

WebLogic alerting

This post seems like a very through process for alerting on the WebLogic platform, and I'll be making sure to snoop around our system to see if we're using something like this!