Friday, February 28, 2014

OBIEE and why TEMP space is really important

Several months ago I shared that the DBAs were proactive in creating a TEMP space alert so we could see dangerous activity before it caused our EBS application a problem.  Releasing a new version of OBIEE to run against our Financials database, nightly they were doing "test" runs for several months of data to iron out any potential problems with their schemas, etc. before they turn the dashboards on in a few weeks.  Each night, we would see our TEMP space alert go off and while it did not reach extreme levels, we were concerned enough to let the OBIEE and DBA teams know about it before they did the entire dataload.

A few days/weeks go by, and they finally are ready to do the entire dataload.  What do you think happens?  That's right, we ran out of TEMP space so the database just paused like I've reported before on "statement suspended, wait error to be cleared" wait events until TEMP space was added so the dataload could be completed over to the Financials database.  Not only is this another chance to reiterate how important TEMP is, but ask the right questions about applications that hook into yours even if you are told it is just to extract data.

Thursday, February 27, 2014

ALERT: Ethernet memory leak may cause network outage

Following on the heels of the ASM alert the other day, today I share with you MOS note ID 1622661.1 that outlines what firmware versions of the following devices are affected by a serious bug:
  • Sun Blade 6000 24-Port 10-Gigabit Ethernet Switched Network Express Module (NEM) (2073A-N)
  • Sun Network 10GE Switch 72p (2074A-F)
What's the issue here you ask?  Well, it seems that memory leaks each time an admin logs in and out of the switch on these platforms if they are NOT configured for HA (High Availability).  This causes an eventual critical issue where the switch will reboot causing network outages at durations which may not be regular and make it very hard to pinpoint the cause of the issue.

Wednesday, February 26, 2014

Celebrating 300 posts!

When the blog started, I had no idea where it would take me but since then I've:
What do the next 300 posts bring?  I don't know, I guess we'll just have to find out together!  I'm really excited for what is still yet to come, as I think I've just started to tap into this energy of running the blog and digging into our system to learn more each day.

Tuesday, February 25, 2014

ASM Alert: RAC ASM disk offline after rebalance

Just the other day, I was telling you about an outage caused by ASM maintenance so when I was on My Oracle Support and they had an article entitled "ASM Alert! : After A Rebalance Operation, An ASM Disk Goes Offline In A RAC ASM Configuration (External Redundancy Diskgroups)" with note ID 1537143.1 you're darn right I was interested and read it.  I suggest you do the same if you have to deal with ASM on RAC instances because it describes a very real situation where an ASM instance may not realize the disk was dropped on another ASM instance on purpose and then it gets expelled from the first ASM instance.

Why is this so interesting?  It sounds a lot like what I've encountered several times over the past year, and it affects several different database products INCLUDING Exadata on certain releases of the database and Bundle Patches.  Of course we have seen this on non Exadata instances, where the likelihood of us encountering the issue are higher as they might not see large database patch increments which could introduce the fix and have us avoid this altogether.  I'll be asking my DBAs about this tomorrow.  Will you?  Why not?

Monday, February 24, 2014

R12: Hiding the DQM Serial Sync Index Program

Several months ago I shared with you that in R12 there is a new process called "DQM Serial Sync Index Program" which is kicked off after A/R customer information is updated, and per My Oracle Support note 1627787.1 it seems that others have found out about this Concurrent Report and want it to be hidden from their users.  This is interesting because I had never thought about a Concurrent Report being hidden before, and while Oracle does not say such a thing is impossible, the answer here is that it cannot be done since the program being submitted is standard functionality of the EBS application.

Sunday, February 23, 2014

Weekend Learning: Adding more security to Oracle Workflow

