Presenting again!! OAUG Connection Point - AppsTech 2016

Hi everybody!!  Have you been wondering what I have been up to lately?  Well, the cat is out of the bag now with the presenter list for the OAUG Connection Point - AppsTech 2016 conference now online and my brand new presentation "R12: Reducing the data footprint of Oracle E-Business Suite" is listed!!  This is a great honor as this was by invite only, and this project is the one I alluded too earlier in the year in my Happy New Years post which I have been doing work on for quite some time but now I feel confident that I have enough material for a new presentation!  Do not miss this opportunity to learn from a lot of experts in application technology by registering today!!

Weekend Learning: Enabling data encryption for Oracle Payments module in R12

If you have upgraded to R12, or have a plan to do so, make sure you read MOS document "How To Enable Oracle Payments Data Encryption Functionality" from document ID 1301337.1 to learn how to get on the middle tier, create an Oracle Wallet .p12 file, and what concurrent requests you need to be running as well depending on what profile options you choose for the encryption method.

R12: Account Analysis Report performance issue

A few months ago I shared a post about the Invoice Workbench slowing down due to XLA data volume and it appears that is not the only way XLA bloat negatively affects your system.  Per My Oracle Support article "R12 Account Analysis Report (XLAAARPT) Performance Problem" with note ID 1667405.1 it appears that with the amount of data here you need to change how the report runs by modifying the indexes a bit.

DBA_RECYCLEBIN Vs. RECYCLEBIN

Last year I was surprised that Oracle had a RECYCLEBIN, so this year when I started looking at this a bit more I was even more surprised that there is also a DBA_RECYCLEBIN which has even MORE items in it!  In my research, I found a bunch of documentation available for free out there so here you go:

http://docs.oracle.com/cd/B28359_01/appdev.111/b31695/intro.htm

http://docs.oracle.com/cd/B28359_01/backup.111/b28270/glossary.htm

http://docs.oracle.com/cd/B19306_01/backup.102/b14192/flashptr004.htm

http://docs.oracle.com/cd/B28359_01/server.111/b28310/tables011.htm

http://docs.oracle.com/cd/B28359_01/relnotes.111/b32257/toc.htm

Selecting from too many tables hits Oracle DB bug on 11.2.0.3

Doing some research, I found a few hundred tables which were being gathered as if they contained no data but I wanted to verify it myself by looking at the data (or absence of data).  No problem I thought, I would just take the list of tables, put a comma at the end of each one, and then use the list in something like this:

SELECT * FROM table1, table2, etc

They are empty, so it sounds like a great idea right?  Not so much.  I believed that there was such a thing as too many tables (or maybe columns) that can be joined together (in a Cartesian join in this example) as the following ORA-00600 error was spit out at me:

ORA-00600: internal error code, arguments: [mal0-size-too-large], [kksoff : opitca], [], [], [], [], [], [], [], [], [], []

At first, I thought it was simply because of the Cartesian join but when I reduced the query to only have 20 or 30 tables I no longer received my ORA-00600 message and I received the single null row with all of the columns from each table returned.  It turns out, that since we are on 11.2.0.3 this is a bug I'm encountering detailed in MOS note/bug 553051.1/675576 which is resolved in 11.2.0.4 and 12.1.0.1 versions of the database.

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.

Weekend Reading: Database performance and data types

File this one away in the "needed to sit down to review it" type of follow up post, but the article by Pythian's Edwin Sarmiento is no less effective or topical because of that.  As I've peeled back the onion of what makes Oracle databases and applications tick, the lessons suggested here will help you when designing solutions and also are good reminders for how you can either tune something wrong or look for a potential problem just waiting to strike.  Wouldn't we all like to avoid database bloat?  Who is not looking for faster ways to access our data?

In Depth: Data Integrity

Have you ever wondered about the technical nature of data and the associated integrity?  How about some additional insights into constraint enforcement or global and distributed transactions?  Well then, you're in luck because Lucas Jellema does an amazing job here, found via ArchBeat, of not only writing up a paper that is easy to understand but uses several visual aids which seem simple but do a really good job of explaining some complex ideas to us.

Oracle Advanced Compression highlights

Did you know that Oracle Database version 11g brings with it a host of new features, one of which is Oracle Advanced Compression?  If you didn't, you may want to browse this article which has a testimonial on how this feature allowed a customer to reduce their footprint by 50 percent.  Or, you can skip to the next article to see how another client reduced their database resources by 400 percent and you can find tools that let you self-assess your savings with and without compression.

