用service 和 SCAN实现sqlplus/jdbc连接Oracle 11g RAC时负载均衡

说明

11.2推出的SCAN ，简化了客户端连接（当增加或者减少RAC实例时，不需要修改客户端配置，并且scan listener有各个实例的负载情况，可以实现连接时负载均衡。

不过客户端需要使用专门建立的service,而不能用RAC数据库本身的数据库名连接，否则不能实现连接时负载均衡。

使用service 连接时，可以用此sql在每个instance查看其负载，其中goodness就是负载。

 select service_name,goodness,delta,flags from v$servicemetric

本文分别用sqlplus和jdbc测试Oracle 数据库连接时负载均衡。

RAC环境

rac环境，11.2.0.4 PSU Jul20 ， RHEL7.9

scan： 56.78

节点1： 56.71, 56.75（vip）

节点2： 56.72, 56.76（vip）

设置java 环境

测试使用java 1.6 ，

https://www.oracle.com/java/technologies/javase-java-archive-javase6-downloads.html

jdk6u45 下载地址:
https://download.oracle.com/otn/java/jdk/6u45-b06/jdk-6u45-linux-x64.bin

安装步骤：

切换到root,cd /usr/local,执行/home/oracle/jdk-6u211-linux-x64.bin, 解压jdk包,建立soft link jdk到jdk-6u211-linux-x64.

修改oracle用户.bash_profile,PATH加入jdk目录
PATH=/usr/java/jdk1.6.0_211/bin:$PATH

[oracle@testdb1 jdbc]$ which java
/usr/local/jdk/bin/java
[oracle@testdb1 jdbc]$ java -version
java version "1.6.0_45"
Java(TM) SE Runtime Environment (build 1.6.0_45-b06)
Java HotSpot(TM) 64-Bit Server VM (build 20.45-b01, mixed mode)

测试步骤

1. rac建立服务testlb

srvctl add service -d ctp -s testlb -r ctp1,ctp2
srvctl status service -d ctp
srvctl modify service -d ctp -s testlb -m basic -e select -q true -j short -B service_time
srvctl modify service -d ctp -s testlb -B THROUGHPUT

2. 压力测试配置

使用swingbench
swingbench是原oracle公司员工Dominic Giles为oralce内部项目而编写的开源软件，免费使用 。
测试使用的配置文件 test-rac-ctp1-notime.xml

数据库连接 （连接到节点1 的vip， 56.75 ）

    <Connection><UserName>soe</UserName><Password>enc(cw4Xx9RgsQA=)</Password><ConnectString>//192.168.56.75/testlb</ConnectString><DriverType>Oracle jdbc Driver</DriverType><Properties><Property Key="StatementCaching">120</Property><Property Key="FetchSize">20</Property></Properties></Connection> 

jdbc连接测试java代码

LoadBalanceTestSCAN.java
测试的java程序来自： 《Using SCAN With Oracle JDBC Thin Driver 11g And Higher (Doc ID 1290193.1)》

  [oracle@testdb1 jdbc]$ cat LoadBalanceTestSCAN.java
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import oracle.jdbc.pool.OracleDataSource;// ref Using SCAN With Oracle JDBC Thin Driver 11g And Higher (Doc ID 1290193.1)
public class LoadBalanceTestSCAN {private OracleDataSource ods = null;public final String userId = "system";public final String password = "oracle";private static final String url = "jdbc:oracle:thin:@192.168.56.78:1521/testlb";
//  private static final String url = "jdbc:oracle:thin:@192.168.56.78:1521:testlb";
//  private static final String url = "jdbc:oracle:thin:@(DESCRIPTION= (LOAD_BALANCE=yes) (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.78)(PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=testlb)))";
//  private static final String url = "jdbc:oracle:thin:@(DESCRIPTION= (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.75)(PORT=1521)) (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.76)(PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=ctp)))" ;public LoadBalanceTestSCAN() throws SQLException {ods = new OracleDataSource();ods.setUser(userId);ods.setPassword(password);ods.setURL(url);}public Connection getConnection() throws SQLException {return ods.getConnection();}public void run() throws SQLException {Connection[] connArray = new Connection[5];System.out.println("Obtaining 5 connections");for (int i = 0; i < connArray.length; i++) {connArray[i] = getConnection();}for (int j = 0; j < connArray.length; j++) {if (j == 0) {DatabaseMetaData meta = connArray[j].getMetaData();System.out.println("using URL : " + url + "\n");// gets driver info:System.out.println("\n=============\nDatabase Product Name is ... " +meta.getDatabaseProductName());System.out.println("Database Product Version is " +meta.getDatabaseProductVersion());System.out.println("=============\nJDBC Driver Name is ........ " +meta.getDriverName());System.out.println("JDBC Driver Version is ..... " +meta.getDriverVersion());System.out.println("JDBC Driver Major Version is " +meta.getDriverMajorVersion());System.out.println("JDBC Driver Minor Version is " +meta.getDriverMinorVersion());System.out.println("=============");}getInstanceDetails(connArray[j], j);}System.out.println("Closing Connections");for (int y = 0; y < connArray.length; y++) {connArray[y].close();}}public void getInstanceDetails(Connection conn, int i) throws SQLException {String sql = "select sys_context('userenv', 'instance_name'), "+ "sys_context('userenv', 'server_host'), "+ "sys_context('userenv', 'service_name') "+ "from dual";Statement stmt = conn.createStatement();ResultSet rset = stmt.executeQuery(sql);while (rset.next()) {System.out.println("Connection #" + i + " : instance[" +rset.getString(1) + "], host[" + rset.getString(2) +"], service[" + rset.getString(3) + "]");}rset.close();stmt.close();}public static void main(String[] args) {LoadBalanceTestSCAN loadBalanceTest;try {System.out.println("Test Started at " + new Date());loadBalanceTest = new LoadBalanceTestSCAN();loadBalanceTest.run();System.out.println("Test Ended at " + new Date());} catch (SQLException e) {e.printStackTrace();System.exit(1);}}
}
[oracle@testdb1 jdbc]$

sqlplus连接测试脚本

[oracle@testdb1 test_vip_scan]$ cat test_scan.sh
#!/usr/bin/env bash
#test scan ip
sqlplus system/oracle@testlb @sel_instance_name > scan.out
#sleep 2
sqlplus system/oracle@testlb @sel_instance_name >> scan.out
#sleep 2
sqlplus system/oracle@testlb @sel_instance_name >> scan.out
#sleep 2
sqlplus system/oracle@testlb @sel_instance_name >> scan.out
#sleep 2
sqlplus system/oracle@testlb @sel_instance_name >> scan.outgrep ctp  scan.out
[oracle@testdb1 test_vip_scan]$ cat sel_instance_name.sql
select instance_name from v$instance;
exit;
[oracle@testdb1 test_vip_scan]$
[oracle@testdb1 test_vip_scan]$ cat test_tnsnames.ora
testlb =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.78)(PORT = 1521))(CONNECT_DATA =(LOAD_BALANCE=ON)(SERVER = DEDICATED)(SERVICE_NAME = testlb)))

testlb定义使用的service_nme是testlb

测试记录

压力测试

查看2个节点的sar

节点1 的idle 是0, 节点2 的idle 90%左右。

节点1满负载运行。

sqlplus测试连接

测试jdbc连接

从测试看，无论sqlplus还是jdbc， 绝大部分连接到没有压力的节点2。
连接时负载均衡正常。

参考文档

Using SCAN With Oracle JDBC Thin Driver 11g And Higher (Doc ID 1290193.1)

https://mp.weixin.qq.com/s/nnO9SpkWpUVsqPZU02i1Mg