If you're a regular reader of this blog, you'll know that the Oracle Workflow product is near and dear to my heart for several reasons, so when I stumbled upon MOS note 965413.1 my curiosity was captured.  This note outlines several options for making the Workflow product more secure by limiting the amount of data returned to the user based on the partition that the data resides in, but I think the real benefit of this might be the suggestion you can add your own security sets on these data objects which may yield some positives if you're using the Workflow product quite a bit and need to segment your data returns.  How would you use this type of extra security in the Workflow product?

Saturday, February 22, 2014

An odd AME error when submitting expense report

A few days ago we had a user report they had run into an error when they tried to submit their expense report for approval:

Error Name: WFENG_ITEM_ATTR Error Number: 3103 Error Message: 3103: Attribute 'AME_ENABLED' does not exist for item 'APEXP/<expense report ID>'. Error Stack: Wf_Engine.SetItemAttrNumber(APEXP, <expense report ID>, USER_ID, <user ID>) Wf_Engine.SetItemAttrText(APEXP, <expense report ID>, AME_ENABLED, Y

Looking on MOS I found several articles, but none seem to be directly on point as note 401070.1 assumes that a profile option is set wrong (and it isn't for us) and note 317539.1 believes that a tablespace needs to be altered and Apache bounced (neither one needed to be addressed here).  To resolve the issue I had the user withdraw, and then resubmit their expense report, and while I don't know what the root cause for this was I wanted to share it with the community just in case you encounter it in the future.

Friday, February 21, 2014

Weekend Learning: Slow database I/O

If you've always wondered about your database I/O performance, ways to measure and interpret it, or some documentation on the subject you're in luck today!  I found the article "How to Tell if the I/O of the Database is Slow" on My Oracle Support under note 1275596.1 and it starts to help peel back the onion of performance a bit, especially with all the documents linked within the note, so I saved it knowing this was something I wanted to review in depth when I had some time.  I suggest if you're interested in performance on an Oracle system, you do the same this weekend with me.  Maybe we'll compare notes at the end with a quiz.  :}

Thursday, February 20, 2014

When is your database in-doubt?

Following up on yesterday's post where I suggest another reason to have alerting in place, today I explain what happened and the errors we saw in our system which may help you down the road.  A scheduled ASM disk maintenance didn't complete as planned since the system never completed (or took) the commands issued, and several hours the database cluster seemed to panic due to ASM with the following message:

ORA-00020: maximum number of processes 100 exceeded
ORA-20 errors will not be written to the alert log for
the next minute. Please look at trace files to see all
the ORA-20 errors.

so the cluster had to be restarted.  What did that mean for us?  Well, this was a remote database cluster to our local database cluster so overnight when the business had the new R12 Create Accounting report scheduled to run during the remote cluster outage this (and several other) report errored with several messages:

ORA-00060: deadlock detected while waiting for resource
ORA-01591: lock held by in-doubt distributed transaction <ID>

This ID and error message were found in our local database log when the transaction became in-doubt, so this is an opportunity to improve some alerting when this happens the next time to avoid extended locking in the application.

Obviously, the next day after the remote database cluster was up while these reports ran they ran successfully.  For the most part.  I'll tell you more about that later.

Wednesday, February 19, 2014

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.

Tuesday, February 18, 2014

Did I mention?

That in only two short weeks that I'll be attending the Hotsos Symposium as a speaker?  It's true!  If you haven't made plans to attend, you should visit the registration page and reconsider.  There are so many reasons to go, besides me being there, that I should direct you to a sample justification that you should send in to your boss.  Or your boss' boss.  Oh and don't forget about the Training Day by Tanel Poder!

I look forward to meeting you in Dallas!

Monday, February 17, 2014

Weekend Learning: A/R Customer Interface

Yesterday I focused on the A/R module, so it only makes sense to end this long weekend (if you had the day off to celebrate President's Day that is) by sharing some recent MOS articles I've found for A/R!  First off we have note 1493002.1 that goes over the Customer Interface (RACUST) program, terminology, as well as known issues and patches.  Next up, is note 1460677.1 which goes over the meaning of INTERFACE_STATUS error messages which is a useful reference when troubleshooting problems importing or creating records in A/R.  Lastly, we have note 1195997.1 that details out the fields in the table RA_INTERFACE_LINES_ALL including their validation and destination details.

Sunday, February 16, 2014

R12: Missing GL CM error messages in FND_MSG_PUB

I've shared a few A/P stories over the last few days, so it's only far to turn to our A/R crowd for another new bug we found in R12!  A few months after we had R12 up and running, all of a sudden a certain type of receipt was not allowing us to create credit memos on the invoice and all we were getting back was the error code "AR_RW_GL_DATE_BEFORE_CM_GL".  At first I thought it was a custom type of error message because it was very rough, and not what I was expecting from Oracle API error codes, so in searching My Oracle Support I was amazed to not only find it but I also found note 1393304.1 detailing how this was one of several error codes ("AR_APPLY_BEFORE_CM" is another) did not have their robust error reporting defined.

This bug is the exclusion of several error descriptions from FND_MSG_PUB.GET, but it could have been found and reported on before we had R12 in place if we had test cases for a bad data scenario like this.  As with several other R12 posts recently, this isn't to highlight Oracle in a negative light, but shine a positive light on the ability to find these items prior to your deployments with stronger test cases and more rigorous testing.

Saturday, February 15, 2014

R12: Unable to change Supplier information

Since I'm on an A/P bug kick, and I've shared with you some knowledge about Supplier changes in R12, I should probably also clue you in on another item to add to your testing suite!  The other day A/P was in the web portal trying to change the inactivation date on a Supplier record when they received an error message indicating "Context Value Doesn't Exist In Value Set $FLEX$.DF_CONTEXTS" and the form wouldn't let them actually save their changes.  MOS note 1458183.1 indicates this happens because setup wasn't migrated over, or because there was a decision made to not migrate data, so you get two solutions: one if you want to fix your DFF and the other if you want to get a bugfix.

If you aren't testing the removal (or change) of information to your migrated data for A/P you should probably start trying to break the system by doing it, and even branch out to test other application data that was migrated because you might be able to introduce patches to your code base early in the process to prevent re-testing.

Friday, February 14, 2014

R12: Problem with Expense Report Export Program

Our A/P group reported an issue when a line on the Expense Report Export Program showed up indicating "Expense Report Line and Distribution Totals do not match" while the table AP_EXPENSE_REPORT_HEADERS_ALL showed "EXPENSE LINE DIST MISMATCH" for the REJECT_CODE.  This was news to me as I had not seen it in R11 nor the past three months we had been running on R12 so off to My Oracle Support I went, and I found document 1582981.1 entitled "Expense Report Export Rejection: Expense Report Line and Distribution Totals do not match".  This has the patch or patches which may have caused this, one of which WAS in our system, matches our exact error messages/text, and has additional SQL statements which allow you to verify if this note applies to the data in your tables.

You have two options to fix this, as you do with most issues found in MOS: patch or get a datafix.  Luckily after submitting the information requested in this note via the SR process, the Oracle analyst indicated it was a rather simple datafix and after applied it DID resolve the issue at hand.  I wanted to highlight this problem because the cause of it as detailed in the MOS note needs to be in your testing suite if you're on or thinking of going to R12 since it could be more frequent for your shop or happen a lot sooner after implementation.

Thursday, February 13, 2014

Weekend Learning: Employee Supplier in R12

Have you ever wanted to learn more about the R12 architecture changes with respect to suppliers and how Employee Suppliers exist now?  Well friends, look no further!  I had to do some research on this last week, and stumbled upon MOS note 1377888.1 which has a GREAT breakdown of the changes that have taken place on several "standard" tables as well as being able to introduce the new vendor information so it is very easy to understand.  Even for me.  It probably helps that it has pictures huh?  :}

I know it's not the weekend yet, but my 4 day weekend starts tonight so I'm in that mode here on the blog!

Wednesday, February 12, 2014

Who is a developer, and who is a DBA?

This is the question that the oraclenerd, Chet Justice, has burning his in mind so he's asked Tom Kyte and Cary Millsap about where they think they fit in the traditional definitions and I think he was trying to get at the difference between the roles (at least in Cary's interview!).  Of course the comments add some meat to the subject as well, and he links to a few older articles where this has come up before!

