[Oracle] A short SQL*Net research or how-to drill down network related ORA errors
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 18.104.22.168 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.
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.
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.
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.