Benutzer-Werkzeuge

Webseiten-Werkzeuge


dba:sqlnet_trace

Übersicht Oracle SQL*Net Probleme

Angelegt am 2014/05/02

Grundlagen SQL*Net

Um sich an die Oracle Datenbank anzumelden haben Sie zwei Optionen:

SQL*Plus oder ähnliches auf den gleichen Rechner wie die Datenbank

Ist SQL*Plus auf den gleichen Rechner wie die Datenbank installiert, kann auf die laufende Instance ( den Server Prozess der Datenbank) OHNE das SQL*Net Protokoll, d.h. ohne Netzwerk auf die Datenbank zugreifen werden (Aufruf „sqlplus username/password“).

Dazu muss in der Umgebung die Variable ORACLE_SID=<SID DER DB> (SID ist so etwas wie der Name der Datenbank) gesetzt werden, dann können Sie sich mit Name/Passwort anmelden.

Über die Umgebungsvariable ORACLE_SID weiß dann das SQL*Plus (oder ähnliche auf C/C++ basierende Programme) wo es hin muss und sucht im Speicher der lokalen Maschine nach der DB.

Ist die ORACLE_SID nicht oder aber falsch gesetzt, bekommen Sie auch die Fehlermeldung „ORA-12560 TNS protocol adapter error“.

SQL*Plus baut die Verbindung über das Netzwerk zur Datenbank auf

Damit sich ein Client, wie SQL*Plus oder ein Java Programm, über das Netzwerk an der Datenbank anmelden kann, läuft auf der Datenbank Maschine ein sogenannter Listener Prozess, meist auf dem Port 1521, bei SAP oft gerne aber auch ein andere Port wie 1527 / 1522.

Dieser Listener Prozess registriert die Anfrage des Client an eine Datenbank und startet auf der Datenbank Maschine einen Prozess ( den Server Prozess), der mit der Datenbank „redet“ und mit dem Client kommuniziert.

Dieser Prozess führt auch das eigentliche SQL durch. Der Listener wird nur am Anfang benötigt, danach spielt der Listener in der aufgebauten Kommunikation keine Rolle.

Wie findet nun aber der Client zur Datenbank?

Dazu wird zum Beispiel SQL*Plus mit dem Usernamen/Passwort@DatenbankAlias aufgerufen. ( „sqlplus username/password@tnsalias“ )

Der tnsalias oder DatenbankAlias beschreibt dem SQL*Net Protokoll, wie die Verbindung aufgebaut werden soll, vor allen wohin sich der Client verbinden soll und an welche Datenbank.

Diesen DatenbankAlias muss man sich wie einen Namensauflösung vorstellen.

Wie der Name aufgelöst werden soll, sagen dem SQL*Net Protokoll die beiden Dateien sqlnet.ora und tnsnames.ora.

Steht in der sqlnet.ora, dass für die Namensauflösung die Datei tnsnames.ora verwendet werden soll (Parameter: NAMES.DIRECTORY_PATH= (TNSNAMES) ) wird die Datei tnsnames.ora ausgewertet und dort nach dem DatenbankAlias gesucht.

Dieser Alias muss nicht wie die DB heißen!

#Test

Ein Testen erfolgt mit dem Programm „tnsping >DatenbankAlias>“, hier in der Ausgabe auf die Pfadangabe zur sqlnet.ora achten (ein Art ping für das sql*net Protokoll).

Alternativ wenn kein TNSPing zur Verfügung steht:

#youDontNeedTNSping
z6="\0\0\0\0\0\0";exec 3<>/dev/tcp/localhost/1521;echo -ne "\0\x67\0\0\x01\0\0\0\x01\x3e\x01\x2c\0\0\x20\0\xff\xff\x7f\x08\0\0\x01\0\0\x1d\0\x4a$z6\x9c\xd0$z6$z6$z6$z6\x20\0\0\x20$z6\0\0\0\0(CONNECT_DATA=(COMMAND=ping))" >&3; echo $(strings <&3 3>&-)

Quelle siehe ⇒ https://twitter.com/FranckPachot/status/1144617674099568640?s=19

Fallstricke:

#1

Ist die Umgebungsvariable LOCAL in der lokalen Umgebung gesetzt, dann wird als DatenbankAlias der Wert von LOCAL verwendet und eine Anmeldung ist auch ohne Angabe des TNSAlias mit „sqlplus username/password“ möglich, scheinbar ohne den TNSAlias.

#2

