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虚拟地址