R12.1.3 Payables: Invoice Validation program has performance issue

For the article "R12: AP: Invoice Validation program (APPRVL): Performance Issue - Insufficient Parameters" on My Oracle Support via document ID 2138625.1 you need to read up on the attached bug as well since it appears this dynamic query is actually being designed to run for an excessive amount of time if you do not provide an invoice date range.  That means that just entering an Operating Unit parameter does not actually limit the query as much as you would think, and Oracle Development has closed the bug as "not a bug" even though it really sounds like a bug!

Weekend Learning: Data Pump and Oracle DB parameters to increase Data Pump performance

Tonight My Oracle Support article 1611373.1 "What DataPump And Oracle RDBMS Parameters Can Significantly Affect DataPump Performance ?" is a very straightforward listing of parameters which could teach you how to speed up Data Pump but also it can teach you about the Oracle DB as well by introducing you to some of these parameters maybe for the first time!

Hyperion Planning: Managed Server produces core dump after crash

Tonight check out document "Planning Managed Server Crashes and Creates Core Dump." from My Oracle Support document ID 2002962.1 to see what parameter you need to change in which .sh file in order to have this stop happening!

Weekend Learning: Investigating correct setting of SESSION_CACHED_CURSORS and OPEN_CURSORS parameters

Take a few moments this weekend to visit document ID 208857.1 in My Oracle Support to brush up on this "SCRIPT - to Set the 'SESSION_CACHED_CURSORS' and 'OPEN_CURSORS' Parameters Based on Usage" so that you can help make your databases a bit more proactive by monitoring this on a regular basis!

Cursor sharing and RMAN errors on 11.2.0.2

Tonight I learned that if you have the gv$parameter cursor_sharing set to FORCE or SIMILAR on an 11.2.0.2 platform RMAN may spit out some odd ORA-01008 or ORA-07445 error messages.  My Oracle Support article "ORA-01008: RMAN backup fails with Assorted Errors on 11.2.0.2 if cursor sharing is enabled" via note ID 1472116.1 has all the details including the patch you need to apply in order to avoid "resolving" this by flushing the shared pool all the time.

Magical Mystery Event Tour: Part 2

Continuing the outlook for wait events which are out of place comes a post about the mystery behind the "GV$: slave acquisition retry wait time" wait event I have encountered on 12c.  My MOS research found two articles which are on point, but neither of which makes a whole lot of sense:

WAITEVENT: "GV$: slave acquisition retry wait" - Note ID 371117.1

This document talks about a list of instances, and thinking there is a configuration change in process until it no longer thinks the configuration change is going on.  This was something I could not understand because nothing seemed to be reconfiguring, and the document did not point me to a specific thing I could look at to see if it was reconfiguring.  After a few weeks of this being very low on our wait event alert, it eventually went away.

Wait Event GV$ Slave Acquisition High With CURSOR_SHARING To FORCE - Note ID 1589592.1

This article was referring to the parameter (in gv$parameter) cursor_sharing being set to FORCE, which we did not have it set to, and leading to a buffer value needing to be changed.  There was no changing the value before it went away, and we even had the value higher than the recommendation!

Since it went away, and we could not narrow down what was going on, I am sure I will see it again in the future and when I nail this one down there will be an update here.  :}

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.

  VERSION INFORMATION:
        TNS for Linux: Version 12.1.0.1.0 - Production
        Oracle Bequeath NT Protocol Adapter for Linux: Version 12.1.0.1.0 - Production
        TCP/IP NT Protocol Adapter for Linux: Version 12.1.0.1.0 - 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.

  VERSION INFORMATION:
        TNS for Linux: Version 11.2.0.3.0 - Production
        Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production
        TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.3.0 - 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!