Get actual query associated to an Oracle Enterprise Manager metric – the unofficial way

Photo by Annie Spratt on Unsplash

The other day, I was obsessing over an alert in Oracle Enterprise Manager Cloud Control 12c Release 4, because the error message was cryptic. In the Incident Manager console, I got the following line for an Oracle Database 12.2 target :

I expected to have some value or an explicit error message. I did not understand why I would get an “ORA-00942: table or view does not exist”. When drilling down on this alert, I noticed it belonged to a Metric Group called “Deferred Transactions” (and of course I had no idea what it was) :

But I still did not understand why I would get such an error (instead of a numeric value for example) and why the Event Type was “Metric Evaluation Error”.

Where does this result comes from ?

I had no idea how this metric was calculated, and so far, I had no idea how to get the real query launched against the database to get such result. I used a rudimentary method, know as “The grep of desperation” hoping to get a clue, in the Oracle Enterprise Agent directory :

# cd /u01/app/oracle/product/agent/
# grep -ri DeferredTrans

I got of course many results, but I noticed the following file that looked promising :

./plugins/oracle.sysman.db.agent.plugin_12.1.0.7.0/metadata/database.xmlp

Judging by the name of this file, the agent plugin version is 12.1.0.7.0 :

# emctl listplugins agent

Oracle Enterprise Manager Cloud Control 12c Release 4  
Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
oracle.sysman.oh    12.1.0.4.0           /orabin/app/oracle/product/agent12c/plugins/oracle.sysman.oh.agent.plugin_12.1.0.4.0
oracle.sysman.db    12.1.0.7.0           /orabin/app/oracle/product/agent12c/plugins/oracle.sysman.db.agent.plugin_12.1.0.7.0

It seems that this file database.xmlp is part of the Agent plugin, and contains metric definitions for database targets. When looking for pattern DeferredTrans in this file, I came across the following lines :

======================================================================
  == Category:         Deferred Transactions
  == Collection Level: Recommended
  == Purpose:          Alerts
  ======================================================================
    -->
        <Metric NAME="DeferredTrans" TYPE="TABLE">
                <ValidIf>
                        <CategoryProp NAME="MetricScope" CHOICES="DB"/>
                </ValidIf>
                <Display>
                        <Label NLSID="deferredTrans_count">Deferred Transactions</Label>
                </Display>

                <CategoryValue CLASS="Default" CATEGORY_NAME="Error"/>

                <TableDescriptor>
                        <ColumnDescriptor NAME="errortrans_count" TYPE="NUMBER" IS_KEY="FALSE">
                                <Display FOR_SUMMARY_UI="TRUE">
                                        <Label NLSID="errortrans_error">Deferred Transaction Error Count</Label>

                                </Display>
                        </ColumnDescriptor>
                        <ColumnDescriptor NAME="deftrans_count" TYPE="NUMBER" IS_KEY="FALSE">
                                <Display FOR_SUMMARY_UI="TRUE">
                                        <Label NLSID="deftrans_count_name">Deferred Transaction Count</Label>

                                </Display>
                        </ColumnDescriptor>
                </TableDescriptor>
                <QueryDescriptor FETCHLET_ID="SQL">
                        <Property NAME="transpose" SCOPE="GLOBAL">TRUE</Property>
                        <Property NAME="STATEMENT" SCOPE="GLOBAL"><![CDATA[
SELECT trans_count
  FROM (SELECT 'def_trans_err' name, COUNT(*) trans_count
          FROM sys.deferror
         UNION ALL
        SELECT 'def_trans_noerr' name, COUNT(*) trans_count
          FROM sys.deftran)
 ORDER BY name
]]></Property>
                        <Property NAME="MachineName" SCOPE="INSTANCE">MachineName</Property>
                        <Property NAME="Port" SCOPE="INSTANCE">Port</Property>
                        <Property NAME="SID" SCOPE="INSTANCE">SID</Property>
                        <Property NAME="UserName" SCOPE="INSTANCE">UserName</Property>
                        <Property NAME="password" SCOPE="INSTANCE">password</Property>
                        <Property NAME="Role" SCOPE="INSTANCE" OPTIONAL="TRUE">Role</Property>
                </QueryDescriptor>
        </Metric>

And in particular, the following query drawn my attention :

SELECT trans_count
FROM (SELECT 'def_trans_err' name, COUNT(*) trans_count
FROM sys.deferror
UNION ALL
SELECT 'def_trans_noerr' name, COUNT(*) trans_count
FROM sys.deftran)
ORDER BY name ;

Indeed, when I ran this query against my Oracle Database version 12.2, I got the error “ORA-00942: table or view does not exist”. So here is where this error comes from, in the Incident Manager console. But why ?

Do DEFTRAN and DEFERROR exist in Oracle Database 12.2 ?

Simple answer : no.

SQL> select object_name, object_type, owner from dba_objects where object_name='DEFTRAN' ;

no rows selected

SQL> select object_name, object_type, owner from dba_objects where object_name='DEFERROR' ;

no rows selected

Do DEFTRAN and DEFERROR exist in a version earlier than 12.2 ?

I tried against an 11.2.0.4 database :

SQL> select object_name, object_type, owner from dba_objects where object_name='DEFTRAN' ;

OBJECT_NAME		OBJECT_TYPE	    	OWNER
--------------- ------------------- ----------
DEFTRAN		    VIEW		    	SYS

DEFTRAN		    SYNONYM 	    	PUBLIC


SQL> select object_name, object_type, owner from dba_objects where object_name='DEFERROR' ;

OBJECT_NAME		OBJECT_TYPE	    	OWNER
--------------- ------------------- ----------
DEFERROR		VIEW		    	SYS

DEFERROR		SYNONYM 	    	PUBLIC

And of course, the query from Oracle Enterprise Manager worked fine on this 11.2.0.4 database :

SELECT trans_count
FROM (SELECT 'def_trans_err' name, COUNT(*) trans_count
FROM sys.deferror
UNION ALL
SELECT 'def_trans_noerr' name, COUNT(*) trans_count
FROM sys.deftran)
ORDER BY name ;

TRANS_COUNT
-----------
	  0
	  0

The reason is actually pretty simple : Those 2 objects are part of Advanced Replication feature, and starting in Oracle Database 12c release 2 (12.2), the Advanced Replication feature of Oracle Database is desupported.

Lessons learned

First of all, thanks to this insignificant problem, I realized that the Oracle Enterprise Manager version currently used is really obsolete and needs to be updated !

And second, I discovered this extremely valuable documentation : the Enterprise Manager Oracle Database Plug-in Metric Reference Manual, which contains, of course, everything I wanted to know, that is to say, which queries are actually launched for each metric … So next time, I’ll read the doc more carefully instead of relying on a desperate grep 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s