[20170228]dg的日誌傳輸與應用問題.txt

[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;