Oracle SQL Developer Hangs on Linux

If you use SQL Developer 3.x on Linux (tested on Ubuntu, RHEL, but applicable to other distributions, and may happens on Mac) and you do not remember time when you closed it gracefully, but xkill it all the time because it just HAAAAAAAAAAAANGs, keep reading at least next 20 lines, it may be very useful.

Usual scenario is: after some idle time, or after you disconnect from vpn/network, you try to do something (at least close window, or open new session, or really – anything) SQL developer start handing for about 15-18 minutes. And it may cascade to whole gnome session been hanging, so you cant even Ctrl+Alt+F1 to kill sqldeveloper..

1.
To fix hanging after idle time, and also avoid most “connection closed” errors:

Download libkeepalive-0.2.tar.gz from http://libkeepalive.sourceforge.net

$ tar xvf libkeepalive-0.2.tar.gz
$ cd libkeepalive-0.2
$ make
$ sudo cp libkeepalive.so /lib/
$ which sqldeveloper
/usr/local/bin/sqldeveloper
Edit the file above and replace line like:
/opt/sqldeveloper/sqldeveloper.sh
to:
LD_PRELOAD=/lib/libkeepalive.so KEEPCNT=2 KEEPIDLE=30 KEEPINTVL=30 /opt/sqldeveloper/sqldeveloper.sh

2.
To fix hanging after disconnect:

# echo "net.ipv4.tcp_retries2=5" >> /etc/sysctl.conf
# sysctl -p

Have tcpv6 disabled:

# echo "net.ipv6.conf.all.disable_ipv6 = 1" >> /etc/sysctl.conf
# echo "net.ipv6.conf.default.disable_ipv6 = 1" >> /etc/sysctl.conf
# echo "net.ipv6.conf.lo.disable_ipv6 = 1" >> /etc/sysctl.conf
# sysctl -p

If it helped, – this is it, you can ignore what is written below.

But if interested why it hangs – keep reading.

Just before it hangs enable basic debugging on machine where SQL Developer is running:

In first window:

$ ps -ef | grep sqldeve
amoseyev  4637     1  0 Jun22 ?        00:00:00 /bin/sh /usr/local/bin/sqldeveloper
amoseyev  4639  4637  0 Jun22 ?        00:00:00 /bin/bash /opt/sqldeveloper/sqldeveloper.sh
amoseyev  4642  4639  0 Jun22 ?        00:00:00 bash sqldeveloper
amoseyev  4699  4642  1 Jun22 ?        00:21:00 /usr/lib/jvm/java-6-sun/bin/java -Xmx640M -Xms128M -Xverify:none -Doracle.ide.util.AddinPolicyUtils.OVERRIDE_FLAG=true -Dsun.java2d.ddoffscreen=false -Dwindows.shell.font.languages= -XX:MaxPermSize=128M -Doracle.jdbc.mapDateToTimestamp=false -Dide.AssertTracingDisabled=true -Doracle.ide.util.AddinPolicyUtils.OVERRIDE_FLAG=true -Djava.util.logging.config.file=logging.conf -Dsqldev.debug=false -Dide.conf="/opt/sqldeveloper/sqldeveloper/bin/sqldeveloper.conf" -Dide.startingcwd="/opt/sqldeveloper/sqldeveloper/bin" -classpath ../../ide/lib/ide-boot.jar oracle.ide.boot.Launcher
amoseyev 12609 12170  0 01:23 pts/3    00:00:00 grep --color=auto sqldeve

# strace -e trace=network -f -p 4699

In second window:

$ netstat -n | grep 1521
tcp        0      0 10.4.4.51:49261         172.18.99.69:1521       ESTABLISHED

# tcpdump -lnni tun0 host 172.18.99.69 and dst port 1521 -vvv

Make sqldeveloper hang, and after 15-18 minutes (after it will finish handing):

In first window:

# strace -e trace=network -f -p 4699
...
[pid 32467] 17:27:08  0x823a0770, 8192, 0) = -1 ETIMEDOUT (Connection timed out)
[pid 32467] 17:27:08 send(232, "\0\n\0\0\6\0\0\0\0@", 10, 0) = -1 EPIPE (Broken pipe)