Don't mix and match your GUEST passwords

Continuing Security Week here, My Oracle Support note 602425.1 tells you what happens when you change the password for the GUEST account to all lowercase or mixed characters in R12.  This type of situation reminds me of a time where we went to a single sign-on application but didn't test any permutations of accepted user passwords so there was a range of symbols which went untested before release.  When we made the switch to the new solution, it was in conjunction with a localization patch as well so it made it hard to understand what was causing a very small group of users not to be able to successfully log into the EBS system.  It wasn't until we did additional research like enabling logs and asking specific questions about what was in their passwords (without actually getting the passwords of course) that we realized the population having the problem all had special characters in their passwords and that the translation of the passwords from one system to another wasn't setup to allow the special characters until we could release a hot fix to the system.

Slow database? Are you suuuuuure?

Very intriguing article from Gorjan Todorovski with Pythian about how we can start monitoring the health of our DB in some surprising ways.  This really has my mind spinning about how to implement some monitoring for the Support group, even though the article was more geared towards a DBA, I think that this is something crucial for Support to be aware of.  I'm thinking of a temp table, or several, so that we can record the information and have it available for our own historical purposes of tracking performance even when some of the tables Gorjan references (which were new to me, so yay!) get their information purged out by design or DB reboot.

Extending this idea further, I'd like to see what I can do to track not only a few metrics (obviously Gorjan picked just a couple of well known wait events to model his idea with) in the DB but go into the Exadata storage cells to gauge their performance as well!  Additionally, I've never worked with the epsilon subquery so I've got a new tool that needs to be sharpened for my toolbox!

Latency heat mapping

Reading a post from my blog roll, I was looking forward to reading the originating post about how to create a latency heat map/chart but thought it would be clunky and would involve some long and drawn out installation.  Surprisingly, it is neither clunky or involved and I can't wait to run this at work tomorrow!

Of course since the original post, there is a new version of code online after feedback and the rubber meeting the road.

Concurrent Manager holding lock on a report

For some reason, we had a report which was locked up with “Could not reserve record x tries” so you couldn't View Details or Hold Request and even running a script to change the HOLD_FLAG was just hanging out.  Looking at gv$session, the user didn't have any sessions which would've lead me to believe they had this locked up so I ran my handy dandy Exadata lock script to find the session holding my script up.  The session had nothing for MODULE or ACTION, but the CLIENT_INFO "0                                                     0         " looked familiar so I bounced the Conflict Resolution Manager without any changes happening.  I started looking closer, as the report was sitting in the Quick Manager so I issued a Deactivate which took it down to only 1 Actual process and a Target of 0 but it just kept sitting there.  Looking at the log files via Processes showed just one thread Active but when I clicked on Manager Log I received this error message:

APP-FND-01632: Cannot open file <directory>/w<process ID?>.mgr for reading

Cause: [Routine] encountered an error when attempting to open file <directory>/w<process ID?>.mgr for reading.

Action: Verify that the filename is correct, the environment variables controlling that filename are correct, and the file exists.

Action: Verify that protections on that file permit reading by this program.

Knowing that there was a problem with this thread, I then issued a Terminate for the Quick Manager and once it was completed with the Actual/Target both at 0 my script finally finished running as it no longer was encountering the locking condition.  After committing my script, so the report was now Inactive/On Hold rather than Inactive/No Manager, I did an Activate for the Quick Manager and once it was back up I removed the hold on the report.  It ran to completion in 2 seconds.

Now there are obviously some questions I have about this, as to why the manager thread had just locked up when it tried to pick up or start running the request, or if the string of numbers in the file name is actually a process ID which I can then backtrack later and give you more information in a later posting.

Supplier issue in Expense Report Import

This post will run closer to the type of things I deal with daily, and hopefully will be helpful to everybody out there that runs into the same type of problem!  Our business ran the Expense Report Import, just like they usually do every day, but encountered the following error message on this run:

Next import invoice: 6196224
Getting work location address
Validating vendor
Getting Create Sites Error...
BEGIN  AP_UTILITIES_PKG.AP_Get_Message(:msgbuf_err); END;
This Site name already exists for this supplier.  
Re-enter.
APP-SQLAP-10916: apiivs/3:ORA-20001: APP-SQLAP-10357: This Site name already exists for this supplier
Cleaning allocated memory

In my research on My Oracle Support I found and used the following Oracle notes:

