Inhaltsverzeichnis
Ü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
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
Default Wert im ersten Schritt verdoppeln ( 5 auf 10)
Was ist eingestellt:
Get-ItemProperty -Path HKLM:\System\CurrentControlSet\Services\Tcpip\Parameters\TcpMaxDataRetransmissions
Weitere Infos
- http://www.orafaq.com/wiki/SQL*Net_FAQ#I_have_some_trouble_with_SQL.2ANet._How_does_one_produce_a_trace_file.3F
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