In second window:

# tcpdump -lnni tun0 host 172.18.99.69 and dst port 1521 -vvv

17:14:51.924676 IP (tos 0x0, ttl 64, id 28812, offset 0, flags [DF], proto TCP (6), length 151)
    10.181.70.57.39292 > 10.168.66.198.1521: Flags [P.], cksum 0x04f0 (correct), seq 7433:7532, ack 10119, win 1005, options [nop,nop,TS val 39292289 ecr 3105462411], length 99
17:14:52.451556 IP (tos 0x0, ttl 64, id 28813, offset 0, flags [DF], proto TCP (6), length 151)
    10.181.70.57.39292 > 10.168.66.198.1521: Flags [P.], cksum 0x046c (correct), seq 7433:7532, ack 10119, win 1005, options [nop,nop,TS val 39292421 ecr 3105462411], length 99
17:14:53.511511 IP (tos 0x0, ttl 64, id 28814, offset 0, flags [DF], proto TCP (6), length 151)
    10.181.70.57.39292 > 10.168.66.198.1521: Flags [P.], cksum 0x0363 (correct), seq 7433:7532, ack 10119, win 1005, options [nop,nop,TS val 39292686 ecr 3105462411], length 99
17:14:55.631544 IP (tos 0x0, ttl 64, id 28815, offset 0, flags [DF], proto TCP (6), length 151)
    10.181.70.57.39292 > 10.168.66.198.1521: Flags [P.], cksum 0x0151 (correct), seq 7433:7532, ack 10119, win 1005, options [nop,nop,TS val 39293216 ecr 3105462411], length 99
17:14:59.871499 IP (tos 0x0, ttl 64, id 28816, offset 0, flags [DF], proto TCP (6), length 151)
    10.181.70.57.39292 > 10.168.66.198.1521: Flags [P.], cksum 0xfd2c (correct), seq 7433:7532, ack 10119, win 1005, options [nop,nop,TS val 39294276 ecr 3105462411], length 99
17:15:08.335490 IP (tos 0x0, ttl 64, id 28817, offset 0, flags [DF], proto TCP (6), length 151)
    10.181.70.57.39292 > 10.168.66.198.1521: Flags [P.], cksum 0xf4e8 (correct), seq 7433:7532, ack 10119, win 1005, options [nop,nop,TS val 39296392 ecr 3105462411], length 99
17:15:25.263514 IP (tos 0x0, ttl 64, id 28818, offset 0, flags [DF], proto TCP (6), length 151)
    10.181.70.57.39292 > 10.168.66.198.1521: Flags [P.], cksum 0xe460 (correct), seq 7433:7532, ack 10119, win 1005, options [nop,nop,TS val 39300624 ecr 3105462411], length 99
17:15:59.119517 IP (tos 0x0, ttl 64, id 28819, offset 0, flags [DF], proto TCP (6), length 151)
    10.181.70.57.39292 > 10.168.66.198.1521: Flags [P.], cksum 0xc350 (correct), seq 7433:7532, ack 10119, win 1005, options [nop,nop,TS val 39309088 ecr 3105462411], length 99
17:17:06.831496 IP (tos 0x0, ttl 64, id 28820, offset 0, flags [DF], proto TCP (6), length 151)
    10.181.70.57.39292 > 10.168.66.198.1521: Flags [P.], cksum 0x8130 (correct), seq 7433:7532, ack 10119, win 1005, options [nop,nop,TS val 39326016 ecr 3105462411], length 99
17:19:06.895498 IP (tos 0x0, ttl 64, id 28821, offset 0, flags [DF], proto TCP (6), length 151)
    10.181.70.57.39292 > 10.168.66.198.1521: Flags [P.], cksum 0x0bf0 (correct), seq 7433:7532, ack 10119, win 1005, options [nop,nop,TS val 39356032 ecr 3105462411], length 99
17:21:07.215506 IP (tos 0x0, ttl 64, id 28822, offset 0, flags [DF], proto TCP (6), length 151)
    10.181.70.57.39292 > 10.168.66.198.1521: Flags [P.], cksum 0x966f (correct), seq 7433:7532, ack 10119, win 1005, options [nop,nop,TS val 39386112 ecr 3105462411], length 99
