log_archive_dest_n的net_timeout和reopen参数的关系

gdpr-dba

首先看看官方文档的描述：

NET_TIMEOUT=seconds
Specifies the number of seconds the log writer process on the primary system waits for status from the network server (LNSn) process before terminating the network connection. The default is 180 seconds.

Requires attributes ...

LGWR with SYNC

REOPEN[=seconds]
Specifies the minimum number of seconds before the archiver processes (ARCn) or the log writer process (LGWR) should try again to access a previously failed destination. The default is 300 seconds.

MAX_FAILURE
Controls the consecutive number of times redo transport services attempt to reestablish communication and transmit redo data to a failed destination before the primary database permanently gives up on the standby database.

以下是我个人的理解：
假如同时指定lgwr,net_timeout,reopen和max_failure，意思是不是lgwr首先会等待net_timeout时间，如果失败了则计入一次failure，然后间隔reopen秒再重新尝试，直到max_failure终止尝试。

但是觉得我的理解有些不妥，因为net_timeout是用在sync模式下的参数，那这样的话岂不是lgwr会被hang住很久？

而且官方文档里也没有明确说明net_timeout和reopen有什么关系，求刘大解惑，谢谢。

Maclean Liu(刘相兵

NET_TIMEOUT specifies the number of seconds the log writer process on the primary system waits for status from the network server (LNSn) process before terminating the network connection


Maximum Protection           SYNC  Stall primary until acknowledgement is received from replica
Maximum Availability           SYNC Stall primary until acknowledgement is received or net_timeout threshold period expires – then resume processing
Maximum Performance     ASYNC Primary never waits for standby acknowledgement


Set net_timeout parameter to override TCP timeout
Default value is 180 seconds



The NET_TIMEOUT attribute is optional. However, if you do not specify the NET_TIMEOUT attribute it will be set to 180 seconds, but the primary database can potentially stall. To avoid this situation, specify a small, nonzero value for the NET_TIMEOUT attribute so the primary database can continue operation after the user-specified timeout interval expires when waiting for status from the network server.

The NET_TIMEOUT attribute is used only when the log writer process transmits redo data using a network server (LNSn) process.

The log writer process waits for the specified amount of time to receive status about the network I/O. If there is a possible network disconnection, even one that was terminated due to a network timeout, the log writer process automatically tries to reconnect to the standby database to resolve network brownouts and false network terminations. Typically, except when the network is physically broken, the log writer process can successfully reconnect to the network. The reconnection attempts continue for a period of time that depends on the following factors:

    The value of the NET_TIMEOUT attribute on the primary database.

    The protection mode of the primary database, which determines the maximum amount of time that the reconnection will take. Use the following time estimates as a guideline for how long the log writer process will try to reconnect to the standby database:

        In maximum protection mode, the log writer process tries to reconnect for approximately 5 minutes.

        In maximum availability mode, the log writer process tries to reconnect within the NET_TIMEOUT value.

For example, a primary database operating in the maximum availability protection mode with a NET_TIMEOUT attribute value set to 60 seconds could actually take a minimum of 1 minute to connect or up to 3 minutes to terminate the connection to the standby database.



最大保护模式下, LGWR SYNC 若LNS 5分钟内未返回 则shutdown instance(有文档反应实际为7.5分钟)

最大可用性模式下，LGWR SYNC 若超过net_timeout(默认180)，若LNS进程仍未返回则继续工作， 在此期间lgwr 确实会停顿potentially stall，为了避免这种停顿，Oracle建议在网络较好的环境中设置更小的net_timeout(Although a minimum value of 1 second is allowed, Oracle recommends 8 to 10 seconds as a minimum to avoid false errors and disconnection from the standby database.)