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... :)

More info: Oracle Docs on SQLNet

Just a DBA in a developers world...

Sometimes I wonder where this whole web development thing is going...

I was asked to setup some backup and recovery tools for the BerkeleyDB Java Edition (JE).

Hmm... DB... I could probably argue that DB should be dropped from the name if you add Java edition to it. I suppose maybe it's Oracle's plan to drop BerkeleyDB for the newer improved BerkeleyDB Java Edition (JE). Maybe that's why there won't be a BDB storage engine for MySQL beyond 5.1...

So after reading way to many pages of Oracle docs, I try just using a shell script with some fancy tar work and cross my fingers. That doesn't work, so I call on the Java developers to write a little copy tool using a jdb backup class buried somewhere way down deep in those java class libraries somewhere, plug that it to my script, and now we have backups.

All this to make sure we have instant DBA recovery for one of our Java applications, that uses Hibernate, that talks to Terracotta, that uses the BerkeleyDB JE. No complexity there...

Of course, the next thing they ask is does that mean we can restore to a point in time?

Well, that all depends. If we crash Terracotta crashes, and the filesystem is intact, the BerkeleyDB JE has the ability to roll it's logs forward and restore to the point in time of the last valid transaction at the time of the crash.

That won't do us much good, however, if we need to actually restore from a backup and then roll the logs forward, because BerkeleyDB is a append only write model: Meaning that the log and the data are one in the same, so I can't get copy's of the transaction logs on a periodic basis without getting the data as well. So, unless we plan on running backups every 15 minutes or so, we better not be expecting to restore to a point in time.

So the next question is (of course):

How are we supposed to use Terracotta as a highly recoverable persistent data store then?

My Answer (these are my opinions, as a DBA, of course):

Don't, if you expect your persistent data store to act like a relational database engine.... :)

But don't worry... it's the DBA's responsibility to make sure the data is all recoverable... right?

VMWare: The greatest thing since sliced bread

So if you like to try out new things, and you're not using VMWare yet, get with the program!

Really, though - it's so easy now (and the VMServer is free) . Just download and install.

VMWare lets you run mini virtualized server environments within your current operating system. Yea, there is a little overhead, but for testing and trying out new databases, configurations, applications, it's really cool.

The best part of it all, is the fact that you can download a host of pre-built applications to run with in the VMServer you just installed. Just startup the server and, wala... a running MYSql database in slackware on your Dell windows laptop. Yea, they have an Oracle 10g RAC appliance, as well as a SQLServer 2005 appliance.

Sweet. If you haven't checked it out, do so now...

ORACLE: Sending Logs/Files via mailx

This may be irrelevant depending on your choice of monitoring tools, but I tend to prefer good old fashioned shell scripts on UNIX. One issue I've always had with mailx is being able to attach error files.

Here's a sample script that seems to work on most UNIX flavors with sh,bash, or ksh:

#!/bin/sh
#
DT=`date +%d%m%y`
2>&1 echo "Hey - ORA- error !" >> ${ERR_DIR}/$0.$DT.err
ORA_ERRS=`grep -i ora- ${ERR_DIR}/$0.$DT.err wc -l sed "s/ //g"`
if [ $ORA_ERRS -gt 0 ]
then
mailx -s "$0 ORA Errors! ($DT) " dba@x.com <<-EOF WARNING! - $0 ($DT) had errors! Error log is attached. ~< ! uuencode ${ERR_DIR}/$0.$DT.err $0.$DT.err ~. EOF fi # #