Wednesday, July 30, 2014

AWR Interval discussion

A few weeks ago I shared several posts from Doug Burns about incremental global stats, and today are several posts from Doug on AWR Internal here and here.  Why am I suddenly thinking about this subject?  Well I keep an eye on our alert which monitors ASH data every hour as I've said many, many times before and this is absolutely a good discussion about what tools are useful when (especially if you read the comments of the first AWR post from Doug).

Tuesday, July 29, 2014

Hyperion Essbase application and server log files

While I focus on EBS and Oracle DB technology, our profile has grown to include Hyperion products so I thought maybe other support individuals would be interested in being able to find Hyperion Essbase application and server log files.  Bookmark My Oracle Support article "Locate Essbase Log Files" from document ID 1287286.1 to access the application and server log files for Hyperion Essbase from version 7.1.x to 11.1.2 so you can start proactively viewing the logs for trends or errors being thrown.

Monday, July 28, 2014

Folders: Allow Customization profile option

Today I learned something from our team about the profile option "Folders: Allow Customization" from an unusual situation!  A/P reported that they were not able to sort their Invoice Workbench in responsibilities for one organization, as they were with responsibilities in another organization, so I thought this was going to be fairly easy and straightforward but the more I looked everything seemed okay.  The same folders were created and assigned to responsibilities being used in BOTH organizations, and even to some users which weren't able to sort them either, so I was stumped.  Our project team had the solution that the profile option value for "Folders: Allow Customization" needed to be set to Yes at the responsibility level for all of the responsibilities that the users needed to use because we have "Folders: Allow Customization" set to No at the Site level so it didn't matter what the folder assignments were!

Sunday, July 27, 2014

R12.1.1: Payment Workbench issue due to invoice volume

File this under thinking about going to R12.1.3 if you are upgrading, needing to apply this patch if you are on R12.1.1 or even a new entry that should be in your testing suite.  On R12.1.1 it is possible to have the Payment Workbench not allow new payments to be entered if there is a "high number of invoices" per My Oracle Support document "Payment Workbench Does not Work with Payment Patches That has High Number of Invoices" note ID 1467259.1 which seems to be an odd situation to be in for a form to stop a function due to data volume issues.

Saturday, July 26, 2014

SQL*Net vector message from client

Just a few days ago I talked about a lesson learned during our DR exercise, and another thing I picked up was actually not from our DR instance!  With the way we needed to carry out our exercise we stopped our GoldenGate replication and the archive log shipping as well, so when we kept an eye on our PROD system via my often referenced wait event script I noticed when the wait event "SQL*Net vector message from client" showed up.  Not only did it show up, it was right at the top.  I have seen GoldenGate replications stopped before without this event showing up, and there were no negative effects seen in our system, so my assumption is that it is related to the archive log shipping that was stopped.  Next time I see the wait event I will have to do more research on the PROD system to see exactly what processes are getting caught in this and what the origin is so I can solve this mystery!

Friday, July 25, 2014

Weekend Learning: Things to consider before upgrading to avoid poor performance or wrong results

Today I bring you several articles from Oracle about bugs and upgrading!  Starting out with "Things to Consider Before Upgrading to to Avoid Poor Performance or Wrong Results" with note ID 1392633.1, we also have note ID 1320966.1 for versions, and 1645862.1 for versions.  Pick your poison for what version you are on or are planning on going to some day!

Going a step further, I tried to search My Oracle Support for "Things to Consider Before Upgrading to 12.0" and "Things to Consider Before Upgrading to 10" yet, for some reason I do not understand, I was redirected to "Oracle Support Lifecycle Advisors" note ID 250.2 but nothing here shows up about what I searched for.  Still, you may find that article to be of some interest anyways!

Thursday, July 24, 2014

A lesson on resetting EBS passwords

If you have ever reset a password in the E-Business Suite for an FND_USER record, you know the typical password parameters required to get it to be accepted by the application.  Yet what happens when you forget those rules, and try to reset the password using a script?  Let me tell you, the results are not good!

