用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.out

grep 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