RAC架构之负载均衡

1、客户端的负载均衡

(LOAD_BALANCE = ON)     表示开起客户端的负载均衡，等效于(LOAD_BALANCE = YES)。
(LOAD_BALANCE = OFF)   表示关闭客户端的负载均衡，等效于(LOAD_BALANCE = NO)。

tnsnames.ora

ORCL2 =
  (DESCRIPTION =
    (LOAD_BALANCE = ON)                 --客户端负载均衡就是通过这个参数控制
    (FAILOVER = ON)   
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan.oraclerac.com)(PORT = 1521)) 
    (CONNECT_DATA =
    (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
      (FAILOVER_MODE =
        (TYPE = SELECT)
        (METHOD = BASIC)
        (RETRIES = 30)
        (DELAY = 5)
      )
    )
  )

这个例子需要准备两个脚本，一个是shell脚本test.sh，另一个是sql脚本test.sql。调用test.sh脚本需要一个参数，参数是一个数字，用于设置创建的连接数量。脚本内部会创建相同数量的连接，并且执行test.sql脚本，test.sql里面只有一个很简单循环的语句。每创建完一个连接，test.sh脚本会停顿1秒，这样做的目的是为了防止连接风暴。每个连接执行完test.sql脚本并不退出，这样就可以在test.sh脚本执行完后查看每个实例建立的连接数量

[oracle@linux1 shell]$ cat test.sh 
#!/bin/bash
for a in `seq 1 ${1}`
do
sqlplus -s scott/tiger@orcl2 < t.sql &
sleep 1
done
[oracle@linux1 shell]$ 
[oracle@linux1 shell]$ cat test.sql 
set serveroutput on;
set linesize 100;
col INSTANCE_NAME for a20;
declare
  tmp varchar2(100);
begin   
  while 1=1 loop   
     select INSTANCE_NAME into tmp from v$instance;
     dbms_output.put_line('INSTANCE_NAME is：'||tmp);
  end loop;  
end;
/

调用：

[oracle@linux1 shell]$ nohup ./test.sh 200 &
[1] 10249
[oracle@linux1 shell]$ nohup: appending output to “nohup.out”

[oracle@linux1 shell]$ 
[oracle@linux1 shell]$ jobs
[1]+  Running                 nohup ./test.sh 200 &

查看：

[oracle@linux1 ~]$ ps -ef | grep LOCAL=NO | wc -l
90
SQL> 
  1* select inst_id,count(*) from gv$session where SCHEMANAME='SCOTT' group by inst_id

   INST_ID   COUNT(*)
---------- ----------
         1         56
         2         57

SQL> run
  1* select inst_id,count(*) from gv$session where SCHEMANAME='SCOTT' group by inst_id

   INST_ID   COUNT(*)
---------- ----------
         1         69
         2         71

SQL> run
  1* select inst_id,count(*) from gv$session where SCHEMANAME='SCOTT' group by inst_id

   INST_ID   COUNT(*)
---------- ----------
         1         74
         2         76

注意：

[oracle@linux1 shell]$ cat tt1.sh 
#!/bin/bash
for a in `seq 1 100`
do
sqlplus /nolog << EOF
conn scott/tiger@orcl2;                                --这种写法也可以。
@/u01/shell/test.sql;
EOF
done
[oracle@linux1 shell]$ cat tt2.sh 
#!/bin/bash
for a in `seq 1 100`
do
sqlplus /nolog << EOF
conn scott/tiger@orcl2;                                 --这些，如果里面有$的对象，shell会把它当作变量取值。
select sysdate from dual;
EOF
done
[oracle@linux1 shell]$ cat tt3.sh 
#!/bin/bash
for a in `seq 1 ${1}`
do
sqlplus -s scott/tiger@orcl2 < t.sql &                  --这个写法最好，把将本放到后台去执行。
sleep 1
done
[oracle@linux1 shell]$ cat test.sql 
set linesize 100;
col INSTANCE_NAME for a20;
select INSTANCE_NAME from v$instance;
[oracle@linux1 shell]$ cat t.sql
set serveroutput on;
set linesize 100;
col INSTANCE_NAME for a20;
declare
  tmp varchar2(100);
begin   
  while 1=1 loop                                        --一直在这里循环，不然执行完sql，session就断开了。
     select INSTANCE_NAME into tmp from v$instance;
     dbms_output.put_line('INSTANCE_NAME is：'||tmp);
  end loop;  
end;
/
[oracle@linux1 shell]$ cat test.sh 
#!/bin/bash
export ORACLE_UNQNAME=orcl
export ORACLE_BASE=/u01/app
export ORACLE_HOME=/u01/app/oracle
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=.:$PATH:$HOME/bin:$ORACLE_HOME/bin

count=0
while [ ${count} -lt ${1} ]
do
  count=`expr ${count} + 1`
  sqlplus scott/tiger@orcl2 @/u01/shell/test.sql       --这样的写法会报错（Error 45 initializing SQL*Plus Internal error）
  sleep 1
done

2、服务器端的负载均衡

SQL> select INSTANCE_NUMBER,INSTANCE_NAME from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
              1 orcl1

SQL> show parameter service_names

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      orcl
SQL> show parameter local_listener 

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string      (DESCRIPTION=(ADDRESS_LIST=(AD
                                                 DRESS=(PROTOCOL=TCP)(HOST=linu
                                                 x1-vip)(PORT=1521))))
SQL> show parameter remote_listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_listener                      string      rac-scan.oraclerac.com:1521

SQL> select INSTANCE_NUMBER,INSTANCE_NAME from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
              2 orcl2

SQL> show parameter service_names

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      orcl
SQL> show parameter local_listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string      (DESCRIPTION=(ADDRESS_LIST=(AD
                                                 DRESS=(PROTOCOL=TCP)(HOST=linu
                                                 x2-vip)(PORT=1521))))
SQL> show parameter remote_listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_listener                      string      rac-scan.oraclerac.com:1521