Oracle Study之--DataGuard 最大保护模式故障(ORA-16198)

太过爱你忘了你带给我的痛 2022-08-05 07:41 233阅读 0赞

**Oracle Study之--DataGuard 最大保护模式故障(ORA-16198)**

**系统环境：**

**    操作系统：RedHat EL5**

**    Oracle：   Oracle 11gR2 （11.2.0.1.0）**

**故障现象：**

Physical Standby在从Maximum Performance转换到Maximum Protection时，出现以下故障：

**1** **0:13:06 SYS@ prod1>startup force mount;**  
ORACLE instance started.  
Total System Global Area  418484224 bytes  
Fixed Size                  1336932 bytes  
Variable Size             281020828 bytes  
Database Buffers          130023424 bytes  
Redo Buffers                6103040 bytes  
Database mounted.

10:13:30 SYS@ prod1>select name,protection\_mode from v$database;  
NAME      PROTECTION\_MODE  
\--------- --------------------  
PROD1     MAXIMUM PROTECTION  
  
**Open DataBase失败：**  
10:07:04 SYS@ prod1>alter database open;  
alter database open  
\*  
ERROR at line 1:  
ORA-03113: end-of-file on communication channel  
Process ID: 4612  
Session ID: 1 Serial number: 5  
  
**查看告警日志：**  
alter database open  
Thu Jun 11 10:07:10 2015  
LGWR: STARTING ARCH PROCESSES  
Thu Jun 11 10:07:10 2015  
ARC0 started with pid=19, OS id=4614   
ARC0: Archival started  
LGWR: STARTING ARCH PROCESSES COMPLETE  
ARC0: STARTING ARCH PROCESSES  
Thu Jun 11 10:07:10 2015  
ARC1 started with pid=20, OS id=4616   
Thu Jun 11 10:07:10 2015  
ARC2 started with pid=21, OS id=4618   
ARC1: Archival started  
ARC2: Archival started  
ARC0: STARTING ARCH PROCESSES COMPLETE  
ARC0: Becoming the 'no FAL' ARCH  
ARC0: Becoming the 'no SRL' ARCH  
ARC1: Becoming the heartbeat ARCH  
LGWR: Primary database is in MAXIMUM PROTECTION mode  
LGWR: Destination LOG\_ARCHIVE\_DEST\_1 is not serviced by LGWR  
Thu Jun 11 10:07:11 2015  
NSS2 started with pid=18, OS id=4620   
Thu Jun 11 10:07:40 2015  
ORA-16198: LGWR received timedout error from KSR  
Errors in file /u01/app/oracle/diag/rdbms/bjdb/prod1/trace/prod1\_lgwr\_4565.trc:  
ORA-16198: Timeout incurred on internal channel during remote archival  
LGWR: Error 16198 verifying archivelog destination LOG\_ARCHIVE\_DEST\_2  
Destination LOG\_ARCHIVE\_DEST\_2 is UNSYNCHRONIZED  
LGWR: Continuing...  
LGWR: Minimum of 1 applicable standby database required  
Errors in file /u01/app/oracle/diag/rdbms/bjdb/prod1/trace/prod1\_lgwr\_4565.trc:  
ORA-16072: a minimum of one standby database destination is required  
Errors in file /u01/app/oracle/diag/rdbms/bjdb/prod1/trace/prod1\_lgwr\_4565.trc:  
ORA-16072: a minimum of one standby database destination is required  
LGWR (ospid: 4565): terminating the instance due to error 16072  
Instance terminated by LGWR, pid = 4565  
  
**---从日志信息可以看出，所有的归档路径都失败，本地归档及远程归档均失败！  
  
解决方法：  
  
依据Oracle官方建议，修改net\_timeout值：（主备库）**  
  
**10:10:23 SYS@ prod1>alter system set log\_archive\_dest\_2='service=shdb lgwr sync affirm VALID\_FOR=(online\_logfiles,primary\_role) net\_timeout=30 DB\_UNIQUE\_NAME=shdb';**

