具体环境：
192.168.1.4 instance_name=db01,service_name=db01
192.168.1.5 instance_name=cloud,service_name=cloud,db01
在机器192.168.1.3机器上做如下测试：
tnsname.ora
1、按照顺序尝试每个地址，直到有一个成功
DBTEST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.5)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.4)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = db01)
)
)
初始两台服务器监听都开着。
SQL> conn
已连接。
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
cloud
将IP地址换下：
DBTEST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.4)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.5)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = db01)
)
)
SQL> conn
已连接。
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
db01
确实是按照先后的连接顺序。现在关闭192.168.1.4的监听
SQL> conn
已连接。
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
cloud
即使第一个地址失败后，他还会找其他的连接。
2、随机尝试每个地址直到成功
DBTEST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.5)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.4)(PORT = 1521))
(LOAD_BALANCE = yes)
)
(CONNECT_DATA =
(SERVICE_NAME = db01)
)
)
试验1做的连接还继续连接在cloud数据库上。
我另外开session,做了如下的试验。
SQL> conn
已连接。
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
cloud
SQL> conn
已连接。
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
db01
发现再次连接的时候，连接到数据库db01上了。发现他具有负载均衡的特点了。
此外不断的直接连接，sqlplus 发现有时候连接的是db01,有时候是cloud，这也体现了它随机的特点。
3、随机选择一个地址尝试
DBTEST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.5)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.4)(PORT = 1521))
(LOAD_BALANCE = yes)
(FAILOVER = false)
)
(CONNECT_DATA =
(SERVICE_NAME = db01)
)
)
发现fail_over是false,其实该值的默认值是yes
我们停止数据库db01的监听器。
连续测试结果如下:
C:\Documents and Settings\Antiper>sqlplus
SQL*Plus: Release 10.2.0.1.0 - Production on 星期六 11月 15 16:29:56 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR:
ORA-12541: TNS: 无监听程序
请输入用户名:
ERROR:
ORA-01017: 用户名/口令无效; 登录被拒绝
请输入用户名:
ERROR:
ORA-01017: 用户名/口令无效; 登录被拒绝
SP2-0157: 在 3 次尝试之后无法连接到 ORACLE, 退出 SQL*Plus
C:\Documents and Settings\Antiper>
C:\Documents and Settings\Antiper>
C:\Documents and Settings\Antiper>sqlplus
SQL*Plus: Release 10.2.0.1.0 - Production on 星期六 11月 15 16:30:01 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> exit
从 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options 断开
C:\Documents and Settings\Antiper>sqlplus
SQL*Plus: Release 10.2.0.1.0 - Production on 星期六 11月 15 16:30:05 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> exit
从 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options 断开
C:\Documents and Settings\Antiper>sqlplus
SQL*Plus: Release 10.2.0.1.0 - Production on 星期六 11月 15 16:30:08 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR:
ORA-12541: TNS: 无监听程序
请输入用户名:
发现有时候能够连接到数据库，有时候连接失败，那么是因为随机选择一个的时候，如果选择了db01数据库，那么肯定是无法连接的，因为数
据库监听器关闭了。
4、按照顺序使用每个地址，直到目标达到
DBTEST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.5)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.4)(PORT = 1521))
(SOURCE_ROUTE = yes)
(FAILOVER = false)
)
(CONNECT_DATA =
(SERVICE_NAME = db01)
)
)
SQL> conn
已连接。
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
cloud
因为db01的监听被关闭了，所以肯定连接进去的是cloud。
这样我们启动数据库db01的监听，而关闭cloud数据库监听。
因为是按照顺序尝试，当第一个1.5的监听关闭时，就无法连接数据库了。
5、只使用一个地址
DBTEST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.5)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.4)(PORT = 1521))
(FAILOVER = false)
)
(CONNECT_DATA =
(SERVICE_NAME = db01)
)
)
还是只能连接1.5的数据库，因为它不提供fail_over，所以其实他等同于
DBTEST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.5)(PORT = 1521))
(FAILOVER = false)
)
(CONNECT_DATA =
(SERVICE_NAME = db01)
)
)
注意：其实我是故意一个数据库的名字为db01,而数据库cloud对外提供的服务的名字也为db01。
service_names参数可以动态修改，默认就是数据库的名字。
综上所述，能够提供connect-time failover的是1、2这两种情况，也就是fail_over=true的情况，默认值就是true
