Oracle DataGuard環境中主庫收到ORA-16198報錯

    客戶的一套Oracle Active DataGuard環境中，主庫在每天的最高峰的時間段內都會收到如下的報錯：
Fri Apr 24 17:25:59 2015
ORA-16198: LGWR received timedout error from KSR
LGWR: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (16198)
LGWR: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
Error 16198 for archive log file 1 to 'afabdg01'

參考如下的MOS文章：
Redo Transport Services fails with ORA-16198 when using SYNC (synchronous) mode (Doc ID 808469.1)

In this Document

Symptoms

Cause

Solution

References

---

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.1 and later
Information in this document applies to any platform.
***Checked for relevance on 26-Feb-2014***

This will affect LGWR SYNC transport mode in 10.2.0.x databases and SYNC transport mode in 11.2.0.x databases

Symptoms

Redo Transport Services failed with ORA-16198 from primary database to either the physical standby database or logical standby database using LGWR SYNC mode.

The primary alert log file showed:

If you use Data Guard Broker, then the primary drc log showed:

Cause

The NET_TIMEOUT attribute in the LOG_ARCHIVE_DEST_2 on the primary is set too low so that
LNS couldn't finish sending redo block in 10 seconds in this example.

log_archive_dest_2 service="(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PR
OTOCOL=tcp)(HOST=abc)(PORT=1521)))(CONNECT
_DATA=(SERVICE_NAME=xyz_STANDBY_XPT.world)(
INSTANCE_NAME=xyz)(SERVER=dedicated)))",
LGWR SYNC AFFIRM delay=0 OPTIONAL max_failure=0
max_connections=1 reopen=300 db_unique_name="
xyz_STANDBY" register net_timeout=10 valid
_for=(online_logfile,primary_role)

Noticed that you used LGWR SYNC log transport mode and NET_TIMEOUT was set to 10 .

Solution

You'll need to increase the NET_TIMEOUT value in the LOG_ARCHIVE_DEST_2 on the primary to at least 15 to 20 seconds depends on your network speed.

If you don't use Data Guard Broker, then you could change LOG_ARCHIVE_DEST_2 from SQL*Plus using ALTER SYSTEM command. For example,

SQL>ALTER SYSTEM SET LOG_ARCHIVE_DEST_2 SERVICE=xyz_STANDBY
LGWR SYNC DB_UNIQUE_NAME=xyz_STANDBY NET_TIMEOUT=30 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)

If you use Data Guard Broker, then you will need to modify NetTimeout property from DGMGRL or Grid Control.

For example, connect to the DGMGRL command-line interface from the primary machine,

DGMGRL> connect sys/

DGMGRL> EDIT DATABASE '' SET PROPERTY NetTimeout = 30;

=======================================================================

Note: If NET_TIMEOUT attribute has already been set to 30, and you still get ORA-16198, that means

LNS couldn't finish sending redo block in 30 seconds.

The slowness may caused by:

1. Operating System. Please keep track of OS usage (like iostat).

2. Network. Please keep track network flow (like tcpdump).

 

 The purpose here is to figure out if the slowness is caused by temporary OS glitch or temporary network glitch. 

    出現這個報錯是由於在預設的NET_TIMEOUT時間（10秒）內主庫LGWR程式沒有將資料完整的傳送到備庫，可以將NET_TIMEOUT設定為15或者30秒來增加LGWR傳送資料到備庫的時間，減少出現這個問題的機率。如果NET_TIMEOUT設定為30秒仍然存在此問題，那麼就需要考慮是否是主庫到備庫的網路存在效能問題或存在一定的故障，對於WAN外網的Standby資料庫最好不要使用LGWR SYNC進行實時同步，使用ARC NSYNC同步更合適。

--end--