2 ** NetXMS subagent for Oracle monitoring
3 ** Copyright (C) 2009-2012 Raden Solutions
6 #include "oracle_subagent.h"
8 CONDITION g_shutdownCondition
;
9 MUTEX g_paramAccessMutex
;
11 DB_DRIVER g_driverHandle
= NULL
;
12 DatabaseInfo g_dbInfo
[MAX_DATABASES
];
13 DatabaseData g_dbData
[MAX_DATABASES
];
15 THREAD_RESULT THREAD_CALL
queryThread(void *arg
);
17 DBParameterGroup g_paramGroup
[] = {
19 700, _T("Oracle.Sessions."),
20 _T("select ") DB_NULLARG_MAGIC
_T(" ValueName, count(*) Count from v$session"),
24 700, _T("Oracle.Cursors."),
25 _T("select ") DB_NULLARG_MAGIC
_T(" ValueName, sum(a.value) Count from v$sesstat a, v$statname b, v$session s where a.statistic# = b.statistic# and s.sid=a.sid and b.name = 'opened cursors current'"),
29 700, _T("Oracle.Objects."),
30 _T("select ") DB_NULLARG_MAGIC
_T(" ValueName, count(*) InvalidCount from dba_objects where status!='VALID'"),
34 700, _T("Oracle.DBInfo."),
35 _T("select ") DB_NULLARG_MAGIC
_T(" ValueName, name Name, to_char(created) CreateDate, log_mode LogMode, open_mode OpenMode from v$database"),
39 700, _T("Oracle.Instance."),
40 _T("select ") DB_NULLARG_MAGIC
_T(" ValueName, version Version, status Status, archiver ArchiverStatus, shutdown_pending ShutdownPending from v$instance"),
44 1000, _T("Oracle.TableSpaces."),
45 _T("select d.tablespace_name ValueName, d.status Status, d.contents Type, to_char(round(used_percent,2)) UsedPct from dba_tablespaces d, dba_tablespace_usage_metrics m where d.tablespace_name=m.tablespace_name"),
49 700, _T("Oracle.Dual."),
50 _T("select ") DB_NULLARG_MAGIC
_T(" ValueName, decode(count(*),1,0,1) ExcessRows from dual"),
54 700, _T("Oracle.Performance."),
55 _T("select ") DB_NULLARG_MAGIC
_T(" ValueName, (select s.value PhysReads from v$sysstat s, v$statname n where n.name='physical reads' and n.statistic#=s.statistic#) PhysReads, ")
56 _T("(select s.value LogicReads from v$sysstat s, v$statname n where n.name='session logical reads' and n.statistic#=s.statistic#) LogicReads, ")
57 _T("(select round((sum(decode(name,'consistent gets',value,0))+sum(decode(name,'db block gets',value,0))-sum(decode(name,'physical reads',value, 0)))/(sum(decode(name,'consistent gets',value,0))+sum(decode(name,'db block gets',value,0)))*100,2) from v$sysstat) CacheHitRatio, ")
58 _T("(select round(sum(waits)*100/sum(gets),2) from v$rollstat) RollbackWaitRatio, ")
59 _T("(select round((1-(sum(getmisses)/sum(gets)))*100,2) from v$rowcache) DictCacheHitRatio, ")
60 _T("(select round(sum(pins)/(sum(pins)+sum(reloads))*100,2) from v$librarycache) LibCacheHitRatio, ")
61 _T("(select round((100*b.value)/decode((a.value+b.value),0,1,(a.value+b.value)),2) from v$sysstat a,v$sysstat b where a.name='sorts (disk)' and b.name='sorts (memory)') MemorySortRatio, ")
62 _T("(select round(nvl((sum(busy)/(sum(busy)+sum(idle)))*100,0),2) from v$dispatcher) DispatcherWorkload, ")
63 _T("(select bytes from v$sgastat where name='free memory' and pool='shared pool') FreeSharedPool ")
68 700, _T("Oracle.CriticalStats."),
69 _T("select ") DB_NULLARG_MAGIC
_T(" ValueName, (select count(*) TSOFF from dba_tablespaces where status <> 'ONLINE') TSOffCount, ")
70 _T("(select count(*) DFOFF from V$DATAFILE where status not in ('ONLINE','SYSTEM')) DFOffCount, ")
71 _T("(select count(*) from dba_segments where max_extents = extents) FullSegmentsCount, ")
72 _T("(select count(*) from dba_rollback_segs where status <> 'ONLINE') RBSegsNotOnlineCount, ")
73 _T("decode(sign(decode((select upper(log_mode) from v$database),'ARCHIVELOG',1,0)-")
74 _T("decode((select upper(value) from v$parameter where upper(name)='LOG_ARCHIVE_START'),'TRUE',1,0)),1, 1, 0) AutoArchivingOff, ")
75 _T("(select count(file#) from v$datafile_header where recover ='YES') DatafilesNeedMediaRecovery, ")
76 _T("(select count(*) FROM dba_jobs where NVL(failures,0) <> 0) FailedJobs ")
87 LONG
getParameters(const TCHAR
*parameter
, const TCHAR
*argument
, TCHAR
*value
)
89 LONG ret
= SYSINFO_RC_UNSUPPORTED
;
91 TCHAR entity
[MAX_STR
];
93 // Get id of the database requested
94 if (!AgentGetParameterArg(parameter
, 1, dbId
, MAX_STR
))
96 if (!AgentGetParameterArg(parameter
, 2, entity
, MAX_STR
) || entity
[0] == _T('\0'))
97 nx_strncpy(entity
, DB_NULLARG_MAGIC
, MAX_STR
);
99 AgentWriteDebugLog(7, _T("%s: got request for params: dbid='%s', param='%s'"), MYNAMESTR
, dbId
, parameter
);
101 // Loop through databases and find an entry in g_dbInfo[] for this id
102 for (int i
= 0; i
<= g_dbCount
; i
++)
104 if (!_tcsnicmp(g_dbInfo
[i
].id
, dbId
, MAX_STR
)) // found DB
106 if (argument
[0] == _T('R'))
108 ret_string(value
, g_dbInfo
[i
].connected
? _T("YES") : _T("NO"));
109 ret
= SYSINFO_RC_SUCCESS
;
111 // Loop through parameter groups and check whose prefix matches the parameter requested
112 for (int k
= 0; argument
[0] == _T('X') && g_paramGroup
[k
].prefix
; k
++)
114 if (!_tcsnicmp(g_paramGroup
[k
].prefix
, parameter
, _tcslen(g_paramGroup
[k
].prefix
))) // found prefix
116 MutexLock(g_dbInfo
[i
].accessMutex
);
117 // Loop through the values
118 AgentWriteDebugLog(9, _T("%s: valuecount %d"), MYNAMESTR
, g_paramGroup
[k
].valueCount
[i
]);
119 for (int j
= 0; j
< g_paramGroup
[k
].valueCount
[i
]; j
++)
121 StringMap
* map
= (g_paramGroup
[k
].values
[i
])[j
].attrs
;
122 TCHAR
* name
= (g_paramGroup
[k
].values
[i
])[j
].name
;
123 if (!_tcsnicmp(name
, entity
, MAX_STR
)) // found value which matches the parameters argument
126 nx_strncpy(key
, parameter
+ _tcslen(g_paramGroup
[k
].prefix
), MAX_STR
);
127 TCHAR
* place
= _tcschr(key
, _T('('));
131 const TCHAR
* dbval
= map
->get(key
);
132 ret_string(value
, dbval
);
133 ret
= SYSINFO_RC_SUCCESS
;
138 MutexUnlock(g_dbInfo
[i
].accessMutex
);
152 // Subagent initialization
155 static BOOL
SubAgentInit(Config
*config
)
158 static DatabaseInfo info
;
160 static NX_CFG_TEMPLATE configTemplate
[] =
162 { _T("Id"), CT_STRING
, 0, 0, MAX_STR
, 0, info
.id
},
163 { _T("Name"), CT_STRING
, 0, 0, MAX_STR
, 0, info
.name
},
164 { _T("TnsName"), CT_STRING
, 0, 0, MAX_STR
, 0, info
.name
},
165 { _T("UserName"), CT_STRING
, 0, 0, MAX_USERNAME
, 0, info
.username
},
166 { _T("Password"), CT_STRING
, 0, 0, MAX_PASSWORD
, 0, info
.password
},
167 { _T(""), CT_END_OF_LIST
, 0, 0, 0, 0, NULL
}
172 g_driverHandle
= DBLoadDriver(_T("oracle.ddr"), NULL
, TRUE
, NULL
, NULL
);
174 g_driverHandle
= DBLoadDriver(LIBDIR
"/libnxddr_oracle.so", NULL
, TRUE
, NULL
, NULL
);
176 if (g_driverHandle
== NULL
)
178 AgentWriteLog(EVENTLOG_ERROR_TYPE
, _T("%s: failed to load db driver"), MYNAMESTR
);
184 g_shutdownCondition
= ConditionCreate(TRUE
);
187 // Load configuration from "oracle" section to allow simple configuration
188 // of one database without XML includes
189 memset(&info
, 0, sizeof(info
));
191 if (config
->parseTemplate(_T("ORACLE"), configTemplate
))
193 if (info
.name
[0] != 0)
196 _tcscpy(info
.id
, info
.name
);
197 memcpy(&g_dbInfo
[++g_dbCount
], &info
, sizeof(DatabaseInfo
));
198 g_dbInfo
[g_dbCount
].accessMutex
= MutexCreate();
202 // Load full-featured XML configuration
203 if (g_dbCount
== -1) // Didn't load anything from the .conf file
205 for (i
= 1; result
&& i
<= MAX_DATABASES
; i
++)
207 TCHAR section
[MAX_STR
];
208 memset((void*)&info
, 0, sizeof(info
));
209 _sntprintf(section
, MAX_STR
, _T("oracle/databases/database#%d"), i
);
210 if ((result
= config
->parseTemplate(section
, configTemplate
)) != TRUE
)
212 AgentWriteLog(EVENTLOG_ERROR_TYPE
, _T("%s: error parsing configuration template"), MYNAMESTR
);
215 if (info
.name
[0] != _T('\0'))
216 memcpy((void*)&g_dbInfo
[++g_dbCount
], (void*)&info
, sizeof(info
));
219 if (info
.username
[0] == '\0' || info
.password
[0] == '\0')
221 AgentWriteLog(EVENTLOG_ERROR_TYPE
, _T("%s: error getting username and/or password for "), MYNAMESTR
);
224 if (result
&& (g_dbInfo
[g_dbCount
].accessMutex
= MutexCreate()) == NULL
)
226 AgentWriteLog(EVENTLOG_ERROR_TYPE
, _T("%s: failed to create mutex (%d)"), MYNAMESTR
, i
);
232 // Exit if no usable configuration found
233 if (result
&& g_dbCount
< 0)
235 AgentWriteLog(EVENTLOG_ERROR_TYPE
, _T("%s: no databases to monitor"), MYNAMESTR
);
239 // Run query thread for each database configured
240 for (i
= 0; result
&& i
<= g_dbCount
; i
++)
242 g_dbInfo
[i
].queryThreadHandle
= ThreadCreateEx(queryThread
, 0, CAST_TO_POINTER(i
, void *));
253 static void SubAgentShutdown(void)
255 AgentWriteLog(EVENTLOG_INFORMATION_TYPE
, _T("%s: shutting down"), MYNAMESTR
);
256 ConditionSet(g_shutdownCondition
);
257 for (int i
= 0; i
<= g_dbCount
; i
++)
259 ThreadJoin(g_dbInfo
[i
].queryThreadHandle
);
260 MutexDestroy(g_dbInfo
[i
].accessMutex
);
262 ConditionDestroy(g_shutdownCondition
);
266 // Figure out Oracle DBMS version
269 static int getOracleVersion(DB_HANDLE handle
)
271 TCHAR versionString
[32];
273 DB_RESULT result
= DBSelect(handle
,_T("select version from v$instance"));
276 AgentWriteLog(EVENTLOG_WARNING_TYPE
, _T("%s: query from v$instance failed"), MYNAMESTR
);
277 return 700; // assume Oracle 7.0 by default
280 DBGetField(result
, 0, 0, versionString
, 32);
281 int major
= 0, minor
= 0;
282 _stscanf(versionString
, _T("%d.%d"), &major
, &minor
);
283 DBFreeResult(result
);
285 return major
* 100 + minor
* 10;
289 // Thread for SQL queries
292 THREAD_RESULT THREAD_CALL
queryThread(void* arg
)
294 int dbIndex
= CAST_FROM_POINTER(arg
, int);
295 DatabaseInfo
& db
= g_dbInfo
[dbIndex
];
296 const DWORD pollInterval
= 60 * 1000L; // 1 minute
299 TCHAR errorText
[DBDRV_MAX_ERROR_TEXT
];
303 db
.handle
= DBConnect(g_driverHandle
, db
.name
, NULL
/* db.server */, db
.username
, db
.password
, NULL
, errorText
);
304 DBEnableReconnect(db
.handle
, false);
305 if (db
.handle
!= NULL
)
307 AgentWriteLog(EVENTLOG_INFORMATION_TYPE
, _T("%s: connected to DB '%s'"), MYNAMESTR
, db
.name
);
309 db
.version
= getOracleVersion(db
.handle
);
314 startTimeMs
= GetCurrentTimeMs();
317 if (!(db
.connected
= getParametersFromDB(dbIndex
)))
322 waitTimeout
= pollInterval
- DWORD(GetCurrentTimeMs() - startTimeMs
);
323 if (ConditionWait(g_shutdownCondition
, waitTimeout
< 0 ? 1 : waitTimeout
))
327 // Try to reconnect every 30 secs
328 if (ConditionWait(g_shutdownCondition
, DWORD(30 * 1000)))
333 if (db
.connected
&& db
.handle
!= NULL
)
335 DBDisconnect(db
.handle
);
342 bool getParametersFromDB( int dbIndex
)
345 DatabaseInfo
& info
= g_dbInfo
[dbIndex
];
352 MutexLock(info
.accessMutex
);
354 for (int i
= 0; g_paramGroup
[i
].prefix
; i
++)
356 AgentWriteDebugLog(7, _T("%s: got entry for '%s'"), MYNAMESTR
, g_paramGroup
[i
].prefix
);
358 if (g_paramGroup
[i
].version
> info
.version
) // this parameter group is not supported for this DB
361 // Release previously allocated array of values for this group
362 for (int j
= 0; g_paramGroup
[i
].values
[dbIndex
] && j
< g_paramGroup
[i
].valueCount
[dbIndex
]; j
++)
363 delete (g_paramGroup
[i
].values
[dbIndex
])[j
].attrs
;
364 safe_free_and_null(g_paramGroup
[i
].values
[dbIndex
]);
366 DB_RESULT queryResult
= DBSelect(info
.handle
, g_paramGroup
[i
].query
);
367 if (queryResult
== NULL
)
373 int rows
= DBGetNumRows(queryResult
);
374 g_paramGroup
[i
].values
[dbIndex
] = (DBParameter
*)malloc(sizeof(DBParameter
) * rows
);
375 g_paramGroup
[i
].valueCount
[dbIndex
] = rows
;
376 for (int j
= 0; j
< rows
; j
++)
378 TCHAR colname
[MAX_STR
];
379 DBGetField(queryResult
, j
, 0, (g_paramGroup
[i
].values
[dbIndex
])[j
].name
, MAX_STR
);
380 (g_paramGroup
[i
].values
[dbIndex
])[j
].attrs
= new StringMap
;
381 for (int k
= 1; DBGetColumnName(queryResult
, k
, colname
, MAX_STR
); k
++)
383 TCHAR colval
[MAX_STR
];
384 DBGetField(queryResult
, j
, k
, colval
, MAX_STR
);
385 // AgentWriteDebugLog(9, _T("%s: getParamsFromDB: colname '%s' ::: colval '%s'"), MYNAMESTR, colname, colval);
386 (g_paramGroup
[i
].values
[dbIndex
])[j
].attrs
->set(colname
, colval
);
390 DBFreeResult(queryResult
);
393 MutexUnlock(info
.accessMutex
);
399 // Subagent information
403 static NETXMS_SUBAGENT_PARAM m_parameters
[] =
405 { _T("Oracle.Sessions.Count(*)"), getParameters
, "X", DCI_DT_INT
, _T("Oracle/Sessions: Number of sessions opened") },
406 { _T("Oracle.Cursors.Count(*)"), getParameters
, "X", DCI_DT_INT
, _T("Oracle/Cursors: Current number of opened cursors systemwide") },
407 { _T("Oracle.DBInfo.IsReachable(*)"), getParameters
, "R", DCI_DT_STRING
, _T("Oracle/Info: Database is reachable") },
408 { _T("Oracle.DBInfo.Name(*)"), getParameters
, "X", DCI_DT_STRING
, _T("Oracle/Info: Database name") },
409 { _T("Oracle.DBInfo.CreateDate(*)"), getParameters
, "X", DCI_DT_STRING
, _T("Oracle/Info: Database creation date") },
410 { _T("Oracle.DBInfo.LogMode(*)"), getParameters
, "X", DCI_DT_STRING
, _T("Oracle/Info: Database log mode") },
411 { _T("Oracle.DBInfo.OpenMode(*)"), getParameters
, "X", DCI_DT_STRING
, _T("Oracle/Info: Database open mode") },
412 { _T("Oracle.TableSpaces.Status(*)"), getParameters
, "X", DCI_DT_STRING
, _T("Oracle/Tablespaces: Status") },
413 { _T("Oracle.TableSpaces.Type(*)"), getParameters
, "X", DCI_DT_STRING
, _T("Oracle/Tablespaces: Type") },
414 { _T("Oracle.TableSpaces.UsedPct(*)"), getParameters
, "X", DCI_DT_INT
, _T("Oracle/Tablespaces: Percentage used") },
415 { _T("Oracle.Instance.Version(*)"), getParameters
, "X", DCI_DT_STRING
, _T("Oracle/Instance: DBMS Version") },
416 { _T("Oracle.Instance.Status(*)"), getParameters
, "X", DCI_DT_STRING
, _T("Oracle/Instance: Status") },
417 { _T("Oracle.Instance.ArchiverStatus(*)"), getParameters
, "X", DCI_DT_STRING
, _T("Oracle/Instance: Archiver status") },
418 { _T("Oracle.Instance.ShutdownPending(*)"), getParameters
, "X", DCI_DT_STRING
, _T("Oracle/Instance: Is shutdown pending") },
419 { _T("Oracle.CriticalStats.TSOffCount(*)"), getParameters
, "X", DCI_DT_INT
, _T("Oracle/CriticalStats: Number of offline tablespaces") },
420 { _T("Oracle.CriticalStats.DFOffCount(*)"), getParameters
, "X", DCI_DT_INT
, _T("Oracle/CriticalStats: Number of offline datafiles") },
421 { _T("Oracle.CriticalStats.FullSegmentsCount(*)"), getParameters
, "X", DCI_DT_INT64
, _T("Oracle/CriticalStats: Number of segments that cannot extend") },
422 { _T("Oracle.CriticalStats.RBSegsNotOnlineCount(*)"), getParameters
, "X", DCI_DT_INT64
, _T("Oracle/CriticalStats: Number of rollback segments not online") },
423 { _T("Oracle.CriticalStats.AutoArchivingOff(*)"), getParameters
, "X", DCI_DT_STRING
, _T("Oracle/CriticalStats: Archive logs enabled but auto archiving off ") },
424 { _T("Oracle.CriticalStats.DatafilesNeedMediaRecovery(*)"), getParameters
, "X", DCI_DT_INT64
, _T("Oracle/CriticalStats: Number of datafiles that need media recovery") },
425 { _T("Oracle.CriticalStats.FailedJobs(*)"), getParameters
, "X", DCI_DT_INT64
, _T("Oracle/CriticalStats: Number of failed jobs") },
426 { _T("Oracle.Dual.ExcessRows(*)"), getParameters
, "X", DCI_DT_INT64
, _T("Oracle/Dual: Excessive rows") },
427 { _T("Oracle.Performance.PhysReads(*)"), getParameters
, "X", DCI_DT_INT64
, _T("Oracle/Performance: Number of physical reads") },
428 { _T("Oracle.Performance.LogicReads(*)"), getParameters
, "X", DCI_DT_INT64
, _T("Oracle/Performance: Number of logical reads") },
429 { _T("Oracle.Performance.CacheHitRatio(*)"), getParameters
, "X", DCI_DT_INT64
, _T("Oracle/Performance: Data buffer cache hit ratio") },
430 { _T("Oracle.Performance.LibCacheHitRatio(*)"), getParameters
, "X", DCI_DT_INT64
, _T("Oracle/Performance: Library cache hit ratio") },
431 { _T("Oracle.Performance.DictCacheHitRatio(*)"), getParameters
, "X", DCI_DT_INT64
, _T("Oracle/Performance: Dictionary cache hit ratio") },
432 { _T("Oracle.Performance.RollbackWaitRatio(*)"), getParameters
, "X", DCI_DT_INT64
, _T("Oracle/Performance: Ratio of waits for requests to rollback segments") },
433 { _T("Oracle.Performance.MemorySortRatio(*)"), getParameters
, "X", DCI_DT_INT64
, _T("Oracle/Performance: PGA memory sort ratio") },
434 { _T("Oracle.Performance.DispatcherWorkload(*)"), getParameters
, "X", DCI_DT_INT64
, _T("Oracle/Performance: Dispatcher workload (percentage)") },
435 { _T("Oracle.Performance.FreeSharedPool(*)"), getParameters
, "X", DCI_DT_INT64
, _T("Oracle/Performance: Free space in shared pool (bytes)") },
436 { _T("Oracle.Objects.InvalidCount(*)"), getParameters
, "X", DCI_DT_INT64
, _T("Oracle/Objects: Number of invalid objects in DB") }
440 static NETXMS_SUBAGENT_ENUM m_enums[] =
445 static NETXMS_SUBAGENT_INFO m_info
=
447 NETXMS_SUBAGENT_INFO_MAGIC
,
448 _T("ORACLE"), NETXMS_VERSION_STRING
,
449 SubAgentInit
, SubAgentShutdown
, NULL
,
450 sizeof(m_parameters
) / sizeof(NETXMS_SUBAGENT_PARAM
), m_parameters
,
452 /*sizeof(m_parameters) / sizeof(NETXMS_SUBAGENT_PARAM),
454 sizeof(m_enums) / sizeof(NETXMS_SUBAGENT_ENUM),
461 // Entry point for NetXMS agent
464 DECLARE_SUBAGENT_ENTRY_POINT(ORACLE
)
477 BOOL WINAPI
DllMain(HINSTANCE hInstance
, DWORD dwReason
, LPVOID lpReserved
)
479 if (dwReason
== DLL_PROCESS_ATTACH
)
480 DisableThreadLibraryCalls(hInstance
);