ARCH和LGWR进程同步DG日志的区别

Posted on August 10, 2017 by Alfred Zhao

我在做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，另外还有一些细微的差别。

1.ARCH进程传输

主库：

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。

2.LGWR进程传输

主库：

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也被正常使用。

alert日志上也有所区别：

主库：

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 0x958da9ee dest 1:
Media Recovery Log +FRA/mynas/archivelog/2017_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 0x958da9ee dest 1:
Media Recovery Log +FRA/mynas/archivelog/2017_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 0x958da9ee dest 1:
Media Recovery Log +FRA/mynas/archivelog/2017_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 0x958da9ee 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。

This entry was posted in Oracle体系原理 and tagged , , . Bookmark the permalink.