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

Last updated on OCTOBER 18, 2016

Applies to:

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

Goal

 

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

 ******* Checked for relevance 16-OCT-2016 ********

When you find the applied sequence# doesn't increase from grid control for a physical standby database, or MRP (Managed Recovery Process ) sticks 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';
PROCESS  THREAD#   SEQUENCE#  STATUS
-------- --------- ---------- ------------

MRP0     1         548        WAIT_FOR_GAP

 

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,

DGMGRL> ALTER RESOURCE '<resource name>' ON SITE '<site name>' SET STATE='PHYSICAL-APPLY-READY';
DGMGRL> ALTER RESOURCE '<resource name>' ON SITE '<site name>' SET STATE='PHYSICAL-APPLY-ON';

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

http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/scenarios.htm#CIHIAADC
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:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/create_ps.htm#i63561
Oracle� Data Guard Concepts and Administration
10g Release 2 (10.2)

http://download.oracle.com/docs/cd/B28359_01/server.111/b28294/create_ps.htm#i63561 Oracle� Data Guard Concepts and Administration
11g Release 1 (11.1)

http://download.oracle.com/docs/cd/E11882_01/server.112/e17022/create_ps.htm#i63561
Oracle� Data Guard Concepts and Administration
11g Release 2 (11.2)

Solution

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms