Oracle RAC Load Balancing

Relational databases play a central role in many software systems. In many enterprises, Oracle is the RDBMS of choice. Among those, many mission-critical systems make use of Oracle's Real Application Cluster (RAC), a load-balanced multi-node database that can handle failures of individual nodes without causing an outage. This article provides a decent summary of RAC.

While RAC has many features and advantages (as well as trade-offs), that isn't why this post is here. It is here because the way in which the Java ODBC driver establishes a connection to the RAC is worth understanding in the event that one suspects a connectivity issue between the client code and the database server.

For a Java developer, using RAC is very similar to any other Oracle database. Usually, the only indication that you're using a RAC is the contents of your connect string. It typically lists a number of database server addresses in an ADDRESS_LIST element. For the purposes of illustrating the discussion, an example may be helpful:

jdbc:oracle:thin:@(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = dbserver1.somedomain.com)(PORT = 12345))(ADDRESS = (PROTOCOL = TCP)(HOST = dbserver2.somedomain.com)(PORT = 12345))(LOAD_BALANCE = yes)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = dbServiceName.somedomain.com) ) )

There are two types of load balancing offered by the RAC: client-based load balancing and server-based load balancing. The system can be configured to use one, both or neither forms of load balancing.

The LOAD_BALANCE element in the connect string above specifies that the client code should perform its own load balancing. Essentially, this means that the client will randomly choose one of the servers from the ADDRESS_LIST whenever a connection is requested.

If server-based load balancing is enabled, a listener service provides automatic load distribution across all nodes in the RAC. The listener will use the query optimizer to determine which node in the RAC should service the request (based on their current workload, machine profiles and, in newer versions of Oracle, admin-specified rules).

If one were to use the connect string above and server-based load balancing were enabled, the sequence of events that would occur when a call to DriverManager.getConnection(...) is invoked are:

• The driver selects dbserver2.somedomain.com (this choice was made at random by the driver) and issues a connection request.
  
• The listener on dbserver2.somedomain.com determines that the most under taxed node in the RAC is dbserver3.somedomain.com and returns it to the client. NOTE: this assumes that the RAC has 3 nodes. It is important to note that all the nodes do NOT need to be included in the connect string... the nodes in the ADDRESS_LIST simply indicate which nodes the client will balance its initial "getConnection" requests against
  
• The client will attempt to establish a connection to dbserver3.somedomain.com.
  

Sudo Voodoo

The Unix sudo command can be quite useful when one needs to give another user access to a select group of commands that need to be run as a certain user without having to give up the password for that user account. The easiest way to do this is to edit the sudoers file (usually in /etc/sudoers). The file allows an administrator to configure a list of commands that a user can execute as another user without a password. The policy file follows a fairly simple context-free grammer (defined quite concisely in the man page for sudoers). There are a few things to keep in mind when editing the sudoers file:

• You must edit the file using the visudo command as root
• When defining a command, you must use the fully qualified path to the command. The system will report a syntax error if you attempt to save the sudoers file with a command that does not start with a /character. This makes sense when one condiders that the whole point of the command is to run something as another user. That being the case, the system can make no guarantees as to what the working directory will be when running the command so requiring the absolute path is a reasonable constraint.
• The command(s) defined in the sudoers file must exactly match the command to be run.

Here is an example of a simple sudoers configuration:

Cmnd_Alias SOME_COMMANDS = /home/somedir/command1.sh,/home/somedir/command2.sh

user1 ALL=(ALL) NOPASSWD: SOME_COMMANDS

The first line sets up a "command alias" or a list of commands specified using their absolute path with each command separated by a comma. If you needed to pass a comma into a command as an argument, for example, it would need to be escaped using the backslash character.

The next line tells the system that the user user1 is allowed to run any of the commands listed in SOME_COMMANDS as any other user without having to specify a password.

Once you've installed these lines in the sudoers policy file, the user1 user can log in and execute sudo -u user2/home/somedir/command1.sh to run the command1.sh script as user2 without ever having to provide user2's credentials.

For a full treatment of how to configure sudo policies, the man pages are the best resource.