Thursday, February 28, 2013

How to query multiple values in a form

I started supporting an Oracle EBS application with virtually no experience with an Oracle application prior to that, so from time to time I'll find something which may be very simple but incredibly useful if you didn't know it.  This post follows that vein.  A little while ago I learned that in Oracle 11i Forms instead of using % wildcards to substitute for values in your searches, you can also find a list of values by placing #IN within the field, followed by (value1,value2) which will look familiar if you have practice writing a SQL statement putting it together like:

#IN(value1, value2, value3)

Then you can run your query via the menu or Ctrl-F11, and you'll actually find records which contain values 1 through 3 which is pretty cool but also really handy if you have several flags which need to be searched for in an application form so that you can add some complexity to what used to be simple queries in the forms with just %s.

Thursday, February 21, 2013

Using RMAN, but not effectively

Since I've been on a RMAN/DBA kick lately, I figure this article is ripe for sharing!
"This article isn’t going to get into every aspect of backup testing but we will show one example of how an inadequate test of incremental backups can have disastrous results in a database recovery scenario."
Does that sound familiar or scary to anybody else out there?  I know I was scared after reading through the scenario!

Thursday, February 14, 2013

Learning more about DBA_ALERT_HISTORY

In a previous post I indicated how I was going to create an alert based on the table DBA_ALERT_HISTORY because it would show us when a database went down and also come back up.  What I found out the other day was that if a node in our RAC instance becomes unreachable, or can't communicate with the Oracle database for another reason such as a server crash, the table DBA_ALERT_HISTORY may not get logged to until the node becomes available again.

This was interesting because even though the other node went through configuration believing it was the only node available, the remaining node isn't configured to log into this table that it lost track of the other node since it is the responsibility of the offending node to write to the table.  After the server was communicative again 10-20 minutes later, I saw a most interesting series of entries logged into the table indicating that the server is coming up, followed by a notification that the server is down just milliseconds later....even though the instance was still coming up!  This second notification was actually the original notification that should have went out when the instance went down, but it couldn't be logged to the table until the server was operational and communicating with the DB.

I still think I need to create an alert based on this table, but I thought it was very interesting to see the table in a real-time fashion while we had a server basically offline and my expectations didn't match up with what happened at all!

Thursday, February 7, 2013

RAC nodes with unknown code differences

As I said several months ago, while going to Exadata we went to a RAC platform and since that time I have yet to encounter any pitfalls with going to this technology stack....until now!  After applying some Exadata related patches we bounced our system as we've done several times since RAC, and just like we did in our QA mock deployment drills, but when the user community got a chance a few days later to put the system through its paces we received various errors reported in both canned and custom functionality.  Drilling into the issue further, we realized it had something to do with our tax solution with "com.evermind.server.http.HttpIOException: Broken pipe" in our application server logs and this in the error logging table:

ORA-29270: too many open HTTP requests
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1130
ORA-29270: too many open HTTP requests


It is important to note that one of the patches applied upgraded us from 11.2.0.2.0 to 11.2.0.3.0 and subsequent red herrings from My Oracle Support of 1301699.1 and 1293056.1 had us all assuming the issue resided in new Access Control Lists functionality that might have been introduced with the patching.  Eventually we were able to find that the code being run to get a URL response passed back would run successfully on one of the RAC nodes but not the other and that had nothing to do with ACLs at all.  With this we continued to research and found that the package being called was actually corrupt on one node, but not the other, and had no outwards sign of being INVALID or otherwise corrupted since when it was compiled as a new object on the "bad" node it would run successfully.

This could be the end to our story, if not for the fact that after we recompiled the object we were still receiving the "too many open HTTP requests" error.  At this point, we dug deeper into the system and one item we encountered in our research on My Oracle Support stuck out to us from 961468.1:

What is the reason of ORA-29270?

The database server has a hardcoded limit of 5 open HTTP connections per session. When you attempt to open a 6th http connection, this error is thrown.

Now we hadn't changed the application, or the other database which we're communicating with in this scenario, over the weekend but in some way the database received (or otherwise caused) corrupted communications and held onto these 5 HTTP connections in such a way as to not release them for further communications.  Running lsof and netstat found that on the node in question there were several hundred connections stuck from the other database, and once a kill was issued for them the natural order was restored to the universe when they died off and allowed connections to work again without issue.

This was quite an interesting problem as it was nebulous in finding where the root cause resided, and added a few tools into my toolbox for the next time we have something like this!

Friday, February 1, 2013

Purging concurrent and Fixed Assets

If you have a realitively large application, you probably purge your concurrent request tables fairly frequently due to space limitations which most of the time is a benefit but I've recently found a reason that this can hurt you as well.  With the Fixed Assets module, the table fa_book_controls keeps track of the depreciation runs that the system or the users run and if many deprecation runs are being with some frequency there doesn't appear to be any issues.  The problem lies with infrequent deprecation runs when the last run happens and ends in error leaving the request_id populated in the table, and is subsequently then purged out of the tables by your regular concurrent purge processes.  By having this mass_request_id in the table, when either the Calculate Gains and Losses or Depreciation Run concurrent reports are run they will error out barking about how they can't find the previous request:

APP-OFA-48392: Unable to get the concurrent request status using procedure FND_CONCURRENT.GET_REQUEST_STATUS.

This doesn't sound too dire, but if your business is in the midde of trying to close their books and this crops up you need to be able to resolve the issue quickly.  Having encountered this situation several times, I've created the alert below to notify us if these conditions exist:

select mass_request_id, book_type_code
into &REQ_ID, &BOOK
from fa_book_controls
where mass_request_id is not null
and last_update_date < sysdate - (2/24)


The reason I am looking for a last_update_date greater than the past 2 hours is because this means the request has errored within the past few hours and while it is potentially a problem a few days from now this alert is giving us some lead time in fixing the issue.