ORA-10485: Real-Time Query cannot be enabled while applying migration redo

郑全

Completed: alter database recover managed standby database disconnect from session
Recovery interrupted!
Stopping change tracking
2022-02-23T16:42:09.898750+08:00
Errors in file /u01/app/oracle/diag/rdbms/auxdb/orcl/trace/orcl_pr00_19613.trc:
ORA-10485: Real-Time Query cannot be enabled while applying migration redo.
2022-02-23T16:42:11.083918+08:00
Background Media Recovery process shutdown (orcl)

备库打开，直接关闭

通过以下方式解决：

MRP process getting terminated with error ORA-10485 (Doc ID 1618485.1)       

In this Document
Symptoms
Cause
Solution
APPLIES TO:
Oracle Database - Enterprise Edition - Version 11.2.0.1.0 to 11.2.0.4 [Release 11.2]
Oracle Database - Enterprise Edition - Version 12.1.0.2 to 12.1.0.2 [Release 12.1]
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Information in this document applies to any platform.
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 (<dbname>1)
....
....
MRP0: Background Media Recovery terminated with error 10485
Errors in file /u01/app/oracle/diag/rdbms/<dbname>/<dbname>1/trace/<dbname>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 ... 9858.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