After upgrade when starting the Standby database, MRP process fails with error ORA-10485

After upgrade when starting the Standby database, MRP process fails with error ORA-10485

SYMPTOMS

After upgrade when starting the Standby database, MRP process fails with the below error. Observed the below err messages in alert log of standby.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT
Attempt to start background Managed Standby Recovery process (1)
....
....
MRP0: Background Media Recovery terminated with error 10485
Errors in file /u01/app/oracle/diag/rdbms//1/trace/1_pr00_XXXXX.trc:
ORA-10485: Real-Time Query cannot be enabled while applying migration redo.
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Reconfiguration started (old inc 18, new inc 20)

CAUSE

Per Oracle® Database Error Messages 11g Release 2 (11.2) http://docs.oracle.com/cd/E11882_01/server.112/e17766/e9858.htm#sthref3044

ORA-10485: Real-Time Query cannot be enabled while applying migration redo.
The Real-Time Query feature was enabled when an attempt was made to recover through migration redo generated during primary upgrades or downgrades

.

SOLUTION

Close the standby database in order to recover through migration redo. Re-enable the Real-Time Query feature afterwards.

11g CASE
---------

1> Stop DG broker (if used)
i.e., on primary and standby

alter system set dg_broker_start = false scope = both sid = '*' ;

2> Stop managed recovery in the standby, shutdown the standby and startup mount.
start managed recovery without real time apply.

alter database recover managed standby database disconnect ;

3> Wait until all the redo is applied to the standby and the standby is in sync.
Do a couple of log switches on the primary, all instances if RAC, and let them apply to the standby.
4> Shutdown the standby and startup mount
start managed recovery with real time apply.

alter database recover managed standby database using current logfile disconnect ;

5> Restart broker(if used).
on primary and standby

alter system set dg_broker_start = true scope = both sid = '*' ;

12c CASE
----------
in 12c MRP by default starts in REAL TIME APPLY no need to use 'using current logfile' clause.
OPTION :1
-----------
To start the MRP in non real time apply issue,

alter database recover managed standby database using archived logfile disconnect

OPTION 2
----------

If option 1 failed use,

on standby,

SQL>recover standby database;

Type AUTO