这两天配置了一个三节点的RAC环境，想起前一段时间Thomas Zhang给我出的一个题目，配置一个三节点RAC环境的复杂负载均衡以及Transparent Application Failover的服务名。现在有环境了，可以将给出验证后的结果了。

设置不同节点的SERVICE_NAMES，解决最终问题。

灵活实现RAC三节点的负载均衡及TAF配置（一）：http://yangtingkun.itpub.net/post/468/482683

灵活实现RAC三节点的负载均衡及TAF配置（二）：http://yangtingkun.itpub.net/post/468/482724

灵活实现RAC三节点的负载均衡及TAF配置（三）：http://yangtingkun.itpub.net/post/468/487416

灵活实现RAC三节点的负载均衡及TAF配置（四）：http://yangtingkun.itpub.net/post/468/487825

前面尝试了很多的配置，基本上可以满足绝大部分的要求，但是由于没有办法在DESCRIPTION中解决单独指向实例1和实例2问题，因此只能使用DESCRIPTION_LIST解决，但是这也带来了新的问题，使得LOAD_BANLANCE和TAF的设置异常的复杂，而且容易导致ORA-3113等错误。

因此首先修改实例1和实例2的SERVICE_NAMES，使这两个功能相同的实例拥有相同的SERVICE_NAMES，且与实例3的SERVICE_NAMES相区别：

SQL> SELECT INSTANCE_NUMBER, INSTANCE_NAME
2 FROM GV$INSTANCE;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
1 testrac1
3 testrac3
2 testrac2

SQL> COL NAME FORMAT A30
SQL> COL VALUE FORMAT A30
SQL> SELECT INST_ID, NAME, VALUE
2 FROM GV$SYSTEM_PARAMETER
3 WHERE NAME = 'service_names';

INST_ID NAME VALUE
---------- ------------------------------ ------------------------------
1 service_names testrac,testrac1
3 service_names testrac,testrac3
2 service_names testrac,testrac2

SQL> ALTER SYSTEM SET SERVICE_NAMES = 'testrac,testrac12' SCOPE = BOTH SID = 'testrac1';

系统已更改。

SQL> ALTER SYSTEM SET SERVICE_NAMES = 'testrac,testrac12' SCOPE = BOTH SID = 'testrac2';

系统已更改。

SQL> ALTER SYSTEM SET SERVICE_NAMES = 'testrac,testrac3' SCOPE = BOTH SID = 'testrac3';

系统已更改。

SQL> SELECT INST_ID, NAME, VALUE
2 FROM GV$SYSTEM_PARAMETER
3 WHERE NAME = 'service_names';

INST_ID NAME VALUE
---------- ------------------------------ ------------------------------
3 service_names testrac,testrac3
2 service_names testrac,testrac12
1 service_names testrac,testrac12

下面重新配置服务名：

SERVICEA =
(DESCRIPTION_LIST =
(LOAD_BALANCE = no)
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.25.198.224)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.25.198.225)(PORT = 1521))
)
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TESTRAC12)
(FAILOVER_MODE =
(TYPE = SELECT)
(METHOD = BASIC)
(BACKUP = SERVICEA_BACKUP)
)
)
)
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.25.198.227)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TESTRAC3)
(FAILOVER_MODE =
(TYPE = SELECT)
(METHOD = BASIC)
(BACKUP = SERVICEA_BACKUP)
)
)
)
)

SERVICEB =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.25.198.227)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TESTRAC3)
(FAILOVER_MODE =
(TYPE = SELECT)
(METHOD = BASIC)
(BACKUP = SERVICEA)
)
)
)
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.25.198.224)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.25.198.225)(PORT = 1521))
)
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TESTRAC12)
(FAILOVER_MODE =
(TYPE = SELECT)
(METHOD = BASIC)
(BACKUP = SERVICEA)
)
)
)
(LOAD_BALANCE = no)
)

SERVICEA_BACKUP =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.25.198.224)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.25.198.225)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TESTRAC12)
(FAILOVER_MODE =
(TYPE = SELECT)
(METHOD = BASIC)
(BACKUP = SERVICEA_BACKUP)
)
)
)
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.25.198.227)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TESTRAC3)
(FAILOVER_MODE =
(TYPE = SELECT)
(METHOD = BASIC)
(BACKUP = SERVICEA)
)
)
)
(LOAD_BALANCE = no)
)

