实验环境：Oracle 11.2.0.4 RAC
1.为设置TAF在RAC集群上新建服务
2.启动server_taf服务
3.检查确认服务正在运行
4.找到刚创建服务的service_id
5.根据service_id审查服务的信息
6.给服务添加server side failover参数
7.再次审查服务可以看到Method, Type和Retries值
8.检查已注册的服务的监听信息
9.创建网络服务名
10.测试TAF功能
1.为设置TAF在RAC集群上新建服务
eg: srvctl add service -d rac -s server_taf -r "rac1,rac2" -P BASIC
使用oracle用户在RAC集群上新建服务server_taf:
[oracle@jyrac1~]$srvctladdservice-djyzhao-sserver_taf-r"jyzhao1,jyzhao2"-PBASIC[oracle@jyrac1~]$
注意不能使用grid用户操作，如果使用grid 用户执行的话，会报错:
[grid@jyrac1~]$srvctladdservice-djyzhao-sserver_taf-r"jyzhao1,jyzhao2"-PBASICPRCD-1288:Userisnotauthorizedtocreateserviceserver_taffordatabasejyzhaoPRKH-1014:Currentuser"grid"isnottheoracleowneruser"oracle"oforaclehome"/opt/app/oracle/product/11.2.0/dbhome_1"
2.启动server_taf服务
eg: srvctl start service -d rac -s server_taf
启动server_taf服务
[oracle@jyrac1~]$srvctlstartservice-djyzhao-sserver_taf
3.检查确认服务正在运行
eg: srvctl config service -d rac
检查确认服务正在运行:
[oracle@jyrac1~]$srvctlconfigservice-djyzhaoServicename:server_tafServiceisenabledServerpool:jyzhao_server_tafCardinality:2Disconnect:falseServicerole:PRIMARYManagementpolicy:AUTOMATICDTPtransaction:falseAQHAnotifications:falseFailovertype:NONEFailovermethod:NONETAFfailoverretries:0TAFfailoverdelay:0ConnectionLoadBalancingGoal:LONGRuntimeLoadBalancingGoal:NONETAFpolicyspecification:BASICEdition:Preferredinstances:jyzhao1,jyzhao2Availableinstances:
4.找到刚创建服务的service_id
eg: select name,service_id from dba_services where name = 'server_taf';
找到刚创建服务的service_id
SQL>selectname,service_idfromdba_serviceswherename='server_taf';NAMESERVICE_ID--------------------------------------------------------------------------server_taf7
5.根据service_id审查服务的信息
col name format a15
col failover_method format a11 heading 'METHOD'
col failover_type format a10 heading 'TYPE'
col failover_retries format 9999999 heading 'RETRIES'
col goal format a10
col clb_goal format a8
col AQ_HA_NOTIFICATIONS format a5 heading 'AQNOT'
select name, failover_method, failover_type, failover_retries,goal, clb_goal,aq_ha_notifications
from dba_services where service_id = 7;
根据service_id审查服务的信息:
SQL>colnameformata15SQL>colfailover_methodformata11heading'METHOD'SQL>colfailover_typeformata10heading'TYPE'SQL>colfailover_retriesformat9999999heading'RETRIES'SQL>colgoalformata10SQL>colclb_goalformata8SQL>colAQ_HA_NOTIFICATIONSformata5heading'AQNOT'SQL>selectname,failover_method,failover_type,failover_retries,goal,clb_goal,aq_ha_notifications2fromdba_serviceswhereservice_id=73;NAMEMETHODTYPERETRIESGOALCLB_GOALAQNOT-------------------------------------------------------------------server_tafNONENONE0NONELONGNOSQL>
6.给服务添加server side failover参数
execute dbms_service.modify_service (service_name => 'server_taf' -
, aq_ha_notifications => true -
, failover_method => dbms_service.failover_method_basic -
, failover_type => dbms_service.failover_type_select -
, failover_retries => 180 -
, failover_delay => 5 -
, clb_goal => dbms_service.clb_goal_long);
11.2版本可以使用srvctl 修改服务的信息：
srvctl modify service -d RAC -s server_taf -m BASIC -e SELECT -q TRUE -j LONG
给服务添加server side failover参数:
SQL>executedbms_service.modify_service(service_name=>'server_taf'->,aq_ha_notifications=>true->,failover_method=>dbms_service.failover_method_basic->,failover_type=>dbms_service.failover_type_select->,failover_retries=>180->,failover_delay=>5->,clb_goal=>dbms_service.clb_goal_long);PL/SQLproceduresuccessfullycompleted.
7.再次审查服务可以看到Method, Type和Retries值
select name, failover_method, failover_type, failover_retries,goal, clb_goal,aq_ha_notifications
from dba_services where service_id = 7;
再次审查服务可以看到Method, Type和Retries值:
SQL>selectname,failover_method,failover_type,failover_retries,goal,clb_goal,aq_ha_notifications2fromdba_serviceswhereservice_id=7;NAMEMETHODTYPERETRIESGOALCLB_GOALAQNOT-------------------------------------------------------------------server_tafBASICSELECT180NONELONGYES
8.检查已注册的服务的监听信息
lsnrctl services
Service "server_taf." has 2 instance(s).
Instance "rac1", status READY, has 2 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
REMOTE SERVER
(ADDRESS=(PROTOCOL=TCP)(HOST=dell01)(PORT=1521))
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Instance "rac2", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
REMOTE SERVER
(ADDRESS=(PROTOCOL=TCP)(HOST=dell02)(PORT=1521))
我这里版本差异，显示有区别，分别在不同节点显示自己的实例：
--node1:Service"server_taf"has1instance(s).Instance"jyzhao1",statusREADY,has1handler(s)forthisservice...Handler(s):"DEDICATED"established:0refused:0state:readyLOCALSERVERThecommandcompletedsuccessfully--node2:Service"server_taf"has1instance(s).Instance"jyzhao2",statusREADY,has1handler(s)forthisservice...Handler(s):"DEDICATED"established:0refused:0state:readyLOCALSERVERThecommandcompletedsuccessfully
9.创建网络服务名
SERVERTAF =
(DESCRIPTION =
(LOAD_BALANCE = yes)
(ADDRESS = (PROTOCOL = TCP)(HOST = dell01)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = dell02)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = server_taf.)
)
)
服务端RAC所有节点配置tnsnames.ora,添加内容：
SERVERTAF=(DESCRIPTION=(LOAD_BALANCE=yes)(ADDRESS=(PROTOCOL=TCP)(HOST=jyrac1)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=jyrac2)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=server_taf)))
sqlplussystem/oracle@192.168.56.160/server_taf
10.测试TAF功能
select host_name,instance_name from v$instance;
SQL> select instance_name from V$instance;
INSTANCE_NAME
----------------
rac2
SQL> shutdown abort;
ORACLE instance shut down.
select host_name,instance_name from v$instance;
10.1 模拟客户端使用scanVIP测试能否实现TAF
sqlplussystem/oracle@192.168.56.160/server_taf
[grid@jyrac1~]$sqlplussystem/oracle@192.168.56.160/server_tafSQL*Plus:Release11.2.0.4.0ProductiononFriMar1002:59:53Copyright(c)1982,,Oracle.Allrightsreserved.Connectedto:OracleDatabase11gEnterpriseEditionRelease11.2.0.4.0-64bitProductionWiththePartitioning,RealApplicationClusters,AutomaticStorageManagement,OLAP,DataMiningandRealApplicationTestingoptionsSQL>selecthost_name,instance_namefromv$instance;HOST_NAME----------------------------------------------------------------INSTANCE_NAME----------------jyrac1jyzhao1--这里强制关掉jyzhao1实例。SQL>/HOST_NAME----------------------------------------------------------------INSTANCE_NAME----------------jyrac2jyzhao2
10.1 结论： 可以实现TAF功能，相当于客户端不再需要配置，直接通过SCAN VIP连接。
10.2 模拟客户端使用Public IP测试能否实现TAF
sqlplussystem/oracle@192.168.56.150/server_taf
SQL>selecthost_name,instance_namefromv$instance;HOST_NAME----------------------------------------------------------------INSTANCE_NAME----------------jyrac1jyzhao1--这里强制关掉jyzhao1实例。SQL>/selecthost_name,instance_namefromv$instance*ERRORatline1:ORA-12153:TNS:notconnectedProcessID:6SessionID:24Serialnumber:7
如果客户端配置tnsnames.ora,将publicIP配置
TAF=(DESCRIPTION=(LOAD_BALANCE=yes)(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.150)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.152)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=server_taf)))
再次测试：
[oracle@jyrac2admin]$sqlplussystem/oracle@tafSQL*Plus:Release11.2.0.4.0ProductiononFriMar1005:11:30Copyright(c)1982,,Oracle.Allrightsreserved.Connectedto:OracleDatabase11gEnterpriseEditionRelease11.2.0.4.0-64bitProductionWiththePartitioning,RealApplicationClusters,AutomaticStorageManagement,OLAP,DataMiningandRealApplicationTestingoptionsSQL>selecthost_name,instance_namefromv$instance;HOST_NAME----------------------------------------------------------------INSTANCE_NAME----------------jyrac2jyzhao2--这里强制关掉jyzhao2实例。SQL>/HOST_NAME----------------------------------------------------------------INSTANCE_NAME----------------jyrac1jyzhao1
10.2 结论： 直接连接Public IP无法实现TAF功能。但客户端配置Public IP列表，可以实现。
10.3 模拟客户端使用VIP测试能否实现TAF
sqlplussystem/oracle@192.168.56.151/server_taf
[grid@jyrac1~]$sqlplussystem/oracle@192.168.56.151/server_tafSQL*Plus:Release11.2.0.4.0ProductiononFriMar1004:32:20Copyright(c)1982,,Oracle.Allrightsreserved.Connectedto:OracleDatabase11gEnterpriseEditionRelease11.2.0.4.0-64bitProductionWiththePartitioning,RealApplicationClusters,AutomaticStorageManagement,OLAP,DataMiningandRealApplicationTestingoptionsSQL>selecthost_name,instance_namefromv$instance;HOST_NAME----------------------------------------------------------------INSTANCE_NAME----------------jyrac1jyzhao1SQL>/selecthost_name,instance_namefromv$instance*ERRORatline1:ORA-03113:end-of-fileoncommunicationchannelProcessID:32459SessionID:159Serialnumber:3
如果客户端配置tnsnames.ora，可以通过sqlplussystem/oracle@tafvip连接。
TAFVIP=(DESCRIPTION=(LOAD_BALANCE=yes)(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.151)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.153)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=server_taf)))
再次测试：
[oracle@jyrac2admin]$sqlplussystem/oracle@tafvipSQL*Plus:Release11.2.0.4.0ProductiononFriMar1005:15:32Copyright(c)1982,,Oracle.Allrightsreserved.Connectedto:OracleDatabase11gEnterpriseEditionRelease11.2.0.4.0-64bitProductionWiththePartitioning,RealApplicationClusters,AutomaticStorageManagement,OLAP,DataMiningandRealApplicationTestingoptionsSQL>selecthost_name,instance_namefromv$instance;HOST_NAME----------------------------------------------------------------INSTANCE_NAME----------------jyrac2jyzhao2--这里强制关掉jyzhao2实例。SQL>/HOST_NAME----------------------------------------------------------------INSTANCE_NAME----------------jyrac1jyzhao1
10.3 结论： 直接连接VIP无法实现TAF功能。但客户端配置VIP列表，可以实现。
如果觉得《配置Server Side TAF》对你有帮助，请点赞、收藏，并留下你的观点哦！
