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