How to start and stop MRP process of a standby oracle database?

Login into the server and set the environment for your database. then connect to the dgmgrl command line.


. oraenv
Batch 2

dgmgrl sys/abc123

DGMGRL>

DGMGRL> show configuration

Configuration - cfaep

  Protection Mode: MaxPerformance
  Databases:
    Batch2  - Primary database
    Batch2ls - Physical standby database
    Batch2s - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS


Note: batch2 is primary, batch2ls is local standby ls, and batch2s is for standby remote as a DR solution. 

DGMGRL> show database Batch2ls

Database - Batch2ls

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 3 seconds ago)
  Apply Lag:       2 hours  (computed 0 seconds ago)
  Apply Rate:      44.12 MByte/s
  Real Time Query: ON
  Instance(s):
    Batch2ls1
    Batch2ls2 (apply instance)

Database Status:
SUCCESS

To start the mrp process:

DGMGRL> EDIT DATABASE Batch2ls SET STATE='APPLY-ON';

Note: Same operation can be done in sqlpluas using below command. 

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

To stop the mrp process:

DGMGRL> EDIT DATABASE Batch2ls SET STATE='APPLY-OFF';

NOTE: The same operation can also be done in sqlplus using below command. 

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Make sure mrp process is running after the mrp  apply is on and recovery is enabled. If no rows selected apply is off. Enabled it and turn it on. 

select inst_id, process, status from gv$managed_standby where process='MRP0';

SQL> select inst_id, process, status from gv$managed_standby where process='MRP0';

   INST_ID PROCESS   STATUS

---------- --------- ------------

         2 MRP0      WAIT_FOR_LOG

SQL>

Check the lag of the database after starting mrp it should reduce:


If from dgmgrl:


DGMGRL> show database Batch2ls

Database - Batch2ls

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 3 seconds ago)
  Apply Lag:       7 seconds  (computed 0 seconds ago)
  Apply Rate:      44.12 MByte/s
  Real Time Query: ON
  Instance(s):
    Batch2ls1
    Batch2ls2 (apply instance)

Database Status:
SUCCESS

the lag has caught up. 


If from sqlplus:

On Primary:

alter system archive log current;

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;

   THREAD# Last Primary Seq Generated
---------- --------------------------
         1                      31245
         2                      28623
2 rows selected.

On standby:

select thread#, max(sequence#) "Last Standby Seq Received"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;
   THREAD# Last Standby  Seq Generated
---------- --------------------------
         1                      31245
         2                      28623
2 rows selected.