暂无图片
暂无图片
1
暂无图片
暂无图片
暂无图片

关于standby logfile 的 thread问题

原创 zyw 2018-12-12
2983

问题描述

配置 dg 时,我们需要配置 standby logfile,里面有个参数是 thread ,这个 thread 究竟起到什么作用,如果不按照官档配置会存在什么样的问题呢?

专家解答

standby logfile 是我们配置 DG 时所需要的,之前在配置 DG 时,一直对 standby logfile 中的 thread 参数有些模糊,官方的手册是:

The standby redo log must have at least one more redo log group than the redo log at the redo source database, for each redo thread at the redo source database. At the redo source database, query the V$LOG view to determine how many redo log groups are in the redo log at the redo source database and query the V$THREAD view to determine how many redo threads exist at the redo source database。


这么一大长串,意思就是说备库每个 group 的 standby logfile 必须比主库的多一两个,在就是说到了这个 thread 个数要和主库的相等。


如果主库有多个 thread,而备库只有一个 thread,是否会影响 dg 同步呢,今天做了个测试,来验证下 thread 的作用。


情形一:备库只创建了 thread 1 的 standby log


主库1节点

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            +DATA
Oldest online log sequence     33
Next log sequence to archive   34
Current log sequence           34


主库2节点

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            +DATA
Oldest online log sequence     32
Next log sequence to archive   33
Current log sequence           33


备库状态检查

SQL> /
PROCESS          PID STATUS               GROUP#                                   RESETLOG_ID    THREAD#  SEQUENCE#
--------- ---------- -------------------- ---------------------------------------- ----------- ---------- ----------
ARCH           66045 CONNECTED            N/A                                                0          0          0
ARCH           66047 CONNECTED            N/A                                                0          0          0
ARCH           66049 CONNECTED            N/A                                                0          0          0
ARCH           66051 CONNECTED            N/A                                                0          0          0
RFS            66161 IDLE                 N/A                                                0          0          0
RFS            66089 IDLE                 N/A                                                0          0          0
RFS            66159 IDLE                 3                                          961418213          2         33
MRP0           66110 WAIT_FOR_LOG         N/A                                        961418213          2         33
RFS            66122 IDLE                 N/A                                                0          0          0
RFS            66151 IDLE                 N/A                                                0          0          0
RFS            66157 IDLE                 N/A                                                0          0          0
RFS            66153 IDLE                 2                                          961418213          1         34
RFS            66155 IDLE                 N/A                                                0          0          0


当前主库1节点 redo seq 是 34,主库2节点的 redo seq 是 33,通过备库的 RFS 可以看到备库也进行了接收,但备库 mrp0 并没有应用主库2节点的 seq 33 的 redo,而是在 wait_for_log。


1节点做switch

SQL> alter system switch logfile;

System altered.
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            +DATA
Oldest online log sequence     34
Next log sequence to archive   35
Current log sequence           35


备库查询

PROCESS          PID STATUS               GROUP#                                   RESETLOG_ID    THREAD#  SEQUENCE#
--------- ---------- -------------------- ---------------------------------------- ----------- ---------- ----------
ARCH           66045 CONNECTED            N/A                                                0          0          0
ARCH           66047 CONNECTED            N/A                                                0          0          0
ARCH           66049 CONNECTED            N/A                                                0          0          0
ARCH           66051 CLOSING              5                                          961418213          1         34
RFS            66161 IDLE                 N/A                                                0          0          0
RFS            66089 IDLE                 N/A                                                0          0          0
RFS            66159 IDLE                 3                                          961418213          2         33
MRP0           66110 WAIT_FOR_LOG         N/A                                        961418213          2         33
RFS            66122 IDLE                 N/A                                                0          0          0
RFS            66151 IDLE                 N/A                                                0          0          0
RFS            66157 IDLE                 N/A                                                0          0          0
RFS            66153 IDLE                 1                                          961418213          1         35
RFS            66155 IDLE                 N/A                                                0          0          0


13 rows selected.


我们在主库1节点做 switch,可以在备库发现 seq 34 已经被接收了,rfs 的 seq 35 目前是 idle,MRP0 的状态仍然是 wait_for_log。


SQL> select inst_id,name,value,time_computed,DATUM_TIME,sysdate from gv$dataguard_stats order by inst_id;
 ID NAME                           VALUE                          TIME_COMPUTED        LAST_RECEIVED_TIME   SYSDATE
--- ------------------------------ ------------------------------ -------------------- -------------------- -------------------
  1 transport lag                  +00 00:00:00                   06/23/2017 04:47:39  06/23/2017 04:47:38  2017-06-23 04:47:39
    estimated startup time         19                             06/23/2017 04:47:39                       2017-06-23 04:47:39
    apply finish time                                             06/23/2017 04:47:39                       2017-06-23 04:47:39
    apply lag                      +00 01:48:21                   06/23/2017 04:47:39  06/23/2017 04:47:38  2017-06-23 04:47:39


可以看到,日志应用存在延迟。


情形二:备库按照主库的 thread 情况,分别创建了不同的 thread 的 standby log


PROCESS          PID STATUS               GROUP#                                   RESETLOG_ID    THREAD#  SEQUENCE#
--------- ---------- -------------------- ---------------------------------------- ----------- ---------- ----------
ARCH           66045 CONNECTED            N/A                                                0          0          0
ARCH           66047 CONNECTED            N/A                                                0          0          0
ARCH           66049 CONNECTED            N/A                                                0          0          0
ARCH           66051 CLOSING              5                                          961418213          1         34
RFS            66161 IDLE                 N/A                                                0          0          0
RFS            66089 IDLE                 N/A                                                0          0          0
RFS            66159 IDLE                 4                                          961418213          2         34
MRP0           66195 APPLYING_LOG         N/A                                        961418213          2         34
RFS            66122 IDLE                 N/A                                                0          0          0
RFS            66151 IDLE                 N/A                                                0          0          0
RFS            66157 IDLE                 N/A                                                0          0          0
RFS            66153 IDLE                 1                                          961418213          1         35
RFS            66155 IDLE                 N/A                                                0          0          0

这个时候如果去查看 alert 日志的话,就可以发现备库的 thread 1 的 standby logfile再接收主库 thread 1 的日志,thread 2 standby logfile 在接收主库 thread 2 的日志,以此类推,备库的 thread N 在接收主库 thread N 的日志。

对于主库为 rac 的多节点数据库,比如主库是3节点,备库在创建 standby 时,也需要创建 thread 为3个,这样才能保证real-time apply;

如果只创建了 thread 1,并不会影响 archive log 的传输和应用,但备库不会采用 real-time apply,主库 online redo 无法做到实时传输应用,只在归档切换后备库才会应用。

最后修改时间:2019-03-20 21:15:28
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论