Monday, April 29, 2013

Windows 7: Problem Steps Recorder

I've recently been introduced to a new tool, Windows 7 Problem Steps Recorder, which I need an opportunity to try out.  Why is that?  Well, typically I am able to remote into computers with our IM tool but if I needed a user to show me what's going on and our IM tool wasn't able to be used I could certainly use this if they are using Windows 7.  Oh, and the next time I need to provide a video to Oracle we'll see how they accept it.

Saturday, April 27, 2013

Your Oracle DB not playing nice with Hyperion products

When the Oracle database archiver log files fills up the /flash disk volume, it has the result of halting the rest of the Hyperion suite of products (in this environment we have FDM, HFM, Planning, Shared Services, WorkSpace).  Once the archiver logs were emptied and /flash had available space, everything in Hyperion land started proceeding as if nothing ever happened.  Except one oddity.  We were able to validate our WorkSpace nodes individually without encountering any issues, yet when we went through the hardware load balancer VIP we would receive this error message:

The startup document specified does not exist in the repository.  Select a new startup document on the General preferences tab.  com.hyperion.tools.cds.util.HSRpcParseException: The response from the server contained an unexpected content type.

Further troubleshooting puzzled us as we found that this error would occur only when the second node was in the VIP, even though we could hit that node directly without any issues.  We restarted all of the services that we could think of which would resolve this, even restarting the WorkSpace servers themselves, to no effect.  Opening an SR with Oracle, we were asked to remove the offending node from the software load balancing by going into the Agent configuration yet even this had no positive results.  We ended up having to reboot the entire Hyperion environment, with no other changes being made, to restore service.

This makes me believe that there were some persistent connections opened which held either sessions or ports so that once the issue with /flash was resolved some part of Hyperion was not able to communicate back to the rest of the system appropriately.  Next time, I'll gather some additional information to try and correlate so we don't have to bounce the entire ecosystem.

Thursday, April 25, 2013

Empty segments

Here is a good write up on a new feature with 11gR2 called deferred segment creation which I figure we'll be encountering at some point in time since we upgraded from 10g to the Exadata platform.  As Oracle databases get smarter and smarter, we'll continue to see the possibility for things to slip in the cracks due to being designed under another set of criteria.  Unless you have the resources required to keep re-writing your code every time you do a major upgrade, but that's just not feasible in most shops today.

Tuesday, April 23, 2013

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!

Sunday, April 21, 2013

Application cookie value

Recently we cloned our PROD environment back into TEST, and were going through the checklist to reintegrate everything when we encountered a problem.  The cookie value for the application was still showing PROD, even though we had cloned it onto the TEST database so we knew it had to be some type of configuration value but we didn't know exactly what it was.  Doing a quick search of the profile options showed that the value for "Applications Database ID" had the PROD database name in it which isn't the way it should be in TEST!  We changed it to what it should be for TEST, but nothing changed so we bounced Apache and then we had the correct cookie value being returned!

Thursday, April 18, 2013

APPLSYSPUB security

Recently I was asked to post the output from running "Generate concurrent processing environment information" to an Oracle SR, but before I did I scrolled through it before posting just to make sure there wasn't anything sensitive that was being transmitted.  I was quite shocked at what I found!  The password to the APPLSYSPUB account was right there in plain text for the whole world to see!

Listing of all Environment Variables:
.
.
.
.
GWYUID=APPLSYSPUB/<plain text password>
.
.
.
.

Tuesday, April 16, 2013

What is Exalogic?

This post took me over to a great document where page 17 gave me a great visual showing just what the difference between Exadata and Exalogic is.  If you ever wanted to know, I strongly suggest you go look at this PDF to find the answer!

Sunday, April 14, 2013

Oracle's ETRM

Have you ever wondered how Oracle applications go together sometimes?  Several times I've used their Electronic Technical Reference Manual, also known as ETRM, located here which requires a login (OTN I believe, but correct me if I'm wrong) but also has R12 included too.  I used it with one of my recent issues, just because it was easier to navigate around a few pieces of related code, and I'll be adding it to the links on the side too since this isn't the first time I've used it.

Thursday, April 11, 2013

World Time Zones!

Do you have to work with individuals across different time zones?  Maybe are you confused how many hours off you are from somebody giving an Oracle presentation halfway around the world?  If so, this website is the tool for you!  Yes I know this isn't the typical Oracle or technical post you'd expect, but over the past month or two I've had to use this several times and all to good results so I'd like to share with the community.

Wednesday, April 10, 2013

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!

Tuesday, April 9, 2013

Yet another thanks!

Not even 2 weeks ago I was here thanking you for helping me hit 400 page views, and I'm happy to share with you that just this morning I've hit 500 page views!  This has been the most active month for viewership since I started blogging last year, and this month will be shattering the old record just set last month for hits by quite a bit unless I miss my guess.  Thanks again, and looking forward to growing this site with you all!

-The Oracle EMT

Monday, April 8, 2013

Oracle is #1

As if there was any doubt about the position of Oracle within the database industry, this post removes any doubts you may have had!  I knew they were the leaders, but interesting to see comparatively how much market share they have over the competition.

Saturday, April 6, 2013

WebLogic alerting

This post seems like a very through process for alerting on the WebLogic platform, and I'll be making sure to snoop around our system to see if we're using something like this!

Thursday, April 4, 2013

Oracle KB offering

Not talking about Oracle's MetaLink/My Oracle Support knowledge base here, which seems like the obvious subject from my title, but an offering from Oracle called Oracle Knowledge Management.  I highlight this because I hadn't heard of this before, and while it seems to be directed at a call center type atmosphere part of me wonders if this would be a good solution for our shop so I'll keep it in mind when we start talking about knowledge management tools.

Tuesday, April 2, 2013

Free Sessions for 04-02-2013

https://blogs.oracle.com/ebs/entry/boost_your_efficiency_with_oracle

I've attended this before, and it's a good way to get more out of working with Oracle Support.

https://blogs.oracle.com/certification/entry/0867_01

Not really in my timezone, but I'm really looking forward to this anyways to get some insights into certification as I'll be attempting some over the next year or two.

http://apex.oracle.com/pls/apex/f?p=44785:24:0:::24:P24_CONTENT_ID,P24_PREV_PAGE:6940,1

I found this via ArchBeat, and while I am interested to see this particular GoldenGate training because it is in use in our environment, what's more important are the other trainings at this site!