Auto recover Connections in Wildfly 8.2 for Oracle without using validate-on-match

4
votes

I am using wildfly 8.2 and oracle 11g and I have my connection pool flushing an invalid connection and creating a new one if possible. I'm using Spring and Hibernate at the back-end too (we are looking up the connection via JNDI)

I could not find a concise example of pool configuration for oracle on the net that I could get to work, so I cribbed bits and bobs from various sources and managed to get it working.

My question is this. I am setting "Validate On Match" equal to true, but the Jboss documention (https://docs.jboss.org/jbossas/docs/Server_Configuration_Guide/beta500/html/ch13s13.html) states that

<validate-on-match> - Prior to JBoss 4.0.5, connection validation occurred when the JCA layer attempted to match a managed connection. With the addition of <background-validation> this is no longer required. Specifying <validate-on-match> forces the old behavior. NOTE: this is typically NOT used in conjunction with <background-validation>

I would not like to use "old behaviour" if I can avoid it. Does anyone have an example of auto recovering connection pools with Oracle 11g Wildfly 8.2. and using the ojdbc6 driver.

Thanks

Btw my current set up for the pool is as below (nb I know I have the pool size set to 1, it was so I could easily kill the session and know the pool recovers). 

 <datasource jta="true" jndi-name="java:/jboss/datasources/mydatabase" pool-name="mydatabase" enabled="true" use-ccm="true">
                    <connection-url>jdbc:oracle:thin:@localhost:1521:mydatabase</connection-url>
                    <driver-class>oracle.jdbc.OracleDriver</driver-class>
                    <driver>ojdbc6-11.1.0.7.0.jar</driver>
                    <pool>
                        <min-pool-size>1</min-pool-size>
                        <max-pool-size>1</max-pool-size>
                        <flush-strategy>FailingConnectionOnly</flush-strategy>
                    </pool>
                    <security>
                        <user-name>mydatabase</user-name>
                        <password>mydatabase</password>
                    </security>
                    <validation>
                        <check-valid-connection-sql>SELECT 1 FROM DUAL</check-valid-connection-sql>
                        <validate-on-match>true</validate-on-match>
                        <background-validation>false</background-validation>
                    </validation>
                    <timeout>
                        <set-tx-query-timeout>false</set-tx-query-timeout>
                        <blocking-timeout-millis>0</blocking-timeout-millis>
                        <idle-timeout-minutes>0</idle-timeout-minutes>
                        <query-timeout>0</query-timeout>
                        <use-try-lock>0</use-try-lock>
                        <allocation-retry>0</allocation-retry>
                        <allocation-retry-wait-millis>0</allocation-retry-wait-millis>
                    </timeout>
                    <statement>
                        <share-prepared-statements>false</share-prepared-statements>
                    </statement>
                </datasource>
            </datasources>

Cheer to anyone that can help

1
javaoraclejbossoracle11gconnection-pooling

1 Answers

0
votes

Try this validation node:

<validation>
    <background-validation>true</background-validation>
    <background-validation-millis>200000</background-validation-millis>
    <check-valid-connection-sql>select 1 from dual</check-valid-connection-sql>
</validation>

Remember to adjust the value of background-validation-millis.