R12: A/R report goodness (DQM)

The other day a user was perplexed as to why the report DQM Serial Sync Index Program was being kicked off in their concurrent, under their login ID, when they had no idea of what the report was or what it did.  I was curious as well, so I probed a bit and asked some follow up questions like what they were doing at the time and it seemed that there was a particular task they were likely doing at the time these reports were being kicked off as them.  In the new R12 framework, they were going into the canned Oracle Customer:Standard web form (previously the canned Oracle application form) to update demographic information and to prove this out we had a dummy record changed which then resulted in the DQM Serial Sync Index Program to be submitted in the concurrent manager under the user's login ID.

What is the DQM Serial Sync Index Program you ask?  Well, with the changes to TCA (Trading Community Architecture) and the introduction of DQM (Data Quality Management) there have been some new wrinkles added in how updates are made and maintained in the system.  I found My Oracle Support notes 465993.1 (explains quite clearly what this is) and 737030.1 (link to the TCA administration guide) quite useful in my efforts to understand this a bit more.

R12: Event changes

Obviously since we weren't making any upgrades to our database with the R12 upgrade we weren't expecting to see a whole lot of new wait events, but we did realize that the application may act differently which would cause new wait events to either show themselves or get magnified if we'd already seen them.  These are some of the wait events that I've seen over the last few weeks which fall into the previous categories, some of which will get their own blog posts soon, so consider this a general lesson of how your system might change in ways you had not expected:

remote db file write
enq: UL - contention
ARCH wait on SENDREQ
enq: US - contention
LNS wait on SENDREQ
FAL archive wait 1 sec for REOPEN minimum
gc buffer busy release
gc buffer busy acquire
Backup: MML write backup piece
log file sync
gc current block busy

I'll refer to it later as well, but the online documentation "Descriptions of Wait Events" from Oracle is one of my more referenced bookmarks at work (and I should probably post it on the site here as well too) when I encounter new wait events or try to make sense of something I might not have a full handle on.

R12: MO_GLOBAL replacing DBMS_APPLICATION_INFO

In the 11i application we would use the PL/SQL procedure to set our org_id:

begin dbms_application_info.set_client_info(&org_id); end;

With our brave new world of R12, we need to be using this to set our org_id information:

BEGIN MO_GLOBAL.SET_POLICY_CONTEXT('S',&org_id); END;
Which isn't a horrible thing, but my tip is that as a result you need to be granting execute permissions on FND_GLOBAL and MO_GLOBAL to non-system or non-standard users and roles that need to be able to run this.  If you don't do this, then you'll receive ORA-06550 or "PLS-00201: identifier 'MO_GLOBAL.SET_POLICY_CONTEXT' must be declared."

R12: Updating your ETLs

Along the "let me show you our lesson's" vein, like a lot of you there are a lot of moving parts and pieces which make up an infrastructure.  One of those for us is a rather complex series of ETL (Extract, Transform, and Load) events that get run on a regular basis and were tested in our QA environment during multiple mock deployments of R12.  During the first day with our IT partners doing extensive integration testing we found that there were ETL statements which were not referencing new objects which had been introduced such as tables or columns.

So my tip is that if you are going to R12, and have ETLs in your environment, make sure that you look them over closely so outdated tables aren't retained in favor of new tables that are introduced.

R12: Username,Description

In the Adjustment Approval form our business support partners had identified that data appeared to be missing, and when they went to add it back in they would receive the error message:

"This Username,Description does not exist"

I noticed that all three of the examples had an odd description format in FND_USER of "LNAME, FNAME" instead of "F. LNAME/DEPT" like everybody else had.  That being the case, I changed the description of FND_USER for the first employee record and had the business re-try their form.  It worked!  I changed the description for the other two records and the form worked for them, so I thought I was the hero and everything was done here.  I was wrong.  Our development team had taken my issue report and researched it before it was closed by management, and came across MOS article 740203.1 which talks about this very thing and the cause isn't the comma but blank spaces in the field!  By applying my "fix" what I did eliminated the blank spaces in the field, and as an accidental result, fixed the issue.

Thursday Twitter - 10/24/13

This week I go beyond my normal five tweets to share, and drop a big ten tweets that just might interest you!

https://twitter.com/yvelik/status/393563630903377921 - Yury helps out the Twitter newbies with a tip.

