My Oracle Support Banner

How to resolve MRP stuck issues on a physical standby database? (Doc ID 1221163.1)

Last updated on FEBRUARY 03, 2019

Applies to:

Oracle Database - Enterprise Edition - Version to [Release 10.1 to 12.2]
Information in this document applies to any platform.



Would you like to explore this Topic further with other Oracle Customers, Oracle Employees and Industry Experts ??

You can discuss this Note, show your Experiences or ask Questions about it directly right at the Bottom of this Note in the Discussion Thread about this Document.

If you want to discover Discussions about other Articles und Subjects or even post new Discussions you can access the My Oracle Support Community Page for High Availability Data Guard

When you find the applied sequence# doesn't increase from grid control for a physical standby database, or MRP (Managed Recovery Process ) is stuck and doesn't apply more logs, what do you do to find the cause of the issue and resolve the issue to let your physical standby database in-sync with your primary database?

In general, you could perform the following steps to start with:

  1. Please use one of the following method to find the archive log file that MRP sticks at:

a. Please query v$managed_standby from the standby database if MRP is started.


% ps -ef |grep -i mrp 
SQL>select process, thread#, sequence#, status from v$managed_standby where process='MRP0';
-------- --------- ---------- ------------

MRP0     1         548        WAIT_FOR_GAP

Note: Starting from 12.2 use V$DATAGUARD_PROCESS view instead of v$managed_standby


b. Stop the managed recovery and start the manual recovery.

SQL>recover managed standby database cancel;
SQL>recover automatic standby database;

If you use data guard broker, you need to do these from either DGMGRL or grid control.

For 11g data guard broker,

DGMGRL> EDIT DATABASE '<standby db_unique_name>' SET STATE='APPLY-OFF';
DGMGRL> EDIT DATABASE '<standby db_unique_name>' SET STATE='APPLY-ON';


If your standby is a RAC database, then
DGMGRL> EDIT DATABASE '<standby db_unique_name>' SET STATE='APPLY-ON' WITH APPLY INSTANCE = <instance-name>;

<instance-name> is the name of the instance you want to become the apply instance.

For 10g Data Guard broker,

DGMGRL> EDIT DATABASE '<standby db_unique_name>' SET STATE='LOG-APPLY-OFF'; DGMGRL> EDIT DATABASE '<standby db_unique_name>' SET STATE='ONLINE';

If your standby is a RAC database, then

DGMGRL> EDIT DATABASE '<standby db_unique_name>' SET STATE='ONLINE' WITH APPLY INSTANCE = <instance-name>;

To change the state of the standby database from grid control for 10g and 11g standby databases, follow these steps:

1. From the Data Guard Overview page, select the standby database you want to change.
2. Click Edit to go to the Edit Properties page.
3. Select Log Apply Off (or Online).
4. Click Apply.
5. When the process completes, a message indicating success is returned.

For 9i Data Guard Broker,


To change the state of the standby database from grid control for 9i standby database, follow these steps:

1. In the navigator tree, select the standby database resource.
2. In the right-hand property sheet, click Set State.
3. Click Apply Off (or Online).
4. Click OK.

c. Use the lowest checkpoint_change# in the data file header to find the archive log that the recovery needs to recover from:

SQL>select min(fhscn),fhrba_Seq SEQUENCE from x$kcvfh group by fhrba_Seq;

  2. Check whether the archive log file you find from step 1 exists on the standby site in the location defined by the standby init parameter standby_archive_dest or log_archive_dest_1 if standby_archive_dest is not defined on the standby.

If so, then please compare whether it has the same size on the standby as it is on the primary by cksum command. For example

% cksum <archive log file full path/file name>

Note: if cksum is not available on your unix platform please see the document below which provides more commands, such as, SHA-1 and MD5 checksum utilities.

Note 549617.1 How To Verify The Integrity Of A Patch/Software Download?


You could also verify whether the archive log file is corrupted on either standby or primary site by

SQL>alter system dump logfile '<full path/archive log file name>' validate;

If you get the SQL prompt back without error, then the archive log file is not corrupted.

  3. If either the archive log file from step 1 doesn't exist on the standby or it is corrupted, then you would need to get a new copy from the primary site. Please refer to the document below on how to resolve a gap manually.

 <Note 1537316.1> Data Guard Gap Detection and Resolution Possibilities

  4. If the archive log file exists on the standby site and is not corrupted, then please check whether it is registered to the standby controlfile by querying v$archived_log. For example,

SQL>select name from v$archived_log where (thread#=1 and sequence#=192917) or (thread#=2 and sequence#=26903);

If the last applied sequence# for thread 1 is 192916 and the last applied sequence# for thread 2 is 26902. Or the thread# and the sequence# are what you find from step 1 which the MRP sticks at.
If the archive log file names come back from the query above, then they are registered to the standby controlfile. Otherwise, the standby controlfile doesn't aware of them.


2. Please use the queries below to identify the current sequence# on the primary for each thread, the last received sequence# on the standby and the last applied sequence# on the standby for

each thread so that you will know whether the standby is in sync with the primary, whether there is a transport lag or an apply lag.

<Note 290817.1> Rolling a Standby Forward using an RMAN Incremental Backup in 9i
<Note 836986.1> Steps to perform for Rolling forward a standby database using RMAN incremental backup when primary and standby are in ASM filesystem
Oracle� Data Guard Concepts and Administration
10g Release 2 (10.2)

If your primary database is small, you could simply take a full database backup from the primary and restore it to the standby server to refresh or recreate your physical standby database.
Please refer to the document below:
Oracle� Data Guard Concepts and Administration
10g Release 2 (10.2) Oracle� Data Guard Concepts and Administration
11g Release 1 (11.1)
Oracle� Data Guard Concepts and Administration
11g Release 2 (11.2)


To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document
 Cause 1 : Log transport issues.
 Solution 1: Please use cksum command to verify the size of password file on the primary and the standby sites, and make sure SEC_CASE_SENSITIVE_LOGON  is set to false for 11g or above databases.
 Cause 2 : Firewall caused partial archive log transferred.
Solution 2: Please make sure the following firewall features are disabled.
 Cause 3 : ARC process on the primary that is responsible for the heartbeat sticks on the network due to bug 6113783
 Solution 3: The bug 6113783 was fixed in You could workaround the issue by killing the arch processes on the primary database.
 Cause 4. The standby recovery asked for old sequence # that had been applied to the standby.
 Solution 4 : Apply the fix of bug 6029179 or kill the heartbeat arch process of each primary instance if the primary is a RAC database.
 Cause 5 Recover from the wrong location. 
 Solution 5 Specify standby_archive_dest the same location as log_archive_dest_1 on the physical standby. Use from 'location' attribute in the manual recovery clause. 
 Cause 6: All standby redo log files are active on the standby database.
Solution 6: Please make sure you have enough space in the archive location.
log_archive_dest_1 is defined with proper valid_for values and db_unique_name.
standby_archive_dest is specified properly.
Cause 7 : Partial archive log file is applied on the standby database.
 Solution 7 Use rman incremental backup method to roll forward your physical standby database.
Cause 8 : The archive log files were transferred to the standby manually, not through data guard log transport service.
Solution 8 Register those archive log files or use manual recovery.
 Cause 9 : The archive log files are deleted from the primary before they are shipped and applied to the standby.
 Solution 9 Restore archive log files from backup, register them and use managed recovery to recover them; or use manual recovery without registering them.
 Cause 10 : New datafiles are added to the primary, but they are not added to the standby automatically.
 Solution 10 Add the new datafiles to the standby database manually.
 Cause 11 : MRP can stall after control file contention at the standby.
 Solution 11 This is fixed in The workaround is to restart the standby.
 Cause 12 : Cancelling managed recovery hangs.
 Solution 12 Let's shutdown abort the standby, startup mount, then have a clean shutdown, check if there are server processes left over for the standby instance.

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.