Remember my post a few days ago about not jumping to conclusions?  I wish I could tell you that I did not jump to a conclusion during our DR testing when I had just reset a password for a FND_USER record, and had a problem when logging into the account, but honestly I jumped right to the conclusion that the DR project must be part of the problem here.  When I was logging in with the account, I would get the login prompt but when I should have been presented with the EBS application Navigation landing page all I received was an error message indicating that the page could not be reached.  Of course I had logged into the DR environment with my credentials an hour or two ago, so of course something in DR had changed which is preventing me from logging in with the other account right?  WRONG.

What was the problem?  I reset the password for the account using a PL/SQL script that I really have not used a lot in the past, so I did not realize the same rules applied here when creating a password!  What I did was to create a password with consecutive duplicate characters (for instance, the zz in pizza are consecutive duplicate characters so that word cannot be used in a EBS password), and since there was no application validation as I was using the script, it apparently saved but the EBS application did NOT like it one bit.  After I had asked my co-workers about this problem, it was suggested I make the password contain no consecutive duplicate characters in a row, and after doing this with the script I was now able to log into the EBS application with the other account!

Wednesday, July 23, 2014

OAUG Connection Point - AppsTech 2014: Day 3

Following up on a relatively anti-climactic day of not attending sessions yesterday, I actually did get to listen to one today!!

11:30 AM - Glenn Keller - How Do You Select and Truncate 5 Billion EBS Records?  Very Carefully

This was one of the sessions I REALLY wanted to see because purging has been something I have become very interested in over the last several years after starting my own purge journey.  That said, I learned a bit about how Glenn approached his project, and the tool he used to generate 7000 SQL scripts, but we didn't get to really see anything they did.  Complicating matters further, this was not a straightforward EBS purge since it was the removal of proprietary and classified information from a database.  Maybe if there was some take away about scrubbing information in general, I would have felt that I gained something, but again this was just the review of what was done and not a lot of how.

Why did I only attend a single session today?  I found out that being in a virtual conference does have some positives!  Today we had family members in a pretty serious car crash several hours away from us and not being across the country I was able to tell my boss about it, have him tell me to go take care of the family, and then rush home to pack a few things before driving for a few hours to make sure everybody is okay.  That is nothing to sneeze at, and an obviously unexpected benefit of being in town.

I do want to listen to quite a few other sessions from this conference, so when I see them published I will do some reviews after I get the chance to listen to them!

Tuesday, July 22, 2014

OAUG Connection Point - AppsTech 2014: Day 2 - All me!

As expected, today's focus during the OAUG Connection Point - AppsTech conference was giving my presentation but after I gave my presentation I really wanted to see 3 other sessions!  Unfortunately, I learned that a drawback of a virtual conference is being available at work so I was sidetracked from the conference and did not get to attend ANY of the other sessions today!  What else did I learn today?

I learned that depending on technology can let you down when you least expect it.  I practiced my presentation with my Bluetooth earpiece for my phone, and while I made sure to charge it last night, it must have been switched on then or in my pocket on the way to work this morning so it started beeping as soon as the recording started today.  You would think the beeping in my ear would have clued me in to a problem, but the moderator said I would hear a beep when the recording started so I thought the constant beeping was the GoToWebinar product telling me it was recording instead of the battery dying on the earpiece.  Not only was this distracting me a few times every minutes taking me out of my stride, but about 10-15 minutes into the presentation the earpiece totally died so I had to switch to putting my phone on speaker after taking about 30 seconds to realize what just happened.  Also at the beginning of the presentation when the beeping started, I lost another 20-30 seconds trying to figure out what was going on and when the moderator told me that she could hear me I did not give the beeping a second thought.  What a mistake that was!

I also learned that having a presentation with constant bullet points moving in to view leads to a lot of clicking and that can be a problem with a remote presentation tool where it seemed like the first click would just wake up the tool so that my second click would actually advance the slide.  This also helped to take me off stride since at the start of the presentation I never had control until a few bullet points advanced all at once and we were on slide 3 suddenly.  I had a few other times when I had to go back when the first click actually did what I wanted it to!  Options are to get control on my computer locally to avoid this, or modify the presentation to remove the transitions so all of the bullet points show up when I switch to the next slide.