Being in Support I think I have a high level outsiders view of the values and traits that make these roles (and the people) successful or not.  Along these lines, I think I'll have to make up my own definition of what makes a truly good Support individual and then share it to see what everybody else thinks!

What traits or attitudes do you think helps make a good developer, DBA, or Support person?

Tuesday, February 11, 2014

Breaking News: EBS R12.2 now available for 64-bit Windows

This post from John Abraham on Steven Chan's ATG blog details all that you need to know about this announcement, but I will say that this release does mark the end of 32-bit Windows Server releases.  Admit it, you want to know more don't you?  Well what are you waiting for?  Go find out the details!

Monday, February 10, 2014

Purge Inactive Sessions and HWM expectations

Recently I ran the Purge Inactive Sessions concurrent program after it had not run for a while, and based on my Workflow purging I wanted to make sure we didn't purge out too much data or else it would be likely the system would hit High Water Mark contention when trying to query the ICX_SESSIONS table.  On the first day I purged out records to leave us above where I thought the HWM contention would occur, but I noticed even after a Gather Table Statistics concurrent program was run there was slight slow down on querying from ICX_SESSIONS but nothing in HWM enqueue yet.  Reporting the issue to the DBAs, they suggested that I continue purging out past what I felt was safe since this was in QA and they were prepared to do a shrink to restore the table(s) affected by this program.

