Failover and load balancing — getting the most out of RAC

One of the main reasons for running a RAC system is the ability to fail over and balance load. Both features have to be matched to the specific use case and to the application software in use.

Load balancing

Oracle distinguishes between two kinds of load balancing.

Client-side load balancing

Client-side load balancing is not true load balancing. It is enabled in the client's tnsnames.ora with the parameter LOAD_BALANCE=on. The client connects at random to one of the instances listed in the configuration. This distributes new connections almost evenly across the cluster nodes, but without taking the current state or load of the servers into account. The configuration should be based exclusively on service names (SERVICE_NAME) and must not use a SID. Using service names also provides a reconnect path if a cluster node fails.

Example configuration:

SQL / PL/SQL

			sample.world =(DESCRIPTION=
			(LOAD_BALANCE=OFF)
			(ADDRESS_LIST=
			(ADDRESS=(PROTOCOL=tcp)(HOST=viprac1)(PORT=1521))
			(ADDRESS=(PROTOCOL=tcp)(HOST=viprac2)(PORT=1521))
			)
			(CONNECT_DATA=(service_name=sample_service)))

Here the connection to the RAC instances is always attempted in the order given. If LOAD_BALANCE=ON is set, the server instance is chosen at random instead.

Example configuration:

SQL / PL/SQL

			sample.world =(DESCRIPTION=
			(LOAD_BALANCE=ON)
			(ADDRESS_LIST=
			(ADDRESS=(PROTOCOL=tcp)(HOST=viprac1)(PORT=1521))
			(ADDRESS=(PROTOCOL=tcp)(HOST=viprac2)(PORT=1521))
			)
			(CONNECT_DATA=(service_name=sample_service)))

Connection load balancing

Besides handling load balancing on the client, it can also be configured on the server side. The connection is then established in the following order:

the least loaded node
the least loaded instance
the least loaded dispatcher in a shared server installation

This requires the parameter *.remote_listener='SAMPLE_REMOTE_LSNR' to be set for every instance in the cluster. The TNS configuration has to be adjusted accordingly.

Example configuration:

SQL / PL/SQL

		SAMPLE_REMOTE_LSNR=
		(ADDRESS_LIST=
		(ADDRESS=(PROTOCOL=tcp)(HOST=viprac1)(PORT=1521))
		(ADDRESS=(PROTOCOL=tcp)(HOST=viprac2)(PORT=1521))
		)

Important for operations: whenever load balancing is configured across several instances of a RAC system, the way the application works must be taken into account. Under certain circumstances this can put heavy load on the interconnect and cause a build-up of global cache wait events, which can substantially reduce overall system performance. Always weigh these points before enabling load balancing.

Failover

A failover configuration keeps the application available even if one or several RAC nodes go down. Two failover scenarios are distinguished.

Connect time failover

This is relatively uncritical and should be part of every application configuration that uses a RAC database. Oracle Net attempts to connect to one of the instances listed in the configuration.

Example configuration:

SQL / PL/SQL

		sample.world =(DESCRIPTION=
(FAILOVER=ON)
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=viprac1)(PORT=1521))
(ADDRESS=(PROTOCOL=tcp)(HOST=viprac2)(PORT=1521))
)
(CONNECT_DATA=(service_name=sample_service)))

A session that was connected to an instance when it failed is terminated and has to establish a new connection. This does not happen automatically.

Transparent Application Failover

If an instance drops the connection during operation, the session is reconnected to an available instance. There are limitations here that the application has to handle properly. First, open transactions that have not yet been committed are rolled back. Second, session settings (alter session …) are held in the database instance and are lost in the transition.

Example configuration:

SQL / PL/SQL

sample.world=(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = viprac1)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = viprac2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = sample_service )
(FAILOVER_MODE = ( TYPE=SELECT)
( METHOD=BASIC)
( RETRIES=20)
( DELAY=30))))

TYPE

SESSION Aktuelle Abfragen werden abgebrochen.
SELECT Ein aktuell laufendes SELECT-Statement wird über die neue Verbindung noch
einmal ausgeführt. Die Rückgabe des Resultsets des Statements wird an
der richtigen Stelle fortgeführt.

METHOD

BASIC Bei dieser Standardmethode wird die Verbindung zur Failover-Instanz
erst im Failover-Fall aufgenommen.
PRECONNECT Im Gegensatz zur BASIC-Methode werden die Verbindungen zu den anderen Knoten des
Systems beim Connect erstellt und in Reserve gehalten. Dadurch wird bei einem Failover der
Overhead der Verbindungaufnahme vermieden, sodass es zu einer schnelleren Weiterführung der
Session kommt. Nachteilig ist natürlich der damit verbundene Overhead an
Prozessen und der Hauptspeicherverbrauch.

BACKUP

NS_ALIAS Hier kann ein TNS-Alias einer Backup-Instanz angegeben werden. Bei einem Failover wird
dann auf diese Instanz bzw. TNS-Alias-Definition gewechselt.

RETRIES

ANZAHL und DELAY = SEKUNDEN
Definition des zeitlichen Verhaltens bei erneuter Verbindungsaufnahme, also
wie oft und mit welchem Abstand wird ein neuer Connect versucht.