MRP (managed recovery process) stuck after creating standby database

  

Recently I created Physical standby database using ZDM for one of big oracle database migration.

After creating standby database, everything looks good and when I checked the status of MRP process, it shows "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 sometime I ran the same command again and I see the MRP process running but Sequence# not changed and even Block# not changed.
After investigation I saw archive received properly from primary but it's not applying on standby.
When I ran the below query, I saw gap was increasing even MRP process is 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 get to know that MRP is stuck somehow and not moving.

So I tried below steps but nothing worked.
1. Restart MRP.
2. Restart standby database.
3. Try parallel MRP.
After lots of search I found a solution in a big Oracle document, but I want to specify this particular scenario so it help 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.