Saturday, August 15, 2020

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


This error message may appear in the alert log file of standby database after an upgrade/PSU etc. is applied on the primary database and if real time apply is enabled on the standby database.
The procedure to apply a PSU or other (rolling) patches is that it is first applied on the standby database server and after that on the primary database, and any post patch (or upgrade) scripts are executed on the primary database. Once this completes, all database changes happened during patch/upgrade scripts execution are shipped through redo logs to the standby database and standby database eventually becomes in sync with the primary.
If we try to enabling (or already enabled) real time redo apply on the standby database, and patch/upgrade changes have not yet been applied to the standby database, ORA-10485 would be returned by the log apply process. Following is the error message that I received in my standby alert log file.
alter database recover managed standby database using current logfile disconnect
Attempt to start background Managed Standby Recovery process (MYDG)
Wed Jan 31 20:05:38 2018
MRP0 started with pid=36, OS id=20434
MRP0: Background Managed Standby Recovery process started (MYDG)
 started logmerger process
Wed Jan 31 20:05:43 2018
Managed Standby Recovery starting Real Time Apply
Parallel Media Recovery started with 4 slaves
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Log +ARCH/mydg/archivelog/2018_01_31/thread_1_seq_12286.1972.966861633
MRP0: Background Media Recovery terminated with error 10485
Errors in file /u01/app/oracle/diag/rdbms/mydg/MYDG/trace/MYDG_pr00_20439.trc:
ORA-10485: Real-Time Query cannot be enabled while applying migration redo.
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Completed: alter database recover managed standby database using current logfile disconnect
MRP0: Background Media Recovery process shutdown (MYDG)
Wed Jan 31 20:09:46 2018
Archived Log entry 17810 added for thread 1 sequence 12318 ID 0xe0202491 dest 1:
Wed Jan 31 20:09:46 2018
RFS[1]: Selected log 101 for thread 1 sequence 12319 dbid -539173460 branch 955906796

To avoid this error, standby database should not be in OPEN state during redo changes of patch/upgrade scripts are being applied to the standby, and redo real time apply should also be not in use. In order to achieve this, we shutdown the standby database, start in MOUNT mode and then start managed recovery.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
Access and use of this service is for Oracle authorized users only subject to Company policies.
This service will be monitored consistent with applicable law.

Access to this system is audited!

ORACLE instance started.

Total System Global Area 1.0689E+10 bytes
Fixed Size                  2262656 bytes
Variable Size            1744832896 bytes
Database Buffers         8925478912 bytes
Redo Buffers               16900096 bytes
Database mounted.
Access and use of this service is for Oracle authorized users only subject to Company policies.
This service will be monitored consistent with applicable law.

Access to this system is audited!

SQL>> alter database recover managed standby database disconnect;

Database altered.

After that monitor the recovery process. You may open alert log file to see if media recovery has started and all redo logs until the latest sequence number have been applied.
Once all redo logs until latest sequence have been applied, you may switch to real time apply.
SQL> alter database recover managed standby database cancel;

Database altered.

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

Database altered.

Alert log will show that this time real time apply is working fine.
SQL>> alter database recover managed standby database disconnect;

Database altered.

SQL> alter database recover managed standby database cancel;

Database altered.

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

Database altered.

1 comment:

Popular Posts - All Times