[20170228]dg的日志传输与应用问题.txt

--//设置参数log_archive_dest_state_2=defer并不能马上停止日志传输与应用,通过测试说明问题:
--//以前使用dgmgrl管理时也遇到,工作中注意:
--//链接:http://blog.itpub.net/267265/viewspace-1825886/

1.环境:

SYS@book> @ &r/ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SYS@book> alter system set log_archive_dest_state_2=defer ;
System altered.

2.检查备库:
SYS@bookdg> @ &r/dg/dg
PROCESS       PID STATUS       CLIENT_P GROUP#  THREAD#  SEQUENCE#     BLOCK#     BLOCKS DELAY_MINS
--------- ------- ------------ -------- ------- ------- ---------- ---------- ---------- ----------
RFS         28660 IDLE         UNKNOWN  N/A           0          0          0          0          0
RFS         28658 IDLE         LGWR     3             1        697        460          1          0
ARCH        27196 CLOSING      ARCH     6             1        696          1        102          0
MRP0        27229 APPLYING_LOG N/A      N/A           1        697        460     102400          0

--//等几秒.
SYS@bookdg> @ &r/dg/dg
PROCESS       PID STATUS       CLIENT_P GROUP#  THREAD#  SEQUENCE#     BLOCK#     BLOCKS DELAY_MINS
--------- ------- ------------ -------- ------- ------- ---------- ---------- ---------- ----------
RFS         28660 IDLE         UNKNOWN  N/A           0          0          0          0          0
RFS         28658 IDLE         LGWR     3             1        697        604          1          0
ARCH        27196 CLOSING      ARCH     6             1        696          1        102          0
MRP0        27229 APPLYING_LOG N/A      N/A           1        697        604     102400          0

--//可以发现MRP0的BLOCK#一直在变化.要正常停止应用.

SYS@bookdg> alter database recover managed standby database cancel;
Database altered.

SYS@bookdg> @ &r/dg/dg
PROCESS       PID STATUS       CLIENT_P GROUP# THREAD#  SEQUENCE#     BLOCK#     BLOCKS DELAY_MINS
--------- ------- ------------ -------- ------ ------- ---------- ---------- ---------- ----------
RFS         28660 IDLE         UNKNOWN  N/A          0          0          0          0          0
RFS         28658 IDLE         LGWR     3            1        697        711          1          0
ARCH        27196 CLOSING      ARCH     6            1        696          1        102          0

--//等几秒.

SYS@bookdg> @ &r/dg/dg
PROCESS       PID STATUS       CLIENT_P GROUP# THREAD#  SEQUENCE#     BLOCK#     BLOCKS DELAY_MINS
--------- ------- ------------ -------- ------ ------- ---------- ---------- ---------- ----------
RFS         28660 IDLE         UNKNOWN  N/A          0          0          0          0          0
RFS         28658 IDLE         LGWR     3            1        697       2479          1          0
ARCH        27196 CLOSING      ARCH     6            1        696          1        102          0

--//RFS进程BLOCK#一直在变化,也就是日志在传输.

3.回到主库:
--//切换日志
SYS@book> alter system archive log current ;
System altered.

SYS@bookdg> @ &r/dg/dg
PROCESS       PID STATUS       CLIENT_P GROUP# THREAD#  SEQUENCE#     BLOCK#     BLOCKS DELAY_MINS
--------- ------- ------------ -------- ------ ------- ---------- ---------- ---------- ----------
RFS         28660 IDLE         UNKNOWN  N/A          0          0          0          0          0
ARCH        27196 CLOSING      ARCH     5            1        697       2048        491          0

--//这样才停止传输与应用.这时即使执行:

SYS@bookdg> alter database recover managed standby database using current logfile disconnect ;
Database altered.

SYS@bookdg> @ &r/dg/dg
PROCESS       PID STATUS       CLIENT_P GROUP# THREAD#  SEQUENCE#     BLOCK#     BLOCKS DELAY_MINS
--------- ------- ------------ -------- ------ ------- ---------- ---------- ---------- ----------
RFS         28660 IDLE         UNKNOWN  N/A          0          0          0          0          0
ARCH        27196 CLOSING      ARCH     5            1        697       2048        491          0
MRP0        28667 WAIT_FOR_LOG N/A      N/A          1        698          0          0          0

--//MRP0 都等待主库传输过来日志.

4.总结:
--//在正常传输日志的情况下,修改参数log_archive_dest_state_2=defer并不能停止当前日志传输,而且等日志切换的时候.
--//停止日志应该执行alter database recover managed standby database cancel;