Tuesday, February 09, 2010

Firewalls... what can you say?

Every time I've had to setup something 'special' with a database server and a firewall for the first time there always seems to be way to many issues.

With MySQL it's been mostly straight forward; opening an additional port or to for supporting the enterprise monitor or something, but nothing crazy.

Oracle, however, gets me every time. :(

We were setting up a new Oracle 11g instance, and decided we needed to enable shared server mode ( used to be MTS ); Nothing fancy, no connection pooling, no RAC or anything like that.

So, having done this in the past, we setup the dispatcher to use a dedicated port (rather than the dynamic ports, which firewalls love). Started testing things and realized we kept getting forced disconnects.

somewhat cryptic errors:
org.hibernate.util.JDBCExceptionReporter: SQL Error: 17002, SQLState: 08006
org.hibernate.util.JDBCExceptionReporter: IO Error: Size Data Unit (SDU) mismatch

Then we spend a couple days working with devs, trying different dispatcher/server process scenarios, open a support ticket, etc. Nada.

So we try the next obvious thing and take the firewall out of the equation, and sure enough, everything works fine. So we go back to playing with more configurations, turning off sql inspection on the firewall, etc, etc. Then we start doing network packet traces and analyzing those (another couple days go by).

Then our network engineer notices one of the packets has a tcp option he hasn't seen before 'URG'. Hmm... Sure enough, those packets are generated on the database side, but never make it through the firewall.

A bit more reading/googleing and it becomes clear:

Oracle has a sqlnet option called disable_oob that is off by default, and this allows Oracle to send packets marked 'URGENT' for things like client disconnects, and obviously for some things around dispatchers & shared processes.

Anyway, let those packets pass through the firewall, and it starts working like a charm. One week down, more packet sniffing than I care to do again the rest of the year, all so I can get it setup and forget about it for 5 years till I run into the issue again... :)