阿里云-云小站(无限量代金券发放中)
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

DataGuard开启延时应用的测试

211次阅读
没有评论

共计 4447 个字符,预计需要花费 12 分钟才能阅读完成。

DataGuard 开启延时应用的测试

实验环境:RHEL 6.5 + Oracle 11.2.0.4 GI、DB + Primary RAC(2 nodes)+ Standby RAC(2 nodes)

  • 1. 体验 DG 延时应用的效果
  • 2. 总结:开启、关闭延时应用
  • 3. 测试表脚本

1. 体验 DG 延时应用的效果

主库 log_archive_dest_N 参数当前设定值:

log_archive_dest_2                   string      SERVICE=mynas VALID_FOR=(ONLIN
                                                 E_LOGFILES,PRIMARY_ROLE) DB_UN
                                                 IQUE_NAME=mynas

我需要将其修改,增加 DELAY=N 分钟,我这里为了试验方便,设置了 5 分钟,实际一般都需要根据业务要求延时 1 天(1440)甚至更久。

alter system set log_archive_dest_2 = 'SERVICE=mynas DELAY=5 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=mynas';

设置完成之后,如果备库之前是 USING CURRENT LOGFILE 实时应用,那么还需要将备库应用停止重新以不加 USING CURRENT LOGFILE 方式启动应用,否则将会忽略这个 DELAY 的参数,这一点我们从主库和备库的日志均可以看到。

主库日志:

Tue Aug 15 15:19:05 2017
Archived Log entry 741 added for thread 1 sequence 287 ID 0x97764e10 dest 1:
Tue Aug 15 15:19:05 2017
WARNING: Managed Standby Recovery started with USING CURRENT LOGFILE
  DELAY 5 minutes specified at primary ignored
ARC0: Standby redo logfile selected for thread 1 sequence 287 for destination LOG_ARCHIVE_DEST_2

备库日志:

Tue Aug 15 15:18:28 2017
Media Recovery Waiting for thread 1 sequence 287
Tue Aug 15 15:19:05 2017
WARNING: Managed Standby Recovery started with REAL TIME APPLY
  DELAY 5 minutes specified at primary ignored
RFS[33]: Assigned to RFS process 5482
RFS[33]: Selected log 11 for thread 1 sequence 287 dbid -1785877518 branch 919999037
Tue Aug 15 15:19:06 2017
Archived Log entry 353 added for thread 1 sequence 287 ID x97764e10 dest 1:
ARC3: Archive log thread 1 sequence 287 available in 5 minute(s)
Tue Aug 15 15:19:06 2017
Managed Standby Recovery started with USING CURRENT LOGFILE
Ignoring previously specified DELAY 5 minutes for thread 1 sequence 287
Media Recovery Log +FRA/mynas/archivelog/017_08_15/thread_1_seq_287.563.952096745
Media Recovery Waiting for thread 2 sequence 235

注:我建立一个每秒都会插入一条数据的测试表,可用来更清楚的去辅助验证延迟应用的设置是否生效。

因此, 需要将备库取消应用,再以不加 USING CURRENT LOGFILE 的方式重新开启应用,命令如下:

alter database recover managed standby database cancel;
alter database recover managed standby database disconnect from session;

再观察主库日志:

Tue Aug 15 15:33:22 2017
Archived Log entry 743 added for thread 1 sequence 288 ID x97764e10 dest 1:
Tue Aug 15 15:33:22 2017
ARC0: Standby redo logfile selected for thread 1 sequence 288 for destination LOG_ARCHIVE_DEST_2

备库日志:

Tue Aug 15 15:33:22 2017
RFS[33]: Selected log 11 for thread 1 sequence 288 dbid -1785877518 branch 919999037
Tue Aug 15 15:33:23 2017
Archived Log entry 354 added for thread 1 sequence 288 ID x97764e10 dest 1:
ARC2: Archive log thread 1 sequence 288 available in 4 minute(s)

可以看到延迟应用的设置已经生效。