The biggest take away for me is I have to learn how to deal with adversity so I do not let it fluster me.  The beeping, the lack of response from the virtual audience, the change in having to hold my phone halfway during the presentation, and the time I lost dealing with the technical difficulties all contributed to me speeding up my talk and even dropping a few bits of information (intentionally or not) so instead of the 45-50 minutes I rehearsed yesterday became 38 minutes today.

What would I give myself today in terms of a rating?  I would be generous in saying 5.5 out of 10 because of pretty much everything I detailed above!  I was happy to receive a few direct responses letting me know that the performance was not as horrible as I think it was, but I am really determined to up my personal score for the next presentation because it is not anywhere near where I think I should be.  I was really glad that we had about 10 minutes of Q&A because I would have felt really bad for the attendees (around 50 were scheduled) if they had paid for an hour and they only got 2/3 of that.

Monday, July 21, 2014

OAUG Connection Point - AppsTech 2014: Day 1

The first day of our OAUG Connection Point - AppsTech virtual conference was a great event!  I wasn't able to attend all of the sessions, but I did get to all of the ones I was looking forward to!

10:00 AM - Mike Swing (@mike_swing) - The Big Picture of the R12.2.3 Upgrade

The best tip from this presentation was his recommendation to wait for 12.2.4!
There was a LOT of information shared, so much so that Mike would have had to do more than 2 slides every minute but he was so detailed and gave so much additional information that he went slightly over the allotted timeframe.  That means we got plenty of different choices in how to upgrade, information as to obsolete products, and items to complete prior to the event!
E48839-03 is a document you need to review for upgrading from R12.0 to R12.2 and read MOS note 1349240.1 for more preparation!
Great tips like turning off CNAME before the upgrade, and then turning them back on might just save the day for you later.
MOS ID 1531121.1 - Online Patching Enablement
MOS ID 1355068.1 - Patching Technology Components
The patch for R12.2.3 is 17020683!
You can check your JRE information with MOS ID 455492.1
A good reminder that you have to have a second file system for your patch edition so prepare for a lot more space needed!
Another great tip was Mike cleaned up tax codes to prevent patching on 93 million records in Order
Entry, and there were also errors in HR and GL as well.
1594274.1 is the main list of bug fixes for R12.2 and later we saw the patch for R12.2.3 was marked as superseded by R12.2.4 which is surprising as there has been no announcement of the release!

