Oracle最佳连接方式之service最佳实践及测试（下）

IT那活儿 2022-08-29

499

点击上方“IT那活儿”公众号，关注后了解更多内容，不管IT什么活儿，干就完了！！！

一

文章前言

本章内容包含测试六个场景：

主库节点1宕库
主库节点1宕机
备库节点1宕库
备库节点1宕机
主备switch over
主备failover

需要回顾早前发布的文章，可点击文章标题跳转原文查看：

《oracle最佳连接方式之service简介及创建（上）》

《oracle最佳连接方式之service维护（中）》

二

service测试相关脚本

2.1 先主库建测试表及测试用户

创建读测试表

create user dbauser identified by oracle account unlock;
grant dba to dbauser;
create table dbauser.test_read(id number);
insert into dbauser.test_read values(1);
insert into dbauser.test_read values(2);
insert into dbauser.test_read values(3);
commit;
复制

创建写测试表

create table dbauser.test_write(id number);
复制

循环写测试脚本

#!/bin/bash
#Autor:Wangergui
#Description:test write
i=0
while true;do
sqlplus dbauser/oracle@PRI_EMREP1<<EOF
insert into dbauser.test_write values ('$i');
commit;
exec dbms_lock.sleep(5);
quit
EOF
sleep 2 && i=$(($i+1))
done
复制

2.2 连接测试脚本

监控读

#!/bin/bash
#Autor:Wangergui
#Description:test read
i=0
while true;do
sqlplus -S dbauser/oracle@PRI_EMREP1 <<EOF
select * from dbauser.test;
exec dbms_lock.sleep(5);
quit
EOF
sleep 1 && i=$(($i+1))
done
复制

监控写

#!/bin/bash
#Autor:Wangergui
#Description:Monitor write
i=0
while true;do
sqlplus dbauser/oracle@PRI_EMREP1 <<EOF
select * from dbauser.test_write;
exec dbms_lock.sleep(5);
quit
EOF
sleep 2 && i=$(($i+1))
done
复制

2.3 主备库tnsnames.ora 配置

说明：

192.168.8.110为主库的SCAN IP
192.168.8.111为备库的SCAN IP

# Primary Node1

PRI_EMREP1 =
(DESCRIPTION =
(ADDRESS_LIST =
(LOAD_BALANCE = off)
(FAILOVER = ON)
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.110)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.111)(PORT = 1521))
)
(CONNECT_DATA =
(service_name = EMREP_R_S1)
(FAILOVER_MODE =
(type = session)
(method = basic)
(retries = 10)
(delay = 5)
)
)
)

# Primary Node2
PRI_EMREP2 =
(DESCRIPTION =
(ADDRESS_LIST =
(LOAD_BALANCE = off)
(FAILOVER = ON)
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.110)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.111)(PORT = 1521))
)
(CONNECT_DATA =
(service_name = EMREP_R_S2)
(FAILOVER_MODE =
(type = session)
(method = basic)
(retries = 10)
(delay = 5)
)
)
)

# Standby Node1
STD_EMREP1 =
(DESCRIPTION =
(ADDRESS_LIST =
(LOAD_BALANCE = off)
(FAILOVER = ON)
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.111)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.110)(PORT = 1521))
)
(CONNECT_DATA =
(service_name = EMREP_RD_S1)
(FAILOVER_MODE =
(type = session)
(method = basic)
(retries = 10)
(delay = 5)
)
)
)

# Standy Node2
STD_EMREP2 =
(DESCRIPTION =
(ADDRESS_LIST =
(LOAD_BALANCE = off)
(FAILOVER = ON)
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.111)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.110)(PORT = 1521))
)
(CONNECT_DATA =
(service_name = EMREP_RD_S2)
(FAILOVER_MODE =
(type = session)
(method = basic)
(retries = 10)
(delay = 5)
)
)
)
复制

2.4 session监控脚本