Es wird die falsche tnsnames.ora Datei von SQL*Plus verwendet oder erst gar nicht diese Datei gefunden (passiert gerne wenn mehrere Oracle Installation auf den Rechner existieren).

Lösung:

Global auf dem System die Umgebungsvariable TNS_ADMIN setzen und auf das richtige Verzeichnis für die sqlnet.ora/tnsname.ora zeigen lassen.

Dies hilft sehr oft bei unverständlichen Verbindungsproblemen mit OLEDB oder ODBC.

Es wird verhindert, dass sich hier, je nach angemeldeten User oder Job Umgebung (System Prozesse!), die lokalen Pfade anders interpretiert werden und die Steuerdateien dann nicht gefunden werden!

Dies ist auch die Lösung für das Problem, das die die tnsalias namen nicht automatisch im ODBC oder OLEDB Connection Dialog angezeigt werden!

Für 64bit System die einen 32bit treiber benötigen siehe Installation eines 32 Bit ODBC Treibers unter Windows 7 / 2008 64bit

#3 FW

Für die Verbindung muss zu Beginn der Listener Port, meist 1521, auf der FW offen sein, und die FW muss so clever sein zu erkennen, dass der SQL*Plus Client mit dem Serverprozess später dann über einen höheren Port kommunizieren.

Der Client „redet“ allerdings nicht dauernd mit dem Serverprozess auf der DB, ist auf der Leitung mal mehr als ca. 1 h Funkstille denkt die Firewall, die Verbindung existiert nicht mehr, die nächste Abfrage wird dann abgeblockt und es gibt einen SQLNet ORA-125 Fehler auf dem Client.

Lösung:

SQLNET.EXPIRE_TIME (in der SQL*Net des Listener der DB!) auf einen Wert unterhalb des eingestellten Werte in der FW für einen Package Drop stellen

Falls der FW Hersteller keinen SQL*Net Proxy zur Verfügung stellen will/kann:

Die gnadenlose Lösung wäre es alle 10 Minuten mit einem „select * from dual“ die Verbindung künstlich am Leben zu halten. ( in der gleichen Session!).

Ansonsten wird es nur helfen, die Programm / Job Logik so zu ändern, dass für jeden Abfrageblock eine neue Session zur DB aufgebaut wird.


Audit Log "LOGOFF BY CLEANUP" Events auswerten

Ist das Auditing auf Sessions eingeschaltet (audit SESSION) , kann auch über das DB Audit Log geprüft werden, ob Session „massiv“ häufig abbrechen.

Immer wenn eine Client Session sich nicht ordentlich beim Server Prozesse abgemeldet hat, erkennt der PMON solche „verlorenen“ Session und beendet diese dann automatisch nach einigen Sekunden.

Im Audit Log taucht dann der Event „LOGOFF BY CLEANUP“ auf.

Auswerten:

  SELECT                                                                               
         to_char (extended_timestamp, 'dd.mm hh24:mi:ss') AS TIMESTAMP
       , instance_number     
       , username
       , action_name
       , userhost
       , CLIENT_ID
    FROM dba_audit_trail
   WHERE     extended_timestamp BETWEEN   sysdate - (  1 / 4) AND sysdate
         AND action_name LIKE 'LOGOFF BY CLEANUP'
ORDER BY extended_timestamp
/

Mit der zeitlichen Information kann dann in weiteren Logs gesucht werden, ob zu diesem Zeitpunkten etwas besonderes im System passiert ist.

siehe auch Node Doc ID 274697.1.

Alternativ über die DBA_AUDIT_SESSION:

SELECT  COUNT(action_name)
      , username
      , action_name 
      , to_char(TIMESTAMP,'hh24') AS stunde 
      , userhost
  FROM DBA_AUDIT_SESSION
   WHERE TIMESTAMP > sysdate - (1/12*3)
GROUP BY   username,action_name,to_char(TIMESTAMP,'hh24') ,userhost
ORDER BY to_number( to_char(TIMESTAMP,'hh24')) DESC,1 DESC
;


Fehler Suche

Immer von unten nach oben den Fehler eingrenzen!

Also zuerst das Netzwerk prüfen! Namensauflösung/IP Adresse / PORT (Firewall beachten!)

Ablauf bei der Fehlersuche Netzwerk:

Auf betroffenen Client aus:

  • Kann der Name des DB Servers zur richtigen IP Adresse aufgelößt werden?
    nslookup mydb_server
  • Kann die IP Adresse mit einem Ping erreicht werden?
     ping mydb_server
  • Kann der Port des Listner der Ziel DB per Telnet erreicht werden
    # Windows Powershell
    Test-NetConnection -ComputerName 10.10.10.1 -Port 1521 -InformationLevel "Detailed"
    #Linux bzw. falls unter Windows telnet zur Verfügung steht
    telnet mydb_server 1521 

