Hello,

We are using JBOSS 5.1.0. GA to host our main application.

Here is our current situation.

We have 25 JBOSS instances that are used for the online part of our web application. Users can make reports which are sent to a seperate JBOSS instance only used for these reports. This report instance has a connect string that looks for a listener on the database server that it connects too.

The load these reports generate tends to slow down our Oracle 11g database, so we decided to implement a failover mechanism. Meaning we want to send all our reports to a standby database.

So we edited the connectstring to use the failover mechanism. It looks like this:

<connection-url>jdbc:oracle:thin:@(DESCRIPTION=(FAILOVER=ON)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=Standby1)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=Online1)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=Report_service)))</connection-url>

This way the reports should first be sent to the standby1 db, and if thats down the online1 database.

We however have some problems getting this to work. When the listener is active on Online1, it does not send any reports to standby1 when we activate the listener service there.

We were thinking this was happening because our <check-valid-connection-sql> was giving a positive result, even tho the listener wasnt active. We were using the default select * from dual.

So we decided to chang the SQL statement so it looked for the listener being active.

<check-valid-connection-sql>select display_value from v$parameter where name='service_names' and display_value like '%Report_service%'</check-valid-connection-sql>

After changing this and starting the listener on Standby1, the reports were being sent to the Standby1 database, so we thought we had it working like we wanted to. But when we stopped the listener on Standby1, reports were still being sent there.

Here is the content of our datasource from teh instance that handles the reports:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

<datasources>

    <local-tx-datasource>

        <jndi-name>ReportDS</jndi-name>

        <rar-name>jboss-local-jdbc.rar</rar-name>

        <use-java-context>true</use-java-context>

        <connection-definition>javax.sql.DataSource</connection-definition>

        <jmx-invoker-name>jboss:service=invoker,type=jrmp</jmx-invoker-name>

        <min-pool-size>0</min-pool-size>

        <max-pool-size>20</max-pool-size>

        <blocking-timeout-millis>30000</blocking-timeout-millis>

        <idle-timeout-minutes>30</idle-timeout-minutes>

        <prefill>false</prefill>

        <background-validation>false</background-validation>

        <background-validation-millis>0</background-validation-millis>

        <validate-on-match>true</validate-on-match>

        <statistics-formatter>org.jboss.resource.statistic.pool.JBossDefaultSubPoolStatisticFormatter</statistics-formatter>

        <isSameRM-override-value>false</isSameRM-override-value>

        <allocation-retry>0</allocation-retry>

        <allocation-retry-wait-millis>5000</allocation-retry-wait-millis>

        <application-managed-security xsi:type="securityMetaData" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"/>

        <metadata>

            <type-mapping>Oracle9i</type-mapping>

        </metadata>

        <type-mapping>Oracle9i</type-mapping>

        <local-transaction/>

        <user-name>**********</user-name>

        <password>*********</password>

        <new-connection-sql>alter session set nls_date_format='DD-MM-YYYY HH24:MI:SS'</new-connection-sql>

        <check-valid-connection-sql>select display_value from v$parameter where name='service_names' and display_value like '%Report_service%'</check-valid-connection-sql>

        <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>

        <prepared-statement-cache-size>50</prepared-statement-cache-size>

        <share-prepared-statements>true</share-prepared-statements>

        <set-tx-query-timeout>false</set-tx-query-timeout>

        <query-timeout>0</query-timeout>

        <use-try-lock>60000</use-try-lock>

        <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>

        <connection-url>jdbc:oracle:thin:@(DESCRIPTION=(FAILOVER=ON)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=Standby1)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=Online1)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=Report_service)))</connection-url>

    <connection-property name="SQLnetDef.TCP_CONNTIMEOUT_STR">5000</connection-property>

    </local-tx-datasource>

</datasources>

Any ideas how we can get the failover to work correctly?

i.e. When the listener on Standby1 is on, send reports there. When the listener is down, send reports to Online1.

Thank you!