#!/bin/bash
#Autor:Wangergui
#Description: Montor service_name
while true;do sqlplus -S  'as sysdba' <<EOF
alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
set linesize 500 pagesize 100 echo off feedback off trims on newpage none;
set lines 400 pages 1000
col username for a15;
col machine for a25;
col program for a45;
col service_name for a20;
select inst_id,
sid,
username,
machine,
program,
service_name,
status,
logon_time
from gv\$session where username='DBAUSER';
quit;
EOF
echo && sleep 1
done
复制

三

测试场景

场景一：主库节点1宕库

1）测试过程说明

session 1运行连接测试service.sh脚本；
session 2 运行session监控session.sh 脚本；
session 3关闭节点1数据库shutdown immediate；
监控service会不会漂移。

结论：service会漂移到节点2上，数据库启动之后service不会自动回切至节点1，需要手动relocate；客户端无需更改任何配置。

注意：如果使用srvctl stop instance关闭数据库service不会漂移，需要加-f选项。

srvctl stop instance –d <db_unique_name> –n <node_name> –f -failover
复制

示例：

srvctl stop instance –d <db_unique_name> -n <node_name> -f -failover
复制

2）场景一测试如下

测试主机：备库两个节点

VIP ：10.30.69.18 && 10.30.69.19

SCAN: 192.168.8.111

节点1 session1 运行循环查询脚本,连接串为STD_ORCL1(优先连接备库的节点1)。
预期效果：正常shutdown，节点1的service可以漂移到节点2上面，营业无需修改配置。
测试结果：达到预期效果。

#!/bin/bash
#Autor:Wangergui
#Description:test read
i=0
while true;do
sqlplus -S wangergui/oracle@STD_ORCL1 <<EOF
select * from wangergui.test_read;
exec dbms_lock.sleep(5);
quit
EOF
sleep 1 && i=$(($i+1))
done
复制

节点2 运行session监控脚本如下：

while true;do sqlplus -S  'as sysdba' <<EOF
alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
set linesize 500 pagesize 100 echo off feedback off trims on newpage none;
set lines 400 pages 1000
col username for a15;
col machine for a25;
col program for a45;
col service_name for a20;
select inst_id,
sid,
username,
machine,
program,
service_name,
status,
logon_time
from gv\$session where username='WANGERGUI';
quit;
EOF
echo && sleep 1
done
复制

说明：此时通过STD_ORCL1连接的session全部在节点1上面。

节点1 session 2执行shutdown immediate关闭数据库。

shutdown immedaite;
复制

会话监控如下：

session已经动态的漂移到了节点2上面。

节点1 session 3 执行启动数据库。
startup;
session 监控还是在节点2上面。

节点1启库后service还是在节点2上面，没有漂回节点1，此时需要手动relocate到节点1。

srvctl relocate service -db DGORCL -service ORCL_rd_s1 -oldinst orcl2 -newinst orcl1
复制

session 监控已经飘回节点1。

场景二：主库节点1宕机

1）测试场景说明

节点2 session 1运行连接测试service.sh脚本；
节点2 session 2 运行session监控session.sh 脚本；
节点1 session 1关闭节点1主机,执行重启reboot；
监控service会不会漂移。

结论：service会漂移到节点2上，数据库启动之后service不会自动回切至节点1需要手动relocate。

2）节点2 session1运行循环查询脚本

#!/bin/bash
#Autor:Wangergui
#Description:test read
i=0
while true;do
sqlplus -S wangergui/oracle@STD_ORCL1 <<EOF
select * from wangergui.test_read;
exec dbms_lock.sleep(5);
quit
EOF
sleep 1 && i=$(($i+1))
done
复制

3）节点2 session2 运行session监控脚本

