Listener Connection Failover and Load Balancing Explained

To improve the reliability and performance of Oracle Databases, proper configuration of failover and load balancing in listener connections is essential. This article explains the basic concepts, specific configuration methods, and operational considerations in detail.

1. Introduction
2. Basic Concepts of Failover
1. What is Failover?
2. Types of Failover
3. Basic Concepts of Load Balancing
1. What is Load Balancing?
2. Types of Load Balancing
4. Implementing Failover via TNS Configuration
5. Implementing Load Balancing via TNS Configuration
6. Combining Failover and Load Balancing
7. Using TAF (Transparent Application Failover)
1. Overview of TAF
2. How to Configure TAF and Key Considerations
8. Practical Configuration Examples
1. Single Instance Environment
2. RAC (Real Application Clusters) Environment
9. Troubleshooting
10. Operational Considerations
11. Summary

1. Introduction

Oracle Database is widely used by many companies as a mission-critical system. Therefore, high availability and performance are extremely important. Proper configuration of failover and load balancing in listener connections is the key to achieving these goals.

For more details about the listener, see:
Oracle Listener: Complete Guide from Basics to Best Practices

2. Basic Concepts of Failover

What is Failover?

Failover is the capability to automatically switch to another system when part of the system encounters a failure, allowing service to continue.
In Oracle Database, when the target database or network components fail, the client can automatically connect to another database instance to achieve high availability.

Types of Failover

Connect-Time Failover: If a failure occurs during a new connection attempt, automatically switch to another destination.
Session Failover: If an existing session is disconnected, automatically reconnect.
Transaction Failover: If a failure occurs during a transaction, re-execute the transaction.

3. Basic Concepts of Load Balancing

What is Load Balancing?

Load balancing is the capability to distribute client connections or load evenly across multiple servers or instances.
This prevents the concentration of load on a particular server and improves overall performance.

Types of Load Balancing

Client-Side Load Balancing: The client selects a connection destination randomly or according to a specific algorithm.
Server-Side Load Balancing: The server (listener) receives the connection request and assigns it to the appropriate instance.

4. Implementing Failover via TNS Configuration

Oracle Database connection information is defined in the tnsnames.ora file.
By configuring failover here, you can achieve high availability at connection time.

Basic TNS Setting

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = host1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

Detailed Failover Setting
To enable failover, set (FAILOVER=ON).

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (FAILOVER = ON)
      (ADDRESS = (PROTOCOL = TCP)(HOST = host1)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = host2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

Key Points:

Define multiple ADDRESS entries in ADDRESS_LIST to specify failover destinations.
FAILOVER=ON automatically switches to the next destination if the primary connection fails.

5. Implementing Load Balancing via TNS Configuration

To enable load balancing, set (LOAD_BALANCE=ON).

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (LOAD_BALANCE = ON)
      (ADDRESS = (PROTOCOL = TCP)(HOST = host1)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = host2)(PORT = 1522))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

Key Points:

With LOAD_BALANCE=ON, the client randomly selects a server for each connection.

Configuration Notes:

When using both failover and load balancing, pay attention to the order and combination of settings.
Consider server-side configuration and resource conditions to ensure balanced load distribution.

6. Combining Failover and Load Balancing

By combining both functions, you can achieve high availability and performance at the same time.

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (LOAD_BALANCE = ON)
      (FAILOVER = ON)
      (ADDRESS = (PROTOCOL = TCP)(HOST = host1)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = host2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

Behavior:

The client randomly selects a server at connection time.
If the selected server is unavailable, it will attempt to connect to the next server.

Best Practices for Combined Settings:

Set both LOAD_BALANCE and FAILOVER to ON.
Regularly monitor server resources and network status to ensure proper load distribution.

7. Using TAF (Transparent Application Failover)

Overview of TAF

TAF automatically reconnects and continues processing when an existing session is disconnected.
Also known as session failover, it offers higher availability than connect-time failover.

How to Configure TAF and Key Considerations

Add the following settings to the tnsnames.ora file:

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = host1)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = host2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
      (FAILOVER_MODE =
        (TYPE = SELECT)
        (METHOD = BASIC)
        (RETRIES = 180)
        (DELAY = 5)
      )
    )
  )

Parameter Description:

TYPE: SESSION or SELECT. SELECT re-executes a query during fetch.
METHOD: BASIC or PRECONNECT. PRECONNECT establishes a secondary connection in advance but consumes more resources.
RETRIES: Number of reconnection attempts.
DELAY: Interval (seconds) between reconnection attempts.

Notes:

TAF does not re-execute DML operations (INSERT, UPDATE, DELETE).
Application-level handling may be required.

8. Practical Configuration Examples

Single Instance Environment

Even in a single database instance, you can achieve failover by building a standby environment on another host.

RAC (Real Application Clusters) Environment

In RAC, multiple instances share the same database.
The following settings efficiently handle both load balancing and failover:

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (LOAD_BALANCE = ON)
      (FAILOVER = ON)
      (ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = node2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
      (FAILOVER_MODE =
        (TYPE = SELECT)
        (METHOD = BASIC)
        (RETRIES = 5)
        (DELAY = 10)
      )
    )
  )

9. Troubleshooting

Common Issues and Solutions

Failover does not occur: Check for missing FAILOVER=ON or incorrect hostname/port.
Load balancing not working: Confirm LOAD_BALANCE=ON is set correctly; ensure the server-side listener is running.
TAF not functioning: Verify FAILOVER_MODE parameters and correct service name.

Log Check Methods

Client-side log: sqlnet.log
Server-side logs: listener.log, alert.log

10. Operational Considerations

Performance Monitoring: Monitor the effects of load balancing regularly; adjust settings as needed.
Use tools like Oracle Enterprise Manager or Statspack.
Regular Configuration Review: Review tnsnames.ora and listener.ora settings when expanding or changing the system.
Keep failover destinations and load balancing target servers up to date.

11. Summary

Proper configuration of failover and load balancing in listener connections directly leads to high availability and performance improvement for Oracle Databases.
Refer to the configuration methods and points described in this article to build an optimal configuration that matches your system requirements.

[reference]
7 Oracle Net Listener Parameters in the listener.ora File