Oracle Network Administration  «Prev  Next»

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

Configuring Loadbalancing Network

Connection Load Balancing

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

Oracle Database Administration

Configuring RMAN for Use with Shared Server

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

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 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[1], 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.
load-balance-tnsnames
  1. This switch must be set ON to tell Network Services to use the connection load balancing feature
  2. This states that if the chosen address fails to make a connection, other addresses listed will automatically be tried.
  3. Here are two addresses listing two distinct database nodes that will participate in connection load balancing.
  4. Here are two addresses listing two distinct database nodes that will participate in connection load balancing.
  5. The service name for all the nodes must be the same. In this example, it is HOUSEOPETS.COM
tnsnames.ora file connection loadbalancing
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.

Configure Oracle Connection

Click the exercise link below to practice configuring load balancing.
Configure Oracle Connection
The next lesson covers automatic instance registration.

[1]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.