How to resolve MRP stuck issues on a physical standby database?
(Doc ID 1221163.1)
Last updated on JUNE 13, 2023
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 22.214.171.124 [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.
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:
- 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.
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 126.96.36.199. 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.|