Blog

Materialized View : OEM Monitoring and Alerting through Metric Extension( ME ), Incident Rule ( IR ) and Self Healing through Corrective Action( CA )

In previous posts, we have created following related posts to monitor FRA and auto backup archive logs:

  1. Create a Metric Extension (ME) to Monitor FRA Usage on OEM
  2. Create an Incident Rule for Metric Extension ( ME ) to Monitor FRA Used in OEM
  3. Use Corrective Action ( CA ) to Automate Archivelog Backup When FRA Usage is Over Threshold of Metric Extension ( ME ) in OEM

In this post, we will do the same thing for Materialized Views to monitor, alert and self heal the materialized views.

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe

How to Pause and Resume Oracle Materialized View Refresh on Commit

A client wants to temporarily stop terabytes Oracle materialized views refresh for application issue diagnosing, and then resume the refresh without invalidating the materialized views.

SOLUTION

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe

“ORA-06533: Subscript beyond count” from DBMS_STATS

DBMS_STATS threw the following errors:

SQL> EXEC DBMS_STATS.gather_table_stats('USERNAME','TABLENAME');
BEGIN DBMS_STATS.gather_table_stats('USERNAME','TABLENAME'); END;

*
ERROR at line 1:
ORA-06533: Subscript beyond count
ORA-06512: at "SYS.DBMS_STATS", line 34634
ORA-06512: at line 1

WORKAROUND

Use a numeric value for estimate_percent instead of default dbms_stats.auto_sample_size:

SQL> exec dbms_stats.gather_table_stats( ownname=>'USERNAME',tabname=>'EVENT',estimate_percent=>100);

PL/SQL procedure successfully completed.

OEM Agent Home Page Shows ” Communication between the Oracle Management Service to the Agent is unavailable. Any features or displayed information requiring this communication will be unavailable”

After OMS upgraded to 13.4, the agent home pages are showing the following error:

Communication between the Oracle Management Service to the Agent is unavailable. Any features or displayed information requiring this communication will be unavailable

WORKAROUND

This is known issue because of SSLCipherSuites compatibility issue between OMS and Agents after OMS upgrading.

Run the following commands on EM agent server to resolve this issue:

$ emctl status agent

$ emctl pingOMS

$ emctl getproperty agent -name SSLCipherSuites
Oracle Enterprise Manager Cloud Control 13c Release 2
Copyright (c) 1996, 2016 Oracle Corporation.  All rights reserved.
SSLCipherSuites is unset; default value is SSL_RSA_WITH_RC4_128_MD5:SSL_RSA_WITH_RC4_128_SHA:SSL_RSA_WITH_3DES_EDE_CBC_SHA

$ emctl setproperty agent -name SSLCipherSuites -value TLS_RSA_WITH_AES_128_CBC_SHA:TLS_RSA_WITH_AES_256_CBC_SHA:RSA_WITH_AES_256_CBC_SHA256
Oracle Enterprise Manager Cloud Control 13c Release 2
Copyright (c) 1996, 2016 Oracle Corporation.  All rights reserved.
EMD setproperty succeeded

$ emctl getproperty agent -name SSLCipherSuites
Oracle Enterprise Manager Cloud Control 13c Release 2
Copyright (c) 1996, 2016 Oracle Corporation.  All rights reserved.
SSLCipherSuites=TLS_RSA_WITH_AES_128_CBC_SHA:TLS_RSA_WITH_AES_256_CBC_SHA:RSA_WITH_AES_256_CBC_SHA256

$ emctl stop agent

$ emctl start agent

ORA-01450: maximum key length (3215) exceeded

A client is trying to rebuild an index online with the following errors:

SQL> alter index indx_cdr rebuild online;
alter index IDX_CDR rebuild online
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01450: maximum key length (3215) exceeded



SQL> select max(length( call_message)) from  cdr;

MAX(LENGTH(CALL_MESSAGE))
-------------------------
               3586

WORKAROUND

Build the index without ONLINE option:

SQL> alter index indx_cdr rebuild;

Index altered.