Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 

 

Listener Load Balancing

Oracle RAC Cluster Tips by Burleson Consulting

This is an excerpt from the bestselling book Oracle Grid & Real Application Clusters.  To get immediate access to the code depot of working RAC scripts, buy it directly from the publisher and save more than 30%.


Oracle10g provides virtually the same listener connection load balancing feature is provided as was available in 9i. This feature is utilized to increase connection performance through the balancing, almost like the time share networks of the past, the active connections between multiple user defined dispatchers and instances. When utilizing load balancing in a single-instance environment, the listener process selects the dispatcher to handle the incoming client requests based on its percent busy. When moving into an Oracle Real Application Clusters environment, this connection load balancing process also provides the capability to spread the number of active connections using a pseudo-round-robin methodology between the instances in the RAC cluster based on entries in the tnsnames file.

Dynamic service registration (DSR) allows a listener to always know of all instances, and when multi-threaded server (MTS) is utilized, the listener is also always aware of the database dispatcher processes, no matter where they are running. Oracle uses the following criteria when deciding which dispatcher to utilize:

* The least loaded node

* The least loaded instance

* The least loaded dispatcher for that instance

 If, on the other hand, a dedicated server configuration is being utilized, the listener uses this reduced set of criteria.  OF course, dispatchers need not be considered if they are not being used:

* The least loaded node

* The least loaded instance

In Oracle versions lower than 9.0.1.3, for dedicated connections, the server side load balancing will route most connections to one node. This is due to bug 2134254. The workaround is to add dispatchers="(pro=ipc)(dis=0)" in the init.ora file.

The following script includes an example listener.ora.  This script shows how easy the setup of the listener file can be.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
      )
      (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = TCP)(HOST = burllinux1)(PORT = 1521))
      )
    )
  )
 
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/oracle/product/9.2.0)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (ORACLE_HOME = /u01/oracle/product/9.2.0)
      (SID_NAME = burl1)
    )
  )

Example TAF Configurations

The next few sections will provide example TAF configurations:

* Connect-Time Failover with Client Load Balancing

* Retry of connections

* Pre-established failover connections

* TAF with Connect-Time Failover and Client Load Balancing

To implement TAF with connect-time failover and client load balancing for multiple addresses, the tnsnames.ora files should be configured similar to the script shown below, in which Oracle Net connects randomly to one of the protocol addresses on burllinux1 or burllinux2. If the instance were to fail after the connection, the TAF application fails over to the other node's listener, reissuing any SELECT statements in flight.

burl=
 (DESCRIPTION=
  (LOAD_BALANCE=on)
  (FAILOVER=on)
  (ADDRESS=
       (PROTOCOL=tcp) 
       (HOST=burllinux1) 
       (PORT=1521))
  (ADDRESS=
       (PROTOCOL=tcp) 
       (HOST=burllinux2) 
       (PORT=1521))
  (CONNECT_DATA=
     (SERVICE_NAME=burl)
     (FAILOVER_MODE=
       (TYPE=select)
       (METHOD=basic))))

Configuring TAF to Retry a Connection

With a proper configuration, TAF will automatically retry connecting if the first connection attempt fails. The tnsnames.ora file is configured with the retries and delay parameters. In the script below, Oracle Net tries to reconnect to the listener on the burllinux1 server. If the failover connection fails, Oracle Net waits ten seconds before trying to reconnect again. Using this tnsnames.ora file, Oracle Net attempts to reconnect up to ten times.

burl=
 (DESCRIPTION=
  (ADDRESS=
       (PROTOCOL=tcp) 
       (HOST=burllinux1) 
       (PORT=1521))
  (CONNECT_DATA=
     (SERVICE_NAME=burl)
     (FAILOVER_MODE=
       (TYPE=select)
       (METHOD=basic)
       (RETRIES=10)
       (DELAY=10))))

Configuring TAF for Pre-Establishing Connections

TAF can be configured to create pre-established connections to a second instance. The initial and backup connections are explicitly specified. In the following example script, clients that use net service name burl1 to connect to the listener on burllinux1 are also preconnected to burllinux2. If the burllinux1 fails after the connection, Oracle Net automatically fails over to burllinux2, reissuing any SELECT statements in progress. In this reciprocal setup, Oracle Net preconnects to burllinux1 for those clients that use burl2 to connect to the listener on burllinux2.

burl1 =
 (DESCRIPTION=
  (ADDRESS=
       (PROTOCOL=tcp) 
       (HOST=burllinux1) 
       (PORT=1521))
  (CONNECT_DATA=
     (SERVICE_NAME=burl)
     (INSTANCE_NAME=burl1)
     (FAILOVER_MODE=
       (BACKUP=burl2)
       (TYPE=select)
       (METHOD=preconnect))))
burl2=
 (DESCRIPTION=
  (ADDRESS=
       (PROTOCOL=tcp) 
       (HOST=burl2) 
       (PORT=1521))
  (CONNECT_DATA=
     (SERVICE_NAME=burl)
     (INSTANCE_NAME=burl2)
     (FAILOVER_MODE=
       (BACKUP=burl1)
       (TYPE=select)
       (METHOD=preconnect))))

 


This is an excerpt from the bestselling book Oracle Grid & Real Application Clusters, Rampant TechPress, by Mike Burl and Madhu Tumma.

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

http://www.rampant-books.com/book_2004_1_10g_grid.htm


 

 
��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational