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: , ,
  • bugs world 1 download
  • antonia vank pleaca download mp3
  • atheist's guide to christmas download
  • cambio di gioco download ita
  • 11x11 football manager hack download
  • aumentare numero download internet explorer
  • automatically download dvd cover art
  • baki b3 mrak mp3 download
  • club hitz mp3 download
  • 3com etherlink xl 3c905c tx treiber download
  • chut lola mp3 download
  • armin van buuren collection download
  • absolutamente los angeles filme download
  • benga one and only download
  • antony santos vuelve free download
  • avert fate download mac
  • asp.net 3.5 step by step free download
  • angel in disguise download corrinne may
  • atheist second to sun download
  • all american rejects affection download
  • chimarruts 2010 download cd
  • all american rejects swing download
  • 3ds max 5 download trial
  • acima de qualquer suspeita download rmvb legendado
  • adrian minune ma minte inima download
  • astral projection dvd download
  • canon mf5750 driver download for mac
  • chahat album ali haider download
  • brother pe design 6 download
  • boy band mp3 download
  • armin van buuren imagine download dvd
  • arata le la toti download fileshare
  • andre rieu download dvd som livre
  • ali um heroi americano download
  • aventail vpn download linux
  • aplicativo de download para iphone
  • burlar limite de download uploaded
  • christmas carols mp3 download boney m
  • asus p7p55 lx download
  • bob seger songs download free
  • brigada theme song free download
  • aerobics music free download 2011
  • a escolha download dublado avi
  • bisoncam download windows 7 baixaki
  • 3ds max modelling download
  • basara 2 download games
  • brokencyde music video download
  • alien t rock the nation download
  • chessmaster 11th edition download free
  • bisoncam download for vista
  • bodhu re free mp3 download
  • a4 tech kbs 26 driver download
  • automatically download lyrics itunes
  • antonia shake mamma download zippy
  • atta kathai song download
  • big poppa big download
  • babe noc bez sna download mp3
  • another body murdered mp3 download
  • 5001 amazing facts full download
  • clannad ep 24 download
  • bA­blia de estudo online download
  • anelia 2011 album download
  • alabanza g lavi mp3 download
  • burmese days george orwell download
  • castlevania portrait of ruin download ita
  • asas livres volume 1 download
  • brother mfc 7340 mac download
  • cassia eller acustico download gratis
  • battery 2 download completo
  • chyna anne mcclain unstoppable download
  • besplatan download google chrome
  • chicken invader game download free
  • banco de dados postgresql download
  • book of challenges diabete download
  • clube da esquina 1972 download blogspot
  • blackberry storm2 9520 software download
  • carry on jatta download full movie hd
  • apparat live 2012 download
  • business cards organizer download
  • cartella download browser nokia
  • cara download video mivo.tv
  • adventist hymnal songs download
  • cardio party cd zumba download
  • carvalho de rei rfactor download
  • astral tournament game download
  • canon pixma mx 880 driver download
  • banjo kazooie download xbox
  • beastie boys whatcha want download
  • beyblade v force music download
  • alkohol brennprogramm kostenlos download
  • 4shared download game hp
  • canon mx 410 manual download
  • climax heroes fourze download pc
  • acid black cherry xmas live download
  • besplatna muzika za download
  • adobe shockwave 11.6 msi download
  • better keyboard 7 apk download
  • bundespolizei virus chip download
  • call of juarez download full
  • akcent religious girl download mp3
  • a outra download dublado gratis
  • automatically download facebook photos
  • acer iconia a500 update.zip download
  • app to download albums
  • adha ishq mp4 download
  • aerobics music download online
  • banda eva conexao tambor download
  • amilo l series driver download
  • a sociedade do anel download rmvb dublado
  • browser tries to download index.php
  • buffy l'ammazzavampiri stagione 1 download
  • boy striped pyjamas book download
  • 20 years carre cd download
  • cineva cineva cristi dules download
  • 3com etherlink xl 10 100 treiber download
  • apache commons vfs 1.1 download