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

164次阅读

共计 6692 个字符，预计需要花费 17 分钟才能阅读完成。

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

1.ARCH 进程传输
2.LGWR 进程传输

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

主库：

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>