Expense Report Import Errors With APP-SQLAP-10916: apiivs/3:ORA-20001: APP-SQLAP-10357 [ID 1228868.1] - this was more on point and useful
"Expense Report Import" Is Finishing In Error When No Purchasing Sites Available For Supplier [ID 1351417.1]

Which helped me understand that I had to run this script to find the expense information in question:

select * from ap_expense_report_headers_all
where report_header_id = 6196224

That lead me to the supplier information from the expense report:

select a.* from po_vendors a, ap_expense_report_headers_all b
where a.vendor_id = b.vendor_id
and b.report_header_id = 6196224

Then I was able to use this script to find the supplier site information:

select a.* from po_vendor_sites_all a, ap_expense_report_headers_all b
where a.vendor_id = b.vendor_id
and a.vendor_site_id = b.vendor_site_id
and b.report_header_id = 6196224

With the information we've gathered at this point, we know that the supplier site has been end dated for the OFFICE site that the Expense Report Import expects to be open when it runs.  Having the business remove the end date via the application allowed them to re-run the Expense Report Import without any further issues!

Batches? We don't need no stinking batches!

The error message of "Unable to create batch" was being presented to our users when they were either trying to manually create receipt batches or when the automated processes would run from their requests.  Digging into the SQL of the package started taking me down a rabbit hole from one custom package to another, until I finally found that even though the error message being returned to the users was custom but it was being raised as a result to a call to ARP_RW_BATCHES_PKG.INSERT_MANUAL_BATCH which is a completely canned Oracle procedure.  Tracing through this started making me suspect the call over to arp_rw_batches_check_pkg.check_unique_batch_name but that didn't make any sense since we're talking about receipt classes as defined in ar_batch_sources which are set to AUTOMATIC for their numbering scheme in the auto_batch_numbering field.  So I wind up taking a look at this piece of code a bit closer because nothing else looked possible if I was in the right spot:

SELECT  COUNT(*)
INTO    l_count
FROM    AR_BATCHES bat
WHERE   bat.batch_source_id = p_batch_source_id
AND     bat.name = p_batch_name
AND     (     p_row_id IS NULL
          OR  bat.rowid <> p_row_id );
IF ( l_count <> 0 ) THEN
     FND_MESSAGE.set_name( 'AR', 'AR_DUP_BATCH_NAME' );
     APP_EXCEPTION.raise_exception;

when I decide to see what the data in ar_batches shows me.  Sure enough, the last_batch_num in ar_batch_sources for the batch_source_id in question was currently 5792097 and we have a sequence of batches with a name defined in ar_batches ranging from 5791705 to 5792958 which is preventing the package from successfully getting an available batch name to use in the creation of the batch as shown in these pieces of ARP_RW_BATCHES_PKG.INSERT_MANUAL_BATCH:

IF ( l_auto_batch_numbering = 'AUTOMATIC' ) THEN
UPDATE ar_batch_sources
SET last_batch_num = NVL( LAST_BATCH_NUM, 0 ) + 1
WHERE batch_source_id = l_batch_rec.batch_source_id;

SELECT NVL( LAST_BATCH_NUM, 0 )
INTO   l_batch_rec.name
FROM   ar_batch_sources
WHERE  batch_source_id = l_batch_rec.batch_source_id;

This meant that the application was continuing to increment the last_batch_num value every time the users were trying to do their work, even though the batch names were already taken and it would continue to do so until it encountered a data value which did not already exist for the name field in ar_batches.  The only option I could see is that we would need to manually push out the last_batch_num to match the last item in the sequence, so that a unique value would be returned the next time it would try to get a batch name (see above UPDATE statement).  I didn't want to change the batch names of the items which were created months ago because then we’re changing historical data, and I can’t be 100% sure that it’s not used for reporting or other audit type functions by the business.  After the appropriate approvals, we ran the below script:

UPDATE ar_batch_sources_all
SET last_batch_num = 5792958
WHERE batch_source_id = &batch_source_id
and last_batch_num = 5792097

Service was restored to the batch source after this update was applied, and all was well with the world!  Then we were asked how we could make sure this doesn't happen in the future.  After thinking through the answer a bit, I've come up with the below script which needs to go into a daily alert for us:

select a.batch_source_id, a.last_batch_num, a.org_id from ar_batch_sources_all a, ar_batches_all b
where (a.status not in ('I') or a.status is null)
and a.end_date_active is null
and a.batch_source_id = b.batch_source_id
and a.org_id = b.org_id
and b.name > a.last_batch_num