**10:11:25 SYS@ shdb>alter system set log\_archive\_dest\_2='service=bjdb lgwr sync affirm VALID\_FOR=(online\_logfiles,primary\_role) net\_timeout=30 DB\_UNIQUE\_NAME=bjdb';  
  
  
增加standby redo log：**

**主库：（在Maximum Performance下添加Standby redo logs）**  
10:20:35 SYS@ prod1>select group\#,status ,bytes from v$log;  
  
    GROUP\# STATUS                BYTES  
\---------- ---------------- ----------  
         1 INACTIVE           52428800  
         2 CURRENT            52428800  
         3 INACTIVE           52428800  
  
10:20:54 SYS@ prod1>select member from v$logfile;  
MEMBER  
\------------------------------------------------------------------------------------------------------------------------  
/u01/app/oracle/oradata/prod1/redo03.log  
/u01/app/oracle/oradata/prod1/redo02.log  
/u01/app/oracle/oradata/prod1/redo01.log  
/u01/app/oracle/oradata/prod1/std\_redo01.log  
/u01/app/oracle/oradata/prod1/std\_redo02.log  
6 rows selected.  
  
10:21:03 SYS@ prod1>alter database add standby logfile   
10:21:25   2  '/u01/app/oracle/oradata/prod1/std\_redo03.log' size 50m;  
Database altered.  
  
10:21:46 SYS@ prod1>alter database add standby logfile  
10:21:51   2  '/u01/app/oracle/oradata/prod1/std\_redo04.log' size 50m;  
Database altered.  
  
10:01:48 SYS@ shdb>select member from v$logfile;  
MEMBER  
\------------------------------------------------------------------------------------------------------------------------  
/u01/app/oracle/oradata/shdb/redo03.log  
/u01/app/oracle/oradata/shdb/redo02.log  
/u01/app/oracle/oradata/shdb/redo01.log  
/disk2/arch\_prod11\_0\_881851982.dbf  
/u01/app/oracle/oradata/shdb/std\_redo01.log  
/u01/app/oracle/oradata/shdb/std\_redo02.log  
6 rows selected.  
  
**备库：**  
  
10:18:17 SYS@ shdb>alter database open;  
Database altered.  
  
10:20:21 SYS@ shdb>select group\#,status ,bytes from v$log;  
  
    GROUP\# STATUS                BYTES  
\---------- ---------------- ----------  
         1 CLEARING           52428800  
         2 CLEARING           52428800  
         3 CLEARING\_CURRENT   52428800  
  
10:20:45 SYS@ shdb>alter database add standby logfile   
10:22:41   2  '/u01/app/oracle/oradata/shdb/std\_redo03.log' size 50m;  
Database altered.  
  
10:22:57 SYS@ shdb>alter database add standby logfile  
10:23:02   2  '/u01/app/oracle/oradata/shdb/std\_redo04.log' size 50m;  
Database altered.  
  
10:23:14 SYS@ shdb>col member for a50  
10:23:23 SYS@ shdb>select group\#,member from v$logfile;  
    GROUP\# MEMBER  
\---------- --------------------------------------------------  
         3 /u01/app/oracle/oradata/shdb/redo03.log  
         2 /u01/app/oracle/oradata/shdb/redo02.log  
         1 /u01/app/oracle/oradata/shdb/redo01.log  
         5 /u01/app/oracle/oradata/shdb/std\_redo01.log  
         6 /u01/app/oracle/oradata/shdb/std\_redo02.log  
         7 /u01/app/oracle/oradata/shdb/std\_redo03.log  
         8 /u01/app/oracle/oradata/shdb/std\_redo04.log  
8 rows selected.

**---经过以上方式处理后，问题依旧，在Maximum Protection模式下主库依然不能被Open ；但在Maximum Availablity 和 Maximum Performance下主库可以Open 。出错原因依旧在探索。。。**

**参考文档：**

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

**http://blog.itpub.net/28546804/viewspace-1260003/**

**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等。**

[ORA-16198]: http://blog.itpub.net/28546804/viewspace-1260003/