Checking into Hotsos!

Through some bumpy weather, I finally arrived in the Dallas-Fort Worth area to below 30 degree weather and snowflakes.  In a t-shirt.  Good thing I'm originally from Michigan and am used to cold weather!

A short ride later, and checked into the Omni Mandalay hotel and then relaxed a bit before going downstairs to pick up my registration badge.  Next it was on to the technical portion of our broadcast where I got to try out my hardware to see how it would present to the attendees (both local and virtual), which went rather well without a hitch!

The best part was actually getting to meet with some of the speakers for a few minutes, some names I've seen, some people I follow on Twitter or my blog roll, others I had not yet met:

Amit Podder
George Srdanov
Jim Czuprynski - @jczuprynski and http://jimczuprynski.wordpress.com
John Hurley - @GrumpyOldDBA and http://grumpyolddba.blogspot.com
Luca Canali - @LucaCanaliDB and http://externaltable.blogspot.com

George had presented last year and was kind enough to take Jim and I down to a local deli that had a bunch of stuff that we could stock up on and we all grabbed a few things after looking around a bit.  While we mentioned something about a return trip later tonight, the hail that came down just as we left the deli made us rethink those plans a bit even though it was just 2 blocks away.  Hail.  In Dallas.

If you haven't registered, it's still not too late as you could enjoy the virtual option and not even leave your house!

Weekend Learning: Oracle Data Dictionary

This likely will be old hat to many out there in the community, but I found note 1500058.1 in My Oracle Support that deserves a good viewing if you're new to looking for information related to Oracle database and application technology.  Titled "Master Note: Overview of Oracle Data Dictionary" it goes over the difference between the views that are prefixed with ALL_, DBA_, and USER_ plus teaches us about the view DICTIONARY which lists all views in the data dictionary.

For more advanced individuals, this note also has several interesting tidbits about the O7_DICTIONARY_ACCESSIBILITY parameter as well as a change in 12c to the SELECT ANY DICTIONARY privilege which limits access to several items so they are no longer able to be viewed.

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.

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.

Celebrating 300 posts!

When the blog started, I had no idea where it would take me but since then I've:

• been promoted
• obtained my Master's degree
• presented professionally for OAUG
• been selected to present for Hotsos
• been published by Oracle
• started supporting Exadata
• seen R12 be installed
• started on Twitter, introduced a regular feature, and fell behind on it as well
• celebrated a year of the blog
• remotely blogged about the keynote of OOW 2013, seen both R12.2.2 and R12.2.3 introduced

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.

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?

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.

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?

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.

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.  :}

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.

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.

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!

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.

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.