Skip to Content

Introduction

In the last weeks i was involved in a SAP migration project for troubleshooting Oracle database related issues when my client noticed multiple “ORA-12571: TNS:packet writer failure” errors. This specific error occurred only on high network load like running R3load in a migration scenario or SGEN for the whole ABAP source code afterwards. Luckily this was not a sporadic issue as it occurs every time at high network load and so it could be reproduced easily.

The R3load error looks like this (trimmed the R3load output to the most important lines):

(RTF) ########## WARNING ###########
        Without ORDER BY PRIMARY KEY the exported data may be unusable for some databases
ORADPL:2012.12.20 11:52:36 (ret=-1) ORA-12571: TNS:packet writer failure
ORADPL:2012.12.20 11:52:36 fatal error in line 4357, (status=12571) error loading stream
(IMP) ERROR: ExeFastload: rc = 2
(DB) INFO: disconnected from DB

SAP already provides sapnote #534729 with general explanations for such issues, but unfortunately it includes no description or further information how-to troubleshoot such network related ORA errors in SQL*Net beside using niping which is mostly useless in such cases. This blog keeps it focus on unix based operation systems like AIX or Linux and the database version 11g R2.

Enable the trace

As previously mentioned we were running on Oracle 11.2.0.3 with ADR enabled (by default). Please check the references (Diagnostic Parameters in sqlnet.ora), if you want to get more information about ADR and the SQL*Net tracing possibilities (for older Oracle database releases).

We need to set the following parameters in sqlnet.ora (usually located in /sapmnt/<SID>/profile/oracle for newer SAP releases or specified by environment variable TNS_ADMIN) to enable the SQL*Net trace on client side.

ADR_BASE = <FOLDER_FOR_TRACE_FILES>
TRACE_LEVEL_CLIENT = SUPPORT

Please be aware, that these trace files can become very large in a short time – so be sure that you have enough space in the corresponding file system. Each oracle database connection will create a separate trace file from now.

Reproduce and analyze

After we have enabled the SQL*Net trace it is time to reproduce the specific ORA error and check the corresponding trace file. In our case we ran another R3load run and waited for the “ORA-12571: TNS:packet writer failure” issue.

The first section of the trace file contains a summary about the trace options and client settings.

Meta_Trace.png

Afterwards the usual data stream (like an INSERT statement with all of its data content) is traced until the specific network error occurs and the SQL*Net stream is interrupted and aborted. I omit the whole SQL*Net data stream and jump to the error itself right now.

Network_Issue_Trace.png

The INSERT data stream aborted right before line “ntt2err”. The following trace information points us to the real issue. You find the “generic” network related ORA-12571, but you also see the root cause of this error (line “nioqper: nt (2) err code: 70”). I have marked the underlying error code of the operating system in blue (in our case 70).

Now let’s check the operating system error definition for that code (in my case the system was running on AIX, but it works that way on Linux as well).

shell> cat /usr/include/errno.h | grep 70
#define ENETUNREACH 70 /* Network is unreachable */

Now we exactly know why an ORA-12571 was raised, because of the network itself was unreachable. Starting from here we could go on with checking the operating system logs for network issues (like a temporary link down). In my particular case the network traffic was routed on the loopback interface (localhost) so searching the bug database of the operating system vendor for TCP/IP stack issues or running tcpdump (and checking the output with Wireshark) would be a better way here.

Summary

Enabling the SQL*Net trace and checking for the root (operating system) cause is the proper way, if you can not find any obvious network outtakes / errors in your environment when an network related ORA error is raised.

If you have any further questions – please feel free to ask or get in contact directly, if you need assistance by troubleshooting Oracle database issues.

References

To report this post you need to login first.

4 Comments

You must be Logged on to comment or reply to a post.

  1. Mark Fรถrster

    Hello Stefan,

    thanks for shaing that information. Summarizing how to make use of the trace file information is greatly appreciated. From all Oracle error messages the TNS errors are the worst, from my point of view. Some of them can have a really wide variety of root causes.

    Regards,

    Mark

    (0) 
  2. Jamie Neilan

    Agreed, an excellent blog. As a Basis specialist I have spent many an hour delving into ORACLE issues during migrations/upgrades and it is a fine art to understand all the best tools to use. Great to see an article that is both concise and well illustrated.

    (0) 
  3. Lars Breddemann

    Hi Stefan,

    I haven’t spent too much time reading non-HANA DB stuff lately and I seem to have missed out!

    Well done – not just this blog post – but basically all of yours I stumble over here in SCN.

    With active authors like yourself, there’s no reason to worry about the Oracle community ๐Ÿ™‚ .

    Keep up the good work!

    Cheers, Lars

    (0) 
    1. Stefan Koehler Post author

      Hi Lars,

      thanks for your feedback. “Somebody needs to do the dirty work” as everybody is thrilled by HANA ๐Ÿ˜‰ . Luckily not all exceptional SAP employees (like you) are moving to HANA area .. *beckon to Martin Frauendorfer*

      See you in the HANA area ๐Ÿ™‚

      Regards

      Stefan

      (0) 

Leave a Reply