Treten Fehler nur sporadisch auf, auf dem Netzwerkadapter vom Client und der DB testen ob es TCP Fehler gegeben hat.

Windows:

netsh interface ipv4 show ipstats
 
get-NetAdapterStatistics
 
Get-NetAdapter
 
# hier die Nummer des Ethernet Adapters suchen und in folgenden Befehl korrekt statt der 9 setzen
Get-NetAdapter -ifIndex 9 | Get-NetAdapterStatistics | format-list *
 
 
# Einstellungen
netsh int ipv4 show dynamicport tcp

⇒ siehe auch für Details https://devblogs.microsoft.com/scripting/gathering-network-statistics-with-powershell/

Linux:

ifconfig
#alternativ
ip -s link
 
 
..
 RX packets
 TX packets
..
 
 
# var log  messages auf grobe Fehler
grep NIC /var/log/messages
 
 
 
#display a table of all network interface including recived and send packets
 
netstat -i
 
# Display Summary Statistics For Each Protocol
 
netstat -s
 
#Display Quick Stats
 
ss -s
 
 
#falls verfügbar
 
ethtool 
 
# mit sar laufend die statistik anzeigen
# sar -n DEV
 
sar -n DEV 1 3

siehe auch:

VMWare Probleme

In einer VMWare Umgebung auch die Physikalischen Adapter auf der Host Maschine kontrollieren!

Z.B. mit „esxtop“ Feld „drpx“

Siehe auch hier ⇒ https://vswitchzero.com/2017/09/26/vmxnet3-rx-ring-buffer-exhaustion-and-packet-loss/ und als Lösung auch ⇒ https://kb.vmware.com/s/article/1010071

Ping Verhalten

Prüfen wie sich die Adapter bei Ping mit großen Paketen verhalten:

ping 10.10.10.1 -t -l  65500

Hier dann wieder auf den Adaptern prüfen ob Fehler auftauchen.

SQL*Net Ebene

Wenn hier alle Fehler ausgeschlossen sind, kann weiter auf SQL*Net Ebene gesucht werden.

  • Auf welchen Wert steht die Umgebungsvariable TNS_ADMIN?
    echo $ENV:TNS_ADMIN 
  • Wird die richtige sqlnet.ora verwendet? Welche Werte stehen in der sqlnet.ora?
    tnsping gpidb
    ..
    Used parameter files:
    c:\oracle\TNS_ADMIN\sqlnet.ora
    ...

    Genauestens auf die Angabe der verwendeten sqlnet.ora achten!

Kann nun prinzipell eine Verbindung zur DB Aufgebaut werden und bricht diese dann gelegentlich/regelmäßig ab muss der SQL*Net Stack im Detail mit einem Trace beobachtet werden.


Tnsping Trace

Im ersten Schritt ein Trace mit Tnsping über den generellen Verbindungsaufbau bis zum Listener ohne eigentliche DB Connection.

Parameter in der sqlnet.ora:

TNSPING.TRACE_LEVEL=SUPPORT
TNSPING.TRACE_DIRECTORY= c:\temp

Diese Einstellung kann eingeschaltet beleiben, da nur ein Log bei TNSPing erzeugt wird und diese recht klein ist.


SQL Net Trace anlegen

Können keine Standard Fehler erkannt werden, hilft oft nur noch das Tracing von SQL*Net zu aktivieren.

Zu vor muss aber geklärt werden ob sich der Client mit JDBC (Java Client ) oder mit OCI ( C bzw. .Net oder ähnlich, ODBC etc. ) an der DB anmeldet.

Der klassische SQL*Net Trace funktioniert nur mit dem OCI Stack von SQL*Net, d.h. in der Regel nicht mit dem JDBC Treiber!

Ablauf:

  • Mit tnsping testen, wo die aktuelle sqlnet.ora liegt
  • sqlnet.ora editieren
  • Testen
  • Wieder ausschalten

SQL*Net Trace Client

Parameter in der sqlnet.ora setzen und damit SQL*NET Trace einschalten

TRACE_LEVEL_CLIENT = SUPPORT
TRACE_UNIQUE_CLIENT = on
TRACE_DIRECTORY_CLIENT = c:\temp
TRACE_FILE_CLIENT = sqlnet_
TRACE_TIMESTAMP_CLIENT=ON