Tue Aug 15 15:36:11 2017
RFS[33]: Selected log 11 for thread 1 sequence 289 dbid -1785877518 branch 919999037
Tue Aug 15 15:36:12 2017
Archived Log entry 355 added for thread 1 sequence 289 ID x97764e10 dest 1:
ARC3: Archive log thread 1 sequence 289 available in 5 minute(s)
Tue Aug 15 15:36:14 2017
RFS[34]: Assigned to RFS process 6921
RFS[34]: Selected log 21 for thread 2 sequence 235 dbid -1785877518 branch 919999037
Tue Aug 15 15:36:15 2017
Archived Log entry 356 added for thread 2 sequence 235 ID x97764e10 dest 1:
ARC0: Archive log thread 2 sequence 235 available in 3 minute(s)
Tue Aug 15 15:36:20 2017
Media Recovery Delayed for 3 minute(s) (thread 2 sequence 235)
Tue Aug 15 15:39:17 2017
Media Recovery Log +FRA/mynas/archivelog/017_08_15/thread_2_seq_235.566.952097775
Media Recovery Log +FRA/mynas/archivelog/017_08_15/thread_1_seq_287.563.952096745
Media Recovery Log +FRA/mynas/archivelog/017_08_15/thread_1_seq_288.564.952097603
Media Recovery Delayed for 5 minute(s) (thread 1 sequence 289)

注:我这里同时观察测试表的数据,可以看到,实际上延迟应用,是保证达到设定的值之后才能应用对应的归档日志,本身还会有延迟。
当备库日志显示刚刚应用日志时,这时的差距是最小的,基本和设置的时间一致。

2. 总结:开启、关闭延时应用

开启延时应用方法:
主库直接动态修改参数 log_archive_dest_N,加入 DELAY=(单位是分钟)的参数,然后备库确保不加 USING CURRENT LOGFILE 启动应用。

-- 主库:
alter system set log_archive_dest_2 = 'SERVICE=mynas DELAY=5 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=mynas';


-- 备库:
alter database recover managed standby database cancel;
alter database recover managed standby database disconnect from session;

那么如何从延时应用重新改回实时应用呢?
就是再设置回去就 ok 了。

-- 主库:
alter system set log_archive_dest_2 = 'SERVICE=mynas VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=mynas';

-- 备库:
alter database recover managed standby database cancel;
alter database recover managed standby database using current logfile disconnect from session;

注:由于整个操作涉及的参数是动态可以修改的,所以整个过程都可以在线完成,不需要停机。

3. 测试表脚本

每隔 1s 插入一条数据的测试表

-- 业务用户 jingyu 下创建序列和测试表 test
conn jingyu/jingyu
create sequence jingyu.s1;
drop table jingyu.test;
create table jingyu.TEST(id number, insert_time timestamp);

-- 需要 sys 用户授权 dbms_lock 给业务用户 jingyu
grant execute on dbms_lock to jingyu;

-- 创建每隔 1s 插入一条数据的存储过程,持续 10 小时 
create or replace procedure p1 is
begin
for i in  1..36000
loop
insert into jingyu.test select s1.nextval, systimestamp from dual;
commit;
dbms_lock.sleep(1);
end loop;
end;
/

-- 编辑执行存储过程的脚本 
[oracle@jyrac1 ~]$ vi insert.sh
sqlplus jingyu/jingyu <<EOF
exec p1;
EOF

-- 后台执行脚本 
[oracle@jyrac1 ~]$ nohup sh insert.sh &



更多 Oracle 相关信息见 Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址 :http://www.linuxidc.com/Linux/2017-08/146322.htm

正文完
星哥玩云-微信公众号
post-qrcode
 0
星锅
版权声明:本站原创文章,由 星锅 于2022-01-22发表,共计4447字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
【腾讯云】推广者专属福利,新客户无门槛领取总价值高达2860元代金券,每种代金券限量500张,先到先得。
阿里云-最新活动爆款每日限量供应
评论(没有评论)
验证码
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中