dbadm
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
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;
Thanks
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
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
I can see GAP is 46, How Can I fix a standby database gap?Thanks
Hi,
It seems you should ENABLE CONFIGURATION from DGMGRL
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;
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.