#
# AB 11g werden die Trace Dateien in das DIAG Verzeichnis geschrieben
# Windows Default C:\Documents and Settings\<username>\Oracle
#

ADR_BASE = D:\temp

Mögliche Werte:

  • off oder 0 - for no trace output
  • user oder 4 - for user trace information
  • admin oder 10 - for administration trace information
  • support poer 16 - for Oracle Support Services trace information

Unter der DB Version 11g lassen sich die traces hier finden: $ORACLE_BASE/diag/clients/user_oracle/host_$NUMBER/trace

Nicht vergessen wieder auszuschalten!!

Oracle JDBC Client SQL*Net tracen

Vor 11g R2:

  • How to Perform the Equivalent of SQL*Net Client Tracing with Oracle JDBC Thin Driver (Doc ID 793415.1)

ab 11g R2

  • How to Trace the Network Packets Exchanged Between JDBC and the RDBMS (Doc ID 1050942.1)

Voraussetzungen ! Debug JDBC Treiber Library im Einsatz!


SQL*Net Trace Server

Hilft auch der Trace des Clients nicht mehr weiter kann auch auf dem Server ein Trace aktiviert werden:

TRACE_LEVEL_SERVER=16
TRACE_FILE_SERVER=srv_.trc
TRACE_DIRECTORY_SERVER=/tmp
TRACE_UNIQUE_SERVER=TRUE
 
# one way TO find the trace files easyer:
DIAG_ADR_ENABLED=OFF

! Nun werden Dateien in beträchtlicher Größe und Anzahl erzeugt! Diese Einstellungen können schnell dazu führen das die Platten des DB Servers überlaufen!


SQL*Net Trace auswerten

Aber was nun tun mit dem Trace Dateien?

Auswerten mit grep

Die Dateien sind oft so groß das die Trace Files nicht mehr mit dem Editor geöffnet werden können. Da hilft es meist nur noch die Dateien mit „grep“ zu analysieren.

Nach Verbindungsfehlern suchen (Powershell Syntax):

Get-ChildItem -Filter *.trc -Recurse | Select-String -Pattern "nserror:"
 
Get-ChildItem -Filter *.trc -Recurse | Select-String -Pattern " ntt2err: soc"
 
... ntt2err: soc 1900 error - operation=5, ntresnt[0]=530, ntresnt[1]=53, ntresnt[2]=0 ...

Auf die Fehlercodes achten , besonders die nt[0],[1] Fehlernummer identifiziert das OS Problem, in obigen Beispiel ein Windwos Socket Fehler 53 ⇒ 10053

Die Fehlernummern sollten nun über https://docs.microsoft.com/en-us/windows/desktop/WinSock/windows-sockets-error-codes-2 bzw. https://support.microsoft.com/en-us/help/819124/windows-sockets-error-codes-values-and-meanings ausgewertet werden können.

In diesem Fall ein

WSAECONNABORTED (10053)  
Translation: Software caused connection abort.  
Description: An established connection was stopped by the software in your host computer, possibly because of a data transmission time-out or protocol error.

Den ganzen Fehler aus dem Log versuchen heraus zu „schneiden“, bzw. im Editor öffnen, falls die Dateigröße das noch zuläßt:

Get-ChildItem -Filter *1112.trc -Recurse | Select-String -Pattern "06:31"
Get-ChildItem -Filter *1112.trc -Recurse | Select-String -Pattern " nttcni: connected on" -context 2 

Auf den zeitlichen Abblauf achten, sind größere Zeitliche abstände sichtbar, diese Support Node beachten ⇒ „Siehe Oracle Net SQLNET.SEND_TIMEOUT and SQLNET.RECV_TIMEOUT Parameters and errors ORA-12170 TNS-12535 ORA-12609 ORA-12608 (Doc ID 1335630.1)” “

trcasst

Mit dem Programm trcasst kann der Trace ebenfalls ausgewertet werden, falls ein level 16 (SUPPORT) Trace File vorliegt.

Leider habe ich hier eine Bug TNS-04317:

TNS-04317: Trace Assistant Internal error: Could not decode OPI function: O3LOGON.

Lösung: Ergebnisse in eine Datei pipen, dann funktioniert es meistens dann doch

trcasst
 
