Weblogic & Multipools....

Using MultiPools with Global Transactions

In this configuration, a MultiPool "pins" a transaction to one and only one Oracle RAC instance and failover is handled at the MultiPool level when a RAC instance becomes unavailable. If there is a failure on a RAC instance before PREPARE, the operation is retried until the retry duration has expired. If there is a failure after PREPARE the transaction is failed over to another instance.

Rules for Connection Pools within a MultiPool Using Global Transactions

The following rules apply to the XA connection pools within a MultiPool:

• All the connection pools must be homogeneous. In other words, either all of them must be XA connection pools or none of them can be XA connection pools.
• If you choose to specify them, all XA-related attributes must be set to the same values for each connection pool. The attributes include the following:
  • XARetryDurationSeconds
  • SupportsLocalTransaction
  • KeepXAConnTillTxComplete
  • NeedTxCtxOnClose
  • XAEndOnlyOnce
  • NewXAConnForCommit
  • RollbackLocalTxUponConnClose
  • RecoverOnlyOnce
  • KeepLogicalConnOpenOnRelease

Notes:

•  
  • WebLogic Server 8.1 SP5 is certified to support Multipools with XA only on Oracle RAC. For information on supported versions of Oracle RAC, see Supported Database Configurations.
  • If you are not using XA, BEA recommends the use of MultiPools for failover and load balancing across RAC instances, but the XA-specific configuration requirements above do not apply. For more information about configuring WebLogic JDBC MultiPools, see Configuring Multipools in the Administration Console Online Help.

Required Attributes of Connection Pools within a MultiPool Using Global Transactions

Each connection pool within the MultiPool should have the following attributes:

• Oracle JDBC Thin driver 10g. For example:

DriverName="oracle.jdbc.xa.client.OracleXADataSource"
URL="jdbc:oracle:thin:@db_server1:1521:SNRAC1"

• KeepXAConnTillTxComplete="true"
  • Forces the connection pool to reserve a physical database connection and provide the same connection to an application throughout transaction processing until the distributed transaction is complete.
  • Required for proper transaction processing with Oracle RAC.
• XARetryDurationSeconds="300"
  • Enables the WebLogic Server transaction manager to retry XA recover, commit, and rollback calls for the specified amount of time.
• CountOfTestFailuresTillFlush="1"
  • Enables WebLogic Server to close all connections in the connection pool after the number of test failures that you specify to minimize the delay caused by further database testing. See "JDBC Connection Pool Testing Enhancements" in Programming WebLogic JDBC for more details about this attribute.
  • Minimizes the failover time when an Oracle RAC node fails.
• TestConnectionsOnReserve="true"
  • Enables testing of a database connection when an application reserves a connection from the connection pool. See "Testing Connection Pools and Database Connections" in Programming WebLogic JDBC for more details about this attribute.
  • Required to enable failover to another RAC node.
• TestTableName="name_of_small_table" The name of the table used to test a physical database connection. For more details about this attribute, see "JDBC Connection Pool Configuration Connections" in the Administration Console Online Help.

Sample config.xml Code

An example of the connection pools, a WebLogic JDBC MultiPool, and an associated data source in the config.xml file would be:

<JDBCConnectionPool 
	CapacityIncrement="1"
	ConnLeakProfilingEnabled="true" 
	CountOfTestFailuresTillFlush="1"
	DriverName="oracle.jdbc.xa.client.OracleXADataSource"
	InitialCapacity="5" 
	KeepXAConnTillTxComplete="true" 
	MaxCapacity="100"
	Name="jdbcXAPool1" 
	PasswordEncrypted="{3DES}lBifoTsg8fc="
	Properties="user=wlsqa"
	RefreshMinutes="1" SupportsLocalTransaction="true"
	Targets="WLSCluster" 
	TestConnectionsOnReserve="true"
	TestTableName="dual"
	URL="jdbc:oracle:thin:@db_server1:1521:SNRAC1"
	XAEndOnlyOnce="true" 
	XARetryDurationSeconds="300" 
	XASetTransactionTimeout="true"/>
	XATransactionTimeout="302"/>

