Oracle Plug-In - Technical Reference

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 5.x Compatibility Matrix.

Views

SERVER-STATUS View

The SERVER-STATUS view shows information relating to the server that is running the database instance.

oracle_image0

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:

  • STARTED (after the command STARTUP NOMOUNT)
  • MOUNTED (after the command STARTUP MOUNT or ALTER DATABASE CLOSE)
  • OPEN (after the command STARTUP or ALTER DATABASE OPEN)
  • OPEN MIGRATE (after the command ALTER DATABASE OPEN { UPGRADE | DOWNGRADE })
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.

oracle_image1

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.

oracle_image2

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.

oracle_image3

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:

  • Row-SELECT (SS)
  • Row-X (SX)
  • Share (S)
  • s/Row-X (SSX)
  • exclusive (X)
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.

oracle_image4

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 number of maximum DB connections that can be made by a Netprobe is configurable with the environment variable MAX_DATABASE_CONNECTIONS. This configuration option can be accessed from the Gateway Setup Editor under the Advanced tab for Probes. The default 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_files

Access 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_1

On UNIX systems, Netprobe also requires the following additions to the LD_LIBRARY_PATH environment variable:

$ORACLE_HOME/lib32:$ORACLE_HOME/lib

The 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: 0

databaseName

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.ora file.
  • If you are not using the tnsnames.ora file, 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

username

The username to authenticate with the database instance.

Mandatory: Yes

traceFiles

This section contains configuration related to the Oracle Views section.

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.

Mandatory: No
Default: A blank (empty) password

locks

This section contains configuration related to the Oracle Views section.

Mandatory: No

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.

Mandatory: No
Default: 20
Unit: seconds

locks > disableView

Disables the Oracle Views view from being used in the Oracle plug-in when set to True.

Mandatory: No
Default: false

queries

This section contains configuration related to the Oracle Views view.

Mandatory: No

queries > threshold

Queries that have been running for less time than this threshold will be filtered out.

Specifying 30 as its value filters out all queries that have been running for less than 30 seconds.

Mandatory: No
Default: 30
Unit: seconds