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=no2、随机尝试每个地址，直到成功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-vipPING dbserver1-vip: (192.168.102.201): 56 data bytes64 bytes from 192.168.102.201: icmp_seq=0 ttl=255 time=0 ms64 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 lossround-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/address127.0.0.1               loopback localhost      # loopback (lo0) name/address# Public Network - (eth0)192.168.102.200 dbserver1192.168.102.202 dbserver2# Private Interconnect - (eth1)192.168.103.200 dbserver1-priv192.168.103.202 dbserver2-priv# Public Virtual IP (VIP) addresses192.168.102.201 dbserver1-vip192.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 FAILOVERON 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虚拟地址