jdbc连接rac

1、按顺序尝试每个地址，直到有一个成功
tnsnames.ora 里面的配置是这样的
RAC =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.102.202)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.102.200)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = sun)
    )
  )
默认情况下,LOAD_BALANCE=no,FAILOVER=true,SOURCE_ROUTE=no

 

2、随机尝试每个地址，直到成功
tnsnames.ora 里面的配置是这样的
RAC =
  (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.102.202)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.102.200)(PORT = 1521))
      (LOAD_BALANCE = yes)
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = sun)
    )
  )
 

3、随机选择尝试一个地址
tnsnames.ora 里面的配置是这样的
RAC =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.102.202)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.102.200)(PORT = 1521)))
      (LOAD_BALANCE = yes)
      (FAILOVER = false)
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = sun)
    )
  )

4、按顺序使用每个地址，直到到达目标
tnsnames.ora 里面的配置是这样的
RAC =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.102.202)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.102.200)(PORT = 1521))
      (SOURCE_ROUTE = yes)
      (FAILOVER = false)
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = sun)
    )
  )

5、只使用第一个地址
RAC =
  (DESCRIPTION =
    (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.102.202)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.102.200)(PORT = 1521))
      (FAILOVER = false)
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = sun)
    )
  )

根据网上的提示

jdbc:oracle:thin@(DESCRIPTION =(ADDRESS_LIST=(ADDRESS=(PROTOCOL = TCP)(HOST = 192.168.102.202)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.102.200)(PORT = 1521))(LOAD_BALANCE = no)(FAILOVER=YES))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = jrtdbtest)))

或者
core.db.url=jdbc:oracle:thin@(DESCRIPTION =(ADDRESS_LIST=(ADDRESS=(PROTOCOL = TCP)(HOST = dbserver1-vip)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = dbserver2-vip)(PORT = 1521))(LOAD_BALANCE = no)(FAILOVER=YES))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = jrtdbtest)))
报错：java.sql.SQLException: Io exception: NL Exception was generated
根据实验，居然是url中的空格引起的

通过网络查找及查询数据库，数据库的rac信息如下：

"./10g/db/network/admin/tnsnames.ora" 40 lines, 1003 characters 
# tnsnames.ora Network Configuration File: /oracle/10g/db/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LISTENERS_JRTDB =
  (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dbserver1-vip)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = dbserver2-vip)(PORT = 1521))
  )

JRTDB2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dbserver2-vip)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = jrtdb)
      (INSTANCE_NAME = jrtdb2)
    )
  )

JRTDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dbserver1-vip)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = jrtdb)
      (INSTANCE_NAME = jrtdb1)
    )
  )

JRTDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dbserver1-vip)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = dbserver2-vip)(PORT = 1521))
    (LOAD_BALANCE = yes)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = jrtdb)
    )
  )

:q
[jrtdb2]@dbserver2[/oracle]$ping dbserver1-vip
PING dbserver1-vip: (192.168.102.201): 56 data bytes
64 bytes from 192.168.102.201: icmp_seq=0 ttl=255 time=0 ms
64 bytes from 192.168.102.201: icmp_seq=1 ttl=255 time=0 ms

----dbserver1-vip PING Statistics----
2 packets transmitted, 2 packets received, 0% packet loss
round-trip min/avg/max = 0/0/0 ms
[jrtdb2]@dbserver2[/oracle]$vi /etc/hosts
"/etc/hosts" [Read only] 61 lines, 2096 characters 
# @(#)47        1.1  src/bos/usr/sbin/netstart/hosts, cmdnet, bos530 7/24/91 10:00:46
# IBM_PROLOG_BEGIN_TAG
# This is an automatically generated prolog.
#
# bos530 src/bos/usr/sbin/netstart/hosts 1.1
#
# Licensed Materials - Property of IBM
#
# (C) COPYRIGHT International Business Machines Corp. 1985,1989
# All Rights Reserved
#
# US Government Users Restricted Rights - Use, duplication or
# disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
#
# IBM_PROLOG_END_TAG
#
# COMPONENT_NAME: TCPIP hosts
#
# FUNCTIONS: loopback
#
# ORIGINS: 26  27
#
# (C) COPYRIGHT International Business Machines Corp. 1985, 1989
# All Rights Reserved
# Licensed Materials - Property of IBM
#
# US Government Users Restricted Rights - Use, duplication or
# disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
#
#  /etc/hosts
#
# This file contains the hostnames and their address for hosts in the
# network.  This file is used to resolve a hostname into an Internet
# address.
#
# At minimum, this file must contain the name and address for each
# device defined for TCP in your /etc/net file.  It may also contain
# entries for well-known (reserved) names such as timeserver
# and printserver as well as any other host name and address.
#
# The format of this file is:
# Internet Address      Hostname        # Comments
# Items are separated by any number of blanks and/or tabs.  A '#'
# indicates the beginning of a comment; characters up to the end of the
# line are not interpreted by routines which search this file.  Blank
# lines are allowed.

