Oracle DB (JDBC)

h4. Oracle Database Profile (JDBC)

This profile monitors Oracle databases using methods based on a JDBC client. This profile provides a basic level of monitoring for Oracle instances but more importantly it provides the framework for extending monitoring through use of standard SQL queries.

h5. Services Configuration

* *Service* \- Definitions in Monarch are stored under this name.
* *Command Line* \- Service command name with arguments to be passed to the plugin.
* *Plugin Command Line* \- Plugin script called by Nagios for this Service.
* *Extended Info* \- The Extended Service Info definition, typically used for generating graphs.
{Note}Command lines displayed below are intended to be single line commands.{Note}

This package includes the following files:

Profile Definitions
* service-profile-jdbc-oracle.xml
* perfconfig-jdbc-oracle.xml

Plugins Scripts
* jdbc-oracle.tar.gz

Performance Graphing Programs
* number_graph.cgi

h5. Installation

GroundWork Monitor includes many monitoring profiles for a variety of devices, systems and applications. Profiles already imported on a new GroundWork installation include Service Ping, SNMP Network, and SSH UNIX. The GroundWork Monitor Configuration tool is used to import updated Profiles and Profiles that require additional setup; the Profile XML file and its companion Performance Configuration definition file. Services can also be imported in addition to Service Profiles in the Profile Importer. The import process is documented under GROUNDWORK PROFILES > [How to import profiles].

h5. Implementation

This section contains detail settings used by this Profile. These parameters can be altered with the Configuration tool.

h6. Command Parameters

Command parameters are in the Configuration Services section with the following names and default values. Any {color:red}{*}bolded{*}{color} arguments MUST be set before this Service Profile will work properly.

check_oracle_available
* Checks if the Oracle database is available by performing a select against the sys.dual table.
* {color:red}*$ARG1$*{color} \- TCP port on which the TNS listener is available.
* {color:red}*$ARG2$*{color} \- SID to connect to.
* {color:red}*$ARG3$*{color} \- Username to connect as.
* {color:red}*$ARG4$*{color} \- Password to connect with.
* $ARG5$ - ‘up_down’ is the default and is the name of the SQL script that is executed.
* $ARG6$ - ‘2’ is the default and is the warning threshold for alarming.
* $ARG7$ - ‘2’ is the default and is the critical threshold for alarming.
* $ARG8$ - ‘0’ is the default and is the column number to validate the threshold against.
* $ARG9$ - ‘Y’ is the default and specifies that an error should be generated if a connection fails.
* $ARG10$ - ‘N’ is the default and specifies that performance data should not be returned by the plugin.

check_oracle_locks
* Checks total, dml_allowed, and blocking locks.
* {color:red}*$ARG1$*{color} \- TCP port on which the TNS listener is available.
* {color:red}*$ARG2$*{color} \- SID to connect to.
* {color:red}*$ARG3$*{color} \- Username to connect as.
* {color:red}*$ARG4$*{color} \- Password to connect with.
* $ARG5$ - ‘locks’ is the default and is the name of the SQL script that is executed.
* $ARG6$ - ‘2’ is the default and is the warning threshold for alarming.
* $ARG7$ - ‘5’ is the default and is the critical threshold for alarming.
* $ARG8$ - ‘3’ is the default and is the column number to validate the threshold against.
* $ARG9$ - ‘N’ is the default and specifies that an error should not be generated if a connection fails.
* $ARG10$ - ‘Y’ is the default and specifies that performance data should be returned by the plugin.

check_oracle_max_processes
* Checks number of processes against the maximum and returns a percentage.
* {color:red}*$ARG1$*{color} \- TCP port on which the TNS listener is available.
* {color:red}*$ARG2$*{color} \- SID to connect to.
* {color:red}*$ARG3$*{color} \- Username to connect as.
* {color:red}*$ARG4$*{color} \- Password to connect with.
* $ARG5$ - ‘max_processes’ is the default and is the name of the SQL script that is executed.
* $ARG6$ - ‘80’ is the default and is the warning threshold for alarming.
* $ARG7$ - ‘90’ is the default and is the critical threshold for alarming.
* $ARG8$ - ‘3’ is the default and is the column number to validate the threshold against.
* $ARG9$ - ‘N’ is the default and specifies that an error should not be generated if a connection fails.
* $ARG10$ - ‘Y’ is the default and specifies that performance data should be returned by the plugin.

check_oracle_tablespace
* Checks available tablespace and returns a percentage.
* {color:red}*$ARG1$*{color} \- TCP port on which the TNS listener is available.
* {color:red}*$ARG2$*{color} \- SID to connect to.
* {color:red}*$ARG3$*{color} \- Username to connect as.
* {color:red}*$ARG4$*{color} \- Password to connect with.
* $ARG5$ - ‘tablespace’ is the name of the SQL script that is executed.
* $ARG6$ - ‘80’ is the default and is the warning threshold for alarming.
* $ARG7$ - ‘90’ is the default and is the critical threshold for alarming.
* $ARG8$ - ‘1’ is the default and is the column number to validate the threshold against.
* $ARG9$ - ‘N’ is the default and specifies that an error should not be generated if a connection fails.
* $ARG10$ - ‘N' is the default and specifies that performance data should not be returned by the plugin.

h6. Performance Graphing Parameters

The following parameters are used to generate performance charts. These parameters are set using the Configuration>Performance tool in GroundWork Monitor.

check_oracle_max_processes\_
* Graphs the actual number of processes for the database instance.
* The Nagios service description must contain the string "check_oracle_max_processes_”. &nbsp;The trailing underscore is used because it is expected that you would use Monarch service instances to create a copy of the service for each Oracle instance on the host in question and then name the service instance with a trailing \_<SID> where <SID> is the name of the Oracle instance.

check_oracle_locks\_
* Graphs the total, blocking, and max locks for the database instance.
* The Nagios service description must contain the string "check_oracle_locks_”. &nbsp;The trailing underscore is used because it is expected that you would use Monarch service instances to create a copy of the service for each Oracle instance on the host in question and then name the service instance with a trailing \_<SID> where <SID> is the name of the Oracle instance.

h6. Implementation Notes

The use of this service profile assumes correct credentials have been set up use of the SQL scripts for each service check against the desired databases. &nbsp;Simple instructions for installing an Oracle 10g Release 2 server on a Red Hat 4 ES 32-bit server running X-Windows are given below to assist with testing:

As user root in a shell, issue the following commands:

{code}/usr/sbin/groupadd oinstall

/usr/sbin/groupadd dba

/usr/sbin/groupadd oper

/usr/sbin/useradd \--g oinstall \--G dba,oper oracle

/usr/bin/passwd oracle ( and enter an appropriate password )

/bin/mkdir \--p /app/oracle

/bin/chown oracle.oinstall /app/oracle

/bin/chmod \--R /app/oracle{code}