Verwendung: trcasst [options] <filename>
     [options]  Standardwerte sind -odt -e0 -s
     <filename>  immer das letzte Argument
   -o[c|d][u|t][q]  Net Services- und TTC-Informationen
     [c]  Zusammenfassung der Net Services-Informationen
     [d]  Detaillierte Net Services-Informationen
     [u]  Zusammenfassung der TTC-Informationen
     [t]  Detaillierte TTC-Informationen
     [q]  SQL-Befehle
   -s  Statistiken
   -e[0|1|2]  Fehlerinformationen, Standard ist 0
     [0]  NS-Fehlernummern ⁿbersetzen
     [1]  Fehlerⁿbersetzung
     [2]  Fehlernummern ohne Übersetzung
   -l[a|i <connection_id>]  Verbindungsinformationen
     [a]  Auflisten aller Verbindungen in einer Trace-Datei
     [i <connection_id>]  Decodieren einer angegebenen Verbindung
 

Beispiele:

SQL Extrahieren

trcasst  -ot .\sqlnet__xxxx.trc > sqlnet__xxxx_trace_ot_flag.txt

Zusammenfasssung:

trcasst  -s    .\sqlnet__xxxx.trc  > sqlnet__xxxx_trace_s_flag.txt

Fehler:

trcasst  -e0 .\sqlnet__xxxx.trc > sqlnet__xxxx_trace_e0_flag.txt
trcasst  -e1 .\sqlnet__xxxx.trc > sqlnet__xxxx_trace_e1_flag.txt
trcasst  -e2 .\sqlnet__xxxx.trc > sqlnet__xxxx_trace_e2_flag.txt

Interessanterweise ist gerade in älteren Dokumentation das fast besser beschrieben als in der aktuellen Version ⇒ https://docs.oracle.com/cd/A84055_01/EE806DOC/doc/network.804/a58230/ch10.htm#422229 und https://docs.oracle.com/cd/B28359_01/network.111/b28316/profile.htm#NETAG091


Mögliche Maßnahmen um Verbindungsproblem zu bekämpfen

Maßnahmen SQL*NET

Folgende generelle Maßnahmen unter NT können die Verbindungsqualität verbessern ( den TCP Stack stabilisieren)

SQL*Net Parameter ( sqlnet.ora auf dem Client)

TCP.NODELAY ⇒ https://docs.oracle.com/cd/E11882_01/network.112/e10835/sqlnet.htm#NETRF239

OS soll keine Buffer zwischenspeichern

Einzustellender Wert:

TCP.NODELAY = YES

Dem Client untersagen auf den lokalen Rechner nach einer Datenbank zu suchen,

AUTOMATIC_IPC=OFF

Folgenden Parameter mit # auskommentieren Auch hier wieder etwas Zeit sparen, da nicht mehr nach einer lokalen Authorisierungsmethode gesucht wird

# SQLNET.AUTHENTICATION_SERVICES=(NTS)

Einstellung SQLNET.RECV_TIMEOUT und SQLNET.SEND_TIMEOUT überprüfen (Wert ist Time in seconds)

https://docs.oracle.com/cd/B28359_01/network.111/b28317/sqlnet.htm#NETRF228

Maßnahmen OS

TCP Stack optimieren

TcpMaxDataRetransmissions des TCP Stacks optimieren

Siehe ⇒ https://support.microsoft.com/en-us/help/170359/how-to-modify-the-tcp-ip-maximum-retransmission-time-out

Default Wert im ersten Schritt verdoppeln ( 5 auf 10)

Was ist eingestellt:

Get-ItemProperty -Path HKLM:\System\CurrentControlSet\Services\Tcpip\Parameters\TcpMaxDataRetransmissions

Weitere Infos

Support:

  • Using and Disabling the Automatic Diagnostic Repository (ADR) with Oracle Net for 11g
  • Examples of Troubleshooting Slow Oracle Net Connections (Doc ID 1076022.1)
  • Oracle Net Diagnostics (Doc ID 834822.1)
  • Troubleshooting Guide ORA-3136: WARNING Inbound Connection Timed Out (Doc ID 465043.1)

SQL*Net Details:

  • SQL*NET PACKET STRUCTURE: NS PACKET HEADER (Doc ID 1007807.6)

Oracle 12c - DEAD Connection Dedection

Diese Website verwendet Cookies. Durch die Nutzung der Website stimmen Sie dem Speichern von Cookies auf Ihrem Computer zu. Außerdem bestätigen Sie, dass Sie unsere Datenschutzbestimmungen gelesen und verstanden haben. Wenn Sie nicht einverstanden sind, verlassen Sie die Website.Weitere Information
dba/sqlnet_trace.txt · Zuletzt geändert: 2021/11/08 17:29 von gpipperr