11:30 AM - John Peters ( - R12.2 Development and Deployment of Customizations

John reminds us how there is lots of extra storage required in terms of DB/OS because of data changes/OS file changes.
I also learned a bit more about using great graphics in the presentation, as there were several really awesome informational graphics!
How do you set your edition at the OS?  Run "source /oracle/ebs122/EBSapps.env run" or "source /oracle/ebs122/EBSapps.env patch".
The biggest thing I took away from this was table names have to be 29 characters or less, while column names have to be 28 characters or less.  Why was this the big take away?  This is something we can be working on for the next few years to prep for our R12.2 before we need to deploy it!
MOS ID 1577661.1 - Customizations in R12.2

3:30 PM - Elke Phelps (Oracle) - Oracle E-Business Suite Upgrade Best Practices: Technical Insight

Elke presented a LOT of information, and even though most of it is hosted by My Oracle Support, do not let that fool you into thinking it is not worth reading more about it!

MOS ID 1638535.1 - RPCs
MOS ID 1506669.1 - R12.2.2 readme
MOS ID 1586214.1 - R12.2.3 readme
MOS ID 1583092.1 - AD/TXK deltas
MOS ID 1290886.1 - Data Model Comparison
Purge Portal in SysAdmin responsibility!
MOS ID 1583752.1 - R12 Upgrade Performance issues

5:00 PM - Mike Brown ( - Getting Ready for 12.2: Edition-Based Redefinition

One interesting item Mike shared was edition changes do not change for current connections, and that is why you have an apps bounce at the end of the upgrade process!
While this might seem like a repeat of John Peters' presentation earlier, Mike did a real world demo which showed us what happens when editions are changed and how it affects the connections.
R12.2.3 is better than beta code (12.2.0)
select * from dba_editions
select property_value from database_properties where property_name = 'DEFAULT_EDITION'
select sys_context('Userenv', 'Current_Edition_Name') from dual
First time I believe I have ever seen the error "ORA-38810: Implementation restriction: cannot drop edition that has a parent".

6:30 PM - George Somogyi (@georgesomogyi (maybe?)) and Rey Mendez - E-Business Suite Big Data Purge - An Approach to Archive and Purge Financial Accounting Hub Data

This presentation was the one I was MOST looking forward to today, but honestly while I did get quite a bit from it, there was not a big reveal of HOW it was done which disappointed me.  I wanted to get more from George as he was the one that was with the company that needed the purge to happen, and Rey was in the outside consultant/PM type role.
There were several architecture images which makes me think about how I can present some things down my road, so that was a great takeaway.
Having 20 million records created in the Financial Accounting Hub (FAH) every day, there was a lot of data they needed to take care of after several years of running so it was another great example of what we need to prepare for in our own system!
Again, there were a lot of takeaways for me from a presentation format going forward especially when George and Rey were talking about data.
The size of FAH represented 85% of their 8 TB database!
There was a FAH interface table GL_XLT shared which had a lot of their data in it.
What I thought odd was they were not aware of any Oracle purge processes for FAH, so I need to research some documentation on the XLA tables and understand why there might not such utilities out there already because it was also odd that Rey said there were not APIs followed to do the actual purging.

Sunday, July 20, 2014

OAUG Connection Point - AppsTech 2014: Last Chance!

I have talked about how great of a deal the E-Learning format for the OAUG Connection Point - AppsTech 2014 is, and I have told you that I am presenting for them (now on Tuesday morning), but this is pretty much your last chance to register for the conference.  Tomorrow at 7 AM PDT/10 AM EDT the first sessions kick off and I really cannot wait to hear some of the presentations!  I hope to "see" you tomorrow, and hear some of your questions during my session's Q&A on Tuesday!

Saturday, July 19, 2014

Weekend Learning: Exadata recommended IORM patches

I have mentioned favoriting articles on My Oracle Support a time, or two, or even three and today is yet another blog post in that same mindset.  If you are on an Exadata platform, you should add the article "Recommended Patches for Exadata I/O Resource Manager" document ID 1340181.1 in your MOS favorites so you can stay up to date with critical patches for the Exadata IORM.  Plus, as usual with these articles on MOS you can find a link to the master note for IROM which contains bug fixes, monitoring scripts (yay alerts!) and a bevy of other article links for Exadata minded individuals!

Still hungry for more on IORM?  Visit my post from last year where Carlos Sierra clued us in on a great blog post from a colleague at Enkitec about using the Oracle monitoring scripts to detect problems with Exadata IO latency.

Friday, July 18, 2014

Weekend Learning: Emergency ASH flush

Today we had to wrangle a database that was having a problem, and going through the log for the instance I found this gem:

Active Session History (ASH) performed an emergency flush. This may mean that ASH is undersized. If emergency flushes are a recurring issue, you may consider increasing ASH size by setting the value of _ASH_SIZE to a sufficiently large value. Currently, ASH size is (setting) bytes. Both ASH size and the total number of emergency flushes since instance startup can be monitored by running the following query:
 select total_size,awr_flush_emergency_count from v$ash_info;

While I knew that ASH did get flushed, I did not know about an emergency flush for ASH!  Maybe that is the parameter that Tim Gorman shared during the Hotsos Symposium this year.  So not only do we know this can pop into the database log but we are given the exact table so we can build an automated alert to tell us when ASH is flushed and how many emergencies have happened.

Thursday, July 17, 2014

General project lesson: Testing

Following up on the technical post from yesterday I wanted to focus on another general project lesson, this time surrounding the testing phase of the project.  Obviously, testing suites are critical for the QA team to replicate what the users do but just as important as what they are doing is how they are doing it!  What do I mean here?

When we were researching the problem from yesterday key questions asked were: what is the user impact, and how do we replicate this?  At the time, we were not able to identify any user impact so it was really impossible for us to replicate this on command.  Until a week later.  Why is that?  The delay allowed users to encounter the problem timing out forms in the EBS application, because the key to this was letting the sessions time out.  What did we miss in testing?

We knew the steps involved in testing what the users needed to do, but we failed to record how they did it during the day by letting the EBS application sessions rest while they did other tasks.  Sure it was correct to go through the steps one by one in order, without pausing, but the lack of timing information caused us to miss this scenario and the problem that could have been found in testing.

Wednesday, July 16, 2014

Why the wait event "SQL*Net message from dblink" can suddenly increase for no reason

Following yesterday's article about making assumptions post project implementation, are the technical details to what I was talking about!

We launched our 12c upgrade to a major Oracle database in our infrastructure and being one of the first in the company to do so made us (okay, probably just me) very cautious (or leery) about what bugs would shake out of the system after we put it in.  Over the weekend we did the upgrade by installing 12c on new hardware, made sure GoldenGate was up to date before we broke the link between systems, and then linking to the new system.  After the logs were synchronized and everything was tested, not a creature was stirring, not even a mouse.

The next day, we still had no obvious issues but our system wait event alert told me that the activity for the wait event "SQL*Net message from dblink" was starting to trend upwards from where it normally is during the day.  By midday, the activity was measured to be 10-15 TIMES what it was Friday before the deployment and not only that but "TCP Socket (KGAS)" activity was trending higher on the new 12c database.  Of course, 12c is the culprit here, and we have to find out what bug we are encountering right?  Not so fast.  We need to get evidence and prove that out!

First off, is what we were seeing in the database log every few seconds on the new 12c database connecting back out to our main database:

Fatal NI connect error 12170.

        TNS for Linux: Version - Production
        Oracle Bequeath NT Protocol Adapter for Linux: Version - Production
        TCP/IP NT Protocol Adapter for Linux: Version - Production
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12535

TNS-12535: TNS:operation timed out
    ns secondary err code: 12560
    nt main err code: 505

TNS-00505: Operation timed out
    nt secondary err code: 110
    nt OS err code: 0

This leads us to My Oracle Support article "ORA-0600 [17099]" in document ID 1325533.1 but why is nobody noticing a problem here that is being reported if "things are not right" to such a degree?  I'm glad you asked, because while I started researching this I noticed that my SQL client tool was hanging for up to 15 minutes before the error "ORA-03113: end-of-file on communication channel" was returned.  That is not right at all.

So, what kind of errors are we seeing in our main database log?  I am glad you asked!

Fatal NI connect error 12170.

        TNS for Linux: Version - Production
        Oracle Bequeath NT Protocol Adapter for Linux: Version - Production
        TCP/IP NT Protocol Adapter for Linux: Version - Production
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12535

TNS-12535: TNS:operation timed out
    ns secondary err code: 12560
    nt main err code: 505

TNS-00505: Operation timed out
    nt secondary err code: 110
    nt OS err code: 0
  Client address:
Error 3135 trapped in 2PC on transaction 3283.9.55580. Cleaning up.
Error stack returned to user:
ORA-03135: connection lost contact
ORA-02063: preceding line from 12C_DB

Seeing that most of the error messages are the same between both databases made me do more research on My Oracle Support!  What I found was "Alert Log Errors: 12170 TNS-12535/TNS-00505: Operation Timed Out" in document ID 1628949.1 and again the article was extensive.  Not only in depth, but it had another hint to what the problem was which was found in the first MOS article I shared here.

What is the problem?  The SQLNET.EXPIRE_TIME parameter was not set in the $TNS_ADMIN/sqlnet.ora file on the new 12c platform, the way it was in the original database which we "cloned" over.  After setting this on both RAC nodes and reloading the listeners, the issue went away along with all of the error messages in the log file!

We never had a problem with 12c, even though it was easy to blame it outright at the start!