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

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

After upgraded databases using DataGuard rolling method, then standby database reports ORA-10485 error:

ORA-10485: Real-Time Query cannot be enabled while applying migration redo.
Recovery interrupted!
stopping change tracking
2022-04-29T21:52:31.513013+10:00
Errors in file /u01/app/oracle/diag/rdbms/stboemrep/STBOEMREP/trace/STBOEMREP_mrp0_23330.trc:
ORA-10485: Real-Time Query cannot be enabled while applying migration redo.
2022-04-29T21:52:31.513045+10:00
Background Media Recovery process shutdown (STBOEMREP)

Oracle support explains this ORA error:

$ oerr ora 10485
10485, 00000, "Real-Time Query cannot be enabled while applying migration redo."
// *Cause:  The Real-Time Query feature was enabled when an attempt was made
//          to recover through migration redo generated during primary upgrades or
//          downgrades.
// *Action: Close the standby database in order to recover through
//          migration redo. Reenable the Real-Time Query feature afterwards.

CAUSE

Real-time query has been ON when upgrading the primary database.

SOLUTION

SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.


SQL> startup mount;
ORACLE instance started.

Total System Global Area 3221223152 bytes
Fixed Size                  9139952 bytes
Variable Size             687865856 bytes
Database Buffers         2516582400 bytes
Redo Buffers                7634944 bytes
Database mounted.

SQL> alter database recover managed standby database disconnect from session;

Database altered.


SQL> alter database recover managed standby database cancel;

Database altered.


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

Database altered.

SQL> alter database open;

Database altered.


DGMGRL>  show configuration;

Configuration - dg_oemrep

  Protection Mode: MaxPerformance
  Members:
  oemrep    - Primary database
    stboemrep - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 7 seconds ago)

DGMGRL> show database verbose "stboemrep";

Database - stboemrep

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 1 second ago)
  Apply Lag:          0 seconds (computed 1 second ago)
  Average Apply Rate: 12.00 KByte/s
  Active Apply Rate:  0 Byte/s
  Maximum Apply Rate: 0 Byte/s
  Real Time Query:    ON
  Instance(s):
    STBOEMREP

  Properties:
    DGConnectIdentifier             = 'stboemrep'
    ObserverConnectIdentifier       = ''
    FastStartFailoverTarget         = ''
    PreferredObserverHosts          = ''
    LogShipping                     = 'ON'
    RedoRoutes                      = ''
    LogXptMode                      = 'ASYNC'
    DelayMins                       = '0'
    Binding                         = 'OPTIONAL'
    MaxFailure                      = '0'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyLagThreshold               = '30'
    TransportLagThreshold           = '30'
    TransportDisconnectedThreshold  = '30'
    ApplyParallel                   = 'AUTO'
    ApplyInstances                  = '0'
    StandbyFileManagement           = ''
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '0'
    LogArchiveMinSucceedDest        = '0'
    DataGuardSyncLatency            = '0'
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = ''
    DbFileNameConvert               = ''
    LogFileNameConvert              = ''
    ArchiveLocation                 = ''
    AlternateLocation               = ''
    StandbyArchiveLocation          = ''
    StandbyAlternateLocation        = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    LogXptStatus                    = '(monitor)'
    SendQEntries                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    HostName                        = 'stbnode1.virtuallab'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=stbnode1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=STBOEMREP_DGMGRL)(INSTANCE_NAME=STBOEMREP)(SERVER=DEDICATED)))'
    TopWaitEvents                   = '(monitor)'
    SidName                         = '(monitor)'

  Log file locations:
    Alert log               : /u01/app/oracle/diag/rdbms/stboemrep/STBOEMREP/trace/alert_STBOEMREP.log
    Data Guard Broker log   : /u01/app/oracle/diag/rdbms/stboemrep/STBOEMREP/trace/drcSTBOEMREP.log

Database Status:
SUCCESS

DGMGRL>
Advertisement