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/bashfor a in `seq 1 ${1}`dosqlplus -s scott/tiger@orcl2 < t.sql &sleep 1done[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 -l90SQL>   1* select inst_id,count(*) from gv$session where SCHEMANAME='SCOTT' group by inst_id   INST_ID   COUNT(*)---------- ----------         1         56         2         57SQL> run  1* select inst_id,count(*) from gv$session where SCHEMANAME='SCOTT' group by inst_id   INST_ID   COUNT(*)---------- ----------         1         69         2         71SQL> 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/bashfor a in `seq 1 100`dosqlplus /nolog << EOFconn scott/tiger@orcl2;                                --这种写法也可以。@/u01/shell/test.sql;EOFdone[oracle@linux1 shell]$ cat tt2.sh #!/bin/bashfor a in `seq 1 100`dosqlplus /nolog << EOFconn scott/tiger@orcl2;                                 --这些，如果里面有$的对象，shell会把它当作变量取值。select sysdate from dual;EOFdone[oracle@linux1 shell]$ cat tt3.sh #!/bin/bashfor a in `seq 1 ${1}`dosqlplus -s scott/tiger@orcl2 < t.sql &                  --这个写法最好，把将本放到后台去执行。sleep 1done[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.sqlset 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/bashexport ORACLE_UNQNAME=orclexport ORACLE_BASE=/u01/appexport ORACLE_HOME=/u01/app/oracleexport LD_LIBRARY_PATH=$ORACLE_HOME/libexport PATH=.:$PATH:$HOME/bin:$ORACLE_HOME/bincount=0while [ ${count} -lt ${1} ]do  count=`expr ${count} + 1`  sqlplus scott/tiger@orcl2 @/u01/shell/test.sql       --这样的写法会报错（Error 45 initializing SQL*Plus Internal error）  sleep 1done

2、服务器端的负载均衡

SQL> select INSTANCE_NUMBER,INSTANCE_NAME from v$instance;INSTANCE_NUMBER INSTANCE_NAME--------------- ----------------              1 orcl1SQL> show parameter service_namesNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------service_names                        string      orclSQL> 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_listenerNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------remote_listener                      string      rac-scan.oraclerac.com:1521

SQL> select INSTANCE_NUMBER,INSTANCE_NAME from v$instance;INSTANCE_NUMBER INSTANCE_NAME--------------- ----------------              2 orcl2SQL> show parameter service_namesNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------service_names                        string      orclSQL> show parameter local_listenerNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------local_listener                       string      (DESCRIPTION=(ADDRESS_LIST=(AD                                                 DRESS=(PROTOCOL=TCP)(HOST=linu                                                 x2-vip)(PORT=1521))))SQL> show parameter remote_listenerNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------remote_listener                      string      rac-scan.oraclerac.com:1521