https://twitter.com/yvelik/status/393553280934367232 - Yury also drops a bombshell that OOW13 presentations are now FREE to the public.  Go now, before Oracle changes their mind!!

https://twitter.com/oracle_ebs/status/393425435717292032 - Oracle's EBS group shares the fact that JRE 1.7.0_45 is now certified with EBS.

https://twitter.com/oracleexalogic/status/393406557486325760 - As part of OracleDay events, 12 cities in North America get FREE Exalogic presentations!

https://twitter.com/pythian/status/393115881674252288 - The great folks at Pythian have opened a UK office!

https://twitter.com/oracle_ebs/status/393031574956294144 - The Oracle EBS team answers their question on what methods are available to diagnose R12 upgrade performance issues.

https://twitter.com/uwehesse/status/393014927461715968 - Uwe Hesse advocates for the use of SQL Plan Baselines from his current Oracle University course.

https://twitter.com/gregrahn/status/392865256801718272 - Greg Rahn (amongst others I've seen this week) tries to help us understand just how scary software bugs can be for the bottom line of a company that loses almost $500M due to it.

https://twitter.com/uwehesse/status/392643676569997312 - Uwe asks a rather good question about a hidden database parameter which appears to do nothing at all.

https://twitter.com/kylehhailey/status/392339714981646336 - Kyle Hailey has a great statement, and probably hits really close to home for a lot of Support folks out there.

R12: Credit Card Information

In 11i, we had a handle on how to find out what employees were linked with which credit cards by using the AP_CARDS_ALL table so during our first business day validation when we had a report of missing information this is where we first went to.  Finding the table had no entry for the employee's credit card information, we were curious so expanded our investigation to check how many other entries were missing.  What did we find?  ALL of the information we expected to see in the CARD_NUMBER column was gone for EVERY employee!

After a mini freak out, because we thought this was the first report of an epidemic problem, we turned to our project team for some information about this issue and were told that the information now resides in the IBY_CREDITCARD table which has a CCNUMBER field which is now used by the application.  Whew!  We dodged a bullet there!

If you want to get some additional information on this, and other new IBY (Oracle Payments) objects that can be installed with R12, visit MOS Note ID 1391460.1

R12: Changing of the URLs

I told you about the Knowledge Transfer we had with our DBAs, and one of the items I stressed was that the URLs used by our population shouldn't be changed.  I was assured that they wouldn't change at all.  Imagine my surprise when it appeared that at least one did change.  :}

I had bookmarked our URL as http://server:port/OA_HTML/US/ICXINDEX.htm which was all fine and dandy until I had to clear my Internet Temporary Files out to test something else and then my bookmark stopped working.  It seems odd, but it's like there was a file locally which redirected to the real URL which was http://server:port/OA_HTML/AppsLocalLogin.jsp and MOS Note ID 306075.1 explains how the ICXINDEX.htm file is no longer supported with 11.5.10.2 yet doesn't explain why there was a local file doing the redirection while the server no longer supports the file existing now.  Additionally, it wasn't only Internet Temporary Files which canceled this out but also clearing out the local Java cache files on a PC since we that happen for another user also while trying to troubleshoot another issue.

So not only is this a heads up as to the repercussions of going to R12, but a friendly tip that you should be clearing all of your PC caches before testing out your URLs in the QA environment.

R12: Application server craziness

A few days ago I let you know that we were going to R12, but I never really said if we were successful or not on the blog.  Well, we were!  Yay us!  Now, that brings new challenges obviously so I'm going to spend a week or two sharing with you some brand new content directly related to my experiences with 12.1.3.

The first thing I had noticed in the hours proceeding our validation and sign off, was that the application servers were running "hot" with their OS load averages being a bit higher than I'd normally seen them.  I wasn't too surprised by that since our DBA group had a Knowledge Transfer session with us and this was one of the issues brought up as a result of a known bug described in MOS Note 745711.1 that required us to set the value for FORMS_RECORD_GROUP_MAX to 100,000 but I thought differently the next morning.  We were already seeing load averages FOUR times normal on EACH server and nobody was really in and doing anything yet.  What really puzzled me was that the threads on the OS weren't tracking back to anything I could find existing in gv$session; not just INACTIVE, but they just weren't there trying to match up the PID with the gv$session.process column information so I figured maybe there is something new with how R12 deals with session information.

