Tuesday, January 26, 2010

Oracle RAC load balancing and failover

LOAD BALANCING: The Oracle RAC system can distribute the load over many nodes this feature called as load balancing.

There are two methods of load balancing
1.Client load balancing
2.Server load balancing

Client Load Balancing distributes new connections among Oracle RAC nodes so that no one server is overloaded with connection requests and it is configured at net service name level by providing multiple descriptions in a description list or multiple addresses in an address list. For example, if connection fails over to another node in case of failure, the client load balancing ensures that the redirected connections are distributed among the other nodes in the RAC.

Configure Client-side connect-time load balancing by setting LOAD_BALANCE=ON in the corresponding client side TNS entry.

TESTRAC =
(DESCRIPTION =
(ADDRESS_LIST=
(LOAD_BALANCE = ON)
(ADDRESS = (PROTOCOL = TCP)(HOST = TESTRAC1-VIP)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = TESTRAC2-VIP)(PORT = 1521))
)
(CONNECT_DATA = (SERVICE_NAME = testdb.oracleracexpert.com))
)

Server Load Balancing distributes processing workload among Oracle RAC nodes. It divides the connection load evenly between all available listeners and distributes new user session connection requests to the least loaded listener(s) based on the total number of sessions which are already connected. Each listener communicates with the other listener(s) via each database instance’s PMON process.

Configure Server-side connect-time load balancing feature by setting REMOTE_LISTENERS initialization parameter of each instance to a TNS name that describes list of all available listeners.

TESTRAC_LISTENERS =
(DESCRIPTION =
(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = TESTRAC1)(PORT = 1521)))
(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = TESTRAC2)(PORT = 1521))))
)

Set *.remote_listener= TESTRAC_LISTENERS’ initialization parameter in the database’s shared SPFILE and add TESTRAC_LISTENERS’ entry to the TNSNAMES.ORA file in the Oracle Home of each node in the cluster.

Once you configure Server-side connect-time load balancing, each database’s PMON process will automatically register the database with the database’s local listener as well as cross-register the database with the listeners on all other nodes in the cluster. Now the nodes themselves decide which node is least busy, and then will connect the client to that node.

FAILOVER:

The Oracle RAC system can protect against failures caused by O/S or server crashes or hardware failures. When a node failure occurs in RAC system, the connection attempts can fail over to other surviving nodes in the cluster this feature called as Failover.

There are two methods of failover
1. Connection Failover
2. Transparent Application Failover (TAF)

Connection Failover - If a connection failure occurs at connect time, the application failover the connection to another active node in the cluster. This feature enables client to connect to another listener if the initial connection to the first listener fails.

Enable client-side connect-time Failover by setting FAILOVER=ON in the corresponding client side TNS entry.

TESTRAC =
(DESCRIPTION =
(ADDRESS_LIST=
(LOAD_BALANCE = ON)
(FAILOVER = ON)
(ADDRESS = (PROTOCOL = TCP)(HOST = TESTRAC1-VIP)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = TESTRAC2-VIP)(PORT = 1521))
)
(CONNECT_DATA = (SERVICE_NAME = testdb.oracleracexpert.com))
)

If LOAD_BALANCE is set to on then clients randomly attempt connections to any nodes. If client made connection attempt to a down node, the client needs to wait until it receives the information that the node is not accessible before trying alternate address in ADDRESS_LIST.

Transparent application Failover (TAF) – If connection failure occurs after a connection is established, the connection fails over to other surviving nodes. Any uncommitted transactions are rolled back and server side program variables and session properties will be lost. In some case the select statements automatically re-executed on the new connection with the cursor positioned on the row on which it was positioned prior to the failover.

TESTRAC =
(DESCRIPTION =
(LOAD_BALANCE = ON)
(FAILOVER = ON)
(ADDRESS = (PROTOCOL = TCP)(HOST = TESTRAC1-VIP)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = TESTRAC1-VIP)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = testdb.oracleracexpert.com)
(FAILOVER_MODE = (TYPE = SELECT)(METHOD = BASIC)(RETRIES = 180)(DELAY = 5))
)
)

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com