On the next day I ran the Purge Inactive Sessions report again, and as expected, there is now a performance problem with ICX_SESSIONS which I thought was due to purging too many records since a simple SELECT was not returning even after 90 minutes.  Why is that bad performance?  Before purging, the same statement was returning in less than a minute so obviously my statement must have been affected by the HWM enqueue contention right?  Wrong!  Curiouser, and curiouser.  That wasn't what I expected at all.  Since the report was still running, I decided to let it continue deleting before doing anything else and then when it completed I ran my SELECT statement.  Guess what I found?  That statement no longer took 90 minutes, as it was completing in a minute or two, nor did it go into HWM contention even before another Gather Table Statistics was done.

Why did my query take so long to return on the second day?  My best guess is that the system was trying to calculate the number of results in the table, but was unable to do so while I was busy having the report deleting from the table so it was stuck until after the report was completed.  When I do this in another environment prior to going to PROD, I'll be able to look at this closer and hopefully I'll gain more insight as to what was going on!

Sunday, February 9, 2014

Ten Thousand

Once again it is time for me to digest another milestone for the blog, as this weekend I have passed the big 10,000 views mark!  As I get closer to presenting again, creating more abstracts for presentations, and just generally dive deeper in the Oracle pool, I'm amazed at how my audience has grown and stayed with me throughout this journey.  Here's to another 10k views, and a lot more articles to go with it!

Thank you SO much for sharing your interest in the blog.  I hope you'll continue to enjoy it, as I continue to enjoy sharing it with you!

Saturday, February 8, 2014

Weekend Reading: Oracle Whitepaper on MOAC for custom code in R12

Researching this week, I found the Oracle Whitepaper entitled "Oracle Applications Multiple Organizations Access Control for Custom Code" in MOS note ID 420787.1.  This article contains primarily R12 content, since MOAC was introduced in R12, and while it is not required that you enable it when you install R12 it may be important information for you during your planning if having multiple organizations is a possibility.

Friday, February 7, 2014

Weekend Learning: Redgate on caching