# Internet Address      Hostname        # Comments
# 192.9.200.1           net0sample      # ethernet name/address
# 128.100.0.1           token0sample    # token ring name/address
# 10.2.0.2              x25sample       # x.25 name/address
127.0.0.1               loopback localhost      # loopback (lo0) name/address
# Public Network - (eth0)
192.168.102.200 dbserver1
192.168.102.202 dbserver2
# Private Interconnect - (eth1)
192.168.103.200 dbserver1-priv
192.168.103.202 dbserver2-priv
# Public Virtual IP (VIP) addresses
192.168.102.201 dbserver1-vip
192.168.102.203 dbserver2-vip

最终修改如下，网上还说不能用ip，但是测试用ip通过，如果用主机名的话，需要在etc/host下添加

jdbc:oracle:thin:@(DESCRIPTION =(ADDRESS_LIST =(ADDRESS=(PROTOCOL=TCP)(HOST=dbserver1-vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=dbserver2-vip)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=jrtdb))) 
jdbc:oracle:thin:@(DESCRIPTION =(ADDRESS_LIST =(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.102.200)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.102.202)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=jrtdb)))

LOAD_BALANCE
 FAILOVER
 

ON
 ON
 客户端新建连接随机尝试ADDRESS_LIST中的监听，直到连接到一个可用监听，或者连完列表中的所有监听,如没有一个监听可用，则返回错误信息
 
ON
 OFF
 客户端新建连接随机尝试ADDRESS_LIST中的任一监听,无论这个监听是否可用，如不可用直接返回错误。
 
OFF
 ON
 客户端新建连接按顺序尝试ADDRESS_LIST中的监听,直到连接到一个可用监听，或者连完列表中的所有监听如没有一个监听可用，则返回错误信息
 
OFF
 OFF
 客户端新建连接仅连接ADDRESS_LIST中第一个监听，无论这个监听是否可用，如不可用直接返回错误。

数据源连接数据库问题
最开始用的是dbcp连接数据库的，如下：

	<bean id="coreDataSource" class="org.apache.commons.dbcp.BasicDataSource"
		destroy-method="close">
		<property name="driverClassName" value="${core.db.driver}" />
		<property name="url" value="${core.db.url}"/>
		<property name="username" value="${core.db.username}" />
		<property name="password" value="${core.db.password}" />
		<property name="maxActive"><value>200</value></property>   
        <property name="initialSize"><value>20</value></property>   
        <property name="maxWait"><value>600</value></property>   
        <property name="maxIdle"><value>20</value></property>   
        <property name="minIdle"><value>3</value></property>  
        <property name="removeAbandoned"><value>true</value></property> 
        <property name="removeAbandonedTimeout"><value>180</value></property>   
        <property name="validationQuery"><value>select 1 from dual</value></property>

但是换了之后第一次连接失败，往后连接正出常，换了驱动也是一样，不知道怎么回事，是否是dbcp的bug,换了数据源后，该问题解决，如下Bonecp

		<!-- BoneCP数据源配置 -->
		<bean id="coreDataSource" class="com.jolbox.bonecp.BoneCPDataSource"
		destroy-method="close">
		<property name="driverClass" value="${core.db.driver}" />
		<property name="jdbcUrl" value="${core.db.url}"/>
		<property name="username" value="${core.db.username}" />
		<property name="password" value="${core.db.password}" />
		<!-- 检查数据库连接池中空闲连接的间隔时间，单位是分，默认值：240，如果要取消则设置为0 -->
		<property name="idleConnectionTestPeriod" value="60" />
		<!-- 连接池中未使用的链接最大存活时间，单位是分，默认值：60，如果要永远存活设置为0 -->
		<property name="idleMaxAge" value="240" />
		
		<!-- 每个分区最大的连接数 -->
		<property name="maxConnectionsPerPartition" value="20" />
		<!-- 每个分区最小的连接数 -->  
		<property name="minConnectionsPerPartition" value="3" />
		<!-- 分区数 ，默认值2，最小1，推荐3-4，视应用而定-->  
		<property name="partitionCount" value="3" />
		<!-- 每次去拿数据库连接的时候一次性要拿几个,默认值：2 -->  
		<property name="acquireIncrement" value="5" />
		<!-- 缓存prepared statements的大小，默认值：0 -->       
		<property name="statementsCacheSize" value="10" />
		<!-- 每个分区释放链接助理进程的数量，默认值：3，除非你的一个数据库连接的时间内做了很多工作，不然过多的助理进程会影响你的性能 -->    
		<property name="releaseHelperThreads" value="3" />

经测试发现，所有的参数应该是off,on如下：

jdbc:oracle:thin:@(DESCRIPTION =(ADDRESS_LIST =(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.150.203)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.150.201)(PORT=1521)))(SOURCE_ROUTE = off)(FAILOVER = on)(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=jrtdb)))

注：ip地址是rac虚拟地址