Automatic GAP Resolution

Onur ARDAHANLI

4 years ago

I will explan Automatic GAP Resolution in Oracle Data Guard in this article. You may want to read below articles two understand Oracle Data Guard architecture.

“Oracle Data Guard Architecture“,
“Oracle Data Guard Redo Transport Services”

What is Redo GAP?

In some cases, the LNS background process cannot transmit the generated redo data quickly enough and REDO GAP occurs. Some reasons for the occurrence of redo gap are as follows;

Too many commits
Network interruption
Closing the standby database

What is Automatic GAP Resolution?

When the Redo GAP occurs, the LGWR background process continues to write to the “Online Redo Log” file, and when Redo is full, it continues to “Switch” to the next redo log file.
When the switch occurs, the ARCH background process generates an archive. The ARCH background process continues to generate an archive file until the Primary to Standby problem is resolved.
The ARCH process running on the primary database regularly pings the standby database during the interruption and checks to see if it responds.

When the problem between Primary and Standby is solved;

The ARCH process in the primary database determines the last received log file from the Primary by querying the “Standby Control File” over the RFS operation in the standby database.
Data Guard automatically detects the “Archive Log Files” required to close the gap and tries to close the gap via the ARCH process.

Check Log GAP Between Primary and Standby

You can learn log gap with two scripts;

Check Last Processed Archive at Stanby(Execute the script in Primary):

column arsiv_farki format a11
SELECT a.resetlogs_id, DECODE (a.thread#, 1, 'node1', 2, 'node2') HOST, b.last_seq prmy_son_dosya,
       a.applied_seq stdby_son_dosya, b.last_seq - a.applied_seq arsiv_farki, TO_CHAR (a.uygulanan_son_zaman, 'dd/mm/yyyy hh24:mi:ss') stdby_son_zaman
  FROM (SELECT   resetlogs_id, thread#, MAX (sequence#) applied_seq, MAX (next_time) uygulanan_son_zaman
            FROM v$archived_log
           WHERE applied = 'YES'
        GROUP BY resetlogs_id, thread#) a,
       (SELECT   resetlogs_id, thread#, MAX (sequence#) last_seq
            FROM v$archived_log
        GROUP BY resetlogs_id, thread#) b
 WHERE a.thread# = b.thread#
 ORDER BY a.thread#;

column arsiv_farki format a11

SELECT a.resetlogs_id, DECODE (a.thread#, 1, 'node1', 2, 'node2') HOST, b.last_seq prmy_son_dosya,

a.applied_seq stdby_son_dosya, b.last_seq - a.applied_seq arsiv_farki, TO_CHAR (a.uygulanan_son_zaman, 'dd/mm/yyyy hh24:mi:ss') stdby_son_zaman

FROM (SELECT resetlogs_id, thread#, MAX (sequence#) applied_seq, MAX (next_time) uygulanan_son_zaman

FROM v$archived_log

WHERE applied = 'YES'

GROUP BY resetlogs_id, thread#) a,

(SELECT resetlogs_id, thread#, MAX (sequence#) last_seq

FROM v$archived_log

GROUP BY resetlogs_id, thread#) b

WHERE a.thread# = b.thread#

ORDER BY a.thread#;

Check How Much LAG is between Primary and Standby(Execute the script in Standby):

set linesize 500 feedback off pages 0 head off
column value format a12
column name format a13
column time_computed format a25
SELECT  name, value, time_computed FROM v$dataguard_stats where name in ('transport lag','apply lag');

set linesize 500 feedback off pages 0 head off

column value format a12

column name format a13

column time_computed format a25

SELECT name, value, time_computed FROM v$dataguard_stats where name in ('transport lag','apply lag');