Avatar of dbadm
dbadm
Flag for Italy

asked on 

Data Guard gap with primary MRP0 WAIT_FOR_LOG

Hi,
my Oracle version is 18c on Linux
I've following DataGuard configuration:

DGMGRL> show configuration

Configuration - DR_MMFP

  Protection Mode: MaxPerformance
  Members:
  MMFP    - Primary database
    MMFP_DR - Physical standby database


Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

from standby MMFP_DR
SQL> select inst_id, process, status, thread#, sequence#, block#, blocks from gv$managed_standby where process in ('RFS','LNS','MRP0');


   INST_ID PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
---------- --------- ------------ ---------- ---------- ---------- ----------
         1 RFS       IDLE                  0          0          0          0
         1 LNS       CLOSING               1      39317     153237         22
         1 MRP0      WAIT_FOR_LOG          1      39318          0          0
         1 RFS       IDLE                  0          0          0          0
         1 RFS       IDLE                  0          0          0          0
         1 RFS       IDLE                  0          0          0          0
         1 RFS       IDLE                  0          0          0          0
         1 RFS       IDLE                  0          0          0          0
         1 RFS       IDLE                  0          0          0          0
         1 RFS       IDLE                  0          0          0          0
         1 RFS       IDLE                  0          0          0          0

Open in new window



From Primary MMFP:
SELECT   (SELECT db_unique_name
            FROM v$database) AS db_unique_name, p.thread#,
         p.dest_id AS primary_dest_id, d.dest_id AS standby_dest_id,
         primary_archived_sequence, standby_applied_sequence,
         primary_archived_sequence - standby_applied_sequence AS gap
    FROM (SELECT   dest_id, thread#,
                   MAX (sequence#) AS primary_archived_sequence
              FROM v$archived_log
             WHERE standby_dest = 'NO'
          GROUP BY dest_id, thread#) p,
         (SELECT   dest_id, thread#,
                   MAX (sequence#) AS standby_applied_sequence
              FROM v$archived_log
             WHERE standby_dest = 'YES' AND applied = 'YES'
          GROUP BY dest_id, thread#) d
   WHERE p.thread# = d.thread#
     AND p.dest_id IN (
            SELECT SUBSTR (NAME, -1, 1)
              FROM v$parameter
             WHERE NAME LIKE 'log_archive_dest_%'
               AND NAME NOT LIKE '%state%'
               AND VALUE IS NOT NULL)
     AND d.dest_id IN (
            SELECT SUBSTR (NAME, -1, 1)
              FROM v$parameter
             WHERE NAME LIKE 'log_archive_dest_%'
               AND NAME NOT LIKE '%state%'
               AND VALUE IS NOT NULL)
ORDER BY db_unique_name, p.thread#, d.dest_id;

DB_UNIQUE_NAME                    THREAD# PRIMARY_DEST_ID STANDBY_DEST_ID PRIMARY_ARCHIVED_SEQUENCE STANDBY_APPLIED_SEQUENCE  GAP
------------------------------ ---------- --------------- --------------- ------------------------- ------------------------ ----------
MMFP                                    1               1               2             39362                     39316         46

Open in new window

I can see GAP is 46, How Can I fix a standby database gap?

Thanks
Disaster RecoveryOracle Database

Avatar of undefined
Last Comment
Franck Pachot

8/22/2022 - Mon