Using Oracle RAC with JDBC to enable HA/Load Balancing of Database Nodes is a tricky one. Following are the different ways that I have used to configure the JDBC URLs to use with RAC based Database.
1) Pinned DB Instance/Listener with failover / No Server side Instance Load Balancing
In this approach, the appserver nodes are pinned to a primary DB node. For example, if you have two appservers (app1/app2) and two DB nodes (db1/db2), then pin app1 to db1 to be used as primary DB node and db2 as secondary DB node (for failover) and vice versa for app2
On App2, the JDBC URL may look like
jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=off)(FAILOVER=ON)(ADDRESS=(PROTOCOL=TCP)(HOST=db02-vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=db01-vip)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=dbServiceName)))
Pros
- Interconnect traffic is minimized since global cache data doesnt need to be frequently exchanged between DB nodes
- 2PC can be avoided thus eliminating the dreaded "in-doubt distributed transaction" exception (ORA-1591)
Cons
- DB node load may not be even if appserver load is uneven
- Relatively longer failover time for the appserver connection pool to relinquish all the connections from dead node to active node, since all connections will be pointing to the primary node
2) Pinned Listener with failover / Server side Instance Load Balancing
In this approach, the appserver nodes are pinned to a primary listener (not the instance as above). Server Side Load Balancing must be enabled using REMOTE_LISTENER and Load Balancing Advisory options. In this method, any listener can be used to reach any of the DB instances (SIDs) and decision is based on the Load balancing advisory done at server side. No change in the client URL.
jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=off)(FAILOVER=ON)(ADDRESS=(PROTOCOL=TCP)(HOST=db02-vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=db01-vip)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=dbServiceName)))
Pros
- More interconnect traffic
- 2PC and "in-doubt transaction" exceptions (ORA-1591) can occur since the appserver connection pool can hold connections pointing to multiple instances
Cons
- DB node load balancing is relatively more accurate since LBA looks at actual load and routes the connection request at server side
- Relatively shorter failover time for the appserver connection pool since connections comes from multiple instances
- Listener load may not be even if appserver load is uneven
3) Driver based Listener Load Balancing / Server side Instance Load Balancing
This approach is similar to the previous one except the listener traffic is also load balanced (round robin) by the JDBC driver.
jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=ON)(FAILOVER=ON)(ADDRESS=(PROTOCOL=TCP)(HOST=db02-vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=db01-vip)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=dbServiceName)))
4) Using Oracle SCAN (From 11g onwards)
This approach is similar to above but the pain of configuring individual nodes/their VIPs in JDBC URLs are eliminated. All we need is a Round Robin enabled DNS name resolution and use the SCAN DNS name in the JDBC URLs.
jdbc:oracle:thin:@dbScanName:1521/dbServiceName
Note: Apart from above methods which are mostly generic, each appserver vendor have their own ways to use RAC DB. Example is Weblogic MDS.
1) Pinned DB Instance/Listener with failover / No Server side Instance Load Balancing
In this approach, the appserver nodes are pinned to a primary DB node. For example, if you have two appservers (app1/app2) and two DB nodes (db1/db2), then pin app1 to db1 to be used as primary DB node and db2 as secondary DB node (for failover) and vice versa for app2
On App2, the JDBC URL may look like
jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=off)(FAILOVER=ON)(ADDRESS=(PROTOCOL=TCP)(HOST=db02-vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=db01-vip)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=dbServiceName)))
Pros
- Interconnect traffic is minimized since global cache data doesnt need to be frequently exchanged between DB nodes
- 2PC can be avoided thus eliminating the dreaded "in-doubt distributed transaction" exception (ORA-1591)
Cons
- DB node load may not be even if appserver load is uneven
- Relatively longer failover time for the appserver connection pool to relinquish all the connections from dead node to active node, since all connections will be pointing to the primary node
2) Pinned Listener with failover / Server side Instance Load Balancing
In this approach, the appserver nodes are pinned to a primary listener (not the instance as above). Server Side Load Balancing must be enabled using REMOTE_LISTENER and Load Balancing Advisory options. In this method, any listener can be used to reach any of the DB instances (SIDs) and decision is based on the Load balancing advisory done at server side. No change in the client URL.
jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=off)(FAILOVER=ON)(ADDRESS=(PROTOCOL=TCP)(HOST=db02-vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=db01-vip)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=dbServiceName)))
Pros
- More interconnect traffic
- 2PC and "in-doubt transaction" exceptions (ORA-1591) can occur since the appserver connection pool can hold connections pointing to multiple instances
Cons
- DB node load balancing is relatively more accurate since LBA looks at actual load and routes the connection request at server side
- Relatively shorter failover time for the appserver connection pool since connections comes from multiple instances
- Listener load may not be even if appserver load is uneven
3) Driver based Listener Load Balancing / Server side Instance Load Balancing
This approach is similar to the previous one except the listener traffic is also load balanced (round robin) by the JDBC driver.
jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=ON)(FAILOVER=ON)(ADDRESS=(PROTOCOL=TCP)(HOST=db02-vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=db01-vip)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=dbServiceName)))
4) Using Oracle SCAN (From 11g onwards)
This approach is similar to above but the pain of configuring individual nodes/their VIPs in JDBC URLs are eliminated. All we need is a Round Robin enabled DNS name resolution and use the SCAN DNS name in the JDBC URLs.
jdbc:oracle:thin:@dbScanName:1521/dbServiceName
Note: Apart from above methods which are mostly generic, each appserver vendor have their own ways to use RAC DB. Example is Weblogic MDS.