<local-tx-datasource> vs <xa-datasource>

• <local-tx-datasource> is used for a single JBoss App Server.
• <xa-datasource> is used in a clustered JBoss App Server environment.
• Note that both <local-tx-datasource> and <xa-datasource> handle distributed transactions involving multiple data source\

Preventing Stale Connections

For this you can add the following to the datasource (-ds.xml):

<validation>
<valid-connection-checker 
class-name="org.jboss.jca.adapters.jdbc.vendor.OracleValidConnectionChecker"/>
<check-valid-connection-sql>
select 1 from dual
</check-valid-connection-sql>
<stale-connection-checker 
class-name="org.jboss.jca.adapters.jdbc.vendor.OracleStaleConnectionChecker"/>
<exception-sorter 
class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleExceptionSorter"/>
</validation>

The above has performance implications:

• The check-valid-connection-sql, wil be invoked everytime a Connection is borrowed from the Pool.
• Therefore, this should not be used in heavy load scenarios.

Fail-Over with Oracle RAC

This is possible via one of these ways:

• FCF – Fast Connection Failover.
• TAF – Transparent Application Failover.

FCF

• This is possible using JDBC-Thin (Type 4) driver (typically used with this).
• It is event based, and supports load-balancing across nodes.
• Works with help of ONS – Oracle Notification Service. A Service that runs in Oracle Database notifying interested parties about Oracle RAC Node status
• On the client side you need: ojdbcN.jar and ons.jar
• Do we need ons.config file?
• A datasource that supports Fast Connection Failover is needed. (it will use ons internally to achieve this?)
• Tomcat datasource configuration provides support for this. JBoss datasource configuration (eg. oracle-ds.xml) support is not known.
• Perhaps you can programatically setup the datasource and achieve this.
• Read Steps here: http://www.drdobbs.com/jvm/jdbc-fast-connection-failover-with-oracl/222700353
• What happens to the Connection Pool when a node goes down, does it automatically get refreshed?
• What is the role of ucp here?

Tomcat datasource config:

<Resource name="JDBC/Pool" auth="Container"
 ONSConfiguration="nodes=rac1:6200,rac2:6200"
 connectionCachingEnabled="true"
 description="FCF Datasource"
 driverClassName="oracle.jdbc.OracleDriver"
 factory="oracle.jdbc.pool.OracleDataSourceFactory"
 fastConnectionFailoverEnabled="true"
 implicitCachingEnabled="true"
 connectionCacheProperties=
"(InitialLimit=5, MinLimit=5, MaxLimit=20, ConnectionWaitTimeout=30)"
 connectionCacheName="tomcatConnectionCache"
 user="username" password="password"
 type="oracle.jdbc.pool.OracleDataSource"
 url="jdbc:oracle:thin:@(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = SERVICE)))"
/>

Ref: http://keremerkan.net/posts/supporting-oracle-fcf-in-tomcat/

Spring Datasource Configuration:

<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:orcl="http://www.springframework.org/schema/data/orcl"
       xsi:schemaLocation=
"http://www.springframework.org/schema/beans 
http://www.springframework.org/schema/beans/spring-beans-3.0.xsd 
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-3.0.xsd
http://www.springframework.org/schema/data/orcl
http://www.springframework.org/schema/data/orcl/spring-data-orcl-1.0.xsd">

    <orcl:pooling-datasource id="racDataSource"
        url="jdbc:oracle:thin:@(description=(address_list=
            (address=(host=rac1)(protocol=tcp)(port=1521))
            (address=(host=rac2)(protocol=tcp)(port=1521)))
            (connect_data=(service_name=racdb1)))"
        properties-location="classpath:orcl.properties"
        fast-connection-failover-enabled="true" 
        ONS-configuration="rac1:6200,rac2:6200"/> 

    <bean id="transactionManager" 
          class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="racDataSource"/>
    </bean>
</beans>

Ref: http://static.springsource.org/spring-data/jdbc/docs/current/reference/html/orcl.failover.html

TAF

• This is possible only using Oracle OCI Thick Client.
• The fail-over happens from within OCI driver.
• The tnsnames.ora file has the necessary configuration for fail-over across nodes.
• The failover is configured in tnsnames.ora file, the TAF settings are placed in CONNECT_DATA section of the tnsnames.ora using FAILOVER_MODES parameters.
• Your datasource configuration (in tomcat or say in jboss oracle-ds.xml) remains same as what it would be to connect via OCI driver.
• Only SELECT Queries can be retried in case of fail-over. DML (insert/update/delete) are not retried, instead Exception is thrown to the application.

Example Config:

TESTDB1 =
(DESCRIPTION =
(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.easyoradba.com)
(FAILOVER_MODE = (TYPE = SELECT)(METHOD = PRECONNECT)(BACKUP=TESTDB2))
)
)

The FAILOVER_MODE can be: SESSION/SELECT/NONE

• SESSION: Ideal for OLTP systems where transactions are small. In case of failover, a new session is automatically created for the user on the backup node.
• SELECT: If the connection is lost, Oracle Net establishes a connection to another node and re-executes the SELECT statements with cursor positioned on the row on which it was positioned prior to the failover. This mode involves overhead on the client side and Oracle NET keeps track of SELECT statements. This approach is best for data warehouse systems, where the transactions are big and complex.
• NONE: Default. Prevents failover.