Earlier this week I blogged about using the Oracle AlternateServers connection string parameter with ColdFusion Oracle datasources for connection failover, a feature of the DataDirect 3.5 driver bundle. This blog entry shows a network trace of connection failover in action using the Ethereal Network Analyzer (a favorite tool) to packet sniff a simple query made from a ColdFusion page. The packets were traced between the ColdFusion server and the Oracle database. Effectively, the trace shows the TCP communication between the JDBC driver in macromedia_drivers.jar version 3.5 to the Oracle database server.

To demonstrate how this works, I configured the datasource with incorrect information to simulate a situation where an Oracle database is unreachable. I listed the default port incorrectly, and provided a list of two alternate Oracle servers where the first in that list also had an incorrect port. The last in the list, which was the third server/port pair in the sequence provided a known good host/port combination. I then restarted the ColdFusion server to make sure any existing connections were dropped, and then began tracing all connections from the ColdFusion server to Oracle, and finally I made a single page request for a simple query to that datasource. You can see in the trace that three connection attempts were made in the order specified in the JDBC URL, where the first two (highlighted in red by Ethereal) failed to connect, and the third was successful.

CF Admin Settings for Oracle DSN AlternateServersColdFusion Administrator screenshot of datasource details. Shows AlternateServers connection string parameter used for an Oracle DSN to specify a list of alternate database hosts/ports to use in the event of connection failure. For this test the default port was incorrectly listed as 1234, and the first alternate server also used an incorrect port of 3456. The last alternateserver in the list used the right port of 1521.

Packet trace of Oracle JDBC Driver  AlternateServers functionalityAn Ethereal packet trace of TCP network activity made by an Oracle JDBC driver (part of the macromedia_drivers.jar version 3.5) from ColdFusion MX 7. This trace demonstrates how three attempts were made to connect to an Oracle database when using the AlternateServers connection string parameter. The default port was intentionally listed incorrectly as 1234, followed by another incorrect port of 3456, and the last alt server used the right port of 1521. The trace shows that the third connection attempt was to port 1521 and was successful.