这两天配置了一个三节点的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参数,终于完美的解决了这个问题。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/4227/viewspace-626982/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/4227/viewspace-626982/