Monitoring

Oracle Monitoring

Introduction to 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 to name just a few.

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

Oracle Compatibility

LogicMonitor's Oracle package is verified to be compatible with:

  • Oracle version 12c through 19c

The package may work with earlier Oracle versions with the caveat that some datapoints may not be available. Additionally, the user creation instructions provided in the following section may be different for Oracle versions older than 12c.

As Oracle releases newer versions of its database server, LogicMonitor will test and extend coverage as necessary. ​

Setup Requirements

Import LogicModules

From the LogicMonitor repository, import 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.

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

Note: The instructions provided in this section assume the reader has some knowledge of PL/SQL and has the administrative privileges (e.g. SYSDBA) necessary to set up a new 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.

User Setup for Container Database

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> and <username> placeholders with the credentials you would like to assign to the user account.

alter session set "_ORACLE_SCRIPT"=true; 
CREATE USER <username> IDENTIFIED BY <password> ;
GRANT CREATE SESSION TO <username> CONTAINER=ALL;
GRANT SELECT ON V_$SYSSTAT TO <username> CONTAINER=ALL;
GRANT SELECT ON v_$session TO <username> CONTAINER=ALL;
GRANT SELECT ON v_$process TO <username> CONTAINER=ALL;
GRANT SELECT ON v_$sql 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 DBA_TABLESPACE_USAGE_METRICS to <username> CONTAINER=ALL;
GRANT SELECT ON DBA_TABLESPACES TO <username> CONTAINER = ALL;
GRANT SELECT ON v_$SESSION_LONGOPS TO <username> CONTAINER=ALL;
GRANT SELECT ON DBA_SEGMENTS TO <username> CONTAINER=ALL;
GRANT SELECT ON V_$RESOURCE_LIMIT TO <username> CONTAINER=ALL;
GRANT SELECT ON V_$BACKUP_FILES TO <username> CONTAINER=ALL;
GRANT SELECT ON V_$RMAN_OUTPUT TO <username> CONTAINER=ALL;
GRANT SELECT ON V_$RMAN_BACKUP_JOB_DETAILS TO <username> CONTAINER=ALL;
GRANT SELECT ON V_$BACKUP_ASYNC_IO TO <username> CONTAINER=ALL;
GRANT SELECT ON v_$SYSMETRIC to <username> CONTAINER=ALL;

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

User Setup for Non-Container Database

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> and <username> placeholders with the credentials you would like to assign to the user account.

alter session set "_ORACLE_SCRIPT"=true;
CREATE USER <username> IDENTIFIED BY <password>;
GRANT CREATE SESSION TO <username> ;
GRANT SELECT ON V_$SYSSTAT TO <username> ;
GRANT SELECT ON v_$session TO <username> ;
GRANT SELECT ON v_$process TO <username> ;
GRANT SELECT ON v_$sql TO <username> ;
GRANT SELECT ON v_$LIBRARYCACHE TO <username> ;
GRANT SELECT ON v_$LOCK TO <username> ;
GRANT SELECT ON DBA_TABLESPACE_USAGE_METRICS to <username> ;
GRANT SELECT ON DBA_TABLESPACES TO <username> ;
GRANT SELECT ON v_$SESSION_LONGOPS TO <username> ;
GRANT SELECT ON DBA_SEGMENTS TO <username>  ;
GRANT SELECT ON V_$RESOURCE_LIMIT TO <username>  ;
GRANT SELECT ON V_$BACKUP_FILES TO <username> ;
GRANT SELECT ON V_$RMAN_OUTPUT TO <username> ;
GRANT SELECT ON V_$RMAN_BACKUP_JOB_DETAILS TO <username> ;
GRANT SELECT ON V_$BACKUP_ASYNC_IO TO <username> ;
GRANT SELECT ON v_$SYSMETRIC to <username>;

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 Device Properties.

Property

Description

Required or Optional

jdbc.oracle.user Username assigned to the Oracle user account. It is easiest if the same username is set up everywhere. Required
jdbc.oracle.pass Password assigned to the Oracle user account. Required
jdbc.oracle.port Port 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.user For Linux systems, enter SSH username to enable database auto-discovery; Windows systems should automatically auto-discover databases. Optional
ssh.pass For Linux systems, enter SSH password to enable database auto-discovery; Windows systems should automatically auto-discover databases. Optional
ssh.cert For Linux systems, enter certificate (if required) for SSH to enable database auto-discovery; Windows systems should automatically auto-discover databases. Optional
oracle.dbnames Enter comma separated list of database names. Required ONLY if database auto-discovery is not enabled via SSH properties for Linux systems.
system.categories When 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.

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. If set on a resource, these properties override the jdbc.oracle.user/pass/port properties highlighted in the previous table.

