Difference between revisions of "Subagent:Oracle"

From NetXMS Wiki
Jump to navigation Jump to search
(Replaced content with "Information moved to documentation: https://www.netxms.org/documentation/adminguide/database-monitoring.html#oracle")
Line 1: Line 1:
{{DISPLAYTITLE:Oracle Monitoring Subagent}}
Information moved to documentation:
= Summary =
NetXMS subagent for Oracle DBMS monitoring (further referred to as Oracle subagent) monitors one or more instances of Oracle databases and reports various 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.
https://www.netxms.org/documentation/adminguide/database-monitoring.html#oracle
 
= Configuration =
 
Subagent can be configured using XML configuration file (usually created as separate file in configuration include directory), or in simplified INI format, usually in main agent configuration file.
 
== XML configuration ==
 
There is a sample Oracle subagent configuration file in XML format:
 
<syntaxhighlight lang="xml">
<config>
<agent>
<subagent>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>
</syntaxhighlight>
 
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 definition supports the following parameters:
 
{| class="wikitable"
|-
! Parameter !! Description !! Default value
|-
| Id || Database identifier. It will be used to address this database in parameters. || ''empty''
|-
| TnsName || Database TNS name. This is a name for Oracle connect string as described in tnsnames.ora. It can also be instant client connection string. || ''empty''
|-
| UserName || User name for connecting to database. || ''empty''
|-
| Password || Database user password. || ''empty''
|-
| EncryptedPassword || Database user password encrypted with nxencpasswd. || ''empty''
|}
 
== INI configuration ==
 
There is a sample Oracle subagent configuration file in INI format:
 
<syntaxhighlight lang="ini">
SubAgent = oracle.nsm
 
*ORACLE
ID = DB1
TNSName = TEST
Username = NXMONITOR
Password = NXMONITOR
</syntaxhighlight>
 
You can specify only one database when using INI configuration format. If you need to monitor multiple databases from same agent, you should use configuration file in XML format.
 
'''ORACLE''' section can contain the following parameters:
 
{| class="wikitable"
|-
! Parameter !! Description !! Default value
|-
| ID || Database identifier. It will be used to address this database in parameters. || ''empty''
|-
| TNSName || Database TNS name. This is a name for Oracle connect string as described in tnsnames.ora. It can also be instant client connection string. || ''empty''
|-
| Username || User name for connecting to database. || ''empty''
|-
| Password || Database user password. || ''empty''
|-
| EncryptedPassword || Database user password encrypted with nxencpasswd. || ''empty''
|}
 
= 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.
 
{| class="wikitable"
|-
! Parameter !! Argument(s) !! Data Type !! Description
|-
| Oracle.CriticalStats.AutoArchivingOff(*) || Database ID || Integer || Archive logs enabled but auto archiving is off
|-
| Oracle.CriticalStats.DatafilesNeedMediaRecovery(*) || Database ID || Integer || Number of data files that need media recovery
|-
| Oracle.CriticalStats.DFOffCount(*) || Database ID || Integer || Number of offline data files
|-
| Oracle.CriticalStats.FailedJobs(*) || Database ID || Integer || Number of failed jobs
|-
| Oracle.CriticalStats.FullSegmentsCount(*) || Database ID || Integer || Number of segments that cannot extend
|-
| Oracle.CriticalStats.RBSegsNotOnlineCount(*) || Database ID || Integer || Number of rollback segments not online
|-
| Oracle.CriticalStats.TSOffCount(*) || Database ID || Integer || Number of offline tablespaces
|-
| Oracle.Cursors.Count(*) || Database ID || Integer || Number of opened cursors (system-wide)
|-
| Oracle.DBInfo.IsReachable(*) || Database ID || String || YES if the database is reachable, NO otherwise
|-
| Oracle.DBInfo.Name(*) || Database ID || String || Database name
|-
| Oracle.DBInfo.CreateDate(*) || Database ID || String || Database creation date
|-
| Oracle.DBInfo.LogMode(*) || Database ID || String || Database log mode (e.g. noarchive)
|-
| Oracle.DBInfo.OpenMode(*) || Database ID || String || Database open mode (e.g. read-only)
|-
| Oracle.Instance.ArchiverStatus(*) || Database ID || String|| Archiver status (e.g. stopped)
|-
| Oracle.Instance.ShutdownPending(*) || Database ID || String|| Indicates whether a shutdown action is pending at the moment
|-
| Oracle.Instance.Status(*) || Database ID || String|| Instance status (e.g. open, mounted)
|-
| Oracle.Instance.Version(*) || Database ID || String|| Version of Oracle DBMS
|-
| Oracle.Sessions.Count(*) || Database ID || Integer || Number of sessions opened
|-
| Oracle.TableSpaces.Status(*) || Database ID, Tablespace Name || String || Status for specific tablespace (e.g. offline)
|-
| Oracle.TableSpaces.Type(*) || Database ID, Tablespace Name || String || Type for specific tablespace (e.g. temporary)
|-
| Oracle.TableSpaces.UsedPct(*) || Database ID, Tablespace Name || Integer || Percentage of space used for specific tablespace
|}
 
 
{| 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. The parameter returns 1 if there are more than 1 record in DUAL, 0 otherwise.
 
|}
 
{| 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;"| Rollback wait ratio. A percentage of delays during the access to rollback segments.
 
|}
 
{| 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. A percentage of sort operations performed in memory.
 
|}
 
{| 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. A percentage of time dispatchers are busy.
 
|}
 
{| 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 in 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 the database.
 
|}
 
= 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.

Revision as of 16:43, 24 November 2017

Information moved to documentation:

https://www.netxms.org/documentation/adminguide/database-monitoring.html#oracle

Retrieved from "https://wiki.netxms.org/index.php?title=Subagent:Oracle&oldid=1628"