April 2022 – Make DBA Life Easy

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>

Oracle Enterprise Manager 13c Cloud Control Agent Shows Agent Unreachable Status

One of client’s OEM agent is working fine, but OEM console show “Agent Unreachable’ for this agent and all the other targets monitored by this agent.

Subscribe to get access

Read more of this content when you subscribe today.

“Server not initialized. Please make sure the repository is ready” from Accessing BIP Console in OEM

After two nodes OMS upgraded to new version, when clicking BI Publisher Enterprise Reports, the follow error “Server not initialized. Please make sure the repository is ready.” occurs:

Sometimes you might get the following errors:

“Cannot retrieve the list of reports from BI Publisher. Enterprise Manager cannot establish a connection to BI Publisher.”

SOULUTION

Subscribe to get access

Read more of this content when you subscribe today.

Slow Complete Refresh of Materialized View in Oracle Database

The status of the materialized view with 30 million records is ‘UNUSABLE’, so a complete refresh is required to fix this problem .

The complete refresh process ran for 3 hours, then we have to kill it.

SQL>  execute dbms_mview.refresh(MVIEW1','c');



^C^C
^C^C^C^C^C^C^C

BEGIN dbms_mview.refresh('MVIEW1','c'); END;

*
ERROR at line 1:
ORA-12152: TNS:unable to send break message


Elapsed: 03:11:16.16

There are a couple of following ways to speed up this complete refresh, one of them only takes 27 minutes.

Subscribe to get access

Read more of this content when you subscribe today.

Change Materialized View Refresh Mode Makes Materialized View Unusable

The materialized view was in good status until when changes its refresh mode:

SQL>  select MVIEW_NAME,REFRESH_MODE,REFRESH_METHOD,STALENESS 
      dba_mviews 
      where mview_name='MVIEW1';

MVIEW_NAME     REFRES     REFRESH_  STALENESS  
-------------- ---------- --------- ----------
MVIEW1         COMMIT     FAST      FRESH    


SQL> alter materialized view MVIEW1 refresh fast on demand;



SQL>  select MVIEW_NAME,REFRESH_MODE,REFRESH_METHOD,STALENESS 
      dba_mviews 
      where mview_name='MVIEW1';

MVIEW_NAME     REFRES     REFRESH_  STALENESS  
-------------- ---------- --------- ------------------
MVIEW1         COMMIT     FAST      COMPILATION_ERROR 

 

SQL> alter materialized view MVIEW1 compile;

Materialized view altered.


SQL>  select MVIEW_NAME,REFRESH_MODE,REFRESH_METHOD,STALENESS 
      dba_mviews 
      where mview_name='MVIEW1';

MVIEW_NAME     REFRES     REFRESH_  STALENESS  
-------------- ---------- --------- --------------------
MVIEW1         COMMIT     FAST      UNUSABLE

It might be a bug ? not quite sure why. In this situation, the materialized view has to be refreshed completed or rebuilt.