19 comments:

  1. This is a nice post, thanks
    raju

    ReplyDelete
  2. Its really nice post .. but i have few queries on this above discussion .

    Are we giving the TAF configuration parameters under ORACLE HOME tns file or in the client side tns file ?

    Please correct me if am wrong .

    Thanks ,
    Senthil

    ReplyDelete
  3. Please note that your will configure TAF at client side.

    Regards
    Satishbabu Gunukula
    http://www.oracleracexpert.com

    ReplyDelete
  4. This comment has been removed by the author.

    ReplyDelete
  5. Is it mandatory to set the FAILOVER=ON in connection string even when we use FCF ? I mean while using FCF, failover is implied so it is still necessary to specify FAILOVER=ON in the connection string.

    ReplyDelete
  6. If you don't mention then it will take default value.

    Regards
    Satishbabu Gunukula
    http://www.oracleracexpert.com

    ReplyDelete
  7. use jdbc:oracle:thin:@ :

    Getting the hostname, SID,DB User, DB port
    from JAVA FLAGS:
    JAVA_FLAGS = -classpath $(CLASSPATH) -Dorg.omg.CORBA.ORBClass=IE.Iona.OrbixWeb.CORBA.ORB -Dorg.
    omg.CORBA.ORBSingletonClass=IE.Iona.OrbixWeb.CORBA.singletonORB -DRME_PORT="14150" -DRME_SERVER="efk
    xracs" -DDB_NAME="RMET1" -DDB_PORT="1526" -DDB_USER="rm" -DDB_PASS="rm"
    JAVAC_FLAGS = -d $(CLASS_DIR) -classpath $(CLASSPATH)

    How can I add another host and SID where load balance is turned on

    ReplyDelete
  8. Use below syntax when you configure connection to an Oracle RAC environment.

    jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST= rac1.domain.com)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST= rac2.domain.com)(PORT=1521))(FAILOVER=on)(LOAD_BALANCE=on)(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=dbservice)))

    Regards
    Satishbabu Gunukula
    http://www.oracleracexpert.com

    ReplyDelete
  9. Hi Satishbabu,
    Thanks so much for your reply.. My problem is I have this Makefile that has some info to make jdbc connection. It takes the -DRME_Server = db servername,-DDB_NAME which is SID and the port and db user info. Any idea how to add the syntax you mentioned to these settings in Makfile? Thanks so much.

    # JDK application settings
    #----------------------------------------------------------------------
    JAVA_FLAGS = -classpath $(CLASSPATH) -Dorg.omg.CORBA.ORBClass=IE.Iona.OrbixWeb.CORBA.ORB -Dorg.omg.CORBA.ORBSingletonClass=IE.Iona.OrbixWeb.CORBA.singletonORB -DRME_PORT="12502" -DRME_SERVER="efkxrme11" -DDB_NAME="rme1" -DDB_USER="rm" -DDB_PASS="rm"
    JAVAC_FLAGS = -d $(CLASS_DIR) -classpath $(CLASSPATH)
    #-----------------------------------------------

    ReplyDelete
  10. In its simplest form, for example one app service 2 node RAC, if the service is ever active in only one node, does that mean I will never have load balancing in place?

    ReplyDelete
  11. Thanks for your help. Nice post.

    ReplyDelete
  12. Hi. Suppose i have 80 clients and 4 nodes.
    So every node will have 20 connection each?
    If not exactly same, then how can we call it as LOAD BALANCE .. How will client choose to node accordingly??

    ReplyDelete
  13. Your blog provided us with valuable information to work with. I found so many interesting stuff in your blog especially it's discussion, Each & every tips of your post are awesome. I Really like this post, thanks for sharing. Read more

    ReplyDelete
  14. Hi sir,

    At the time of database creation one default tnsnames is there
    in that load_balance=on and FAILOVER=on parameters not set in tnsnames
    so we need to set those parameters or default load balancing option is there 12c ?

    ReplyDelete
  15. افضل واقوى الشركات التي تقدم خدمات مكافحة حشرات المنزل البق والصراصير والنمل والعته تلك التي تستخدم مبيدات آمنة وفعالة ومضمونة مثل اقوى شركه مكافحه حشرات بجده تقدم خدمات تتعامل في مكافحة الحشرات وتستخدم مبيدات آمنة ومضمونة

    ReplyDelete
  16. Great Article! In this competitive business world, no one can afford time so outsourcing data entry services becomes the most favorite term.

    ReplyDelete