Start & Stop MRP Process in Oracle Data Guard

Add W3Buddy as a preferred source on Google

हि En

🔊 Listen

MRP applies redo logs on a physical standby to keep it synchronized with primary. Starting/stopping MRP controls redo apply.

1. Verify Standby Role & State

-- Confirm standby database role and state
SELECT DATABASE_ROLE, OPEN_MODE FROM V$DATABASE;

Expected output:

DATABASE_ROLE     OPEN_MODE
----------------  --------
PHYSICAL STANDBY  MOUNTED

2. Start MRP

-- Start managed recovery (redo apply) in background (recommended)
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

-- (Optional) Enable real-time apply in background
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE REALTIME APPLY DISCONNECT;

Note: Use DISCONNECT to run MRP in background, freeing your session for other work.

3. Verify MRP Status

-- Check MRP process status
SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY WHERE PROCESS LIKE 'MRP%';

Expected status: APPLYING_LOG or WAIT_FOR_LOG

4. Stop MRP

-- Stop managed recovery process
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

5. Confirm MRP is Stopped

-- Confirm MRP stopped
SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY WHERE PROCESS LIKE 'MRP%';

Expected status: CANCELLED

6. Additional Monitoring

-- Detailed recovery process status
SELECT PROCESS, STATUS, THREAD#, SEQUENCE# FROM V$MANAGED_STANDBY;

-- View archived logs applied (DEST_ID usually 2)
SELECT THREAD#, SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG WHERE DEST_ID = 2 ORDER BY SEQUENCE#;

7. Data Guard Broker Monitoring (if used)

DGMGRL> SHOW CONFIGURATION;
DGMGRL> SHOW DATABASE '<standby_db_name>';

Notes

• MRP must run on a physical standby in MOUNT mode.
• DISCONNECT runs MRP asynchronously, freeing the SQL session immediately. Without it, session remains connected until MRP is stopped.
• Real-time apply minimizes redo apply lag but is optional.
• Always monitor alert logs and Data Guard broker logs for issues.
• Stopping MRP pauses redo apply but does not affect data consistency.