|
DataGuard开启延时应用的测试 实验环境：RHEL 6.5 + Oracle 11.2.0.4 GI、DB + Primary RAC（2 nodes）+ Standby RAC（2 nodes） 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 0x97764e10 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/2017_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 0x97764e10 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 0x97764e10 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 0x97764e10 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 0x97764e10 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/2017_08_15/thread_2_seq_235.566.952097775 Media Recovery Log +FRA/mynas/archivelog/2017_08_15/thread_1_seq_287.563.952096745 Media Recovery Log +FRA/mynas/archivelog/2017_08_15/thread_1_seq_288.564.952097603 Media Recovery Delayed for 5 minute(s) (thread 1 sequence 289)注：我这里同时观察测试表的数据，可以看到，实际上延迟应用，是保证达到设定的值之后才能应用对应的归档日志，本身还会有延迟。 那么如何从延时应用重新改回实时应用呢？ 注：由于整个操作涉及的参数是动态可以修改的，所以整个过程都可以在线完成，不需要停机。 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 & (责任编辑：) |