Oracle Monitoring

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.

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:

• Multitenant : Manage Users and Privileges For Container Databases (CDB) and Pluggable Databases (PDB) in Oracle Database 12c Release 1 (12.1)
• Overview of the Multitenant Architecture

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.

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.

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.
2. Update the PropertySource addCategory_OracleDatabase and Oracle_Database_Names. 
3. Verify that addCategory_OracleDatabase has run and has added the OracleDB category to the device.
4. Verify that the Oracle_Database_Names PropertySource has run and 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.

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.

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.

Sample Setup