JDBC Thin Driver and Tomcat

From: Kevin Hale Boyes <kcboyes_at_gmail.com>
Date: Wed, 20 May 2015 11:26:06 -0600
Message-ID: <CADAecHWz3rL5EGauXf4pbd4Wx51zgL-JcjisrK78CEKO_R-p4g_at_mail.gmail.com>

I'm setting up a new Tomcat 8 server and configuring the database resource connections.
I'm running Tomcat 8 on JDK 8 (1.8.0_45) and using ojdbc7.jar (or ojdbc7_g.jar).
The oracle server is: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
It's a RAC configuration with 3 nodes in the cluster.

I have the following Resource entry:

    <Resource

        name="datasource"
        auth="Container"
        type="javax.sql.DataSource"
        factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
        driverClassName="oracle.jdbc.OracleDriver"

        defaultAutoCommit="false"

        testWhileIdle="false"
        testOnBorrow="false"
        testOnReturn="false"
        validationQuery="SELECT 1 FROM DUAL"
        validationInterval="30000"
        timeBetweenEvictionRunsMillis="5000"
        minEvictableIdleTimeMillis="30000"

        initialSize="35"
        maxActive="100"
        minIdle="35"
        maxIdle="100"
        maxWait="300000"

        removeAbandoned="true"
        removeAbandonedTimeout="60"
        logAbandoned="true"

        jmxEnabled="true"

        username="username"
        password="password"

        url="jdbc:oracle:thin:_at_
            (DESCRIPTION=
                (ADDRESS_LIST=


                    (LOAD_BALANCE=ON)

                    (ADDRESS=(PROTOCOL=TCP)(HOST=host1)(PORT=1521))

                    (ADDRESS=(PROTOCOL=TCP)(HOST=host2)(PORT=1521))

                    (ADDRESS=(PROTOCOL=TCP)(HOST=host3)(PORT=1521))

                )
                (CONNECT_DATA=(SERVICE_NAME=servname))
            )"

    />

One of my questions has to do with the url setting. As you can see, I'm using a TN-like string to list all three nodes.

Have I put the LOAD_BALANCE setting in the correct place? The intention is that the driver sends queries to each node instead of sending all of the queries to a single node.

Is there any way for me to verify that it's working? i.e., can I see what host the driver is connected to for sending a query? I cranked up debugging on the driver and see a host name associated with a AUTH_SC_SERVER_HOST property but I only see that when the connection pool is being initialized. When I send a query I no longer see a host information.

I've also read a bit about the (FAILOVER=ON) setting and am unsure if it's supported or what it will do.

If I change the connection string to:

        url="jdbc:oracle:thin:_at_
            (DESCRIPTION=
                (ADDRESS_LIST=


                    (LOAD_BALANCE=ON)

                    (FAILOVER=ON)

                    (ADDRESS=(PROTOCOL=TCP)(HOST=host1)(PORT=1521))

                    (ADDRESS=(PROTOCOL=TCP)(HOST=host2)(PORT=1521))

                    (ADDRESS=(PROTOCOL=TCP)(HOST=host3)(PORT=1521))

                )
                (CONNECT_DATA=(SERVICE_NAME=servname))
            )"

What will adding the FAILOVER part do?
If a RAC node goes down and then comes back, will the driver recover and start sending queries to that node again?

Thanks,
Kevin.

--
http://www.freelists.org/webpage/oracle-l

Received on Wed May 20 2015 - 19:26:06 CEST