troubleshooting Question

Data Guard gap with primary MRP0 WAIT_FOR_LOG

Avatar of dbadm
dbadmFlag for Italy asked on
Disaster RecoveryOracle Database
3 Comments1 Solution12 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Franck Pachot
Oracle DBA

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros