数据库报ORA-16198故障的解决方法分析

数据库报ORA-16198故障的解决方法分析

1. 首先看官方文档关于ORA-16198报错的说明

........................
报错可能原因是因为net_timeout设置低，在以前老版本默认是10，建议更改为30
……………………………
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.
…………………………….
如果设置30还不行，请检查磁盘的IO使用情况或者网络传输情况
…………………………..
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).
……………………………
也有可能是BUG，受影响的版本为11.2.0.1或10.2.0.4，建议升级到11.2.0.2以上的版本
…………………………..
Bug 9259587  Multiple LGWR reconnect attempts in Data Guard MAXIMUM_AVAILABILITY
 This note gives a brief overview bug 9259587. 
Affects:
Product (Component) Oracle Server (Rdbms)
Range of versions believed to be affected Versions BELOW 12.1
Versions confirmed as being affected 11.2.0.1 10.2.0.4
Platforms affected Generic (all / most platforms affected)
Fixed:
This issue is fixed in 12.1 (Future Release) 11.2.0.2 (Server Patch Set)
Symptoms:
Related To:
Hang (Process Spins)
Active Dataguard (ADG)
Physical Standby Database / Dataguard
Description
…………………………………………………
发生的报错，大概类似于下面的显示
…………………………………………………
Rediscovery Notes:
 Alert log contains messages like:
  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
  Errors in file 
  /app/oracle/diag/rdbms/ora11g_dga/ora11g/trace/ora11g_lgwr_290838.trc:
  ORA-16198: Timeout incurred on internal channel during remote archival
  LGWR: Network asynch I/O wait error 16198 log 2 service 'ora11g_DGb'
  LGWR: Error 16198 disconnecting from destination LOG_ARCHIVE_DEST_2 standby 
  host 'ora11g_DGb'
  Destination LOG_ARCHIVE_DEST_2 is UNSYNCHRONIZED
  LGWR: Failed to archive log 2 thread 1 sequence 1422 (16198)
…………………………………………………
In a Data Guard configuration using LGWR SYNC transport on one or more LOG_ARCHIVE_DEST_n parameters, and using a protection mode of MAXIMUM_AVAILABILITY, then if the primary database becomes disconnected from the standby database, LGWR continues to attempt to reconnect to the standby database. It should instead avoid attempts to reconnect until an ARCH process has re-established communication with the standby database.
所以可以确定的是：
报这种错误主要发生在DATAGUARD这种架构上，原因就是主机的日志向备机传输时没在规定时间完成，或无法向备机传送日志，那么我们就下面主要的两种故障原因来进行说明：


2. 参数设置过低导致的故障

可能由于设置的LOG_ARCHIVE_DEST_2的NET_TIMEOUT值过低，导致的日志无法在规定时间传输完成，建议设置成30。
查询NET_TIMEOUT：
SQL> select DEST_NAME,NET_TIMEOUT FROM V$ARCHIVE_DEST;
DEST_NAME                 NET_TIMEOUT
-------------------------         -----------
LOG_ARCHIVE_DEST_1                  0
LOG_ARCHIVE_DEST_2                 30
……………输出省略
查看LOG_ARCHIVE_DEST_2参数：
SQL> show parameter log_archive_dest_2
值为'service=orcl_std reopen=120 lgwr sync valid_for=(online_logfiles,primary_role) db_unique_name=orcl_std'
我没有设置NET_TIMEOUT参数，默认却是30，因为我的版本是11.2.0.3的。
如果你的参数不是30，请进行修改，参考如下：
SQL>ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='service=orcl_std reopen=120 lgwr sync  net_timeout=30 valid_for=(online_logfiles,primary_role) db_unique_name=orcl_std'；
然后观察一下是否还报此类问题。

3. 由于网络不通畅或存储IO繁忙等其他原因导致的故障

如果是由于网络不通畅和存储繁忙的原因导致的报错，请用操作系统命令类似于，tcpdump或IOSTAT,VMSTAT来查看相关资源使用情况，或联系网络，存储管理员来协助分析。
如果以上都没问题，还有一种可能性就是你主机或备机单独改sys密码了，但是相关的备机或主机没有同时改，造成主机向备机验证时失效也是很有可能的。

4. 数据库的BUG

如果以上方法还没有解决问题，你也分析不出具体的原因，恰好你的数据库版本是11.2.0.1或10.2.0.4,那么升级吧少年。。

5. 总结
考虑此类问题，要从多角度分析，比如：参数值低，存储使用情况，网络传输情况，sys密码改了，数据库的BUG等。