Tuesday, June 11, 2013

Oracle Error Messages You Don't See Everyday:  ORA-03111

I've been working with Oracle software for 25 years now.  It is still amazing to me, that after all this time, I will run into error messages which are not new, but are new to me.  Once example of this is my recent exposure to an ORA-03111.  Anecdotal evidence from the web indicates this may be a pre-Oracle8 error.   

Related to the network interface layer of Oracle (SQL*Net), the short explanation for this error from Oracle makes it seem even more obscure:

[oracle@MSSNKAODB015 ~]$ oerr ora 03111
03111, 00000, "break received on communication channel"
// *Cause:
// *Action:

In layman's terms, anytime there is interference in communications in a three-tiered transaction, bad things usually result.  In this case, a Java application was passing a larger than average packet size from a unix client to the database server.  Sometimes this can be mitigated with changes to the client configuration (see http://royontechnology.blogspot.com/2009/06/mysterious-ora-03111-error.html ), other times it can be handled with a database SQL*Net Configuration change, or a client SQL*Net configuration change.  The database software configuration changes are discussed in detail in Oracle Support note ID 44694.1.  In summary these are changes to sqlnet.ora on the server's listener.ora configuration file, as follows:

SID_LIST_LISTENER =
(SID_LIST =
    (SID_DESC =
        (SDU = 8192)         <- 8192="" br="" for="" sdu="" setting="" sid="" this="" to="">         (TDU = 8192)         <- br="" nbsp="" position="">         (SID_NAME = V112)
        (ORACLE_HOME = /oracle/product/11.2.0)))

Or the client sqlnet.ora configuration file:

TEST = 
(DESCRIPTION =
(SDU=8192)
(TDU=8192)
(ADDRESS=(PROTOCOL=TCP)(HOST=bill.johndoe.com)(PORT=1521))
(CONNECT_DATA=(SID=V112)))


Ideally, an application will be analyzed and possibly modified so that it does not exceed network standards and default values for these parameters.  In this case, the application was third party, making doing so quite difficult, so a change to the client sqlnet.ora did the trick.