Re: JDBC Thin Driver and Tomcat

  • From: Stefan Koehler <contact@xxxxxxxx>
  • To: Kevin Hale Boyes <kcboyes@xxxxxxxxx>
  • Date: Wed, 20 May 2015 21:24:54 +0200 (CEST)

Hi Kevin,
at first RAC and JDBC Thin is usually not a good idea (e.g. no TAF support).
For example you can use FCF with UCP for Tomcat instead. Martin Bach
described this in his book "Pro Oracle Database 11g RAC on Linux" (
http://www.apress.com/9781430229582 ) on page 589.

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?

I assume you are using SCAN listeners (maybe with DNS round robin??), so
usually no need to. Workload management settings should be done at server
service side (= Server-Side Load Balancing) nowadays. The whole topic is also
covered in Martin Bach's book "Pro Oracle Database 11g RAC on Linux" on
page 573. Please also be aware that only 11.2.0.1 clients (or newer) support
SCAN (MOS ID #1150835.1 & #1365631.1).

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.

As i previously mentioned "Workload management settings should be done at
server service side (= Server-Side Load Balancing) nowadays". The
functionality you are looking for is load based FAN events (on page 585 in
Martin's book).

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

Depends on the connection pooling and used client/server technology.

I highly recommend Martin's book for setting up HA RAC infrastructures /
applications properly.

Best Regards
Stefan Koehler

Freelance Oracle performance consultant and researcher
Homepage: http://www.soocs.de
Twitter: @OracleSK

Kevin Hale Boyes <kcboyes@xxxxxxxxx> hat am 20. Mai 2015 um 19:26 geschrieben:

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:@
(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:@
(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.
--
//www.freelists.org/webpage/oracle-l


Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 05-2015