共计 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 >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 >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 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。
更多 Oracle 相关信息见 Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12
本文永久更新链接地址 ：http://www.linuxidc.com/Linux/2017-08/146332.htm
正文完
星哥玩云-微信公众号
