Friday, October 12, 2012

ORA-00942 table or view does not exist: Oracle 11g Grid Control and OEM MSSQL monitoring plugin

We were facing the below mentioned error on Oracle Grid Control 11g related to OEM Monitoring  plugin for SQL Server 2005.

Below is the screen shot of the error from the Oracle 11g Grid Control


In order to collect SQL Server Metrics from OEM following are the important objects that OEM queries in order to reflect SQL Server information

MGMT_EMX_MSSQL_REGSETTING_VIEW
MGMT_EMX_MSSQL_DBSETTING_VIEW
MGMT_EMX_MSSQL_SQLSERVER_VIEW


for further information follow the link below

http://docs.oracle.com/cd/E11857_01/em.111/b28748/database_sql.htm

So I used the below SQL to view if Synonyms are created for these above mentioned objects in the SYSMAN schema

select owner,SYNONYM_NAME,TABLE_OWNER,TABLE_NAME
from dba_synonyms
where SYNONYM_NAME like  '%MGMT_EMX%';

no rows selected

Since there are no synonym, I created them using the below script to fix ORA-06550 error on Grid Control,

create public synonym MGMT_EMX_MSSQL_DBSETTING_VIEW for SYSMAN.MGMT_EMX_MSSQL_DBSETTING_VIEW;

create public synonym MGMT_EMX_MSSQL_REGSETTING_VIEW for SYSMAN.MGMT_EMX_MSSQL_REGSETTING_VIEW;

create public synonym MGMT_EMX_MSSQL_SQLSERVER_VIEW for SYSMAN.MGMT_EMX_MSSQL_SQLSERVER_VIEW;

That solved the problem and OEM is now displaying all the metric information for SQL Server databases.

No comments:

Post a Comment