Eclipsys Blog

MRP (Managed Recovery Process) Stuck after Creating Standby Database – Eclipsys

Written by Manoj Kumar | Sep 7, 2023 6:30:00 PM

Recently I created a physical standby database using ZDM for one of the big Oracle Database Migration.

After creating the standby database, everything looked good and when I checked the status of the MRP process, it showed “APPLYING_LOGS“. 

SQL> select INST_ID,PROCESS,STATUS,THREAD#,SEQUENCE#,BLOCK# from GV$MANAGED_STANDBY;

INST_ID PROCESS STATUS THREAD# SEQUENCE# BLOCK#
---------- --------- ------------ ---------- ---------- ----------
1 DGRD ALLOCATED 0 0 0
1 ARCH CLOSING 1 533782 116736
1 DGRD ALLOCATED 0 0 0
1 ARCH CLOSING 1 533712 3309568
1 ARCH CONNECTED 0 0 0
1 ARCH CONNECTED 0 0 0
1 ARCH CONNECTED 0 0 0
1 ARCH CONNECTED 0 0 0
1 ARCH CONNECTED 0 0 0
1 ARCH CONNECTED 0 0 0
1 ARCH CONNECTED 0 0 0

INST_ID PROCESS STATUS THREAD# SEQUENCE# BLOCK#
---------- --------- ------------ ---------- ---------- ----------
1 ARCH CONNECTED 0 0 0
1 RFS IDLE 0 0 0
1 RFS RECEIVING 1 533783 146785
1 RFS IDLE 0 0 0
1 RFS IDLE 0 0 0
1 RFS IDLE 0 0 0
1 RFS IDLE 0 0 0
1 RFS IDLE 0 0 0
1 RFS IDLE 0 0 0
1 RFS IDLE 0 0 0
1 RFS IDLE 0 0 0

INST_ID PROCESS STATUS THREAD# SEQUENCE# BLOCK#
---------- --------- ------------ ---------- ---------- ----------
1 RFS IDLE 0 0 0
1 MRP0 APPLYING_LOG 1 533707 1789286

After some time I ran the same command again and I saw the MRP process running but Sequence# had not changed and even Block# had not changed.

After investigation, I saw the archive was received properly from the primary but it’s not applying on standby.

When I ran the below query, I saw the gap was increasing even MRP process was running.

SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1; 2 3 4 5 6 7
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 533607 533590 17

Then I got to know that MRP is stuck somehow and not moving.

So I tried the below steps but nothing worked.

  1. Restart MRP
  2. Restart the standby database
  3. Try parallel MRP

After lots of research, I found a solution in a big Oracle document, but I want to specify this particular scenario so it helps others…

 

Solution

SQL> shutdown abort;
SQL>startup mount;
- Then do a clean shutdown again to release any stuck process
SQL> shutdown immediate;
Check if any standby process still running , if yes, kill it
kill -9 ospid
SQL> startup mount;
Now start the MRP process..
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Now if we check, archives are applying, and no MRP stuck issue.