Lesson 5	Using load balancing
Objective	Configure a network for load balancing.

Configuring Loadbalancing in your Oracle Network

Connection load balancing is another interesting tool for spreading your connection load out more evenly. This concept is usually implemented for Oracle Parallel Servers, so we will discuss an Oracle Parallel Servers example in this lesson. The advantage of connection load balancing is that you can allow your network service (Network Services) to redirect connections to a database server that is less busy, even if that database server is on a different machine. The load balancing cycles through each dispatcher in the service and connects with the least busy dispatcher. In the case of Oracle Parallel Servers, it can be set up to cycle through all the dispatchers in other nodes in the Oracle Parallel Service, even if they are on a remote site's database.

Configuring Database Server
The following initialization parameters must be set for the database server to support load balancing:
1. SERVICE_NAMES for the database service name. This name will be shared among multiple database instances that are involved in load balancing.
2. INSTANCE_NAME for the current instance name

Configuring RMAN for Use with Shared Server in Oracle 11g R2

RMAN cannot connect to a target database through a shared server dispatcher. RMAN requires a dedicated server process. If your target database is configured for a shared server, then you must modify your Oracle Net configuration to provide dedicated server processes for RMAN connections. To ensure that RMAN does not connect to a dispatcher when a target database is configured for a shared server, the net service name used by RMAN must include (SERVER=DEDICATED) in the CONNECT_DATA attribute of the connect string. Oracle Net configuration varies greatly from system to system. The following procedure illustrates only one method. This scenario assumes that the following service name in tnsnames.ora file connects to a target database using the shared server architecture, where inst1 is a value of the SERVICE_NAMES initialization parameter:

inst1_shs =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=inst1_host)(port=1521))
(CONNECT_DATA=(SERVICE_NAME=inst1)(SERVER=shared))
)

To use RMAN with a shared server:
1. Create a net service name in the tnsnames.ora file that connects to the nonshared SID. For example, enter:

inst1_ded =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=inst1_host)(port=1521))
(CONNECT_DATA=(SERVICE_NAME=inst1)(SERVER=dedicated))
)

2. Start SQL*Plus and then connect using both the shared server and dedicated server service names to confirm the mode of each session. For example, connect with SYSDBA privileges to inst1_ded and then execute the following SELECT statement (sample output included):

SQL> SELECT SERVER
2 FROM V$SESSION
3 WHERE SID = (SELECT DISTINCT SID FROM V$MYSTAT);
SERVER
---------
DEDICATED
1 row selected.

To connect to a shared server session, you connect with SYSDBA privileges to inst1_shs and then execute the following SELECT statement (sample output included):

SQL> SELECT SERVER
2 FROM V$SESSION
3 WHERE SID = (SELECT DISTINCT SID FROM V$MYSTAT);
SERVER
---------
SHARED
1 row selected.

3. Start RMAN and connect to the target database using the dedicated service name. Optionally, connect to a recovery catalog. For example, enter:

% rman
RMAN> CONNECT TARGET SYS@inst1_ded
target database Password: password
connected to target database: INST1 (DBID=39525561)
RMAN> CONNECT CATALOG rman@catdb

Oracle Database Administration

Configuring the Listener

Each node's listener must contain the addresses of all the nodes that are to be shared in the connection load balancing. Here is an example of the listener.ora file^[1] configured so that two nodes participate in the connection load balancing.

LISTENER =
(DESCRIPTION =
(ADDRESS_LIST=
(ADDRESS = 
(PROTOCOL = TCP)
(HOST = ATLANTA.EPETS.COM)
(PORT = 1521))
(ADDRESS = 
(PROTOCOL = TCP)
(HOST = JACKSONVILLE.EPETS.COM)
(PORT = 1521))
)
)

Configuring Net
When configuring Oracle Network Services for connection load balancing^[2], you must work with the tnsnames.ora file. You can configure by directly editing the file, or you can use the Network Services Configuration Assistant. Due to some inconsistencies in the Network Services Configuration Assistant, we will continue to work directly with the tnsnames.ora file as we have throughout this module. Look at the MouseOver here to see an example of the listener.ora file.

Oracle tns names — tnsnames.ora configuration file entry named `PETS_USA`. This configuration defines connection parameters for Oracle Net Services and demonstrates connection load balancing and failover features.
**Breakdown and Analysis:**

✅ **1. `(LOAD_BALANCE=ON)`**

**Purpose**: Enables **client-side connection load balancing**.

