Wednesday, October 30, 2013

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.

Tuesday, October 29, 2013

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.

Monday, October 28, 2013

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."

Saturday, October 26, 2013

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.

Friday, October 25, 2013

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, October 24, 2013

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.

Wednesday, October 23, 2013

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

Tuesday, October 22, 2013

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.

Monday, October 21, 2013

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!

Sunday, October 20, 2013

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.

Saturday, October 19, 2013

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.

Friday, October 18, 2013

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, October 17, 2013

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.

Wednesday, October 16, 2013

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!

Tuesday, October 15, 2013

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.

Monday, October 14, 2013

Blog spotlight: Tanel Poder

Last month during the week long 12c spotlight, I promised that I'd be including one of the authors and for the blog spotlight this month I've picked Tanel Poder!  I "met" Tanel during the Hotsos Symposium in 2012 after attending one of his sessions, and his is one of several blogs that inspired me to join the blogosphere.

In this really great article, Tanel starts by explaining what direct path reads are, why they happen (or don't) in Exadata, and introduced us to a hidden parameter that seems to be documented in My Oracle Support.  While still a dangerous choice if you don't understand what the _serial_direct_read option will do to your environment, at least now we know about it and some of the associated documentation from MOS so we can open our own SRs referencing that to make sure what would work best in our own systems.

Sunday, October 13, 2013

General project lesson: Access

This weekend we went to R12, and I was surprised by how many people had not logged into the Production environment.  This made us troubleshoot issues that were never encountered previously, as we had the application in a state of flux, so it gave me the idea for this post that when you are granted access to a server or application that you make sure you have access several days in advance.  If you don't verify your access early, you'll run into unique issues or impossible scenarios which may prevent tasks on your plan from being completed on time when it comes to pull the trigger on them.

Saturday, October 12, 2013

Learning more about Oracle Alerts

As a follow up to my previous article on Oracle Alerts, when I had shared that information with my team one of my co-workers pointed out that we should do Run as well as Verify because it could pick up other issues with your alert.  So remembering that, I went to another alert which was erroring and Verify gave me the thumbs up but Run gave me the error being encountered:

APP-ALR-04108: SQL error ORA-01840: input value not long enough for date format

In the details for the alert are several variable lengths defined and one of the variables is too short for the date information being passed into it.

Friday, October 11, 2013

Goodbye 11i, I knew you well

Yesterday we took down our main 11i EBS system for a total overhaul to the EBS 12.1.3 platform!

I hope all the followers of this blog that are on 11i will continue to read on, (we still have another 11i instance that will be running) since many of the issues I find in R12 will be relevant to you some day in the future and because I'm sure there will be many more "general" problems in either processes or systems so that you'll get some value from the blog.

Join me on this ride into the wild blue yonder!

Thursday, October 10, 2013

Thursday Twitter - 10/10/13

Here are the top FIVE tweets of the week that grabbed my attention (sorry, no bonus tweets this week but I'm thinking about expanding to a top TEN list next week):

https://twitter.com/yvelik/status/388074845059047425 -Yury shares his OOW slidedeck with us for his presentation on needing a RMAN catalog.

https://twitter.com/kylehhailey/status/388069739928371200 - Kyle has a great stat which boggles, and blows the mind simultaneously.

https://twitter.com/gregrahn/status/388040259897790464 - Greg Rahn has a hilarious comment about the recent government issues!

https://twitter.com/oracleasktom/status/387614013808316416 - Tom Kyte will be providing the foreword to a new eBook primer for the new 12c database, which you can preorder.

https://twitter.com/oratweets/status/387205209367138304 - Iggy Fernandez introduces us to a light "read-only" version of OEM in 12c so DBAs can let developers and support individuals into the OEM application without fear of external parties getting too much power.  Great option!

Wednesday, October 9, 2013

Concurrent Manager and APPLPTMP parameter

Digesting the information in this article, it reminds me that sometimes it is the smallest of items which have the largest of impacts.  While the author starts off telling us about the ORA-20100 error encountered for FND_FILE, the important meat here is what the parameter $APPLPTMP is and just how it is different from APPLTMP.  I found this interesting because I'm not quite sure what $APPLPTMP is set to in our environment, and I'd like to investigate this a bit more in 11i as well as when we go to R12.

Tuesday, October 8, 2013

Apache DMZ HTTP forwarding

Having servers behind or within a DMZ presents a different set of issues, and one of those is an inability to have Patch Wizard directly make a connection to My Oracle Support.  This article via ArchBeat, gives me the idea that placing an Apache server within our DMZ would be able to get our servers out to the real world without it being able to get back in.

Monday, October 7, 2013

WebADI (BNE caching)

In our prep for going to R12, I was asked how to access the BneAdminServlet in our current 11i environment so the WebADI cache can be cleared.  I found that My Oracle Support Doc ID 1075840.1 shows that it should follow this methodology:


Release 11i:


http://hostname.domain:portnumber/oa_servlets/oracle.apps.bne.framework.BneAdminServlet


Release 12:


http://hostname.domain:portnumber/OA_HTML/BneAdminServlet


I can confirm that for our configuration, this is how things were setup in both environments so you many find some validity in this information for quite a while. 

Saturday, October 5, 2013

Thursday Twitter (on Saturday) - 10/5/13

I realized on Friday, that I totally had not remembered about my new feature I had introduced on Thursdays!  We'll see how well I remember going forward.  :}

https://twitter.com/thatjeffsmith/status/386128151367385088 - Do you want to try out some Oracle beta software?

https://twitter.com/dbakevlar/status/385905865124896768 - Are you interested in your Twitter stats?

https://twitter.com/kylehhailey/status/385816989253369856 - Kyle Hailey has some words of wisdom for us about simplicity.

https://twitter.com/kylehhailey/status/385526621437849601 - Kyle also reminds us about Oracle-L which is a site I have on my to-do list to join and participate in, but right now I am getting behind in other places so I can't add too much more right now.

https://twitter.com/stevefeuerstein/status/385504076412112896 -Stretch yourself a bit with PL/SQL challenges which are supposed to start today. 

BONUS:

Slideshares from:



Friday, October 4, 2013

E-mail special characters and SQL

Looking at the system post “network issue” I found that the “Journal Import Health Check” (yes, that one) alert has been erroring after the alert was changed and looking back it’s been alerting for several days.  Here is the relevant request log text:

Performing alert "Journal Import Health Check" (ROUTINE=alepaa) (FILE=7175946/alr/lib/alepea.c)

SQL error ORA-00911: invalid character
occurred while executing the user-defined SQL statement for this alert (STMT=select request_id,
decode(status_code, 'E', 'ERROR', 'X', 'TERMINATED', 'D', 'CANCELLED', 'I', 'SCHEDULED/NO MANAGER', 'NOT LISTED') as "STATUS"
from fnd_concurrent_requests
where concurrent_program_id = 20215
and status_code not in (
'C' --Normal
, 'G' --Warning
, 'Q' --Standby
, 'R' --Running
)
and (requested_start_date > sysdate – 1/24 OR actual_completion_date > sysdate – 1/24))

Looking at the SQL in the form for Oracle Alerts didn’t make me think there was any issue, and the alert showed:


But when you copy and paste this into a SQL development tool it showed:


With ASCII characters instead of the minus sign we were looking for, and copying this back into the e-mail application shows:



It is hard to avoid something like this happening if we’re copying from e-mail, but this reinforces that after we change an Oracle Alert, we should utilize the Verify button to get it to run the SQL and make sure everything is correct:

Thursday, October 3, 2013

SOA Fault Handling - Part 2

Remember that great article I shared a few days ago about SOA Fault Handling?  Did you think it couldn't get better?  Well, that's not true!  In the next installment, the authors go into detail about the Oracle Service Bus and the hows and the whys behind it helping with fault handling and prevention within the integration layer.  Caching, throttling, retry, load balancing, and replies are all types of measures that are at hand to deal with SOA fault handing in a BPM/BPEL environment and this article explains them in good detail with enough pictures to make sure we understand everything.

Wednesday, October 2, 2013

Food for thought: Cloud Security

This post raises a good question: Why are more people and organizations not choosing to go with Software as a Service as their first option when it makes sense?  The answer is: cloud security.  I thought it was amazing that 50% of organizations are putting sensitive data into the cloud, but the figures of about 1/3 of companies have any type of cloud security policy shows how immature this field is right now and how dangerous it could be for organizations.  Lots of ideas, and articles, in this one.  Chew on it a bit.

Tuesday, October 1, 2013

SOA Fault Handling - Part 1

Did you ever want to learn more about how to deal with fault handling and the associated prevention with SOA and BPM (or BPEL)?  Well look no further than this article, found via ArchBeat, to sate your interests!  Honestly the article had me from the first lines:

"It is one thing to design and code the "happy flow" of your automated business processes and services. It is another thing to deal with unwanted, unexpected situations that might occur in your processes and services."

Absolutely spot on!  Not only do you get the expected rights and wrongs, but we learn very important lessons about what Business Faults are versus the Technical Faults, how they get implemented, and what are some recovery/prevention strategies are.