Archive

Posts Tagged ‘Oracle’

Oracle Gateway for SQL Server – General network error

February 28th, 2009 Comments off

Like many organizations, the company I work for needed to link an Oracle database to a SQL Server database. For various reasons I decided that we would use the Oracle database to pull data from the SQL Server rather than push the data from the SQL Server. This meant that an Oracle Gateway was necessary.

The Gateway install was painless and the setup was relatively easy. Within an hour I was running queries against the SQL Server through an Oracle database link. I set up various scripts to run autonomously in the early mornings and they were working flawlessly. The scripts were pulling entire tables and ran for between 3-5 hours.

After a few weeks the scripts began to fail intermittently with a “General Network Error” and a few weeks later the failure became constant. Below is an account of the fixes that were suggested to me by both the SQL Server DBAs, online resources, Oracle service reps and finally the steps I took to find the issue and the actual fix.

Symptoms:

  1. Runtime before failure varies from between 1 and 30 minutes.
  2. Error:
    ERROR [NA000] [Microsoft][ODBC driver for Oracle][Oracle]ORA-28500: connection from ORACLE to a non-Oracle system returned this message: [Transparent gateway for MSSQL][Microsoft][ODBC SQL Server Driver]Error in row[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead (recv()).[Microsoft][ODBC SQL Server Driver][DBNETLIB]General network error. Check your network documentation.

SQL Server:

  1. There are known issues with Microsoft’s Name Pipes Net-Library. This particular error has happened during SQL Server backup/restore operations, see KB827452.
    1. Make sure TCP/IP is enabled in the connection configurations screen so that the client can connect using TCP/IP rather than Named Pipes.
    2. Check the MDAC 2.8 version to see if it is 2.80.1045.0 or greater. You can find this value at registry entry: HKEY_LOCAL_MACHINE\Software\Microsoft\DataAccess\FullInstallVer.
  2. TCP/IP parameters in registry:
    HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\SynAttackProtect

    1. KeepAliveTime=60000 (60 seconds)
    2. KeepAliveInterval=1000 (try every second)
    3. TcpMaxDataRetransmissions=60 (stop after 60 retransmissions)
  3. TCP/IP settings in the SQL Server Manager:
    SQL Server Configuration Manager -> SQL Server 2005 Network Configuration ->Protocols -> TCP/IP

    1. KeepAliveTime=60000 (60 seconds)
    2. KeepAliveInterval=1000 (try every second)
    3. TcpMaxDataRetransmissions=60 (stop after 60 retransmissions)

Oracle Gateway:

  1. In the gateway init file, inittg4msql.ora, add the following entry: HS_FDS_CONNECT_PROPERTIES=”timeout=’120′”. You can change the value, here 120, depending on your needs. This config value is not in Oracle documentation and is used to stop long running queries from losing connection.
  2. TCP/IP parameters in registry:
    HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\SynAttackProtect

    1. KeepAliveTime=60000 (60 seconds)
    2. KeepAliveInterval=1000 (try every second)
    3. TcpMaxDataRetransmissions=60 (stop after 60 retransmissions)

Fix:

  1. Downloaded a packet sniffer; wireshark (the best I’ve used so far): http://www.wireshark.org/download.html
  2. Filtered to see only traffic between the Gateway and the SQL Server.
  3. Ran the the script and monitored the traffic.
  4. I noticed a lot of lost packets and data retransmissions.
  5. At intermittant times there is a 30 second lag and the SQL Server reset the connection (the KeepAlive setting was still 30,000).
  6. I ran a ping test to the server with 5600 btye packet to see if this was a network layer issue:
    ping -t -l 5600 [ip addres]
  7. The test revieled 20-40% packet loss, which is unheard of.
  8. After contacting the Network team the SQL Server DBA was told to change a setting on the NIC.
    LinkSpeed/Duplex from Auto/Auto to Full/100

The NIC settings fixed the network issue and the scripts are passing with flying colors.

Hope this helps.

Rodger

Categories: Development, Oracle Tags: , ,
  • blair the inside story download
  • cistychov thug life download mp3
  • bA?lent ersoy tarkan dA?eti download
  • breakup to makeup jeremih download
  • canon mx320 software download mac
  • cassie official girl download free
  • amma pillai mp3 download
  • cleveland show saison 2 download
  • a7n8x x drivers download xp
  • baul song download site
  • bloodhound gang uhn tiss download free
  • boney m feliz navidad download mp3
  • adf.ly clone php script free download
  • canoscan 8800f driver download vista
  • blondie maria download mp3
  • arabic radio tv player 1.0 download
  • arrietty dvd cover download
  • bhai harjinder singh download mp3
  • checkpoint ngx r65 iso download
  • bridget jones subtitles download
  • aprilia sr gta sa download
  • aakasha deepavu neenu song download
  • akshara lori song download
  • cassie official girl 320 download
  • 1 litre no namida download
  • climbing magazine april 2011 download
  • add n to x download blogspot
  • canon mx300 printer download
  • banco de dados avg download
  • celentano album 2011 download
  • canon 1000d dummies download
  • 70 642 ebook sonicview download
  • chennai map google download
  • blackberry os v.5.0 download
  • cloud computing bible download
  • aventail connect configuration tool download
  • clang download for windows
  • anthony hamilton charlene download link
  • alcatel pc suite ot 800 download
  • cartella download di firefox
  • agenzia delle entrate download 730
  • canopus video editing software download
  • chaotic download o jogo
  • bully download three days grace
  • adriano celentano confessa download
  • album l'animale di adriano celentano download
  • canon mp160 download windows 7
  • a onda download rmvb dublado
  • bicicleta para gta download
  • aplikacije za lg cookie download
  • adaa garam masala mp3 download
  • asus wl 138g v2 driver download free
  • avantgarde lt bold font download
  • adobe data visualization components download
  • bahut pyar song download
  • 20th century intro video download
  • asa s prietenii hi q download fisierul meu
  • bram stoker's dracula download movie
  • anaganaga o dheerudu songs download free
  • blue best in me download free mp3
  • arcade fire suburbs download gratis
  • asus motherboard drivers p5vdc mx free download
  • bhajan gaurav shastri free download
  • cara masuk mode download galaxy young
  • benny benassi spaceship original download
  • chrome download flash extension
  • bleach cap 466 ita download
  • business plan pro 2009 download
  • 3 1 s miroslavem donutilem download
  • aadha ishq mp3 download
  • canon mg6150 driver download
  • antenna fuse odg mp3 download
  • a p political map download
  • atras da porta ivete download
  • 90 day antivirus trial download
  • bt phone book download
  • 20th century mp3 download
  • asus z77 bios download
  • ani mru mru mp3 download
  • ar rahman hits ringtones download
  • canon ip1880 download driver
  • black swan rising download
  • andy c mixmag cd download
  • chlopaki na ibizie soundtrack download
  • battleship download legendado dvdrip
  • besplatan download domace serije
  • babasonicos discografia completa download
  • 2012 l'avvento del male download ita
  • astral projection audio download
  • acdsee pro 3 download free
  • armin van buuren imagine download free mp3
  • beatport download top 100 december 2011
  • alex guerrero caminando mp3 download
  • asus p5q se2 manual download
  • 4 elemente spiel download
  • ato de coragem filme download
  • cat de departe video download
  • bratz forever diamondz song download
  • beogradski sindikat download oni su
  • 4shared download 7 icons jealous
  • cd r&b in the mix 2010 download
  • blueberry star slinger remix download
  • a4tech pks 635k cam driver download
  • caAža as bruxas download hd
  • boski A?igolo w europie download pl