Introduction
The Oracle plug-in displays a number of metrics from an Oracle database including (but not limited to) table space data, database locks and long running queries. In addition the Oracle plug-in can also monitor the Oracle trace file directory, which contains a log file for every time an Oracle process failed after encountering an exception.
In order to operate, the Oracle plug-in must be able to connect to and issue queries on the Oracle database and also access the Oracle trace file directory for trace monitoring functionality.
This plug-in should work fine if the tables and view specified in the Oracle Database Configuration section exist. To know the supported database versions, see Database support in Geneos Compatibility Matrix.
Views
SERVER-STATUS View
The SERVER-STATUS view shows information relating to the server that is running the database instance.
SERVER-STATUS Headline Legend
| Name | Description |
|---|---|
| connectionStatus | The current connection status of the plug-in to the Oracle database. |
SERVER-STATUS Table Legend
| Name | Description |
|---|---|
| serverVersion | The Oracle product name and version number, including some of the components installed. |
| serverName | The hostname or IP address of the database server. |
| databaseName | The name of the database instance being monitored. |
| startupTime | The time the database was last started. |
| instanceStatus |
The status of the database instance. This can one of the following:
|
| clusterMode | Shows if the database instance is mounted in cluster mode. |
| archiverLogStatus | Shows the status of the archiver, one of STOPPED, STARTED or FAILED. FAILED means that the archiver failed to archive a log the last time but will try again in 5 seconds. |
| loginsAllowed | Shows if logins are ALLOWED or RESTRICTED. |
| shutdownPending | Shows either YES or NO. |
| databaseStatus | Shows the current status of the database. |
| databaseRole | Show which role the database instance is in either PRIMARY_INSTANCE or SECONDARY_INSTANCE, or UNKNOWN if the instance has been started but not mounted. |
| activeState | Shows three different states NORMAL, QUIESCING or QUIECED. |
TRACE-FILES View
The TRACE-FILES view shows information relating to trace files that have been created by the database server.
TRACE-FILES Headline Legend
| Name | Description |
|---|---|
| traceFilesPath | The configured Oracle trace files directory. |
| traceFiles | The number of trace files detected. |
TRACE-FILES Table Legend
| Name | Description |
|---|---|
| filename | Name of the trace file. |
| creationDate | Date and time the file was created. |
| lastModifiedDate | Date and time the file was last modified. |
| size | Size of the file in kilobytes. |
| user | The file owner. |
| permissions | UNIX style permissions for the file, showing read/write/execute permissions for the owner, user group and everyone respectively. |
TABLE-SPACE View
The TABLE-SPACE view show information relating to how much storage space the database instance is taking up.
TABLE-SPACE Headline Legend
| Name | Description |
|---|---|
| connectionStatus | The current connection status of the plug-in to the Oracle database. |
TABLE-SPACE Table Legend
| Name | Description |
|---|---|
| tableName | Shows the name of the tablespace. |
| mbAllocated | The space in megabytes currently allocated for the tablespace. |
| mbFree | The space in megabytes currently free in the tablespace. |
| mbUsed | The space in megabytes currently used in the tablespace. |
| percentageFree | free space as a percentage of the allocated space. |
| percentageUsed | The used space as a percentage of the allocated space. Once the tablespace is filled, the allocated space may auto-extend depending upon the Oracle configuration. |
| autoExtend | Shows whether the tablespace can automatically extend its currently allocated size. |
DB-LOCKS View
The DB-LOCKS view show information on the locks currently held on the database instance, for a period longer than a configured threshold time.
DB-LOCKS Headline Legend
| Name | Description |
|---|---|
| connectionStatus | The current connection status of the plug-in to the Oracle database. |
| timeThreshold | The current configured lock threshold in seconds. Locks held for less time than this threshold will not be displayed in the view. |
| totalDatabaseLocks | The total number of database locks held for at least the threshold time. |
DB-LOCKS Table Legend
| Name | Description |
|---|---|
| ID | A unique lock identifier. |
| sessionID | The session that this lock belongs to. |
| username | Database username of the user that created this lock. A blank value indicates that the lock was created internally by the database server. |
| blocked | Shows if the lock is a blocker or not. |
| waiting | Shows if the lock is waiting or not. |
| lockID1 | Shows the lock identifier #1 (depends on type). Consult the Oracle documentation for v$lock for further information. |
| lockID2 | Shows the lock identifier #2 (depends on type). Consult the Oracle documentation for v$lock for further information. |
| lockMode |
The current lock mode - one of the following values:
|
| lockDuration | The time (in seconds) that the lock has been in this state. |
LONG-QUERIES View
The LONG-QUERIES view shows metrics on queries currently running on the database, for a period longer than the configured threshold time (by default 10 seconds). In addition to user queries this view will also show system queries such as a backup, recovery and statistics gathering operations, provided they run for longer than the threshold time.
The timeRemaining is an estimate of the time left before the query completes, as reported by the Oracle server. To ensure this field is populated, enable the Oracle TIMED_STATISTICS or SQL_TRACE parameters and calculate statistics for the monitored database using the analyze statement in SQL, or by using the DBMS_STATS package.
LONG-QUERIES Headline Legend
| Name | Description |
|---|---|
| connectionStatus | The current connection status of the plug-in to the Oracle database. |
| timeThreshold | The configured time threshold in seconds. Queries running for less than this threshold will not be displayed in the view. |
| totalLongRunningQueries | The total number of queries running for at least the threshold time. |
LONG-QUERIES Table Legend
| Name | Description |
|---|---|
| ID | A unique query identifier. |
| sessionID | The session that this query belongs to. |
| username | Database username of the user that is running the query. A blank value indicates that the query was started internally by the database server. |
| processID | The ID of the process running the query on the client machine. |
| machineName | The OS machine name of the client running the query. |
| elapsedTime | Time (in seconds) elapsed since the query was started. |
| timeRemaining | The estimated time remaining in seconds until the query is completed. |
| percentageComplete | An estimated percentage of how complete the query is. |
| message | A statistics summary message. |
Note: The maximum number of DB
connections that can be made by a Netprobe is
configurable by setting the environment variable
MAX_DATABASE_CONNECTIONS. This configuration option
can be accessed from the Gateway Setup Editor > Advanced tab for Probes. The default value is 10.
Oracle Database Configuration
The Oracle plug-in obtains its information by querying the database system views / tables for data. The user that the plug-in is connecting as must have permissions to access these views for correct operation. The views which must be available are as follows:
dba_free_space
v$instance
v$session_longops
v$session
v$lock
dba_data_files
sys.v_$TEMP_SPACE_HEADER
sys.v_$Temp_extent_pool
dba_temp_filesAccess to a view can be granted by issuing the following command.
Note: You will need to issue this command as sysdba or another user with sufficient permissions to grant privileges.
GRANT SELECT ON <insert view name here> TO <object>E.g.,
GRANT SELECT ON v$instance TO public;Environment Configuration
The Oracle plug-in requires that the Oracle client
libraries are installed on the Netprobe host. The
Netprobe process also requires that the environment
variable ORACLE_HOME is set before running.
For more information on how to configure the Oracle libraries, see Oracle database in SQL Toolkit Plug-In User Guide.
ORACLE_HOME=/usr/local/oracle/product/client_1On UNIX systems, Netprobe also requires the following additions to the LD_LIBRARY_PATH environment variable:
$ORACLE_HOME/lib32:$ORACLE_HOME/libThe client library should also be configured with a connection to the Oracle server hosting the database that will be monitored. One way of doing this is to edit the tnsnames.ora configuration file to include the information, or to use the Oracle SQL*Net Easy Configurator application provided with the client library.
In addition, Microsoft.VC80.CRT redistributables are required when using AES-256 passwords in Windows machines.
Plug-In Configuration
The Oracle plug-in is configured in the oracle configuration section. Oracle will connect to the server as configured for the Oracle client library. The plug-in will connect using the supplied username and password, and the user must have permissions to access the system views as described in the Oracle Database Configuration section.
A sample configuration is shown below:
databaseName: ORCL
username: sysman
password: oracle
traceFiles
path: .
queries
threshold: 0databaseName
The name of the database instance. This is
specified in the tnsnames.ora file.
The full TNS definition in tnsnames.ora may be specified in place of the database name in any of these cases:
- tnsnames.ora is available
- tnsnames.ora is not available
- LDAP is used to connect
If tnsnames.ora is available, doing so will override the details of the file in your Oracle directory.
For example, if you have these configuration details:
MYALIAS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = MYHOST)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = MYDATABASE)
(SERVICE_NAME = MYDATABASE) ) )In the database name field, you may specify as follows:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=MYHOST)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SID=MYDATABASE)(SERVICE_NAME=MYDATABASE)))Ensure that the alias is not included and the syntax does not have spaces. All necessary environment variables must also be set in the start-up environment of the Netprobe and adjusted for the operating system and start-up script in use. E.g. for UNIX/Linux components started from a bash script:
export ORACLE_HOME=/usr/local/oracle/instantclient_11_2
export LD_LIBRARY_PATH=${ORACLE_HOME}/lib64:${LD_LIBRARY_PATH}
Remember to adjust the path to the Oracle installation directory and choose the correct library subdirectory based on the architecture of the Netprobe in use.
When connecting to Oracle database, you might encounter the ORA-12154: TNS:could not resolve the connect identifier specified error message. This means that the value in the Gateway Setup Editor > Database logging > Database name parameter cannot be resolved.
To resolve this, do one of the following:
- Ensure that the database name is correctly used in the
tnsnames.orafile. - If you are not using the
tnsnames.orafile, enter the following values in the Database name parameter:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<database server host/IP>)(PORT=<database server port>))(CONNECT_DATA=(SERVICE_NAME=database name/database global name)))
Mandatory: Yes
traceFiles > path
The path to the trace files directory, e.g. /export/share/app/oracle/udump
Mandatory: Yes
password
The password to authenticate with the database instance. To set an encrypted password, click on the "Set Password" button, then enter and confirm the password to be used.
locks > threshold
Locks that have been held for less time than this threshold will be filtered out.
Specifying 20 as its value filters out all locks that have been held for less than 20 seconds.
Unit: seconds
locks > disableView
Disables the Oracle Views view from being used in the Oracle plug-in when set to True.