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!

Tuesday, July 15, 2014

Sherlock Holmes, project implementations and leaps of logic

In what possible way can I tie Sherlock Holmes and projects together in talking about leaps of logic?  If you are familiar with the famous detective you may associate him with deductive reasoning, which has us building on top of things we believe are true to reach a conclusion but just as often he would practice abductive reasoning that starts with an observation, to our conclusion, and then we try to explain it.  Why talk about this?  Simply put, when you have a implementation project it is easy to use either path of reason to believe that the object of your project is the cause of the problem you encounter but it is entirely possible for that not to be the case.

I will go into the technical details tomorrow about a situation we just had, but in a broad sense think about this scenario:

You install new servers, install a new version of Oracle DB, synchronize all your data, and turn it on but there is a problem.

What is the cause of your problem?  Did you say the new servers?  Did you say it was the new version of the database?  Was your guess that we had a data issue?  Would you be surprised if I told you that it was really none of the above?

It is easy to jump to conclusions when we are in the moment of a project, but we need to keep our scientific focus and prove out hypothesises with facts.

Monday, July 14, 2014

R12.1.3: Rejected PO mistakenly documents approval

Obviously the title catches your eye just the way it did mine as I thought I did not read it right the first time I saw it!  The content of My Oracle Support document "Purchasing Document was Rejected by the PO Approver, but it is found that the Status is Approved in the Action History" in note ID 1684684.1 actually is exactly what it says as rejected POs are instead documenting approval in the history lines due to a known bug.  Are you affected by this?  Repeat the steps as Oracle has shared them, and make sure you have already applied the bug fix to prevent data issues!

Sunday, July 13, 2014

What is the point of Incremental Global Statistics?

Oracle 11g introduced the feature Incremental Global Statistics, but Doug Burns answers the burning question of why this new feature actually takes LONGER when you gather your statistics initially.  Interested in more about this subject?  You are in luck as he has a second post with some more information and a third post with a lot of links to other sites and people on the subject!

Saturday, July 12, 2014

How the NTP service can be your weakest link

If you are not familiar with the Network Time Protocol and the services related to supporting it, do not feel bad; I did not know either until we had a problem with it!  Why could this be your weakest link?  I am glad you asked!  Recently we had our Exadata DBs start reporting different server times because it seemed that they were getting the server time from their switches but the switches were not correctly getting their time from our network time server.  That is obviously a problem, but imagine my surprise when I tried to log in and received "Permission denied, please try again" when trying to SSH into our server.  Why does this relate to NTP?  Well, it seems that if the time drifts too much on a server the SSH login attempt is rejected!

Why am I making a big deal out of this?  What happens when you are alerted that your CPU is starting to spike and you want to log in to take a look, but are rejected?  Do you have time to waste on getting a system administrator to restart the ntpd service so you can get into your machine?  I doubt it!  Now, it is on to making an alert to check our system time against the network time server!

Friday, July 11, 2014

Weekend Learning: My Oracle Support Accreditation

Do you work with Oracle directly through the My Oracle Support site?  Do you have a few minutes free this weekend to learn something, and prove that you have what it takes to work with Oracle?  If you have answered yes to these questions, then you should go to MOS and look up these articles to start getting accredited:

My Oracle Support Accreditation Series - Level 1 My Oracle Support - 1579751.1
My Oracle Support Accreditation - Frequently Asked Questions (FAQ) - 1585906.1
My Oracle Support Accreditation Series - E-Business Suite - 1580100.1

Yes, there are more series besides the EBS application so go check out the other series available!

Thursday, July 10, 2014


I found out today that not only do I have GV$SQLTEXT, but another tool in the toolbox is DBA_HIST_SQLTEXT which will give me some historical data according to I believe our retention options and since SGA or PGA have been cleared.  Speaking of SGA and PGA, did you know there are some DBA_HIST tables related to them as well?

Do yourself a favor, and query your ALL_OBJECTS table for DBA_HIST objects to learn more things that can be put in the toolbox for later!

Wednesday, July 9, 2014

Presentation preparation

Today my post will be not so technical in nature, but I feel it is just as important!  As I have mentioned a few times, I am presenting for OAUG on July 21st and to brush up a bit on my skills I visited Tim Hall's series of posts on presenting today!  I shared something about this before, and I have spotlighted several of Tim's 12c posts before, but I wanted to spotlight this series directly after having a chance to use them to refresh myself.  The best part about these posts by Tim?  You do not need to have a presentation lined up already as several of them are tips about how to get involved or perfect your pitch to get selected!