oracle rac 业务分离,Oracle RAC LoadBalance 之 Service分散负载（业务分散负载）

背景：为了最大化利用rac集群，减少cache fusion,我们可以对业务进行分实例分负载。

如在RAC中创建2个service，每个客户端只需要在tnsnames.ora中配置需要连接的那个service name就可以了

配置Service-Side TAF 示例

(oracle用户操作)

1 创建TAF

Service

node1-> pwd

/u01/app/11.2.0/grid/bin

node1-> ./srvctl add service -d devdb -s server_taf -r "devdb1" -P

BASIC

2 启动

server_taf服务

node1-> ./srvctl start service -d devdb -s server_taf

3 检查service

运行情况

node1-> ./srvctl config service -d devdb

Service name: server_taf

Service is enabled

Server pool: devdb_server_taf

Cardinality: 1

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: devdb1

Available instances:

4 确认service

ID

SQL> select name,service_id from dba_services where name = 'server_taf';

NAME SERVICE_ID

---------------------------------------------------------------- ----------

server_taf  3

5 给service

添加参数

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.

6 确认参数修改

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'

SQL> select name, failover_method, failover_type, failover_retries,goal,

clb_goal,aq_ha_notifications from dba_services where service_id = 3;

NAME METHOD    TYPE RETRIES GOAL  CLB_GOAL AQNOT

--------------- ----------- ---------- -------- ---------- -------- -----

server_taf BASIC   SELECT    180 NONE  LONG

YES

7 检查service

注册情况

node1-> lsnrctl services

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 26-FEB-2017 04:43:55

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))

Services Summary...

Service "+ASM" has 1 instance(s).

Instance "+ASM1", status READY, has 1 handler(s) for this

service...

Handler(s):

"DEDICATED" established:2 refused:0 state:ready

LOCAL SERVER

Service "devdb" has 1 instance(s).

Instance "devdb1", status READY, has 1 handler(s) for this

service...

Handler(s):

"DEDICATED" established:0 refused:0 state:ready

LOCAL SERVER

Service "devdbXDB" has 1 instance(s).

Instance "devdb1", status READY, has 1 handler(s) for this

service...

Handler(s):

"D000" established:0 refused:0 current:0

max:1022 state:ready

DISPATCHER

(ADDRESS=(PROTOCOL=tcp)(HOST=node1.localdomain)(PORT=26677))

Service "server_taf" has 1 instance(s).

Instance "devdb1", status READY, has 1 handler(s) for this

service...

Handler(s):

"DEDICATED" established:0 refused:0 state:ready

LOCAL SERVER

The command completed successfully

8 在客户端就可以使用Service-Side

TAF了

在客户端TNS 配置：

server_taf =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = scan-cluster.localdomain)(PORT

= 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = server_taf)

)

)

连接测试

C:\Users\andy>sqlplus sys/oracle@10.100.25.8:1521/server_taf as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sat Feb 25 20:59:41 2017

Copyright (c) 1982, 2014, 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 options

9 停止service

node1-> ./srvctl stop  service -d devdb -s server_taf -i devdb1

10 删除service

node1-> ./srvctl remove service -d devdb -s server_taf -i devdb1