此次日志旨在证明Oracle RAC是可以依据除了会话数之外的其他要素(在这里测试SERVICE_TIME)实现负载均衡的。此功能依赖LBA(Load Balancing Advisory)[@more@]
不要在生产库上测试,发生任何问题概不负责。
0) 两个节点edolraclin49a,edolraclin49b.
[oracle@edolraclin49a
oracle]$ uname -r
2.4.21-27.ELsmp
1)
首先,创建一个Service,名为SERV,并且如下定义参数:
goal=>DBMS_SERVICE.GOAL_SERVICE_TIME,clb_goal=>DBMS_SERVICE.CLB_GOAL_SHORT
*Note: 可以先用DBCA创建,然后再用DBMS_SERVICE.MODIFY_SERVICE修改。
2)
确保把两个实例都设置为SERV的首选实例。
3) 添加Service到客户端的TNSNAMES.ORA文件:
SERV =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =
edolraclin49a-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL =
TCP)(HOST = edolraclin49b-vip)(PORT = 1521))
(LOAD_BALANCE =
yes)
(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME =
SERV))
)
4) 创建一张测试表和一个测试用户:
connect / as sysdba
create user U1 identified by u1 default tablespace users temporary
tablespace temp;
grant connect, resource, dba to u1;
connect u1/u1
create table fan (c number);
5)
创建一个观察会话,供你观察实例的会话数,将来可反复执行SQL反复观察:
connect / as sysdba
select inst_id,count(*) from gv$session
where username='U1'
group by inst_id order by inst_id;
7)
创建负载SQL脚本fan.sql,内容如下(假设文件都创建在用户home目录下e.g:/home/oracle/):
begin
for
i in 1..1000 loop
insert into fan select sid from v$mystat where
rownum<2;
commit;
delete fan where c in (select sid from
v$mystat where rownum<2);
commit;
end loop;
end;
/
exit;
6)
创建负载脚本fan.sh,调用负载SQL脚本,内容如下(自己修改ORACLE_HOME):
#!/bin/ksh
#
#
fan.sh
#
users=120
x=1
y=$users
UNPW="u1/u1@"$1
ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
export
ORACLE_HOME
while [ $x -le $y ]
do
/u01/app/oracle/product/10.2.0/db_1/bin/sqlplus -s $UNPW @fan.sql
done
7)
创建负载发起脚本startfanload.sh,内容如下(几行看你心情了,参考fan.sql和fan.sh再做决定):
/home/oracle/fan.sh
$1 &
/home/oracle/fan.sh $1 &
/home/oracle/fan.sh $1
&
/home/oracle/fan.sh $1 &
/home/oracle/fan.sh $1 &
/home/oracle/fan.sh
$1 &
/home/oracle/fan.sh $1 &
/home/oracle/fan.sh $1
&
/home/oracle/fan.sh $1 &
/home/oracle/fan.sh $1 &
/home/oracle/fan.sh
$1 &
/home/oracle/fan.sh $1 &
/home/oracle/fan.sh $1
&
/home/oracle/fan.sh $1 &
/home/oracle/fan.sh $1 &
/home/oracle/fan.sh
$1 &
/home/oracle/fan.sh $1 &
/home/oracle/fan.sh $1
&
8) 在客户端对RAC发起工作负载:
./startfanload.sh SERV
9)
在第一个节点上打开一个终端,执行一个程序让CPU忙一下,使SERV在节点一上的服务品质降低(程序文件在我博客资源中心/Misc
Files文件夹里):
./primes
10) 等三分钟,LBA需要时间计算Service品质。
11)
反复执行在第5)步骤中的SQL,观察会话分布:
SQL> select inst_id,count(*) from
gv$session where username='U1' group by inst_id order by inst_id
SQL>
/
INST_ID COUNT(*)
---------- ----------
1
8
2 10
SQL> /
INST_ID
COUNT(*)
---------- ----------
1 8
2
9
SQL> /
INST_ID COUNT(*)
----------
----------
1 5
2 13
SQL>
/
INST_ID COUNT(*)
---------- ----------
1
4
2 14
SQL> /
INST_ID
COUNT(*)
---------- ----------
1 2
2
16
SQL>
看着会话连接倒向第二个节点。
Todd
Go to My Oracle Blog