--- title: "Oracle" description: "" url: "https://docs.itrsgroup.com/docs/geneos/7.11.0/collection/oracle/index.html" --- ## 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](#oracle-database-configuration) section exist. To know the supported database versions, see [Database support in Geneos Compatibility Matrix](https://docs.itrsgroup.com/docs/all/geneos/7x-geneos-compat-matrix/index.html). ## Views ### SERVER-STATUS View The SERVER-STATUS view shows information relating to the server that is running the database instance. ![SERVER-STATUS View](https://docscdn.itrsgroup.com/geneos/images/plug-ins/oracle-server-status-dataview.png) **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. ![TRACE-FILES View](https://docscdn.itrsgroup.com/geneos/images/plug-ins/oracle-trace-files-dataview.png) **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 View](https://docscdn.itrsgroup.com/geneos/images/plug-ins/oracle-table-space-dataview.png) **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 View](https://docscdn.itrsgroup.com/geneos/images/plug-ins/oracle-db-locks-dataview.png) **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. ![LONG-QUERIES View](https://docscdn.itrsgroup.com/geneos/images/plug-ins/oracle-long-queries-dataview.png) **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_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 TO ``` 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](https://docs.itrsgroup.com/docs/geneos/7.11.0/collection/sql-toolkit-config/index.html#oracle). ``` 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](#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. Mandatory: Yes ### username The username to authenticate with the database instance. Mandatory: Yes ### traceFiles This section contains configuration related to the [Oracle Views](#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](#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](#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](#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