實驗環境: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?~]$?srvctl?add?service?-d?jyzhao?-s?server_taf?-r?"jyzhao1,jyzhao2"?-P?BASIC
[oracle@jyrac1?~]$

注意不能使用grid用戶操作,如果使用grid 用戶執行的話,會報錯:

[grid@jyrac1?~]$?srvctl?add?service?-d?jyzhao?-s?server_taf?-r?"jyzhao1,jyzhao2"?-P?BASIC
PRCD-1288?:?User?is?not?authorized?to?create?service?server_taf?for?database?jyzhao
PRKH-1014?:?Current?user?"grid"?is?not?the?oracle?owner?user?"oracle"?of?oracle?home?"/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?~]$?srvctl?start?service?-d?jyzhao?-s?server_taf

3.檢查確認服務正在運行

eg: srvctl config service -d rac

檢查確認服務正在運行:

[oracle@jyrac1?~]$?srvctl?config?service?-d?jyzhao
Service?name:?server_taf
Service?is?enabled
Server?pool:?jyzhao_server_taf
Cardinality:?2
Disconnect:?false
Service?role:?PRIMARY
Management?policy:?AUTOMATIC
DTP?transaction:?false
AQ?HA?notifications:?false
Failover?type:?NONE
Failover?method:?NONE
TAF?failover?retries:?0
TAF?failover?delay:?0
Connection?Load?Balancing?Goal:?LONG
Runtime?Load?Balancing?Goal:?NONE
TAF?policy?specification:?BASIC
Edition:?
Preferred?instances:?jyzhao1,jyzhao2
Available?instances:

4.找到剛創建服務的service_id

eg: select name,service_id from dba_services where name = 'server_taf';

找到剛創建服務的service_id

SQL>?select?name,service_id?from?dba_services?where?name?=?'server_taf';?NAME?????????????????????????????????????????????????????????????SERVICE_ID
----------------------------------------------------------------?----------
server_taf????????????????????????????????????????????????????????????????7

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>?col?name?format?a15??
SQL>?col?failover_method?format?a11?heading?'METHOD'?
SQL>?col?failover_type?format?a10?heading?'TYPE'?
SQL>?col?failover_retries?format?9999999?heading?'RETRIES'?
SQL>?col?goal?format?a10?
SQL>?col?clb_goal?format?a8?
SQL>?col?AQ_HA_NOTIFICATIONS?format?a5?heading?'AQNOT'?
SQL>?select?name,?failover_method,?failover_type,?failover_retries,goal,?clb_goal,aq_ha_notifications??2??from?dba_services?where?service_id?=?73??;NAME????????????METHOD??????TYPE????????RETRIES?GOAL???????CLB_GOAL?AQNOT
---------------?-----------?----------?--------?----------?--------?-----
server_taf??????NONE????????NONE??????????????0?NONE???????LONG?????NOSQL>

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>?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);?PL/SQL?procedure?successfully?completed.

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>?select?name,?failover_method,?failover_type,?failover_retries,goal,?clb_goal,aq_ha_notifications??2??from?dba_services?where?service_id?=?7;NAME????????????METHOD??????TYPE????????RETRIES?GOAL???????CLB_GOAL?AQNOT
---------------?-----------?----------?--------?----------?--------?-----
server_taf??????BASIC???????SELECT??????????180?NONE???????LONG?????YES

8.檢查已注冊的服務的監聽信息

lsnrctl services

Service "server_taf.za.oracle.com" 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"?has?1?instance(s).Instance?"jyzhao1",?status?READY,?has?1?handler(s)?for?this?service...Handler(s):"DEDICATED"?established:0?refused:0?state:readyLOCAL?SERVER
The?command?completed?successfully--node2:
Service?"server_taf"?has?1?instance(s).Instance?"jyzhao2",?status?READY,?has?1?handler(s)?for?this?service...Handler(s):"DEDICATED"?established:0?refused:0?state:readyLOCAL?SERVER
The?command?completed?successfully

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.za.oracle.com)
)
)

服務端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)))

sqlplus?system/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
sqlplus?system/oracle@192.168.56.160/server_taf