17:23:07.535497 IP (tos 0x0, ttl 64, id 28823, offset 0, flags [DF], proto TCP (6), length 151)
    10.181.70.57.39292 > 10.168.66.198.1521: Flags [P.], cksum 0x20ef (correct), seq 7433:7532, ack 10119, win 1005, options [nop,nop,TS val 39416192 ecr 3105462411], length 99
17:25:07.855511 IP (tos 0x0, ttl 64, id 28824, offset 0, flags [DF], proto TCP (6), length 151)
    10.181.70.57.39292 > 10.168.66.198.1521: Flags [P.], cksum 0xab6e (correct), seq 7433:7532, ack 10119, win 1005, options [nop,nop,TS val 39446272 ecr 3105462411], length 99
17:25:07.855511 IP (tos 0x0, ttl 64, id 28824, offset 0, flags [DF], proto TCP (6), length 151)
    10.181.70.57.39292 > 10.168.66.198.1521: Flags [P.], cksum 0xab6e (correct), seq 7433:7532, ack 10119, win 1005, options [nop,nop,TS val 39446272 ecr 3105462411], length 99
17:25:07.855511 IP (tos 0x0, ttl 64, id 28824, offset 0, flags [DF], proto TCP (6), length 151)
    10.181.70.57.39292 > 10.168.66.198.1521: Flags [P.], cksum 0xab6e (correct), seq 7433:7532, ack 10119, win 1005, options [nop,nop,TS val 39446272 ecr 3105462411], length 99

So we have 15 TCP retransmission timeouts (RTO). And default net.ipv4.tcp_retries2=15. so SQL developer waited for TCP timeout..

In Oracle Database JDBC Developer’s Guide and Reference guide (http://docs.oracle.com/cd/B28359_01/java.111/b31224/apxtblsh.htm)
under “Using JDBC with Firewalls” they say:

Pass oracle.net.READ_TIMEOUT as connection property to enable read timeout on socket. The timeout value is in milliseconds.

SQL Developer is kind of tool what has a good chance to be on another side of firewall. But it does not seem to be any internal sqldeveloper timeout, or at least it does not work… SQL Developer for linux, patiently waits for TCP timeout. We are not talking about milliseconds, but 15+ minutes!

I guess it may be OK for somebody, but I personally forget what I was planning to do there after 15 minutes 🙂

First advice above, makes SQL developer to open TCP sockets with setsockopt(SO_KEEPALIVE), and that socket will never idle for more than 30 seconds.
And it will stop 99% of firewalls from closing/banning the connection due to inactivity. (most firewalls have polices for idle connection time limit, to prevent several types of DoS attacks). Some firewalls may still treat keep alives as “not real traffic” and still ban connection.
This is similar to setting SQLNET.EXPIRE_TIME=x in sqlnet.ora on server side. But in this case keep alives will come from client site.

Second advice above, covers case IF connection still gets broken (either due to firewall or just cable unplug).
So it sets TCP RTO limit from default 15 to 5. With RTO been variable value it will bring TCP timeout to below 30 seconds.
It can cause some data network traffic loss in slow networks, but it gotta be really crappy network.

Advertisements
This entry was posted in Linux, Systems, Ubuntu and tagged , . Bookmark the permalink.

11 Responses to Oracle SQL Developer Hangs on Linux

  1. Pingback: Oracle SQL Developer Hangs on Linux « Note To Self

  2. Reblogged this on Note To Self and commented:
    It worked on Kubuntu 12.10

  3. mistrzyni malgorzata says:

    awesome!
    works for me!

  4. Marek Šabo says:

    Thanks a lot – this is a ‘must have’ for me.

  5. Thinh Tran says:

    No way. You are incredible.

  6. Germán Bobr says:

    Reblogged this on Germán Bobr and commented:
    I keep coming to this post every time I set up a development box 🙂
    Very useful solution to make Oracle SQL Developer usable in Linux.

  7. Pingback: Oracle SQL Developer se cuelga en Linux - Germán Bobr

  8. Ron says:

    Excellent tip, thanks a million!!!

    Why is this still an issue almost 2 years later!!!! Can the developers not do something to fix it??

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s