Link to home
Start Free TrialLog in
Avatar of dbadm
dbadmFlag 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
Avatar of Franck Pachot
Franck Pachot
Flag of Switzerland image

Hi,
Configuration Status:
DISABLED 
It seems you should ENABLE CONFIGURATION from DGMGRL
Avatar of dbadm

ASKER

Below the steps to solve the issue:

1) alter database recover managed standby database cancel;
2) ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
3) scp to securely copy archive log files from Primary directory to Standby directory.
4) from DGMGRL enable configuration
5) select PROCESS,STATUS, THREAD#,SEQUENCE#, BLOCK#, BLOCKS from v$managed_standby;

PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH      CLOSING               1      39245     550912       1803
DGRD      ALLOCATED             0          0          0          0
DGRD      ALLOCATED             0          0          0          0
ARCH      CLOSING               1      39207     137541       5018
ARCH      CLOSING               1      39494     122880       1254
ARCH      CLOSING               1      39316     233472        196
MRP0      APPLYING_LOG          1      39356     212007    3360800
RFS       WRITING               1      39364     163841       2048
RFS       IDLE                  1          0          0          0
LNS       WRITING               1      39495       3011          1
RFS       RECEIVING             1      39495       3012          1

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Franck Pachot
Franck Pachot
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial