Oracle Monitoring

Last updated on 06 January, 2023

With LogicMonitor’s Oracle package, you can monitor a large number of Oracle operations including blocked session metrics, library cache performance, backup and recovery activities, and resource usage.

Oracle databases are monitored using Groovy with JDBC connections, through a user account with read-only permissions to statistics tables. If multiple instances are present, LogicMonitor will auto-discover them for clients with correctly configured systems (SSH for Linux or WMI for Windows access) and will automatically begin monitoring the new databases as they are found or created.​​

Compatibility

LogicMonitor’s Oracle package is verified to be compatible with Oracle version 12c through 21c. The package may work with earlier Oracle versions, however some datapoints may not be available.

Setup Requirements

The setup requirements are as follows:

  • Import LogicModules
  • Add devices into monitoring
  • Set up Oracle user

Import LogicModules

From the LogicMonitor Exchange, install all Oracle LogicModules, which are listed in the LogicModules in Package section of this support article. If these LogicModules are already present, ensure you have the most recent version. For more information, see LM Exchange.

Add Devices Into Monitoring

Add your Oracle resources into monitoring. For more information on adding resources into monitoring, see Adding Devices.

Set Up Oracle User

The LogicMonitor Collector must provide the appropriate credentials in order to successfully access the Oracle resource. These credentials must belong to an Oracle user account with access to specific system tables. No user tables need to be exposed and the Oracle user account should be read only.

The process for setting up an Oracle user account for LogicMonitor monitoring purposes varies depending upon whether the Oracle database is a container or non-container database. Follow the instructions provided in the Determining Whether a Database Is a CDB section of the Oracle Database Administrator’s Guide to discover what type of Oracle database it is.

Note: The instructions provided in this section assume the reader has some knowledge of PL/SQL and has the administrative privileges (SYSDBA) necessary to set up a new user.

Container Database Setup

Run the following script to create an Oracle user on a container database with access to only those specific system tables queried by LogicMonitor. Be sure to update the <password>, <username> and <monitorProfile> placeholders with the credentials you want to assign to the user account.

CREATE USER <username> IDENTIFIED BY <password>;
CREATE PROFILE <monitorProfile> LIMIT SESSIONS_PER_USER 30;
GRANT SELECT ON DBA_DATA_FILES to <username>; CONTAINER=ALL;
GRANT SELECT ON DBA_SEGMENTS TO <username>; CONTAINER=ALL;
GRANT SELECT ON DBA_TABLESPACE_USAGE_METRICS to <username>; CONTAINER=ALL;
GRANT SELECT ON DBA_TABLESPACES TO <username>; CONTAINER=ALL;
GRANT SELECT ON DBA_TEMP_FILES to <username>; CONTAINER=ALL;
GRANT SELECT ON V_$ASM_DISKGROUP to <username>; CONTAINER=ALL;
GRANT SELECT ON V_$BACKUP_ASYNC_IO TO <username>; CONTAINER=ALL;
GRANT SELECT ON V_$BACKUP_FILES TO <username>; CONTAINER=ALL;
GRANT SELECT ON V_$DATAFILE TO <username>; CONTAINER=ALL;
GRANT SELECT ON V_$DATAFILE_HEADER to <username>; CONTAINER=ALL;
GRANT SELECT ON v_$LIBRARYCACHE TO <username>; CONTAINER=ALL;
GRANT SELECT ON v_$LOCK TO <username>; CONTAINER=ALL;
GRANT SELECT ON V_$LOG to <username>; CONTAINER=ALL;
GRANT SELECT ON V_$RECOVERY_FILE_DEST to <username>; CONTAINER=ALL;
GRANT SELECT ON V_$RESOURCE_LIMIT TO <username>; CONTAINER=ALL;
GRANT SELECT ON V_$RMAN_BACKUP_JOB_DETAILS TO <username>; CONTAINER=ALL;
GRANT SELECT ON v_$session TO <username>; CONTAINER=ALL;
GRANT SELECT ON v_$SESSION_LONGOPS TO <username>; CONTAINER=ALL;
GRANT SELECT ON v_$sql TO <username>; CONTAINER=ALL;
GRANT SELECT ON v_$SYSMETRIC to <username>; CONTAINER=ALL;
GRANT SELECT ON V_$SYSSTAT TO <username>; CONTAINER=ALL;
GRANT SELECT ON V_$VERSION to <username>; CONTAINER=ALL;

For additional resources on setting up Oracle user accounts, see:

Non-Container Database Setup

Run the following script to create an Oracle user on a non-container database with access to only those specific system tables queried by LogicMonitor. Be sure to update the <password>, <username> and <monitorProfile> placeholders with the credentials you want to assign to the user account.

CREATE USER <username> IDENTIFIED BY <password>;
CREATE PROFILE <monitorProfile> LIMIT SESSIONS_PER_USER 30;
ALTER USER <username> PROFILE <monitorProfile>
GRANT SELECT ON DBA_DATA_FILES to <username>;
GRANT SELECT ON DBA_SEGMENTS TO <username>;
GRANT SELECT ON DBA_TABLESPACE_USAGE_METRICS to <username>;
GRANT SELECT ON DBA_TABLESPACES TO <username>;
GRANT SELECT ON DBA_TEMP_FILES to <username>;
GRANT SELECT ON V_$ASM_DISKGROUP to <username>;
GRANT SELECT ON V_$BACKUP_ASYNC_IO TO <username>;
GRANT SELECT ON V_$BACKUP_FILES TO <username>;
GRANT SELECT ON V_$DATAFILE TO <username>;
GRANT SELECT ON V_$DATAFILE_HEADER to <username>;
GRANT SELECT ON v_$LIBRARYCACHE TO <username>;
GRANT SELECT ON v_$LOCK TO <username>;
GRANT SELECT ON V_$LOG to <username>;
GRANT SELECT ON V_$RECOVERY_FILE_DEST to <username>;
GRANT SELECT ON V_$RESOURCE_LIMIT TO <username>;
GRANT SELECT ON V_$RMAN_BACKUP_JOB_DETAILS TO <username>;
GRANT SELECT ON v_$session TO <username>;
GRANT SELECT ON v_$SESSION_LONGOPS TO <username>;
GRANT SELECT ON v_$sql TO <username>;
GRANT SELECT ON v_$SYSMETRIC to <username>;
GRANT SELECT ON V_$SYSSTAT TO <username> ;
GRANT SELECT ON V_$VERSION to <username>;

Set Up Credentials

Once an Oracle user account has been established for monitoring purposes, the credentials for that account must be set as properties on the Oracle resource(s) within LogicMonitor. As highlighted in the following tables, there are other (optional) properties that can also be set for Oracle resources. For more information on setting properties, see Resource and Instance Properties.

PropertyDescriptionRequired or Optional
jdbc.oracle.userUsername assigned to the Oracle user account. It is easiest if the same username is set up everywhere.Required
jdbc.oracle.passPassword assigned to the Oracle user account.Required
jdbc.oracle.portPort number (if empty, the default port of 1521 is used). Multiple ports should be specified with a comma (i.e. “1521, 2266, 51000”).Optional
ssh.userFor Linux systems, enter SSH username to enable database auto-discovery; Windows systems should automatically auto-discover databases.Optional
ssh.passFor Linux systems, enter SSH password to enable database auto-discovery; Windows systems should automatically auto-discover databases.Optional
ssh.certFor Linux systems, enter certificate (if required) for SSH to enable database auto-discovery; Windows systems should automatically auto-discover databases.Optional
oracle.dbnamesEnter comma separated list of database names.Required only if database auto-discovery is not enabled via SSH properties for Linux systems.
system.categoriesWhen an Oracle server is found on a device, OracleDB is added to the value of system.categories.Required. Under normal operations this property is auto populated; you can manually add OracleDB if missing.
oracle.dbname.urlThe connection string to the named database.Required only if not created by the addCategory_OracleDatabase property source.   

Example: jdbc:oracle:thin:@[host]:[port]:[sid]
oracle.ssh.filemethodUsed to change the default method for finding database names and ports.Optional. Only accepts listener as an option.
oracle.ssh.filelocationChange the location of the file processed for database names.Accepts a directory nameThe default for the oratab file is \etc\ The default for listener.ora is /var/opt/oracle/Must end with a forward slash /Optional

If you have multiple Oracle databases with multiple users and/or port numbers, use the following properties to establish these settings on a per-database-basis. These properties must be set at the resource level to override the jdbc.oracle.user/pass/port properties highlighted in the previous table.

PropertyDescriptionRequired or Optional
oracle.<DatabaseName>.userThe username assigned to the Oracle user account that has access to the database specified (substitute the <DatabaseName> placeholder in the property name with the name of the Oracle database).Optional
oracle.<DatabaseName>.passwordThe password assigned to the Oracle user account that has access to the database specified (substitute the <DatabaseName> placeholder in the property name with the name of the Oracle database).Optional
oracle.<DatabaseName>.portThe port number (if different than default port 1521 or port assigned by jdbc.oracle.port) used by the database specified (substitute the <DatabaseName> placeholder in the property name with the name of the Oracle database).Optional
oracle.<DatabaseName>.urlThe connection string to the named database.Required only if not created by the addCategory_OracleDatabase property source.   

Example: jdbc:oracle:thin:@[host]:[port]:[sid]

Migration from Legacy Oracle DataSources

In October of 2022, LogicMonitor released a new suite of Oracle DataSources. LogicMonitor has rewritten the Oracle modules to be consistent with the other database modules in LogicMonitor. The new modules require a JDBC connection string. A port is no longer required, but can still be used. The exposure of the connection string as a property on a device now allows LogicMonitor to monitor any Oracle database if there is a connection string, a username, and password. This can be an Oracle Cloud database, an Azure Oracle DB, or an AWS RDS instance.

Upgrading Existing Installations

For existing installations, you’ll need to install the new DataSources (conversion).

  1. Delete the PropertySources Oracle_Database_Info and Oracle_Database_Names.   
  2. Update the PropertySource addCategory_OracleDatabase as follows:
    • Make sure that addCategory_OracleDatabase has run and that all of the Oracle databases have a property auto.oracle.dbnames and auto.oracle.dbnames.url where dbnames is one of the names in the auto.oracle.dbnames property.
  3. If the device has manually configured properties for oracle.dbnames and oracle.port the addCategory will attempt to create the JDBC connection string.  If this fails, you will need to add a connection string manually.

For more information about disabling DataSources, see Disabling Monitoring for a DataSource or Instance.

Troubleshooting

The Oracle Database Troubleshooter DataSource exists to capture configuration and permissions issues on initial setup. It is not designed as a monitoring DataSource. If data is not being returned as expected, navigate to this DataSource in the Resources tree and check for the existence of errors. (Errors display as instances in the Resources tree). Additionally, any errors captured by the troubleshooter DataSource are included in alert details.​

LogicModules in Package

LogicMonitor’s package for Oracle consists of the following LogicModules. For full coverage, please ensure that all of these LogicModules are imported into your LogicMonitor platform.

NameTypeDescription
addCategory_OracleDatabasePropertySourceAdds OracleDB to the resource’s system.categories property.
Oracle_Database_NamesPropertySourceCreates properties for database names, connection strings (url) and database version.
Oracle_Database_ASMStorageDataSourceDisplays the status and percent used of Oracle ASM storage.
Oracle_Connection_StatusDataSourceChecks the ability of the collector to connect to the Oracle database. Also reports the round trip time for a select statement.  Returns either the TNS Error code or the ORA error code on a failure.

For more information, see Oracle Connection Status DataSource.
Oracle_Database_DatafileStatusDataSourceDisplays the status of the data files.
Oracle_Database_DiskUsageDataSourceDisplays the amount of space used by data, log and temp files.
Oracle_Database_FRAUtilizationDataSourcedisplays information about the disk quota and current disk usage in the fast recovery area. Will alert if recovery area is close to full.
Oracle_Database_MonitorUserDataSourceMonitors the number of sessions used by the monitoring user in Oracle.  Reports active, inactive and killed sessions.
Oracle_Database_TableSpaceUsageDataSourceUsage metrics for Oracle TableSpace.
Oracle_Database_TableSpaceUsageMaxGrowthDataSourceDisplays the allocated and free bytes for a tablespace within the data files, showing the maximum size to which a datafile can grow.
Oracle_Database_RMANBackupJobsDataSourceInformation about the current RMAN Jobs.
Oracle_Database_RMANLatestBackupJobByTypeDataSourceInformation about the latest RMAN Jobs.
Oracle_Database_RMANBackupSetAge DataSourceDisplays the age of the latest backup set for each file type. Only available when RMAN is used for backups. Will look at whatever the last Backup Set that was created. No alerts are set since acceptable backup time differs for each database.
Oracle_Database_RMANAllBackupFilesDataSourceInformation on backups with a Copy type.
Oracle_Database_RMANLatestBackupFileSetsDataSourceInformation on latest backup files with a Backup Set type.
Oracle_Database_RMANLatestBackupFilesDataSourceInformation on backups without a Set type.
Oracle_Database_RMANBackupAsyncIOByTypeDataSourcePerformance information about ongoing and recently completed RMAN backups and restores.
Oracle_Database_RMANBackupSyncIOByTypeDataSourcePerformance information about ongoing and recently completed RMAN backups and restores.
Oracle_Database_BlockedSessionsDataSourceCreates an instance for every blocked session on the database.
Oracle_Database_BlockedSessionOverviewDataSourceThe number of total blocked sessions on the database.
Oracle_Database_ResourceLimitsDataSourcedisplays information about global resource use for some of the system resources.
Oracle_Database_LongOperationsDataSourceInformation about the latest operations lasting over six seconds.
Oracle_Database_SystemStatsDataSourceGet statistics from Oracle for the database instance.
Oracle_Database_SystemMetricsDataSourceDisplays the system metric values from the V$Sysmetric table on an Oracle database for the prior 60 seconds.
Oracle_Database_TableSpaceStatusDataSourceDiscovers Oracle database tablespaces and determines whether they are online, read-only or offline.
Oracle_Database_TroubleshooterDataSourceShows problems with Oracle Database configuration problems. Checks table permissions to make sure all tables are available.
Oracle_Database_IndividualSchemaSizeDataSourceThe size of the individual schema for each owner.
Oracle_Database_SessionsWaiting_LockReleaseDataSourceThe number of session blocked by another session with locks on a table, schema or database.
Oracle_Database_LibraryCacheDataSourceInformation about about library cache performance and activity.
Oracle_Database_WinServicesDataSourceMonitors the status of the Windows Services associated with an Oracle database.

When setting static datapoint thresholds on the various metrics tracked by this package, LogicMonitor follows the technology owner’s best practice KPI recommendations. If necessary, we encourage you to adjust these predefined thresholds to meet the unique needs of your environment. For more information on tuning datapoint thresholds, see Tuning Static Thresholds for Datapoints.

Oracle Connection Status DataSource

The Oracle_Database_ConnectionStatus DataSource is used to check connectivity from the collector to an Oracle database. By default, the DataSource will attempt to connect to the Oracle database three times before alerting:

  • First attempt fails: DataSource will wait three seconds and try to connect again.
  • Second attempt fails: DataSource will wait 12 seconds and try to connect again.
  • Third attempt fails: An alert is sent.   

The number of tries, as well as the length of time between tries, can be set with device properties. Care must be taken to prevent the DataSource from hitting a timeout. The timeout by default is 180 seconds, but it can be changed in the collector properties.

When a connection to the database is successful, the DataSource runs the select statement SELECT SYSDATE FROM DUAL; and reports the number of rows returned, as well as the time for execution. This select statement can be overridden with a device property. Properties are in the form of oracle.propertyName or oracle.dbname.propertyName.  When oracle.dbname.propertyName is used, the property will be used for a specific database name.  When there is no dbname specified, the DataSource will use the property for each database instance on the device.

Properties

All of these properties are optional, but may be needed for slower connections.

Property NameDescriptionExampleDefault
oracle.sqltest

or

oracle.dbname.sqltest
SQL statement run each time the connection test is run. The jdbc.oracle.user
 must have access to this table for the statement to succeed.
SELECT USER_ID FROM dba_usersSELECT SYSDATE FROM DUAL
oracle.max_retries

or

oracle.dbname.max_retries
Number of retries before generating an alert. Setting this number too high may result in DataSource timeouts, and the alert will not be sent. The combination of max_retries and wait_time
 needs to be less than 180 seconds.
53
oracle.wait_time

or

oracle.dbname.wait_time
Wait time in seconds for the first try. Subsequent tries will be wait_time * retrycount. Setting this number too high will cause a collector timeout, as well as a possible missed alert.43

Sample Setup

In This Article