**Effect**: Distributes incoming client connection requests across multiple listeners (in this case, the hosts listed in `ADDRESS_LIST`).

**Use Case**: Improves connection distribution and reduces overload on a single listener.

✅ **2. `(FAILOVER=ON)`**

**Purpose**: Enables **client-side failover**.

**Effect**: If a connection to one host fails, the client will attempt to connect to the next host in the list.

**Use Case**: Increases availability in case one database node or listener goes down.

✅ **3–4. `ADDRESS` Entries**

**3. `HOST = SEATTLE.HOUSEOPETS.COM`**

**4. `HOST = DAVIS.HOUSEOPETS.COM`**

These represent nodes in a multi-node Oracle RAC (Real Application Clusters) or a replicated environment.

✅ **5. `(SERVICE_NAME=HOUSEOPETS.COM)`**

**Purpose**: Specifies the **Oracle service** to connect to.

**Note**: This must match a service registered with the database's listener.

**Summary:**

Feature Enabled? Functionality

Load Balancing ✅ ON Distributes client connections across multiple nodes

Failover ✅ ON Attempts the next node if one is unavailable

Multiple Addresses ✅ Yes Points to multiple hosts for redundancy/load

Service Name ✅ Set Required for identifying the target database service

🛠️ Practical Use Case:
This setup is ideal for high availability and scalability, often used in environments like:

Oracle RAC clusters

Distributed or replicated databases

Applications requiring minimal downtime

This switch must be set ON to tell Network Services to use the connection load balancing feature

This states that if the chosen address fails to make a connection, other addresses listed will automatically be tried.

Here are two addresses listing two distinct database nodes that will participate in connection load balancing.

Here are two addresses listing two distinct database nodes that will participate in connection load balancing.

The service name for all the nodes must be the same. In this example, it is HOUSEOPETS.COM

Feature	Enabled?	Functionality
Load Balancing	✅ ON	Distributes client connections across multiple nodes
Failover	✅ ON	Attempts the next node if one is unavailable
Multiple Addresses	✅ Yes	Points to multiple hosts for redundancy/load
Service Name	✅ Set	Required for identifying the target database service

Once you have set up the tnsnames.ora file, you should use the same file on all the nodes that are involved in connection load balancing.
Can you validate the tnsnames.ora file entry based on an Oracle 19c environment?

The following section discusses how to configure load balancing.

Configure Oracle Connection and load balancing

The configuration for load balancing is achieved by editing the tnsnames.ora file.
The code you created should look something like this:

PETS_USA =
   (DESCRIPTION =
     (LOAD_BALANCE=ON)
     (FAILOVER=ON)
     (ADDRESS_LIST =
       (ADDRESS = 
        (PROTOCOL = TCP)
        (HOST = PORTLAND.HOUSEOPETS.COM)
        (PORT = 1521))
       (ADDRESS = 
        (PROTOCOL = TCP)
        (HOST = SEATTLE.HOUSEOPETS.COM)
        (PORT = 1521))
       (ADDRESS = 
        (PROTOCOL = TCP)
        (HOST = DAVIS.HOUSEOPETS.COM)
        (PORT = 1521))
     )
     (CONNECT_DATA =
      (SERVICE_NAME = HOUSEOPETS.COM)
     )
   )

Here are suggested answers to your questions.

List the other components that must be configured and the parameters you must use to complete the connection load balancing for the example in the simulation.

Answer 1: The database itself must be started up with these parameters in place: SERVICE_NAMES (set to a common name for all nodes); INSTANCE_NAME (a unique name for each instance); and MTS_DISPATCHERS (to configure the dispatchers for the database instance). Also, the listener for the database must be configured with an address entry for all of the nodes that are to share load balancing.

Why does load balancing make sense for a distributed set of servers that connects its nodes via the Internet?

Answer 2:Connection load balancing would help in two important ways. First, because the Internet can sometimes lose service to certain geographic areas, a node in the area that lost service can be supported by the databases in other geobraphic areas that did not lose service. Second, the database load would be distributed to all the nodes evenly, regardless of which node first received the connection request, so no single node becomes overloaded.
The next lesson covers automatic instance registration.

[1]listener.ora file: The listener.ora file is a configuration file within Oracle Network Services that defines the parameters of an Oracle Net Listener. Specifically, it specifies the network protocols and addresses that the listener uses to receive connection requests from client applications, and also the database services that the listener is to monitor.

[2]Connection load balancing: A feature of Network Services that reviews the load on each of a group of database services or database dispatchers, and then selects the least busy available service or dispatcher and assigns a new connection to it.