In the monthly Redgate newsletter, I found the initial article on result caching which was followed up with the second article in the series but that doesn't end our learning on caching!  In the first article, there are TWO other links to caching articles as well so that means you have FOUR chances to learn something about caching this weekend from Redgate.

Thursday, February 6, 2014

The dangers of hard coding

We have all been there.  Something gets hard coded when it does not need to be, or when it does not make a whole lot of sense, and it makes your life a lot more difficult than it should be.  Today was one of those times for us!

We had a responsibility created to serve business needs, but they later wanted to segregate duties and when they did we found out that the upload report designed had the responsibility_id hard coded into the report which made it impossible to run out of another responsibility.  Great idea, bad execution because we were not given a way to make it dynamic with profile options or some such plan.

So the business decided to go forward with the new segregation of duties responsibility anyways, but let an administrative account have both responsibilities which was tightly controlled.  No problem right?  Until a new manager came along, did an audit of the responsibilities, and removed the responsibility that was needed to run the upload report from the segregated duties responsibility.  Oops.

The lesson here is that if you need to hard code something, do it in such a way that it is soft coded.  Make updates possible so down the road when a parameter, e-mail address, or responsibility_id in our case have to change the people that follow you can do it without having to take your name in vain while they re-code your work.  Even better?  Allow a framework so changes can be centralized and manipulated easily without messy table level updates!

Wednesday, February 5, 2014

R12: Compiling Forms and ORA-04023

A few days ago I shared a new MOS article that caught my eye, and here is another!  Why do you receive ORA-04023 errors when upgrading to R12.1.1 or R12.1.3?  Read note 1503874.1 to find out what the parameter _disable_fast_validate needs to be set at, and why it isn't set the way it is.

Tuesday, February 4, 2014

Hotsos Symposium 14: Early Bird Pricing Ends!

This week ends the early bird pricing for the only conference cool enough to have me present for them this year!  I've already setup my travel plans, signed up for my badge, and am finalizing my presentation to submit this week and then the countdown begins!  If you can't sign up this week, don't worry you can sign up anytime before the Symposium starts on March 2nd.

Hope to see you there!

Monday, February 3, 2014

Missing: OPMNCTL after R12.2 upgrade

Occasionally I will read the title line of new articles in My Oracle Support and it will grab my attention enough that I open the article up to spend a few minutes reading it.  Note 1619645.1 was one such today because while I do not get to do a whole lot of server administration, I have had a chance to use the OPMNCTL command to start and stop services so having it go missing after an EBS R12.2 upgrade really sounds like a bad thing.  Little did I know how bad it was, because while it suggests why this happened (and is completely controllable) it also has a suggested plan for resolving the problem.  In this case, I am sure those that encounter this really wish they found it sooner.

Sunday, February 2, 2014

Magical Mystery Event Tour

I have posted before about R12 having some event changes that you need to prepare for, but a good three months in I am still seeing events that are completely new to me.  We had a session that appeared to be stalled and our investigation found the thread was in the "gcs drm freeze in enter server mode" wait event locking against LMON when it was just sitting there in the event "rdbms ipc message".  Oddly enough, the situation just resolved itself but not before I captured another event in the wild that I had never seen before: "latch: object queue header operation".  I am not quite sure what happened here, so this gets tagged as a mystery....until the next time and I can solve it

Saturday, February 1, 2014

Food for Thought: When IT grows up

I've posted in the past about some articles on expected growth future of roles, and if an architect role is something you're after, but I've never thought what role I wanted to fulfill in relation to IT changes.  I think change is obviously something that can't be stopped, and for the most part it can't be prophesied either, so stepping back and looking long term for your industry can maybe help you find that niche you want to fill.  I've been lucky enough the last 7+ years to be working on an Oracle EBS system, and during that time I've seen the growth in technology associated with Oracle hardware, yet haven't "stopped to smell the roses" to think about where it might go next or how to take advantage of coming changes.  This juncture in my career is a wonderful time to change that so I can leap forward later to where IT will be heading.