<JDBCConnectionPool 
	CapacityIncrement="1"
	ConnLeakProfilingEnabled="true" 
	CountOfTestFailuresTillFlush="1"
	DriverName="oracle.jdbc.xa.client.OracleXADataSource"
	InitialCapacity="5" 
	KeepXAConnTillTxComplete="true" 
	MaxCapacity="100"
	Name="jdbcXAPool2" 
	PasswordEncrypted="{3DES}lBifoTsg8fc="
	Properties="user=wlsqa"
	RefreshMinutes="1" 
	SupportsLocalTransaction="true"
	Targets="WLSCluster" 
	TestConnectionsOnReserve="true"
	TestTableName="dual"
	URL="jdbc:oracle:thin:@db_server2:1521:SNRAC2"
	XAEndOnlyOnce="true" 
	XARetryDurationSeconds="300" 
	XASetTransactionTimeout="true"/> 
	XATransactionTimeout="302"/>

<JDBCMultiPool 
	Name="jdbcXAMultiPool1"
	PoolList="jdbcXAPool1,jdbcXAPool2" 
	Targets="WLSCluster"
	AlgorithmType="Load-Balancing"/>

<JDBCTxDataSource 
	JNDIName="jdbcXADataSource1" 
	Name="jdbcXADataSource1"
	PoolName="jdbcXAMultiPool1" 
	Targets="WLSCluster"/>

Note: Line breaks added for readability.

Using MultiPools without Global Transactions

The following sections describe a configuration that uses Oracle RAC with MultiPools in an application that does not require global transactions.

Attributes of Connection Pools within a MultiPool Not Using Global Transactions

Connection pools must have the following attributes:

• Oracle JDBC Thin driver 10g. For example:

DriverName="oracle.jdbc.OracleDriver"
URL="jdbc:oracle:thin:@db_server1:1521:SNRAC1"

• TestConnectionsOnReserve="true"
  • Enables testing of a database connection when an application reserves a connection from the connection pool. See "Testing Connection Pools and Database Connections" in Programming WebLogic JDBC for more details about this attribute.
  • Required to enable failover and connection request routing within a MultiPool (effectively, failover to another RAC node).
• TestTableName="name_of_small_table"
  • The name of the table used to test a physical database connection. For more details about this attribute, see "JDBC Connection Pool Configuration Connections" in the Administration Console Online Help.

Sample config.xml Code

An example of the connection pools, a WebLogic JDBC MultiPool, and an associated data source in the config.xml file would be:

<JDBCConnectionPool 
        Name="oracleRACPool_1"
        DriverName="oracle.jdbc.OracleDriver"
        InitialCapacity="5"
        MaxCapacity="100"
        Password="{3DES}I5fj3vh4+nI=" 
        Properties="user=SCOTT"
        RefreshMinutes="5"
        TestConnectionsOnReserve="true"
        TestTableName="dual"
        Targets="myWebLogicCluster"
        URL="jdbc:oracle:thin:@dbhost1:1521:dbservice"
        />

<JDBCConnectionPool 
        Name="oracleRACPool_2"
        DriverName="oracle.jdbc.OracleDriver"
        InitialCapacity="5"
        LoginDelaySeconds="1" 
        MaxCapacity="5"
        Password="{3DES}I5fj3vh4+nI=" 
        Properties="user=SCOTT"
        RefreshMinutes="5"
        TestConnectionsOnReserve="true"
        TestTableName="dual"
        PreparedStatementCacheSize="15"
        Targets="myWebLogicCluster"
        URL="jdbc:oracle:thin:@dbhost2:1521:dbservice"
        />

<JDBCMultiPool 
        AlgorithmType="Load-Balancing"
        Name="MyJDBCMultiPool" 
        HealthCheckFrequencySeconds="300"
        PoolList="oracleRACPool_1,oracleRACPool_2" 
        Targets="myWebLogicCluster"
        />

<JDBCDataSource 
        JNDIName="oracleRACDataSource"
        Name="oracleRACDataSource" 
        PoolName="MyJDBCMultiPool" 
        Targets="myWebLogicCluster"
        />

Note: Line breaks added for readability.