If you have a Data Guard environment, where you’ve just applied a Database Bundle Patch and OJVM Patch, it’s possible that your Physical Standby can throw the following error:
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT NODELAY
Wed Oct 11 08:11:57 2017
Media Recovery Log +RECOC1/VER1S/ARCHIVELOG/2017_10_11/thread_1_seq_18251.24912.957080425
MRP0: Background Media Recovery terminated with error 10485
Wed Oct 11 08:11:57 2017
Errors in file /u01/app/oracle/diag/rdbms/ver1s/VER1S2/trace/VER1S2_pr00_220336.trc:
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“.
See MOS note for more info:
MRP process getting terminated with error ORA-10485 (Doc ID 1618485.1):
“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”
The easiest solution is to restart the database as “mount” mode allowing the redo via Data Guard to apply the patch, then restart again as “open” mode:
DGMGRL> show configuration; Configuration - ver1p Protection Mode: MaxPerformance Members: ver1p - Primary database ver1s - Physical standby database Error: ORA-16766: Redo Apply is stopped Fast-Start Failover: DISABLED Configuration Status: ERROR (status updated 37 seconds ago) DGMGRL> show database ver1s; Database - ver1s Role: PHYSICAL STANDBY Intended State: APPLY-ON Transport Lag: 0 seconds (computed 0 seconds ago) Apply Lag: 2 hours 24 minutes 33 seconds (computed 1 second ago) Average Apply Rate: 99.32 MByte/s Real Time Query: OFF Instance(s): VER1S1 VER1S2 (apply instance) Database Error(s): ORA-16766: Redo Apply is stopped Database Status: ERROR DGMGRL>
Now restart the database as “mount” mode allowing the redo via Data Guard to apply the patch:
[oracle@v1ex2dbadm01 ~]$ srvctl status database -d VER1S -v Instance VER1S1 is running on node v1ex2dbadm01 with online services VER1_BK1,VER1_BK2,VER1_BK3,VER1_BK4. Instance status: Open,Readonly. Instance VER1S2 is running on node v1ex2dbadm02. Instance status: Open,Readonly. [oracle@v1ex2dbadm01 ~]$ srvctl config database -d VER1S Database unique name: VER1S Database name: Oracle home: /u01/app/oracle/product/12.1.0.2/dbhome_1 Oracle user: oracle Spfile: +DATAC1/VER1S/PARAMETERFILE/spfileVER1S.ora Password file: Domain: Start options: open Stop options: immediate Database role: PHYSICAL_STANDBY Management policy: AUTOMATIC Server pools: Disk Groups: DATAC1,RECOC1 Mount point paths: Services: VER1_BK1,VER1_BK2,VER1_BK3,VER1_BK4 Type: RAC Start concurrency: Stop concurrency: OSDBA group: dba OSOPER group: dba Database instances: VER1S1,VER1S2 Configured nodes: v1ex2dbadm01,v1ex2dbadm02 Database is administrator managed [oracle@v1ex2dbadm01 ~]$ srvctl stop database -d VER1S [oracle@v1ex2dbadm01 ~]$ srvctl start database -d VER1S -o mount
Re-check Data Guard Broker to check if the transport lag and status have cleared as expected:
DGMGRL> show database ver1s Database - ver1s Role: PHYSICAL STANDBY Intended State: APPLY-ON Transport Lag: 0 seconds (computed 0 seconds ago) Apply Lag: 0 seconds (computed 0 seconds ago) Average Apply Rate: 34.62 MByte/s Real Time Query: OFF Instance(s): VER1S1 VER1S2 (apply instance) Database Status: SUCCESS DGMGRL>
Then restart the database again as “open” mode (Active Data Guard):
[oracle@v1ex2dbadm01 ~]$ srvctl stop database -d VER1S [oracle@v1ex2dbadm01 ~]$ srvctl start database -d VER1S [oracle@v1ex2dbadm01 ~]$ srvctl status database -d VER1S -v Instance VER1S1 is running on node v1ex2dbadm01 with online services VER1_BK1,VER1_BK2,VER1_BK3,VER1_BK4. Instance status: Open,Readonly. Instance VER1S2 is running on node v1ex2dbadm02. Instance status: Open,Readonly.
Re-check Data Guard Broker to check if the “Real Time Query” is back on as expected:
DGMGRL> show configuration Configuration - ver1p Protection Mode: MaxPerformance Members: ver1p - Primary database ver1s - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS (status updated 2 seconds ago) DGMGRL> show database ver1s Database - ver1s Role: PHYSICAL STANDBY Intended State: APPLY-ON Transport Lag: 0 seconds (computed 0 seconds ago) Apply Lag: 0 seconds (computed 0 seconds ago) Average Apply Rate: 84.35 MByte/s Real Time Query: ON Instance(s): VER1S1 VER1S2 (apply instance) Database Status: SUCCESS DGMGRL>
If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.
Thanks
Zed DBA (Zahid Anwar)