I alerted the DBAs and continued to take a look into this, while they did the same, and we came to the realization that all of the application servers were not rebooted.  Why is this a critical issue?  A reboot of the application servers is required after the RPM patching that was done to them for R12!  The DBAs also thought they needed to limit the amount of rows returned as well, so we made a change to the value of FORMS_RECORD_GROUP_MAX to 60,000 and did a rolling bounce of the application servers just in time.  Why just in time?  When we started, they were FIFTY times normal load and by the time we finished were north of SEVENTY times normal load and we had started to see occasional intermittent failures on the nodes that were still up waiting for their reboot.

After reboot, and parameter change, the load never really went over our normal limits and it has been a WEEK now with everything just fine!

Exadata cell scripts and investigative information

Closing our the spotlight for the week on Tanel Poder, are several articles on Exadata gathering more data about cells and diving into performance issues with it.  First up, Tanel shares a script which lists configurations about the individual cells and gives us some additional information about virtual CPU threads, but the best part of this are all of the links in the comments which give us even MORE information!  Secondly, Tanel hosted a free hacking session on Exadata and is exactly why you need to register to get his blog updates so you don't miss out on the next one and I think you might enjoy the PDF he shared as the basis for his hacking session because it has a few tips and tricks in it that are useful even though you weren't able to attend.

SQL Hints & Features Revealed!

Several months ago I squirreled this link away for later, and now that I have some time to catch up on it I'm really blown away by how much Tanel shares with us in this rather straightforward article.  I've read quite a few SQL explain plans over the past 7 years, but I've never had occasion to run into the tables V$SQL_HINT or V$SQL_FEATURE and this starts to open up the rabbit hole for us a bit with the scripts that Tanel shares here by showing us all of the possible hints and what they are used for.  Once I get up on my SQL skills a little more, this is likely the next destination to start understanding more about what the CBO is up to and what is possible to get out of it.

Weekend Reading: Oracle DB 11.2 Upgrade

Working on getting to the 11.2 database patch set?  Well Tanel wants us to know about a slide deck from Oracle that has over 500 pages of information in it just about this!  Awesome reading material, and something to sink your teeth into (Halloween pun intended) over this weekend.  All that AND another document link which details the behavior changes from 8.0 to 11.2 (and I'd bet it'll be soon updated for 12c too).  Have fun!

Thursday Twitter - 10/17/13

Let's start off this week's Twitter recap with the big news:

https://twitter.com/oracle/status/391016170070888449 - Oracle is now the SECOND largest software company!

https://twitter.com/alexgorbachev/status/391034554636853248 -Just a day after Boss' Day, Alex Gorbachev has some good insight about managers.

https://twitter.com/tanelpoder/status/390487789705654273 - Tanel shares a webinar that Kerry Osborne is giving next month on 12c.

https://twitter.com/uwehesse/status/388901568763797504 - Uwe digs deep for some Voltaire!

https://twitter.com/oracle_edu/status/388731698722385920 - Oracle University's top 10 self-paced courses.

How to query Exadata's Storage Cell activity

Yesterday we learned how to go up and down the chain of disks to identify problems and information for Exadata cells, but today in our blog spotlight you'll learn from Tanel about v$cell_thread_history!  He equates this to a database ASH (Active Session History) type of table/report, but only for your cell server and apparently the last 10 minutes of activity on a heavily used cell.  Since I don't really have to dig into Exadata cell problems (yet) this script might gather some dust, but when I have some free time or see the glimmer of a problem on a single cell you can bet I'll have this in my toolkit to use when that day comes.  Another great script for Exadata!

Exadata disk mapping utilities!

Continuing our blog spotlight on Tanel, I'm always surprised by how many scripts he has created and shared with us about Exadata, wait events, performance, and just troubleshooting in general.  The first post we're reviewing today shares how to find out what your disk information is from ASM to the very storage cells of Exadata!  Going into such detail gives me several ideas for how to script out alerts which don't rely on our Oracle ASR machine to tell us when something goes out.

The second thread is very closely related to the first, but in this article he has what I'd call a quick and dirty set of scripts that allows you to see a. a lot of information at one glance and b. some very in-depth information after you've identified that something needs attention.  I'm not an Exadata DBA, but we have had some issues with disk so I'd like to keep these scripts handy for the next time we might be having problems so I can do some quick investigation and verification.