Oracle Data Guard MRP Commands: Complete Start, Stop & Monitor Guide

Essential commands for managing Managed Recovery Process (MRP) in Oracle Data Guard environments

📅 Updated: 2026 | ⏱️ 15 min read | 🎯 Oracle DBA Guide

🎯 What This Guide Covers

This comprehensive reference provides all essential Oracle Data Guard commands for managing the Managed Recovery Process (MRP). Learn to start and stop redo apply operations, enable real-time apply for Active Data Guard, monitor synchronization status, detect archive gaps, and validate primary-standby consistency.

Applies to: Oracle 11g, 12c, 18c, 19c, 21c, 23c | Physical Standby Databases | RAC and Single Instance

🚀 START Operations: Enable Redo Apply (MRP Process)

Start redo apply in foreground:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;

⚠️ Note: Foreground recovery keeps your session active. Use background or real-time apply for production environments.

Start real-time redo apply:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

✅ Best Practice: USING CURRENT LOGFILE enables real-time apply from standby redo logs, essential for Active Data Guard and minimal RTO.

Start redo apply in background:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
or
SQL> RECOVER MANAGED STANDBY DATABASE DISCONNECT;

⛔ STOP Operations: Disable Redo Apply (MRP Process)

Stop redo apply process on the Standby database (stop MRP):

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

🚨 Important: Always stop MRP before maintenance operations, opening standby in read-only mode, or applying patches.

📊 Monitoring & Validation Commands

Check redo apply and Media recovery service status:

SQL> SELECT PROCESS,STATUS, THREAD#,SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY ;

If managed standby recovery is not running or not started with real-time apply, restart managed recovery with real-time apply enabled:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Gather Data Guard configuration information(standby)

SQL> SELECT DATABASE_ROLE,OPEN_MODE, PROTECTION_MODE FROM V$DATABASE

DATABASE_ROLE OPEN_MODE PROTECTION_MODE
---------------- -------------------- --------------------
PHYSICAL STANDBY MOUNTED MAXIMUM PERFORMANCE

SQL> SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS WHERE RECOVERY_MODE!='IDLE';

RECOVERY_MODE
-----------------------
MANAGED REAL TIME APPLY

✅ Expected Result: MANAGED REAL TIME APPLY confirms real-time apply is active and functioning correctly.

Calculate the Redo bytes per second

SQL> SELECT SUM (BLOCKS * BLOCK_SIZE) / 1024 / 1024 / 60 / 60 / 30 REDO_MB_PER_SEC
FROM GV$ARCHIVED_LOG
WHERE FIRST_TIME BETWEEN TO_DATE ('01.05.2016', 'DD.MM.YYYY')
AND TO_DATE ('01.06.2016', 'DD.MM.YYYY')
check status of Data Guard synchronization(standby):

SQL> SELECT NAME, VALUE FROM V$DATAGUARD_STATS;

NAME VALUE
--------------------- -------------------------------
transport lag +00 00:00:00
apply lag +00 00:00:00
apply finish time +00 00:00:00.000
estimated startup time 32

💡 Performance Indicator: Both transport lag and apply lag should be less than 30 seconds for optimal Data Guard operations. Zero lag indicates perfect synchronization.

Verify there is no log file gap between the primary and the standby database:

SQL> SELECT STATUS, GAP_STATUS FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID = 3;

STATUS GAP_STATUS
--------- ------------------------
VALID NO GAP

🚨 Critical Check: Archive gaps prevent successful failover. Always verify NO GAP status before switchover operations.

Find Sync between Primary and STANDBY(Archive Difference)

SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received",
APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN
(SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN
(SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#;

Verify Managed Recovery is running on the standby :

SQL> SELECT PROCESS FROM V$MANAGED_STANDBY WHERE PROCESS LIKE 'MRP%';

PROCESS
---------
MRP0

Information about the protection mode, the protection level, the role of the database, and switchover status:

SQL> SELECT DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS FROM V$DATABASE;

DATABASE_ROLE INSTANCE OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL SWITCHOVER_STATUS
---------------- ---------- ------------ -------------------- -------------------- -------------------- --------------------
PRIMARY TESTCDB READ WRITE MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE STANDBY
On the standby database, query the V$ARCHIVED_LOG view identify existing files in the archived redo log.

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
Or
SQL> SELECT THREAD#, MAX(SEQUENCE#) AS "LAST_APPLIED_LOG" FROM V$LOG_HISTORY GROUP BY THREAD#;
On the standby database, query the V$ARCHIVED_LOG view verify the archived redo log files were applied.

SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

Determine which log files were not received by the standby site.

SQL> SELECT LOCAL.THREAD#, LOCAL.SEQUENCE#
FROM (SELECT THREAD#, SEQUENCE#
FROM V$ARCHIVED_LOG
WHERE DEST_ID = 1) LOCAL
WHERE LOCAL.SEQUENCE# NOT IN (SELECT SEQUENCE#
FROM V$ARCHIVED_LOG
WHERE DEST_ID = 2 AND THREAD# = LOCAL.THREAD#);

Check archive log apply on primary database:

SQL> SET LINESIZE 150
SET PAGESIZE 999
COL NAME FORMAT A60
COL DEST_TYPE FORMAT A10
COL ARCHIVED FORMAT A10
COL APPLIED FORMAT A10

SELECT SEQUENCE#,
NAME,
DEST_ID ,
CASE WHEN STANDBY_DEST = 'YES' THEN 'Standby' ELSE 'Local' END
AS DEST_TYPE ,
ARCHIVED ,
APPLIED
FROM V$ARCHIVED_LOG
WHERE SEQUENCE# > (SELECT MAX (SEQUENCE#)
FROM V$ARCHIVED_LOG
WHERE STANDBY_DEST = 'YES' AND APPLIED = 'YES')
ORDER BY SEQUENCE# , DEST_ID ;

SEQUENCE# NAME DEST_ID DEST_TYPE ARCHIVED APPLIED
---------- -------------------------------------------------------------- ------- ---------- -------- --------
23748 +FRA/TEST/ARCHIVELOG/2016_07_09/thread_2_seq_23748.10041.9 1 Local YES NO
23748 +DATA/TEST/ARCHIVELOG/2016_07_09/thread_2_seq_23748.10062. 2 Local YES NO
23748 TESTSTB 3 Standby YES NO

3 rows selected.

🔍 Advanced Gap Detection & Sequence Analysis

Check GAP sync on Primary and Standby

select max(sequence#) from v$log_history;

Find all standby databases for a primary database in data guard

select * from v$dataguard_config;

Any error if we need to identify

select dest_id,dest_name,status, error from v$archive_dest;

PRIMARY SEQUENCE

select thread#, max(sequence#) "Last Primary Seq Generated" from v$archived_log val, v$database vdb where val.resetlogs_change# = vdb.resetlogs_change# group by thread# order by 1;

STANDBY SEQUENCE

select thread#, max(sequence#) "Last Standby Seq Applied" from v$archived_log val, v$database vdb where val.resetlogs_change# = vdb.resetlogs_change# and val.applied in ('YES','IN-MEMORY') group by thread# order by 1;

✅ Synchronization Validation: Compare PRIMARY SEQUENCE and STANDBY SEQUENCE results. Matching sequence numbers indicate perfect synchronization between primary and standby databases.

Logical sequence

set line 25600

select thread# trd, sequence#,

first_change#, next_change#,

dict_begin beg, dict_end end,

to_char(timestamp, 'dd:mm:yyyy hh24:mi:ss') timestamp,

(case when l.next_change# < p.read_scn then 'YES'

when l.first_change# < p.applied_scn then 'CURRENT'

else 'NO' end) applied

from dba_logstdby_log l, dba_logstdby_progress p

order by thread#, first_change#;

🎯 Quick Reference Summary

Start Real-Time Apply:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Stop MRP:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Check MRP Status:

SELECT PROCESS FROM V$MANAGED_STANDBY WHERE PROCESS LIKE 'MRP%';

Verify No Gaps:

SELECT STATUS, GAP_STATUS FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID = 3;

Check Apply Lag:

SELECT NAME, VALUE FROM V$DATAGUARD_STATS;

⚠️ DBA Best Practices & Validation Checklist

Always verify MRP status after starting recovery operations
Monitor apply lag regularly - set alerts for lag exceeding 30 seconds
Use real-time apply (USING CURRENT LOGFILE) for production environments
Check for archive gaps before performing switchover or failover operations
Document procedures and test them quarterly
Configure sufficient standby redo logs - (online redo groups + 1) per thread
Monitor network bandwidth between primary and standby sites
Validate synchronization using sequence number comparison queries

📅 Last Updated: February 2024 | ⏱️ Reading Time: 15 minutes | 🎯 Difficulty: Intermediate-Advanced

🏷️ Tags: Oracle Data Guard, MRP Process, Redo Apply, Real-Time Apply, Physical Standby, Oracle DBA, V$MANAGED_STANDBY, Data Guard Monitoring, Oracle 19c, Oracle 21c, Archive Gap Detection, Synchronization Validation

❤️ If you like please follow and comment

DataGuard Useful Commands

Oracle Data Guard MRP Commands: Complete Start, Stop & Monitor Guide

🎯 What This Guide Covers

🚀 START Operations: Enable Redo Apply (MRP Process)

⛔ STOP Operations: Disable Redo Apply (MRP Process)

📊 Monitoring & Validation Commands

🔍 Advanced Gap Detection & Sequence Analysis

🎯 Quick Reference Summary

⚠️ DBA Best Practices & Validation Checklist

Post a Comment

Post a Comment

Contact Form

DataGuard Useful Commands

Oracle Data Guard MRP Commands: Complete Start, Stop & Monitor Guide

🎯 What This Guide Covers

🚀 START Operations: Enable Redo Apply (MRP Process)

⛔ STOP Operations: Disable Redo Apply (MRP Process)

📊 Monitoring & Validation Commands

🔍 Advanced Gap Detection & Sequence Analysis

🎯 Quick Reference Summary

⚠️ DBA Best Practices & Validation Checklist

You might like

Post a Comment

Post a Comment

Contact Form