Difference between revisions of "Oracle Monitoring Subagent"

From NetXMS Wiki
Jump to navigation Jump to search
m (Text replacement - "^" to "{{deprecated}}")
 
(6 intermediate revisions by 3 users not shown)
Line 1: Line 1:
= Introduction =
{{deprecated}}#REDIRECT [[Subagent:Oracle]]
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:
 
<source lang="xml">
<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>
</source>
 
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:
 
{| style="border-spacing:0;"
| style="background-color:#b3b3b3;border-top:0.002cm solid #000000;border-bottom:0.002cm solid #000000;border-left:0.002cm solid #000000;border-right:none;padding:0.097cm;"| '''Name'''
| style="background-color:#b3b3b3;border:0.002cm solid #000000;padding:0.097cm;"| '''Description'''
 
|-
| style="border-top:none;border-bottom:0.002cm solid #000000;border-left:0.002cm solid #000000;border-right:none;padding:0.097cm;"| Id
| style="border-top:none;border-bottom:0.002cm solid #000000;border-left:0.002cm solid #000000;border-right:0.002cm solid #000000;padding:0.097cm;"| Database id. It will be used to address this database in DCI parameters
 
|-
| style="border-top:none;border-bottom:0.002cm solid #000000;border-left:0.002cm solid #000000;border-right:none;padding:0.097cm;"| TnsName
| style="border-top:none;border-bottom:0.002cm solid #000000;border-left:0.002cm solid #000000;border-right:0.002cm solid #000000;padding:0.097cm;"| Database TNS name. This is a name for Oracle connect string as described in tnsnames.ora
 
|-
| style="border-top:none;border-bottom:0.002cm solid #000000;border-left:0.002cm solid #000000;border-right:none;padding:0.097cm;"| Username
| style="border-top:none;border-bottom:0.002cm solid #000000;border-left:0.002cm solid #000000;border-right:0.002cm solid #000000;padding:0.097cm;"| Database user name.
 
|-
| style="border-top:none;border-bottom:0.002cm solid #000000;border-left:0.002cm solid #000000;border-right:none;padding:0.097cm;"| Password
| style="border-top:none;border-bottom:0.002cm solid #000000;border-left:0.002cm solid #000000;border-right:0.002cm solid #000000;padding:0.097cm;"| 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.
 
{| style="border-spacing:0;"
| colspan="2"  style="border:none;padding:0.097cm;"| '''Oracle.Sessions.Count(*)'''
 
|-
| style="border:none;padding:0.097cm;"| ''Input''
| style="border:none;padding:0.097cm;"| Database id
 
|-
| style="border:none;padding:0.097cm;"| ''Return type''
| style="border:none;padding:0.097cm;"| DCI_DT_INT
 
|-
| style="border:none;padding:0.097cm;"| ''Description''
| style="border:none;padding:0.097cm;"| Number of sessions opened
 
|}
 
{| style="border-spacing:0;"
| colspan="2"  style="border:none;padding:0.097cm;"| '''Oracle.Cursors.Count(*)'''
 
|-
| style="border:none;padding:0.097cm;"| ''Input''
| style="border:none;padding:0.097cm;"| Database id
 
|-
| style="border:none;padding:0.097cm;"| ''Return type''
| style="border:none;padding:0.097cm;"| DCI_DT_INT
 
|-
| style="border:none;padding:0.097cm;"| ''Description''
| style="border:none;padding:0.097cm;"| Number of opened cursors systemwide
 
|}
 
{| style="border-spacing:0;"
| colspan="2"  style="border:none;padding:0.097cm;"| '''Oracle.DBInfo.IsReachable(*)'''
 
|-
| style="border:none;padding:0.097cm;"| ''Input''
| style="border:none;padding:0.097cm;"| Database id
 
|-
| style="border:none;padding:0.097cm;"| ''Return type''
| style="border:none;padding:0.097cm;"| DCI_DT_STRING
 
|-
| style="border:none;padding:0.097cm;"| ''Description''
| style="border:none;padding:0.097cm;"| YES if the database is reachable, NO otherwise
 
|}
 
{| style="border-spacing:0;"
| colspan="2"  style="border:none;padding:0.097cm;"| '''Oracle.DBInfo.Name(*)'''
 
|-
| style="border:none;padding:0.097cm;"| ''Input''
| style="border:none;padding:0.097cm;"| Database id
 
|-
| style="border:none;padding:0.097cm;"| ''Return type''
| style="border:none;padding:0.097cm;"| DCI_DT_STRING
 
|-
| style="border:none;padding:0.097cm;"| ''Description''
| style="border:none;padding:0.097cm;"| Database name
 
|}
 
{| style="border-spacing:0;"
| colspan="2"  style="border:none;padding:0.097cm;"| '''Oracle.DBInfo.CreateDate(*)'''
 
|-
| style="border:none;padding:0.097cm;"| ''Input''
| style="border:none;padding:0.097cm;"| Database id
 
|-
| style="border:none;padding:0.097cm;"| ''Return type''
| style="border:none;padding:0.097cm;"| DCI_DT_STRING
 
|-
| style="border:none;padding:0.097cm;"| ''Description''
| style="border:none;padding:0.097cm;"| Database creation date
 
|}
 
{| style="border-spacing:0;"
| colspan="2"  style="border:none;padding:0.097cm;"| '''Oracle.DBInfo.LogMode(*)'''
 
|-
| style="border:none;padding:0.097cm;"| ''Input''
| style="border:none;padding:0.097cm;"| Database id
 
|-
| style="border:none;padding:0.097cm;"| ''Return type''
| style="border:none;padding:0.097cm;"| DCI_DT_STRING
 
|-
| style="border:none;padding:0.097cm;"| ''Description''
| style="border:none;padding:0.097cm;"| Database log mode (e.g. noarchive)
 
|}
 
{| style="border-spacing:0;"
| colspan="2"  style="border:none;padding:0.097cm;"| '''Oracle.DBInfo.OpenMode(*)'''
 
|-
| style="border:none;padding:0.097cm;"| ''Input''
| style="border:none;padding:0.097cm;"| Database id
 
|-
| style="border:none;padding:0.097cm;"| ''Return type''
| style="border:none;padding:0.097cm;"| DCI_DT_STRING
 
|-
| style="border:none;padding:0.097cm;"| ''Description''
| style="border:none;padding:0.097cm;"| Database open mode (e.g. read-only)
 
|}
 
{| style="border-spacing:0;"
| colspan="2"  style="border:none;padding:0.097cm;"| '''Oracle.TableSpaces.Status(*)'''
 
|-
| style="border:none;padding:0.097cm;"| ''Input''
| style="border:none;padding:0.097cm;"| Database id, tablespace name
 
|-
| style="border:none;padding:0.097cm;"| ''Return type''
| style="border:none;padding:0.097cm;"| DCI_DT_STRING
 
|-
| style="border:none;padding:0.097cm;"| ''Description''
| style="border:none;padding:0.097cm;"| Status for specific tablespace (e.g. offline)
 
|}
 
{| style="border-spacing:0;"
| colspan="2"  style="border:none;padding:0.097cm;"| '''Oracle.TableSpaces.Type(*)'''
 
|-
| style="border:none;padding:0.097cm;"| ''Input''
| style="border:none;padding:0.097cm;"| Database id, tablespace name
 
|-
| style="border:none;padding:0.097cm;"| ''Return type''
| style="border:none;padding:0.097cm;"| DCI_DT_STRING
 
|-
| style="border:none;padding:0.097cm;"| ''Description''
| style="border:none;padding:0.097cm;"| Type for specific tablespace (e.g. temporary)
 
|}
 
{| style="border-spacing:0;"
| colspan="2"  style="border:none;padding:0.097cm;"| '''Oracle.TableSpaces.UsedPct(*)'''
 
|-
| style="border:none;padding:0.097cm;"| ''Input''
| style="border:none;padding:0.097cm;"| Database id, tablespace name
 
|-
| style="border:none;padding:0.097cm;"| ''Return type''
| style="border:none;padding:0.097cm;"| DCI_DT_INT
 
|-
| style="border:none;padding:0.097cm;"| ''Description''
| style="border:none;padding:0.097cm;"| Percentage of space used for specific tablespace
 
|}
 
{| style="border-spacing:0;"
| colspan="2"  style="border:none;padding:0.097cm;"| '''Oracle.Instance.Version(*)'''
 
|-
| style="border:none;padding:0.097cm;"| ''Input''
| style="border:none;padding:0.097cm;"| Database id
 
|-
| style="border:none;padding:0.097cm;"| ''Return type''
| style="border:none;padding:0.097cm;"| DCI_DT_STRING
 
|-
| style="border:none;padding:0.097cm;"| ''Description''
| style="border:none;padding:0.097cm;"| Version of Oracle DBMS
 
|}
 
{| style="border-spacing:0;"
| colspan="2"  style="border:none;padding:0.097cm;"| '''Oracle.Instance.Status(*)'''
 
|-
| style="border:none;padding:0.097cm;"| ''Input''
| style="border:none;padding:0.097cm;"| Database id
 
|-
| style="border:none;padding:0.097cm;"| ''Return type''
| style="border:none;padding:0.097cm;"| DCI_DT_STRING
 
|-
| style="border:none;padding:0.097cm;"| ''Description''
| style="border:none;padding:0.097cm;"| Instance status (e.g. open, mounted)
 
|}
 
{| style="border-spacing:0;"
| colspan="2"  style="border:none;padding:0.097cm;"| '''Oracle.Instance.ArchiverStatus(*)'''
 
|-
| style="border:none;padding:0.097cm;"| ''Input''
| style="border:none;padding:0.097cm;"| Database id
 
|-
| style="border:none;padding:0.097cm;"| ''Return type''
| style="border:none;padding:0.097cm;"| DCI_DT_STRING
 
|-
| style="border:none;padding:0.097cm;"| ''Description''
| style="border:none;padding:0.097cm;"| Archiver status (e.g. stopped)
 
|}
 
{| style="border-spacing:0;"
| colspan="2"  style="border:none;padding:0.097cm;"| '''Oracle.Instance.ShutdownPending(*)'''
 
|-
| style="border:none;padding:0.097cm;"| ''Input''
| style="border:none;padding:0.097cm;"| Database id
 
|-
| style="border:none;padding:0.097cm;"| ''Return type''
| style="border:none;padding:0.097cm;"| DCI_DT_STRING
 
|-
| style="border:none;padding:0.097cm;"| ''Description''
| style="border:none;padding:0.097cm;"| Indicates whether a shutdown action is pending at the moment
 
|}
 
{| style="border-spacing:0;"
| colspan="2"  style="border:none;padding:0.097cm;"| '''Oracle.CriticalStats.TSOffCount(*)'''
 
|-
| style="border:none;padding:0.097cm;"| ''Input''
| style="border:none;padding:0.097cm;"| Database id
 
|-
| style="border:none;padding:0.097cm;"| ''Return type''
| style="border:none;padding:0.097cm;"| DCI_DT_INT
 
|-
| style="border:none;padding:0.097cm;"| ''Description''
| style="border:none;padding:0.097cm;"| Number of offline tablespaces
 
|}
 
{| style="border-spacing:0;"
| colspan="2"  style="border:none;padding:0.097cm;"| '''Oracle.CriticalStats.DFOffCount(*)'''
 
|-
| style="border:none;padding:0.097cm;"| ''Input''
| style="border:none;padding:0.097cm;"| Database id
 
|-
| style="border:none;padding:0.097cm;"| ''Return type''
| style="border:none;padding:0.097cm;"| DCI_DT_INT
 
|-
| style="border:none;padding:0.097cm;"| ''Description''
| style="border:none;padding:0.097cm;"| Number of offline datafiles
 
|}
 
{| style="border-spacing:0;"
| colspan="2"  style="border:none;padding:0.097cm;"| '''Oracle.CriticalStats.FullSegmentsCount(*)'''
 
|-
| style="border:none;padding:0.097cm;"| ''Input''
| style="border:none;padding:0.097cm;"| Database id
 
|-
| style="border:none;padding:0.097cm;"| ''Return type''
| style="border:none;padding:0.097cm;"| DCI_DT_INT64
 
|-
| style="border:none;padding:0.097cm;"| ''Description''
| style="border:none;padding:0.097cm;"| Number of segments that cannot extend
 
|}
 
{| style="border-spacing:0;"
| colspan="2"  style="border:none;padding:0.097cm;"| '''Oracle.CriticalStats.RBSegsNotOnlineCount(*)'''
 
|-
| style="border:none;padding:0.097cm;"| ''Input''
| style="border:none;padding:0.097cm;"| Database id
 
|-
| style="border:none;padding:0.097cm;"| ''Return type''
| style="border:none;padding:0.097cm;"| DCI_DT_INT64
 
|-
| style="border:none;padding:0.097cm;"| ''Description''
| style="border:none;padding:0.097cm;"| Number of rollback segments not online
 
|}
 
{| style="border-spacing:0;"
| colspan="2"  style="border:none;padding:0.097cm;"| '''Oracle.CriticalStats.AutoArchivingOff(*)'''
 
|-
| style="border:none;padding:0.097cm;"| ''Input''
| style="border:none;padding:0.097cm;"| Database id
 
|-
| style="border:none;padding:0.097cm;"| ''Return type''
| style="border:none;padding:0.097cm;"| DCI_DT_STRING
 
|-
| style="border:none;padding:0.097cm;"| ''Description''
| style="border:none;padding:0.097cm;"| Archive logs enabled but auto archiving is off
 
|}
 
{| style="border-spacing:0;"
| colspan="2"  style="border:none;padding:0.097cm;"| '''Oracle.CriticalStats.DatafilesNeedMediaRecovery(*)'''
 
|-
| style="border:none;padding:0.097cm;"| ''Input''
| style="border:none;padding:0.097cm;"| Database id
 
|-
| style="border:none;padding:0.097cm;"| ''Return type''
| style="border:none;padding:0.097cm;"| DCI_DT_INT64
 
|-
| style="border:none;padding:0.097cm;"| ''Description''
| style="border:none;padding:0.097cm;"| Number of datafiles that need media recovery
 
|}
 
{| style="border-spacing:0;"
| colspan="2"  style="border:none;padding:0.097cm;"| '''Oracle.CriticalStats.FailedJobs(*)'''
 
|-
| style="border:none;padding:0.097cm;"| ''Input''
| style="border:none;padding:0.097cm;"| Database id
 
|-
| style="border:none;padding:0.097cm;"| ''Return type''
| style="border:none;padding:0.097cm;"| DCI_DT_INT64
 
|-
| style="border:none;padding:0.097cm;"| ''Description''
| style="border:none;padding:0.097cm;"| Number of failed jobs
 
|}
 
{| style="border-spacing:0;"
| colspan="2"  style="border:none;padding:0.097cm;"| '''Oracle.Dual.ExcessRows(*)'''
 
|-
| style="border:none;padding:0.097cm;"| ''Input''
| style="border:none;padding:0.097cm;"| Database id
 
|-
| style="border:none;padding:0.097cm;"| ''Return type''
| style="border:none;padding:0.097cm;"| DCI_DT_INT64
 
|-
| style="border:none;padding:0.097cm;"| ''Description''
| style="border:none;padding:0.097cm;"| Excessive rows in DUAL
 
|}
 
{| style="border-spacing:0;"
| colspan="2"  style="border:none;padding:0.097cm;"| '''Oracle.Performance.PhysReads(*)'''
 
|-
| style="border:none;padding:0.097cm;"| ''Input''
| style="border:none;padding:0.097cm;"| Database id
 
|-
| style="border:none;padding:0.097cm;"| ''Return type''
| style="border:none;padding:0.097cm;"| DCI_DT_INT64
 
|-
| style="border:none;padding:0.097cm;"| ''Description''
| style="border:none;padding:0.097cm;"| Number of physical reads
 
|}
 
{| style="border-spacing:0;"
| colspan="2"  style="border:none;padding:0.097cm;"| '''Oracle.Performance.LogicReads(*)'''
 
|-
| style="border:none;padding:0.097cm;"| ''Input''
| style="border:none;padding:0.097cm;"| Database id
 
|-
| style="border:none;padding:0.097cm;"| ''Return type''
| style="border:none;padding:0.097cm;"| DCI_DT_INT64
 
|-
| style="border:none;padding:0.097cm;"| ''Description''
| style="border:none;padding:0.097cm;"| Number of logical reads
 
|}
 
{| style="border-spacing:0;"
| colspan="2"  style="border:none;padding:0.097cm;"| '''Oracle.Performance.CacheHitRatio(*)'''
 
|-
| style="border:none;padding:0.097cm;"| ''Input''
| style="border:none;padding:0.097cm;"| Database id
 
|-
| style="border:none;padding:0.097cm;"| ''Return type''
| style="border:none;padding:0.097cm;"| DCI_DT_STRING
 
|-
| style="border:none;padding:0.097cm;"| ''Description''
| style="border:none;padding:0.097cm;"| Data buffer cache hit ratio
 
|}
 
{| style="border-spacing:0;"
| colspan="2"  style="border:none;padding:0.097cm;"| '''Oracle.Performance.LibCacheHitRatio(*)'''
 
|-
| style="border:none;padding:0.097cm;"| ''Input''
| style="border:none;padding:0.097cm;"| Database id
 
|-
| style="border:none;padding:0.097cm;"| ''Return type''
| style="border:none;padding:0.097cm;"| DCI_DT_STRING
 
|-
| style="border:none;padding:0.097cm;"| ''Description''
| style="border:none;padding:0.097cm;"| Library cache hit ratio
 
|}
 
{| style="border-spacing:0;"
| colspan="2"  style="border:none;padding:0.097cm;"| '''Oracle.Performance.DictCacheHitRatio(*)'''
 
|-
| style="border:none;padding:0.097cm;"| ''Input''
| style="border:none;padding:0.097cm;"| Database id
 
|-
| style="border:none;padding:0.097cm;"| ''Return type''
| style="border:none;padding:0.097cm;"| DCI_DT_STRING
 
|-
| style="border:none;padding:0.097cm;"| ''Description''
| style="border:none;padding:0.097cm;"| Dictionary cache hit ratio
 
|}
 
{| style="border-spacing:0;"
| colspan="2"  style="border:none;padding:0.097cm;"| '''Oracle.Performance.RollbackWaitRatio(*)'''
 
|-
| style="border:none;padding:0.097cm;"| ''Input''
| style="border:none;padding:0.097cm;"| Database id
 
|-
| style="border:none;padding:0.097cm;"| ''Return type''
| style="border:none;padding:0.097cm;"| DCI_DT_STRING
 
|-
| style="border:none;padding:0.097cm;"| ''Description''
| style="border:none;padding:0.097cm;"| Dictionary cache hit ratio
 
|}
 
{| style="border-spacing:0;"
| colspan="2"  style="border:none;padding:0.097cm;"| '''Oracle.Performance.MemorySortRatio(*)'''
 
|-
| style="border:none;padding:0.097cm;"| ''Input''
| style="border:none;padding:0.097cm;"| Database id
 
|-
| style="border:none;padding:0.097cm;"| ''Return type''
| style="border:none;padding:0.097cm;"| DCI_DT_STRING
 
|-
| style="border:none;padding:0.097cm;"| ''Description''
| style="border:none;padding:0.097cm;"| PGA memory sort ratio
 
|}
 
{| style="border-spacing:0;"
| colspan="2"  style="border:none;padding:0.097cm;"| '''Oracle.Performance.DispatcherWorkload(*)'''
 
|-
| style="border:none;padding:0.097cm;"| ''Input''
| style="border:none;padding:0.097cm;"| Database id
 
|-
| style="border:none;padding:0.097cm;"| ''Return type''
| style="border:none;padding:0.097cm;"| DCI_DT_STRING
 
|-
| style="border:none;padding:0.097cm;"| ''Description''
| style="border:none;padding:0.097cm;"| Dispatcher workload (percentage)
 
|}
 
{| style="border-spacing:0;"
| colspan="2"  style="border:none;padding:0.097cm;"| '''Oracle.Performance.FreeSharedPool(*)'''
 
|-
| style="border:none;padding:0.097cm;"| ''Input''
| style="border:none;padding:0.097cm;"| Database id
 
|-
| style="border:none;padding:0.097cm;"| ''Return type''
| style="border:none;padding:0.097cm;"| DCI_DT_INT64
 
|-
| style="border:none;padding:0.097cm;"| ''Description''
| style="border:none;padding:0.097cm;"| Free space in shared pool (bytes)
 
|}
 
|}
 
{| style="border-spacing:0;"
| colspan="2"  style="border:none;padding:0.097cm;"| '''Oracle.Objects.InvalidCount(*)'''
 
|-
| style="border:none;padding:0.097cm;"| ''Input''
| style="border:none;padding:0.097cm;"| Database id
 
|-
| style="border:none;padding:0.097cm;"| ''Return type''
| style="border:none;padding:0.097cm;"| DCI_DT_INT64
 
|-
| style="border:none;padding:0.097cm;"| ''Description''
| style="border:none;padding:0.097cm;"| Number of invalid objects in DB
 
|}
 
= 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.

Latest revision as of 18:13, 13 September 2022

This Wiki is deprecated and we are are currrently migrating remaining pages into product documentation (Admin Guide, NXSL Guide)

#REDIRECT Subagent:Oracle

Retrieved from "https://wiki.netxms.org/index.php?title=Oracle_Monitoring_Subagent&oldid=1975"