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 laterOracle 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:
- 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 |