ARCH和LGWR进程同步DG日志的区别
我在做Standby RAC实验时,起初使用的是ARCH传输,后来将其改为LGWR传输(实际是LGWR分出的小工进程LNS):
- --之前的设置
- alter system set log_archive_dest_2='SERVICE=mynas ARCH VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=mynas';
- --修改设置,可以在线修改:
- alter system set log_archive_dest_2='SERVICE=mynas VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=mynas';
最直观的就是LGWR进程传输可以延迟很低,甚至基本是实时的,即使是ASYNC,另外还有一些细微的差别。
主库:
- SYS@jyzhao1 >select process, client_process, sequence#, status from v$managed_standby;
- PROCESS CLIENT_P SEQUENCE# STATUS
- --------- -------- ---------- ------------
- ARCH ARCH 146 CLOSING
- ARCH ARCH 137 CLOSING
- ARCH ARCH 147 CLOSING
- ARCH ARCH 147 CLOSING
在主库查询可以看到,只有几个归档进程。
备库:
- SQL> select process, client_process, sequence#, status from v$managed_standby;
- PROCESS CLIENT_P SEQUENCE# STATUS
- --------- -------- ---------- ------------
- ARCH ARCH 146 CLOSING
- ARCH ARCH 113 CLOSING
- ARCH ARCH 0 CONNECTED
- ARCH ARCH 111 CLOSING
- RFS ARCH 0 IDLE
- RFS UNKNOWN 0 IDLE
- RFS UNKNOWN 0 IDLE
- RFS ARCH 0 IDLE
- RFS UNKNOWN 0 IDLE
- RFS UNKNOWN 0 IDLE
- MRP0 N/A 147 WAIT_FOR_LOG
- 11 rows selected.
- SQL> select * from v$standby_log;
- GROUP# DBID THREAD# SEQUENCE# BYTES BLOCKSIZE USED ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME LAST_CHANGE# LAST_TIME
- ---------- ---------------------------------------- ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- ------------ ------------ ------------ ------------ ------------
- 11 UNASSIGNED 1 0 52428800 512 0 NO UNASSIGNED
- 12 UNASSIGNED 1 0 52428800 512 0 NO UNASSIGNED
- 13 UNASSIGNED 1 0 52428800 512 0 NO UNASSIGNED
- 21 UNASSIGNED 2 0 52428800 512 0 NO UNASSIGNED
- 22 UNASSIGNED 2 0 52428800 512 0 NO UNASSIGNED
- 23 UNASSIGNED 2 0 52428800 512 0 NO UNASSIGNED
- 6 rows selected.
在备库查询可以看到,RFS中也只有ARCH进程,SRLs也都没有使用,即使指定使用using current logfile。
主库:
- SYS@jyzhao1 >select process, client_process, sequence#, status from v$managed_standby;
- PROCESS CLIENT_P SEQUENCE# STATUS
- --------- -------- ---------- ------------
- ARCH ARCH 149 CLOSING
- ARCH ARCH 137 CLOSING
- ARCH ARCH 148 CLOSING
- ARCH ARCH 149 CLOSING
- LNS LNS 150 WRITING
我们发现,使用LGWR进程传输,在主库查询可以看到差异,比之前ARCH传输多了一个LNS的进程,这就是LGWR分出来的小工进程。
备库:
- SQL> select process, client_process, sequence#, status from v$managed_standby;
- PROCESS CLIENT_P SEQUENCE# STATUS
- --------- -------- ---------- ------------
- ARCH ARCH 148 CLOSING
- ARCH ARCH 149 CLOSING
- ARCH ARCH 0 CONNECTED
- ARCH ARCH 115 CLOSING
- RFS ARCH 0 IDLE
- RFS UNKNOWN 0 IDLE
- RFS UNKNOWN 0 IDLE
- RFS ARCH 0 IDLE
- RFS UNKNOWN 0 IDLE
- RFS LGWR 116 IDLE
- MRP0 N/A 116 APPLYING_LOG
- PROCESS CLIENT_P SEQUENCE# STATUS
- --------- -------- ---------- ------------
- RFS LGWR 150 IDLE
- RFS UNKNOWN 0 IDLE
- 13 rows selected.
- SQL> select * from v$standby_log;
- GROUP# DBID THREAD# SEQUENCE# BYTES BLOCKSIZE USED ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME LAST_CHANGE# LAST_TIME
- ---------- ---------------------------------------- ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- ------------ ------------ ------------ ------------ ------------
- 11 UNASSIGNED 1 0 52428800 512 0 NO UNASSIGNED
- 12 2509089778 1 150 52428800 512 6417408 YES ACTIVE 4026243 10-AUG-17 4050942 10-AUG-17
- 13 UNASSIGNED 1 0 52428800 512 0 NO UNASSIGNED
- 21 UNASSIGNED 2 0 52428800 512 0 NO UNASSIGNED
- 22 2509089778 2 117 52428800 512 2767360 YES ACTIVE 4044272 10-AUG-17 4050945 10-AUG-17
- 23 UNASSIGNED 2 0 52428800 512 0 NO UNASSIGNED
- 6 rows selected.
我们发现,使用LGWR进程传输,在备库查询可以看到差异,相比之前ARCH传输RFS中只有ARCH进程而言,又多了LGWR 进程,另外SRLs也被正常使用。
主库:
- Thu Aug 10 09:36:43 2017
- ALTER SYSTEM SET log_archive_dest_2='SERVICE=mynas VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=mynas' SCOPE=BOTH;
- Thread 1 advanced to log sequence 150 (LGWR switch)
- Current log# 2 seq# 150 mem# 0: +DATA1/jyzhao/onlinelog/group_2.262.919999045
- Current log# 2 seq# 150 mem# 1: +FRA1/jyzhao/onlinelog/group_2.258.919999049
- Thu Aug 10 09:36:45 2017
- Archived Log entry 287 added for thread 1 sequence 149 ID 0x958da9ee dest 1:
- ******************************************************************
- LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
- ******************************************************************
- ARC3: Standby redo logfile selected for thread 1 sequence 149 for destination LOG_ARCHIVE_DEST_2
- LNS: Standby redo logfile selected for thread 1 sequence 150 for destination LOG_ARCHIVE_DEST_2
- Thu Aug 10 09:59:59 2017
- Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
- Thu Aug 10 10:00:00 2017
- Starting background process VKRM
- Thu Aug 10 10:00:00 2017
- VKRM started with pid=44, OS id=7493
可以看到,相比之前,多了“LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2”字样。
备库:
- Thu Aug 10 09:36:42 2017
- Archived Log entry 35 added for thread 1 sequence 148 ID x958da9ee dest 1:
- Media Recovery Log +FRA/mynas/archivelog/017_08_10/thread_1_seq_148.301.951644203
- Media Recovery Waiting for thread 1 sequence 149
- Thu Aug 10 09:36:45 2017
- Primary database is in MAXIMUM PERFORMANCE mode
- Thu Aug 10 09:36:46 2017
- RFS[12]: Assigned to RFS process 28007
- RFS[12]: Selected log 11 for thread 1 sequence 149 dbid -1785877518 branch 919999037
- RFS[13]: Assigned to RFS process 28005
- RFS[13]: Selected log 12 for thread 1 sequence 150 dbid -1785877518 branch 919999037
- Thu Aug 10 09:36:47 2017
- Archived Log entry 36 added for thread 1 sequence 149 ID x958da9ee dest 1:
- Media Recovery Log +FRA/mynas/archivelog/017_08_10/thread_1_seq_149.302.951644207
- Media Recovery Waiting for thread 2 sequence 115
- Thu Aug 10 09:36:48 2017
- Primary database is in MAXIMUM PERFORMANCE mode
- RFS[14]: Assigned to RFS process 28009
- RFS[14]: Selected log 21 for thread 2 sequence 116 dbid -1785877518 branch 919999037
- Thu Aug 10 09:36:49 2017
- RFS[15]: Assigned to RFS process 28011
- RFS[15]: Selected log 22 for thread 2 sequence 115 dbid -1785877518 branch 919999037
- Archived Log entry 37 added for thread 2 sequence 115 ID x958da9ee dest 1:
- Media Recovery Log +FRA/mynas/archivelog/017_08_10/thread_2_seq_115.303.951644209
- Media Recovery Waiting for thread 1 sequence 150 (in transit)
- Recovery of Online Redo Log: Thread 1 Group 12 Seq 150 Reading mem 0
- Mem# 0: +FRA/mynas/standbylog/standby_group_12.log
- Media Recovery Waiting for thread 2 sequence 116 (in transit)
- Recovery of Online Redo Log: Thread 2 Group 21 Seq 116 Reading mem 0
- Mem# 0: +FRA/mynas/standbylog/standby_group_21.log
- Thu Aug 10 10:02:28 2017
- RFS[14]: Selected log 22 for thread 2 sequence 117 dbid -1785877518 branch 919999037
- Thu Aug 10 10:02:35 2017
- Media Recovery Waiting for thread 2 sequence 117 (in transit)
- Thu Aug 10 10:02:35 2017
- Archived Log entry 38 added for thread 2 sequence 116 ID x958da9ee dest 1:
- Recovery of Online Redo Log: Thread 2 Group 22 Seq 117 Reading mem 0
- Mem# 0: +FRA/mynas/standbylog/standby_group_22.log
可以看到多了“ (in transit)”字样。
这种LGWR传输,即便是默认的ASYNC,正常延迟也都很低,符合绝大部分场景需要:
- SQL> select * from v$dataguard_stats;
- NAME VALUE UNIT TIME_COMPUTED DATUM_TIME
- -------------------------------- ---------------------------------------------------------------- ------------------------------ ------------------------------ ------------------------------
- transport lag +00 00:00:00 day(2) to second(0) interval 08/10/2017 11:43:37 08/10/2017 11:43:35
- apply lag +00 00:00:00 day(2) to second(0) interval 08/10/2017 11:43:37 08/10/2017 11:43:35
- apply finish time +00 00:00:00.000 day(2) to second(3) interval 08/10/2017 11:43:37
- estimated startup time 26 second 08/10/2017 11:43:37
- SQL>
可以看到上面的延迟都是0。实际运维经验,一般11g ADG,不出网络等其他问题,这个延迟基本为0。
来源: http://www.linuxidc.com/Linux/2017-08/146332.htm