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 🙂
Floo Bar 