Sunday, October 8, 2017

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

Issue :

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

All non-current ORLs have been archived.
Media Recovery Log +PRODDB1_FRA/prodDB1/archivelog/2017_10_08/thread_1_seq_68958.4014.956804163
MRP0: Background Media Recovery terminated with error 10485
Errors in file /opt/app/prodDB1/oracle/diag/rdbms/prodDB1/prodDB1/trace/prodDB1_pr00_44779.trc:
ORA-10485: Real-Time Query cannot be enabled while applying migration redo.
Managed Standby Recovery not using Real Time Apply
Sun Oct 08 03:07:15 2017
Completed: alter database recover managed standby database using current logfile disconnect
Recovery interrupted!
Block change tracking service stopping.
Stopping background process CTWR
Sun Oct 08 03:07:17 2017
MRP0: Background Media Recovery process shutdown (prodDB1)


Cause :

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

Start the standby database in mount stage and start applying logs till all the logs are applied, once the logs are applied, you can start the database in read only mode and enable Real Time Apply

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 2.1379E+11 bytes
Fixed Size                  2265864 bytes
Variable Size            5.6371E+10 bytes
Database Buffers         1.5730E+11 bytes
Redo Buffers              112762880 bytes
Database mounted.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY disconnect from session;

Database altered.


Alert Log Shows
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY disconnect from session
Attempt to start background Managed Standby Recovery process (prodDB1)
Sun Oct 08 03:10:56 2017
MRP0 started with pid=84, OS id=233034 
MRP0: Background Managed Standby Recovery process started (prodDB1)



Check if all logs are applied and there is no GAP between the Primary database and the Standby


    Thread Last Seq Received Last Seq Applied
---------- ----------------- ----------------
         1             68965            68965
         2             69547            69547

SQL> RECOVER MANAGED STANDBY DATABASE CANCEL;
Media recovery complete.
SQL> alter database open read only;

Database altered.

SQL> alter database recover managed standby database using current logfile disconnect;

Database altered.


Alert Log Shows

alter database recover managed standby database using current logfile disconnect
Attempt to start background Managed Standby Recovery process (prodDB1)
Sun Oct 08 03:14:38 2017
MRP0 started with pid=84, OS id=33618 
MRP0: Background Managed Standby Recovery process started (prodDB1)
 started logmerger process
Sun Oct 08 03:14:43 2017
Managed Standby Recovery starting Real Time Apply
Only allocated 127 recovery slaves (requested 128)
Parallel Media Recovery started with 127 slaves


I hope this helps you.. 

5 comments: