My Oracle Support Banner

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

Last updated on MARCH 12, 2021

Applies to:

Oracle Database Cloud Exadata Service - Version N/A and later
Oracle Database Cloud Service - Version N/A and later
Oracle Database - Enterprise Edition - Version 10.1.0.2 to 12.2.0.1 [Release 10.1 to 12.2]
Oracle Database Cloud Schema Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Information in this document applies to any platform.

Goal

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.

 

Solution

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
Goal
Solution
 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 11.2.0.2. 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 10.2.0.4. 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.
References

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