ORA-10485: Real-Time Query cannot be enabled while applying migration redo.
Issue:- In dataguard environment we have apply the PSU + OJVM patch on the database after this in physical standby database we observed the below error.Sun Nov 24 12:14:55 2019
Media Recovery Log /u01/app/stdby1/FRA/stdby1/archivelog/2019_11_19/o1_mf_1_4682_gx77fcxz_.arc
MRP0: Background Media Recovery terminated with error 10485
Sun Nov 24 12:14:55 2019
Errors in file /u01/app/stdby1/oracle/diag/rdbms/stdby1/stdby1/trace/stdby1_pr00_59541.trc:
ORA-10485: Real-Time Query cannot be enabled while applying migration redo.
Managed Standby Recovery not using Real Time Apply
Sun Nov 24 12:14:56 2019
Completed: alter database recover managed standby database using current logfile disconnect
Recovery interrupted!
Block change tracking service stopping.
Stopping background process CTWR
Sun Nov 24 12:14:58 2019
MRP0: Background Media Recovery process shutdown (stdby1)
If you have dataguard environment and you have apply the psu+ojvm patch on the primary database your physical standby database will throw the below error
Cause:-
ORA-10485: Real-Time Query cannot be enabled while applying migration redo.
This is because your database is “open” i.e. Active Data Guard (license option) and Managed Recovery Process is trying to apply the redo of datapatch which it can’t do when “open“.
The Real-Time Query feature was enabled when an attempt was made to recover through migration redo generated during primary upgrades or downgrades”
Solution:-
Start the database in mount mode and start to applying the longs,onece all the redo get applied stop the redo apply and open the databse with real time apply.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1.6034E+10 bytes
Fixed Size 2269072 bytes
Variable Size 7784628336 bytes
Database Buffers 8220835840 bytes
Redo Buffers 26480640 bytes
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
Database altered.
SQL> alter database recover managed standby database nodelay disconnect ;
Database altered.
Check for all logs get applied, there should be no any GAP between primary and standby database.
SQL> select process,client_process,status,thread#,sequence#,block#,blocks from v$managed_standby;
PROCESS CLIENT_P STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- -------- ------------ ---------- ---------- ---------- ----------
ARCH ARCH CLOSING 1 314027 1 53
ARCH ARCH CLOSING 1 314025 1 1237
ARCH ARCH CONNECTED 0 0 0 0
ARCH ARCH CLOSING 1 314026 2048 1880
RFS UNKNOWN IDLE 0 0 0 0
RFS ARCH IDLE 0 0 0 0
RFS UNKNOWN IDLE 0 0 0 0
RFS LGWR IDLE 1 314028 92 1
MRP0 N/A WAIT_FOR_LOG 1 314028 0 0
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 314027 314027 0
SQL> alter database recover managed standby database cancel;
Media recovery complete.
SQL> alter database open read only;
Database altered.
alert log:-
alter database recover managed standby database cancel
Sun Nov 24 12:45:31 2019
MRP0: Background Media Recovery cancelled with status 16037
SQL> alter database recover managed standby database using current logfile disconnect;
Database altered.
alert log:-
Sun Nov 24 12:45:43 2019
Attempt to start background Managed Standby Recovery process (stdby1)
Starting background process MRP0
Sun Nov 24 12:45:43 2019
MRP0 started with pid=31, OS id=68196
Sun Nov 24 12:45:43 2019
MRP0: Background Managed Standby Recovery process started (stdby1)
Sun Nov 24 12:45:48 2019
Started logmerger process
Sun Nov 24 12:45:49 2019
Managed Standby Recovery starting Real Time Apply
Sun Nov 24 12:45:49 2019
Parallel Media Recovery started with 8 slaves
