Wednesday, July 26, 2017

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