Property

Description

Required or Optional

oracle.<DatabaseName>.user The 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>.password The 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>.port The 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

Migration from Legacy Oracle DataSources

In December of 2019, LogicMonitor released a new suite of Oracle DataSources. The new DataSources are automatically multi database, with each database represented as an instance. Because the new DataSources are multi-database aware and, therefore, create new instances, they are not able to remain backwards compatible with the existing suite.

If you are currently monitoring Oracle using the legacy Oracle DataSources, you will not experience any data loss upon importing the newer DataSources in this package. This is because DataSource names have been changed to eliminate module overwriting.

However, you will collect duplicate data and receive duplicate alerts for as long as both sets of DataSources are active. For this reason, we recommend that you disable the legacy Oracle DataSources. Legacy Oracle DataSources are those with names prepended by "Oracle_DB_" as compared to the new naming convention of "Oracle_Database_".

When a DataSource is disabled, it stops querying the host and generating alerts, but maintains all historical data. At some point in time, you may want to delete the legacy DataSources altogether, but consider this move carefully as all historical data will be lost upon deletion. For more information on disabling DataSources, see Disabling Monitoring for a DataSource or Instance.

Troubleshooting

The "Oracle DB Troubleshooter" DataSource exists to capture configuration and permissions issues. 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.

Name

Display Name

Type

Description

addCategory_OracleDB n/a PropertySource Adds "OracleDB" to the resource's system.categories property
Oracle_Database_Info n/a PropertySource Set the version number of the Oracle Server on the resource.
Oracle_Database_Names n/a PropertySource Adds the database names for Oracle databases installed on a single server. Works on windows with WMI setup and linux servers with ssh.user and ssh.pass setup.
Oracle_Database_TableSpaceUsage Oracle TableSpace Usage DataSource Usage metrics for Oracle TableSpace.
Oracle_Database_TableSpaceUsageMaxGrowth Oracle TableSpace Usage (Maximum TableSpace Growth) DataSource Displays the allocated and free bytes for a tablespace within the data files, showing the maximum size to which a datafile can grow.
Oracle_Database_RMANBackupJobs Oracle Backup and Recovery Jobs DataSource Information about the current RMAN Jobs
Oracle_Database_RMANLatestBackupJobByType Oracle Backup and Recovery Latest Backup Job by Type DataSource Information about the latest RMAN Jobs
Oracle_Database_RMANAllBackupFilesSets Oracle Backup and Recovery All Files (Sets) DataSource Information on backups with a "Backup Set" type.
Oracle_Database_RMANAllBackupFiles Oracle Backup and Recovery All Files (No Sets) DataSource Information on backups with a "Copy" type.
Oracle_Database_RMANLatestBackupFileSets Oracle Backup and Recovery Latest Backup Files by Type (Sets) DataSource Information on latest backup files with a "Backup Set" type.
Oracle_Database_RMANLatestBackupFiles Oracle Backup and Recovery Latest Backup Files by Type (No Sets) DataSource Information on backups without a "Set" type.
Oracle_Database_RMANBackupAsyncIOByType Oracle Backup and Recovery Async I/O by Type DataSource Performance information about ongoing and recently completed RMAN backups and restores.
Oracle_Database_RMANBackupSyncIOByType Oracle Backup and Recovery Sync I/O by Type DataSource Performance information about ongoing and recently completed RMAN backups and restores
Oracle_Database_BlockedSessions Oracle Blocked Sessions DataSource Creates an instance for every blocked session on the database.
Oracle_Database_BlockedSessionOverview Oracle Blocked Sessions Overview DataSource The number of total blocked sessions on the database
Oracle_Database_ResourceLimits Oracle DB Resource Limits DataSource displays information about global resource use for some of the system resources
Oracle_Database_LongOperations Oracle Latest Long Operations DataSource Information about the latest operations lasting over 6 seconds.
Oracle_Database_SystemStats Oracle System Statistics DataSource Get statistics from Oracle for the database instance.
Oracle_Database_Troubleshooter Oracle Database Troubleshooter DataSource Shows problems with Oracle Database configuration problems. Checks table permissions to make sure all tables are available.
Oracle_Database_IndividualSchemaSize Oracle Individual Schema Size DataSource The size of the individual schema for each owner.
Oracle_Database_SessionsWaiting_LockRelease Oracle Sessions Waiting Lock Release DataSource The number of session blocked by another session with locks on a table, schema or database.
Oracle_Database_LibraryCache Oracle Library Cache DataSource Information about about library cache performance and activity.

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.