[grid@jyrac1?~]$?sqlplus?system/oracle@192.168.56.160/server_tafSQL*Plus:?Release?11.2.0.4.0?Production?on?Fri?Mar?10?02:59:53?2017Copyright?(c)?1982,?2013,?Oracle.??All?rights?reserved.Connected?to:
Oracle?Database?11g?Enterprise?Edition?Release?11.2.0.4.0?-?64bit?Production
With?the?Partitioning,?Real?Application?Clusters,?Automatic?Storage?Management,?OLAP,
Data?Mining?and?Real?Application?Testing?optionsSQL>?select?host_name,instance_name?from?v$instance;HOST_NAME
----------------------------------------------------------------
INSTANCE_NAME
----------------
jyrac1
jyzhao1--這里強制關掉jyzhao1實例。SQL>?/HOST_NAME
----------------------------------------------------------------
INSTANCE_NAME
----------------
jyrac2
jyzhao2

10.1 結論: 可以實現TAF功能,相當于客戶端不再需要配置,直接通過SCAN VIP連接。

10.2 模擬客戶端使用Public IP測試能否實現TAF
sqlplus?system/oracle@192.168.56.150/server_taf

SQL>??select?host_name,instance_name?from?v$instance;HOST_NAME
----------------------------------------------------------------
INSTANCE_NAME
----------------
jyrac1
jyzhao1--這里強制關掉jyzhao1實例。
SQL>?/select?host_name,instance_name?from?v$instance
*
ERROR?at?line?1:
ORA-12153:?TNS:not?connected
Process?ID:?20116
Session?ID:?24?Serial?number:?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@jyrac2?admin]$?sqlplus?system/oracle@tafSQL*Plus:?Release?11.2.0.4.0?Production?on?Fri?Mar?10?05:11:30?2017Copyright?(c)?1982,?2013,?Oracle.??All?rights?reserved.Connected?to:
Oracle?Database?11g?Enterprise?Edition?Release?11.2.0.4.0?-?64bit?Production
With?the?Partitioning,?Real?Application?Clusters,?Automatic?Storage?Management,?OLAP,
Data?Mining?and?Real?Application?Testing?optionsSQL>?select?host_name,instance_name?from?v$instance;HOST_NAME
----------------------------------------------------------------
INSTANCE_NAME
----------------
jyrac2
jyzhao2--這里強制關掉jyzhao2實例。
SQL>?/HOST_NAME
----------------------------------------------------------------
INSTANCE_NAME
----------------
jyrac1
jyzhao1

10.2 結論: 直接連接Public IP無法實現TAF功能。但客戶端配置Public IP列表,可以實現。

10.3 模擬客戶端使用VIP測試能否實現TAF
sqlplus?system/oracle@192.168.56.151/server_taf

[grid@jyrac1?~]$?sqlplus?system/oracle@192.168.56.151/server_tafSQL*Plus:?Release?11.2.0.4.0?Production?on?Fri?Mar?10?04:32:20?2017Copyright?(c)?1982,?2013,?Oracle.??All?rights?reserved.Connected?to:
Oracle?Database?11g?Enterprise?Edition?Release?11.2.0.4.0?-?64bit?Production
With?the?Partitioning,?Real?Application?Clusters,?Automatic?Storage?Management,?OLAP,
Data?Mining?and?Real?Application?Testing?optionsSQL>?select?host_name,instance_name?from?v$instance;HOST_NAME
----------------------------------------------------------------
INSTANCE_NAME
----------------
jyrac1
jyzhao1SQL>?/
select?host_name,instance_name?from?v$instance
*
ERROR?at?line?1:
ORA-03113:?end-of-file?on?communication?channel
Process?ID:?32459
Session?ID:?159?Serial?number:?3

如果客戶端配置tnsnames.ora,可以通過sqlplus?system/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@jyrac2?admin]$?sqlplus?system/oracle@tafvipSQL*Plus:?Release?11.2.0.4.0?Production?on?Fri?Mar?10?05:15:32?2017Copyright?(c)?1982,?2013,?Oracle.??All?rights?reserved.Connected?to:
Oracle?Database?11g?Enterprise?Edition?Release?11.2.0.4.0?-?64bit?Production
With?the?Partitioning,?Real?Application?Clusters,?Automatic?Storage?Management,?OLAP,
Data?Mining?and?Real?Application?Testing?optionsSQL>?select?host_name,instance_name?from?v$instance;HOST_NAME
----------------------------------------------------------------
INSTANCE_NAME
----------------
jyrac2
jyzhao2--這里強制關掉jyzhao2實例。
SQL>?/HOST_NAME
----------------------------------------------------------------
INSTANCE_NAME
----------------
jyrac1
jyzhao1

10.3 結論: 直接連接VIP無法實現TAF功能。但客戶端配置VIP列表,可以實現。