Oracle Monitoring Subagent

Introduction

NetXMS subagent for Oracle DBMS monitoring (hereinafter referred to as Oracle subagent) monitors one or more instances of Oracle databases and reports various crucial database-related parameters.

All parameters available from Oracle subagent gathered or calculated once per minute thus its recommended to set DCI poll interval for these items to 60 seconds or more. All parameters are obtained or derived from the data available in Oracle's data dictionary tables and views through regular select queries. Oracle subagent does not monitor any of the metrics related to lower level database layers, such as database processes. Monitoring of such parameters can be achieved through the standard NetXMS functionality.

Configuration

There is a sample Oracle subagent configuration file in XML format:

<config>
	<agent>
		<subagent>c:\netxms\bin\oracle.nsm</subagent>
	</agent>
	<oracle>
		<databases>
			<database id="1">
				<id>DB1</id>
				<tnsname>TEST</tnsname>
				<username>NXMONITOR</username>
				<password>NXMONITOR</password>
			</database>
			<database id="2">
				<id>DB2</id>
				<tnsname>PROD</tnsname>
				<username>NETXMS</username>
				<password>PASSWORD</password>
			</database>
		</databases>
	</oracle>
</config>

You can specify multiple databases in the oracle section. Each database description must be surrounded by database tags with the id attribute. id can be any unique integer, it instructs the Oracle subagent about the order in which database sections will be processed.

Each database description supports the following parameters:

Name	Description
Id	Database id. It will be used to address this database in DCI parameters
TnsName	Database TNS name. This is a name for Oracle connect string as described in tnsnames.ora
Username	Database user name.
Password	Database user password.

Provided Parameters

The Oracle subagent declares and supports a number of Oracle.* DCI parameters. Almost all of them accept a database id (the one specified in “Id” tag in the subagent configuration file) as the only input parameter (with the exception of Oracle.TableSpaces.* which also take a tablespace name). The all supported DCIs are described below.

Oracle.Sessions.Count(*)
Input	Database id
Return type	DCI_DT_INT
Description	Number of sessions opened

Oracle.Cursors.Count(*)
Input	Database id
Return type	DCI_DT_INT
Description	Number of opened cursors systemwide

Oracle.DBInfo.IsReachable(*)
Input	Database id
Return type	DCI_DT_STRING
Description	YES if the database is reachable, NO otherwise

Oracle.DBInfo.Name(*)
Input	Database id
Return type	DCI_DT_STRING
Description	Database name

Oracle.DBInfo.CreateDate(*)
Input	Database id
Return type	DCI_DT_STRING
Description	Database creation date

Oracle.DBInfo.LogMode(*)
Input	Database id
Return type	DCI_DT_STRING
Description	Database log mode (e.g. noarchive)

Oracle.DBInfo.OpenMode(*)
Input	Database id
Return type	DCI_DT_STRING
Description	Database open mode (e.g. read-only)

Oracle.TableSpaces.Status(*)
Input	Database id, tablespace name
Return type	DCI_DT_STRING
Description	Status for specific tablespace (e.g. offline)

Oracle.TableSpaces.Type(*)
Input	Database id, tablespace name
Return type	DCI_DT_STRING
Description	Type for specific tablespace (e.g. temporary)

Oracle.TableSpaces.UsedPct(*)
Input	Database id, tablespace name
Return type	DCI_DT_INT
Description	Percentage of space used for specific tablespace

Oracle.Instance.Version(*)
Input	Database id
Return type	DCI_DT_STRING
Description	Version of Oracle DBMS

Oracle.Instance.Status(*)
Input	Database id
Return type	DCI_DT_STRING
Description	Instance status (e.g. open, mounted)

Oracle.Instance.ArchiverStatus(*)
Input	Database id
Return type	DCI_DT_STRING
Description	Archiver status (e.g. stopped)

Oracle.Instance.ShutdownPending(*)
Input	Database id
Return type	DCI_DT_STRING
Description	Indicates whether a shutdown action is pending at the moment

Oracle.CriticalStats.TSOffCount(*)
Input	Database id
Return type	DCI_DT_INT
Description	Number of offline tablespaces

Oracle.CriticalStats.DFOffCount(*)
Input	Database id
Return type	DCI_DT_INT
Description	Number of offline datafiles

Oracle.CriticalStats.FullSegmentsCount(*)
Input	Database id
Return type	DCI_DT_INT64
Description	Number of segments that cannot extend

Oracle.CriticalStats.RBSegsNotOnlineCount(*)
Input	Database id
Return type	DCI_DT_INT64
Description	Number of rollback segments not online

Oracle.CriticalStats.AutoArchivingOff(*)
Input	Database id
Return type	DCI_DT_STRING
Description	Archive logs enabled but auto archiving is off

Oracle.CriticalStats.DatafilesNeedMediaRecovery(*)
Input	Database id
Return type	DCI_DT_INT64
Description	Number of datafiles that need media recovery

Oracle.CriticalStats.FailedJobs(*)
Input	Database id
Return type	DCI_DT_INT64
Description	Number of failed jobs

Oracle.Dual.ExcessRows(*)
Input	Database id
Return type	DCI_DT_INT64
Description	Excessive rows in DUAL

Oracle.Performance.PhysReads(*)
Input	Database id
Return type	DCI_DT_INT64
Description	Number of physical reads

Oracle.Performance.LogicReads(*)
Input	Database id
Return type	DCI_DT_INT64
Description	Number of logical reads

Oracle configuration

An Oracle user must have the role select_catalog_role assigned.

Required rights can be assigned to user with the following query:

grant select_catalog_role to user;

Where user is the user configured in Oracle subagent for database access.