while true;do sqlplus -S / 'as sysdba' <<EOF
alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
set linesize 500 pagesize 100 echo off feedback off trims on newpage none;
set lines 400 pages 1000
col username for a15;
col machine for a25;
col program for a45;
col service_name for a20;
select inst_id,
sid,
username,
machine,
program,
service_name,
status,
logon_time
from gv\$session where username='WANGERGUI';
quit;
EOF
echo && sleep 1
done
复制

场景三：备库节点1宕库

同场景一。

场景四：备库节点1宕机

同场景二。

场景五：主备switch over

主备切换之后，主备要启动相应的service,应用无需更改配置。

场景六：主备failover

主备切换之后，主备要启动相应的service,应用无需更改配置。

四

最佳实践配置

4.1 MAA架构最佳配置

# 
Primary Node1 优先连接主库节点1



复制

PRI_ORCL1 =
(DESCRIPTION =
(ADDRESS_LIST =
(LOAD_BALANCE = off)
(FAILOVER = ON)
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.110)(PORT = 1521)) #主库SCAN IP
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.111)(PORT = 1521)) #备库SCAN IP
)
(CONNECT_DATA =
(service_name = ORCL_R_S1)
(FAILOVER_MODE =
(type = session)
(method = basic)
(retries = 10)
(delay = 5)
)
)
)
复制

# Primary Node2 优先连接主库节点2
复制

PRI_ORCL2 =
(DESCRIPTION =
(ADDRESS_LIST =
(LOAD_BALANCE = off)
(FAILOVER = ON)
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.110)(PORT = 1521)) #主库SCAN IP
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.111)(PORT = 1521)) #备库SCAN IP
)
(CONNECT_DATA =
(service_name = ORCL_R_S2)
(FAILOVER_MODE =
(type = session)
(method = basic)
(retries = 10)
(delay = 5)
)
)
)
复制

# Standby Node1 优先连接备库节点1
复制

STD_ORCL1 =
(DESCRIPTION =
(ADDRESS_LIST =
(LOAD_BALANCE = off)
(FAILOVER = ON)
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.111)(PORT = 1521)) #备库SCAN IP
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.110)(PORT = 1521)) #主库SCAN IP
)
(CONNECT_DATA =
(service_name = ORCL_RD_S1)
(FAILOVER_MODE =
(type = session)
(method = basic)
(retries = 10)
(delay = 5)
)
)
)
复制

# Standy Node2 优先连接主库节点2
复制

STD_ORCL2 =
(DESCRIPTION =
(ADDRESS_LIST =
(LOAD_BALANCE = off)
(FAILOVER = ON)
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.111)(PORT = 1521)) #备库SCAN IP
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.110)(PORT = 1521)) #主库SCAN IP
)
(CONNECT_DATA =
(service_name = ORCL_RD_S2)
(FAILOVER_MODE =
(type = session)
(method = basic)
(retries = 10)
(delay = 5)
)
)
)
复制

4.2 RAC最佳配置(无ADG)

# Primary Node1 优先连接主库节点1

PRI_ORCL1 =
(DESCRIPTION =
(ADDRESS_LIST =
(LOAD_BALANCE = off)
(FAILOVER = ON)
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.110)(PORT = 1521)) #主库SCAN IP
)
(CONNECT_DATA =
(service_name = ORCL_R_S1)
(FAILOVER_MODE =
(type = session)
(method = basic)
(retries = 10)
(delay = 5)
)
)
)
复制

# Primary Node2 优先连接主库节点2

PRI_ORCL2 =
(DESCRIPTION =
(ADDRESS_LIST =
(LOAD_BALANCE = off)
(FAILOVER = ON)
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.110)(PORT = 1521)) #主库SCAN IP
)
(CONNECT_DATA =
(service_name = ORCL_R_S2)
(FAILOVER_MODE =
(type = session)
(method = basic)
(retries = 10)
(delay = 5)
)
)
)
复制

本文作者：王尔贵（上海新炬王翦团队）

本文来源：“IT那活儿”公众号

文章转载自IT那活儿，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。