Hanging ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL

Cause: Archive log are not applying into standby database but recovery process (MRP) are active, in this situation I decided to restart recovery process but whenever I am applying  ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL, the command itself hanging situation.

Solution: Rather than bouncing the standby database just find and kill the MRP process and start the media recovery process.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active


SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Database altered.

SQL>

Log Details:

Archived Log entry 20997 added for thread 1 sequence 11504 ID 0xffffffffaec2031b dest 1:

Thu Jul 27 10:33:23 2017

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL

Thu Jul 27 10:45:35 2017

Managed Standby Recovery Canceled (erpdb)

Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL

Thu Jul 27 10:46:23 2017

Recovery coordinator died, shutting down parallel recovery

Thu Jul 27 10:46:25 2017

RFS[516]: Selected log 13 for thread 1 sequence 11506 dbid -********** branch 928567359

Thu Jul 27 10:46:28 2017

Archived Log entry 20998 added for thread 1 sequence 11505 ID 0xffffffffaec2031b dest 1:

Thu Jul 27 10:46:41 2017

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION

ORA-1153 signalled during: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION...

Thu Jul 27 10:46:59 2017

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL

Thu Jul 27 10:47:24 2017

RFS[514]: Selected log 21 for thread 2 sequence 11340 dbid -********** branch 928567359

Thu Jul 27 10:47:27 2017

Archived Log entry 20999 added for thread 2 sequence 11339 ID 0xffffffffaec2031b dest 1:

Thu Jul 27 10:47:35 2017

Logmerger died, shutting down parallel recovery slaves

Recovery interrupted!

Recovery interrupted!

Thu Jul 27 10:47:37 2017

Managed Standby Recovery Canceled (erpdb)

Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL

Thu Jul 27 10:48:08 2017

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION

Attempt to start background Managed Standby Recovery process (erpdb)

Thu Jul 27 10:48:08 2017

MRP0 started with pid=38, OS id=9764900

MRP0: Background Managed Standby Recovery process started (erpdb)

 started logmerger process

Thu Jul 27 10:48:14 2017

Managed Standby Recovery starting Real Time Apply

Parallel Media Recovery started with 32 slaves

Waiting for all non-current ORLs to be archived...

All non-current ORLs have been archived.

Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION

Media Recovery Log +DG_FRA/erpdb/archivelog/2017_07_25/thread_2_seq_11299.1849.950272469

Media Recovery Log +DG_FRA/erpdb/archivelog/2017_07_25/thread_1_seq_11451.1854.950272385