测试连接：

SQL> CONN TEST/TEST@SERVICEA已连接。
SQL> SELECT INSTANCE_NAME FROM V$INSTANCE;

INSTANCE_NAME
----------------
testrac2

SQL> CONN TEST/TEST@SERVICEA已连接。
SQL> SELECT INSTANCE_NAME FROM V$INSTANCE;

INSTANCE_NAME
----------------
testrac1

SQL> CONN TEST/TEST@SERVICEB已连接。
SQL> SELECT INSTANCE_NAME FROM V$INSTANCE;

INSTANCE_NAME
----------------
testrac3

SQL> CONN TEST/TEST@SERVICEB已连接。
SQL> SELECT INSTANCE_NAME FROM V$INSTANCE;

INSTANCE_NAME
----------------
testrac3

通过SERVICEA可以在节点1和节点2的实例上负载均衡的连接，而通过SERVICEB则连接节点3：

SQL> SELECT INSTANCE_NAME FROM V$INSTANCE;

INSTANCE_NAME
----------------
testrac3

通过命令行方式关闭节点3：

bash-2.03$ srvctl stop inst -d testrac -i testrac3

检查当前连接的实例：

SQL> SELECT INSTANCE_NAME FROM V$INSTANCE;

INSTANCE_NAME
----------------
testrac2

启动数据库实例：

bash-2.03$ srvctl start inst -d testrac -i testrac3

重复一下同样的操作：

SQL> CONN TEST/TEST@SERVICEB已连接。
SQL> SELECT INSTANCE_NAME FROM V$INSTANCE;

INSTANCE_NAME
----------------
testrac3

SQL> SELECT INSTANCE_NAME FROM V$INSTANCE;

INSTANCE_NAME
----------------
testrac1

需要注意在两次执行当前实例的查询之间，通过命令行的方式关闭了实例3。

可以看到，当通过SERVICEB连接到实例3后，如果实例3发生故障，TAF将会话负载均衡的切换到实例1和实例2上。

下面启动一下实例2，检查最后一种情况，通过SERVICEA连接数据库，当节点1或节点2都失败的情况：

SQL> CONN TEST/TEST@SERVICEA已连接。
SQL> SELECT INSTANCE_NAME FROM V$INSTANCE;

INSTANCE_NAME
----------------
testrac1

SQL> SELECT INSTANCE_NAME FROM V$INSTANCE;

INSTANCE_NAME
----------------
testrac2

SQL> SELECT INSTANCE_NAME FROM V$INSTANCE;

INSTANCE_NAME
----------------
testrac1

SQL> SELECT INSTANCE_NAME FROM V$INSTANCE;

INSTANCE_NAME
----------------
testrac3

SQL> SELECT INSTANCE_NAME FROM V$INSTANCE;

INSTANCE_NAME
----------------
testrac2

对应的后台命令行启动关闭实例的脚本为：

bash-2.03$ srvctl stop inst -d testrac -i testrac1
bash-2.03$ srvctl start inst -d testrac -i testrac1
bash-2.03$ srvctl stop inst -d testrac -i testrac2
bash-2.03$ srvctl stop inst -d testrac -i testrac1
bash-2.03$ srvctl start inst -d testrac -i testrac1,testrac2
bash-2.03$ srvctl stop inst -d testrac -i testrac3

下面简单描述一下上面的过程。

通过SERVICEA服务名登陆，检查当前连接的实例，发现连接到实例1上，在后台关闭实例1，检查连接实例，发现TAF后连接到实例2上，后台启动实例1，然后关闭实例2，再次检查连接，发现TAF操作后，连接又回到了实例1上。然后关闭实例1，检查当前的连接，由于实例1和实例2都已经被关闭，因此连接到了实例3上。最后启动实例1和实例2，关闭实例3，检查连接，当前的连接又回到了实例2上。

终于通过SERVICEA、SERVICEB和SERVICEA_BACKUP三个服务名的配合，以及设置RAC不同节点上的SERVICE_NAMES参数，终于完美的解决了这个问题。