Redo Transport to the standby has stopped after the ORA-16198 error.
ERROR
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 4 to ‘mwstdbydb’
ORA-16198: LGWR received timedout error from KSR
LGWR: Error 16198 disconnecting from destination LOG_ARCHIVE_DEST_2 standby host ‘hostname’
SOLUTİON
You may encounter this error if a network delay occurs while the Redo Transport mode of the error is SYNC. On top of that, if you set Redo Transport mode as ASYNC, the problem will be solved. Solution steps are below.
Step1:
Check the data is sync by quering the tables that frequently changed on primary and standby.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SQL> select count(*) from yourtablename; COUNT(*) ---------- x [MWSTDBYDB1] SQL> select count(*) from yourtablename; COUNT(*) ---------- y |
Step2:
Check if there is a lag on standby?
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SQL> set linesize 9000 SQL> column name format a25 SQL> column value format a20 SQL> column time_computed format a25 SQL> SELECT name, value, time_computed FROM v$dataguard_stats; NAME VALUE TIME_COMPUTED ------------------------- -------------------- ------------------------- transport lag +00 00:20:30 04/06/2017 14:37:30 apply lag +00 00:20:30 04/06/2017 14:37:30 apply finish time +00 00:01:21.338 04/06/2017 14:37:30 estimated startup time 52 04/06/2017 14:37:30 |
Step3:
At the end of the researches, it was seen that the problem was a network delay when the Redo Transport mode was SYNC. For this reason, Redo Transport mode was taken to ASYNC.
Set Redo Transport Mode to ASYNC:
On Primary:
|
1 2 |
SQL> alter system set log_archive_dest_2='SERVICE=standbydb ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=mwstdbydb' scope=both sid='*'; System altered. |
After the above procedure, the alert logs in the primary are as follows.
PrimaryInstance1
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
Thu Apr 06 14:40:24 2017 NSA2 started with pid=1094, OS id=6564 Thu Apr 06 14:40:28 2017 LGWR: Failed to archive log 4 thread 1 sequence 24591 (16198) Thread 1 advanced to log sequence 24592 (LGWR switch) Current log# 5 seq# 24592 mem# 0: +DATA/mwdb/onlinelog/group_5.268.864559847 LNS: Standby redo logfile selected for thread 1 sequence 24592 for destination LOG_ARCHIVE_DEST_2 Thu Apr 06 14:40:30 2017 ALTER SYSTEM SET log_archive_dest_2='SERVICE=mwstdbydb ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=mwstdbydb' SCOPE=BOTH; Thu Apr 06 14:40:30 2017 LOGMINER: End mining logfile for session 4 thread 1 sequence 24591, +DATA/mwdb/onlinelog/group_4.273.864560049 LOGMINER: Begin mining logfile for session 4 thread 1 sequence 24592, +DATA/mwdb/onlinelog/group_5.268.864559847 LOGMINER: End mining logfile for session 4 thread 2 sequence 20308, +DATA/mwdb/onlinelog/group_6.269.864559929 LOGMINER: Begin mining logfile for session 4 thread 2 sequence 20309, +DATA/mwdb/onlinelog/group_7.265.864560013 Thu Apr 06 14:40:42 2017 Archived Log entry 57955 added for thread 1 sequence 24591 ID 0x6e72f9d8 dest 1: Thu Apr 06 14:40:42 2017 ARCd: Standby redo logfile selected for thread 1 sequence 24591 for destination LOG_ARCHIVE_DEST_2 Thu Apr 06 14:44:10 2017 LOGMINER: parent not found: XID 0x1c31.007.001131de LOGMINER: thread 1 RBA 0x006010.00011cf8.00bc, Parent XID 0x1c31.007.001131de Thu Apr 06 14:54:42 2017 ALTER SYSTEM SET log_archive_dest_2='SERVICE=mwstdbydb ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=mwstdbydb' SCOPE=BOTH SID='*'; |
PrimaryInstance2
|
1 2 3 4 5 6 7 8 9 10 11 |
Thu Apr 06 14:40:27 2017 NSA2 started with pid=1194, OS id=7826 Thu Apr 06 14:40:32 2017 LGWR: Failed to archive log 6 thread 2 sequence 20308 (16198) Thread 2 advanced to log sequence 20309 (LGWR switch) Current log# 7 seq# 20309 mem# 0: +DATA/mwdb/onlinelog/group_7.265.864560013 LNS: Standby redo logfile selected for thread 2 sequence 20309 for destination LOG_ARCHIVE_DEST_2 Thu Apr 06 14:40:40 2017 Archived Log entry 57954 added for thread 2 sequence 20308 ID 0x6e72f9d8 dest 1: Thu Apr 06 14:40:40 2017 ARCf: Standby redo logfile selected for thread 2 sequence 20308 for destination LOG_ARCHIVE_DEST_2 |
StandbyInstance1
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
Thu Apr 06 14:40:30 2017 Primary database is in MAXIMUM PERFORMANCE mode RFS[16]: Assigned to RFS process 12056 RFS[16]: Selected log 12 for thread 1 sequence 24592 dbid 1853001179 branch 864558941 Thu Apr 06 14:40:33 2017 Primary database is in MAXIMUM PERFORMANCE mode RFS[17]: Assigned to RFS process 12058 RFS[17]: Selected log 16 for thread 2 sequence 20309 dbid 1853001179 branch 864558941 Thu Apr 06 14:40:40 2017 RFS[18]: Assigned to RFS process 12074 RFS[18]: Selected log 17 for thread 2 sequence 20308 dbid 1853001179 branch 864558941 Thu Apr 06 14:40:42 2017 RFS[19]: Assigned to RFS process 12076 RFS[19]: Selected log 11 for thread 1 sequence 24591 dbid 1853001179 branch 864558941 Thu Apr 06 14:40:47 2017 Archived Log entry 536 added for thread 2 sequence 20308 ID 0x6e72f9d8 dest 1: Thu Apr 06 14:40:54 2017 Archived Log entry 537 added for thread 1 sequence 24591 ID 0x6e72f9d8 dest 1: |
StandbyInstance2
|
1 2 3 4 5 6 7 8 9 |
Thu Apr 06 14:40:55 2017 Media Recovery Waiting for thread 1 sequence 24592 (in transit) Recovery of Online Redo Log: Thread 1 Group 12 Seq 24592 Reading mem 0 Mem# 0: +DATA/mwstdbydb/onlinelog/group_12.270.939160351 Mem# 1: +FRA/mwstdbydb/onlinelog/group_12.264.939160353 Media Recovery Waiting for thread 2 sequence 20309 (in transit) Recovery of Online Redo Log: Thread 2 Group 16 Seq 20309 Reading mem 0 Mem# 0: +DATA/mwstdbydb/onlinelog/group_16.266.939160365 Mem# 1: +FRA/mwstdbydb/onlinelog/group_16.260.939160367 |
Step4:
Check if there is a lag on standby again.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SQL> set linesize 9000 SQL> column name format a25 SQL> column value format a20 SQL> column time_computed format a25 SQL> SELECT name, value, time_computed FROM v$dataguard_stats; NAME VALUE TIME_COMPUTED ------------------------- -------------------- ------------------------- transport lag +00 00:00:00 04/06/2017 14:42:23 apply lag +00 00:00:00 04/06/2017 14:42:23 apply finish time +00 00:00:00.000 04/06/2017 14:42:23 estimated startup time 52 04/06/2017 14:42:23 |
Step5:
Check the data is sync by quering the tables that frequently changed on primary and standby again.