This was one of those problems where it's incredibly challenging, but also rewarding when you get to the end of the day having solved a pretty big problem and learning a lot in the process.  Years ago I would've ran a trace and handed it over to development to figure out, but I've gained some seasoning since then and decided that I'd do as much "dirty work" as possible before I had to escalate up so I ended up digging down through 7 layers of code to find the actual root cause!

Lazarus reports

Once upon a time, I thought that after a report had run in the Concurrent Manager there was no possible way to run the same request over again.  Time has passed and I have become more powerful than you can possibly imagine, so I now know that the concurrent is just a program reading the values from tables within the data structure.  This meant that it was possible to have reports emerge from the dead like Lazarus (or the Pit of the same name) with this script for 10.2.0.4 on EBS 11.5.10.2:

update fnd_concurrent_requests
set phase_code = 'P', status_code ='Q'
where request_id in ()

After going to 11.2.0.2 without changing from EBS 11.5.10.2, I noticed that my old script wouldn't exactly work the same so after several different investigative attempts the script needed to be tweaked to:

update fnd_concurrent_requests
set phase_code = 'P', status_code ='I'
where request_id in ()

Obviously something like this should be used with care, as restarting certain reports may cause data corruption issues, but if you're reading this blog you should be a responsible adult.  Any irresponsible adults reading, please feel free to keep reading the blog.  :}

System Wait Events Part 1

Before I go too much further with the blog, I have to do some housecleaning!  Back in August I had realized that a post went missing, so I put the URL into a draft and saved it to come back to and last week I was looking for that article......but I had never come back to it to re-write the article!  That's bad on me in general, and more specifically this post and the one last week have gone without a true origination and credit which bugs me quite a bit.

After upgrading to Exadata with 11.2.0.2, we started running into sessions hanging in the "inactive transaction branch" which were running statements over a DB link to an instance running on 10.2.0.4.  A quick search lead me to this page which not only suggested a fix (patching didn't actually resolve it), but also a way to look into our systems which is proactive and at a deeper level than we had before.

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.

Recycle bin?

You could've knocked me over with a feather when I found out that an Oracle DB has a recycle bin!  So I'm curious about it obviously, and query the table and lo and behold there are items in there which a. aren't being dropped and b. being created every day as a result of our team's automation.  The good news is that there isn't a whole lot of space being taken up by what's in here, but here again is something we aren't monitoring on so now I'm going to be designing a new alert to let us know what's being dumped in here.

http://connormcdonald.wordpress.com/2012/11/23/why-cant-i-resize-my-datafile/

Timestamp across DB versions

So the other day I decided to write up an alert to let us know when a DB crashes, and nobody hears it, but I was struggling with joining our instances for some unknown reason due to this error message:

ORA-30175: invalid type given for an argument

I shelved it for a day or two until I had a chunk of time to dig into this a bit deeper, and with the help of an Oracle forums post I realized what my problem was.  You see, the source of my confusion resided in the fact that the below script was able to be run individually for each instance and I only had problems when starting to union them together:

select 'INST1' AS "SERVER", process_id, host_id, substr(reason, 0, 40) AS STATUS, creation_time from DBA_ALERT_HISTORY@inst1
where creation_time > sysdate -1
and object_type = 'INSTANCE'
union all
select 'INST2' AS "SERVER", process_id, host_id, substr(reason, 0, 40) AS STATUS, creation_time from DBA_ALERT_HISTORY@inst2
where creation_time > sysdate -1
and object_type = 'INSTANCE'

Looks benign right?  Well I realized that I was joining 11g and 10g DB tables together and that the timestamp object has apparently changed a bit, so that even with the table definitions not changing our system just wouldn't let me join the tables together no matter what I did when I do a value comparison on creation_time!  Finding that post was the key to getting this changed, because when I updated the script to be:

select 'INST1' AS "SERVER", process_id, host_id, substr(reason, 0, 40) AS STATUS, creation_time from DBA_ALERT_HISTORY@inst1
where creation_time > to_timestamp(trunc(systimestamp))
and object_type = 'INSTANCE'
union all
select 'INST2' AS "SERVER", process_id, host_id, substrDBA_ALERT_HISTORY@inst2
where creation_time > to_timestamp(trunc(systimestamp))
and object_type = 'INSTANCE'

everything was right with the world, and no more error messages were returned.  Oddly enough, or not as I've already explained, the same script with creation_time > to_timestamp(trunc(systimestamp)) -1 starts to bomb out again because the timestamp object is again having a comparison done to it.