Java DAL that recovers from database outage?

Question

My DAL consists of Spring Data Access connecting to Oracle via Oracle's UCP. This means I am predominatly working with JdbcTemplate. A requirement for my server is to recover from a database outage, more specifically, if we have an outage our server can, obviously, throw many exceptions, however, once the database is back online the server should function perfectly once again.

Will JdbcTemplate in conjunction with UCP handle this for me? In other words, will JdbcTemplate throw exceptions while the database is down but then function once the databasde recovers (it is backed by UCP so I am hoping this is the case) or is there something else I need to do, like create a new UCP factory for example?

Thanks for your time.

This sounds like a "try and see" kind of question, seeing as you already have an Oracle server and UCP available to simulate this situation. — millimoose, Feb 12 '13 at 17:38
Agreed, unfortunately, the model where I work is to have one very powerful database server, across the firm, controlled by the DBAs. This means producing an outage is a logistical issue as it will impact everyone, so I was hoping someone else might already have their experience to share. — Mark, Feb 12 '13 at 18:26
I negotiated the creation of a backup with the DBAs in order to simulate an outage and will post my results shortly. — Mark, Feb 13 '13 at 19:39

score 3 · Answer 1 · edited Feb 13 '13 at 20:17

In order to simulate a database outage our DBAs turned off the relevant service account and killed all existing connections while our server was running. We then discovered that the answer to my own question, for the benefit of others, is that a JdbcTemplate will not automatically recover without the correct configuration.

My initial config was as follows:

<bean id="DataSource" class="oracle.ucp.jdbc.PoolDataSourceFactory" factory-method="getPoolDataSource">
    <property name="URL" value="${jdbc.url}"/>
    <property name="User" value="${jdbc.user}"/>
    <property name="Password" value="${jdbc.password}"/>
    <property name="ConnectionFactoryClassName" value="oracle.jdbc.pool.OracleDataSource"/>
    <property name="ConnectionPoolName" value="FOX"/>
    <property name="MinPoolSize" value="1"/>
    <property name="MaxPoolSize" value="3"/>
    <property name="InitialPoolSize" value="1"/>
</bean>

<bean id="DataTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
    <constructor-arg ref="DataSource" />
</bean>

Unfortunately, in the case of an outage, this leaves dead connections in your pool, hence, our server did not recover. I imagine there are a number of different ways you could resolve this, possibly by purging the pool when you see an exception for example, however, setting ValidateConnectionOnBorrow to true was a simple change and had the desired effect:

<bean id="DataSource" class="oracle.ucp.jdbc.PoolDataSourceFactory" factory-method="getPoolDataSource">
    <property name="URL" value="${jdbc.url}"/>
    <property name="User" value="${jdbc.user}"/>
    <property name="Password" value="${jdbc.password}"/>
    <property name="ConnectionFactoryClassName" value="oracle.jdbc.pool.OracleDataSource"/>
    <property name="ConnectionPoolName" value="FOX"/>
    <property name="MinPoolSize" value="1"/>
    <property name="MaxPoolSize" value="3"/>
    <property name="InitialPoolSize" value="1"/>
    <property name="ValidateConnectionOnBorrow" value="true"/>
</bean>

<bean id="DataTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
    <constructor-arg ref="DataSource" />
</bean>

Now our pooled connetions are validated and when an outage occurs the broken connections are disposed. As a result, once the database recovered, our server was back online.

Java DAL that recovers from database outage?

1 Answers1