Oracle Standalone Database

You can use the information in this topic to configure an Oracle Enterprise Database monitoring policy with the TrueSight console.

This topic includes the following information:

Before you begin

You must download and install PATROL for Oracle Enterprise Database. For more information see Installing and Performance and scalability data.

Related topics

Monitor types and attributes

To configure the monitor type

In the Add Monitoring Configuration panel, select the following parameters for the Oracle Enterprise Database KM:

Parameter	Selection
Monitoring Solution	Oracle Enterprise Database
Monitoring Profile	Oracle Environment Monitoring
Monitor Type	Oracle Standalone Database

For the Global Monitoring Setting, you can enable Logging for all of the configured Patrol Agent environments to confirm that all of the PATROL for Oracle Enterprise Database environments are correctly configured.

To add an environment configuration

Click Add and enter the details in the Environment Configurations pane.

Field

Description

Environment name

Enter a unique name for the environment. We recommend you to use alphanumeric characters in the environment name.

Note

The name cannot include blank spaces or any of the following special characters: # $ \ ' | ~ ! @ % ^ ; ` ( ) { } ? \ " [ ] + = & : > < * /
Do not use non-English characters to specify an environment name.
Once you save the environment configuration, you cannot change the name without losing the environment's historical data. You can change the environment name by making a copy of the monitoring solution and then you can change the environment name.

The remaining environment details are in the following sections:

Standalone instance connection details

Field	Description
Oracle host	Enter the host name or the IP address of the standalone Oracle database. You can use the `$HOSTNAME` variable for the Oracle host. The host name is taken from the PATROL agent that is registered in the TrueSight console's Devices page . The IP address can be in the IPv4 or IPv6 format. An example of an IPv6 address is `[2001:db8:28:3:f98a:5b31:67b7:67ef]`.
Oracle port	Enter the port number to connect to the standalone Oracle database. The default port number is 1521.
Oracle connection method	Specify the Oracle connection method: Service SID
Oracle connection name	Enter the name of the Oracle database service or SID to monitor. The service or SID name appears in the `tnsnames.ora` file. Use the command `lsnrctl services <listener_name>` to find the service name.
Connect as SYSDBA	Select this option to connect to the Oracle instance as SYSDBA privileged user. Note: We do not recommend to monitor using a user with SYSDBA privileges.
User name	Enter the user name to connect to the Oracle database. Note: Confirm that the user has permission for object access, see Monitoring categories and user permissions.
Password Confirm password	Enter the password to connect to the Oracle database. Confirm the password. If you are using vault to access the password in BMC Helix Operations Management, enter the query string in the Password and Confirm password field.
Oracle Custom SQL Queries
Query name	Enter the name for the SQL query. Note The name cannot include blank spaces or any of the following special characters: # $ \ ' \| ~ ! @ % ^ ; ` ( ) { } ? \ " [ ] + = & : > < * / After you save the environment configuration, you cannot change the name without losing the environment's historical data. You can change the environment name by making a copy of the monitoring solution and then changing the environment name.
SQL query	Enter the SQL query command. Note The SQL query must not contain the semicolon (;).
Collection time (min)	Enter the SQL query collection time. The default collection time is 10 minutes.
Enable number of records annotation	Select this option to display the query result as an annotation on number of records metric.

Monitoring Categories details

Select the Oracle Database activity categories that you want to monitor.

Monitoring Categories	Description
ASM	Monitors the performance of the Oracle Database ASM disks. For more information, see the ASM Disk (KOE_INST_ASM_DISK) monitoring category.
Fast Area Recovery	Monitors the performance of the Oracle Fast Recovery Area disks. For more information, see the Oracle Fast Recovery Area (KOE_INST_FRA) monitoring category.
Listeners	Monitors the listener port of the Oracle Database. For more information, see the Listeners (KOE_INST_LISTENER) monitoring category.
Operating System usage	Monitors the operating system utilization statistics and system resources. For more information, see the Oracle OS Usage (KOE_INST_OS) monitoring category.
Pluggable Database	Monitors the pluggable databases. If the Oracle Database is not a pluggable type, this monitoring category is not created for this monitoring policy. For more information, see the Pluggable Database (KOE_INST_PLUGGABLE_INSTANCE) monitoring category.
Services	Monitors the Oracle services executed inside the database. For more information, see the Services (KOE_INST_SERVICE) monitoring category.
Sessions	Monitors the sessions activity of the Oracle Database. For more information, see the Sessions (KOE_INST_SESSIONS) monitoring category.
System	Monitors the system performance of the Oracle Database. For more information, see the System (KOE_INST_SYSTEM) monitoring category.
UNDO Usage	Monitors the UNDO space usage in the system. If the Oracle Database is set for automatic UNDO_MANAGEMENT, this monitoring category is not created for this monitoring policy. For more information, see the UNDO Usage (KOE_INST_UNDO) monitoring category.
Users	Monitors the Oracle Database users activity. For more information, see the Users (KOE_INST_USERS) Users (KOE_INST_USERS) monitoring category.
Wait Events	Monitors the wait events in the Oracle Database. For more information, see the Event Class (KOE_INST_EVENT_CLASS) monitoring category.

Jobs

Field	Description
Jobs	Select this option to monitor the jobs and transactions running in the Oracle Database.
Long running job time (min)	The duration for jobs that are running longer than the defined duration (default 30 minutes). Any job that exceeds this duration sets an alert.
Job overdue time (min)	The duration for jobs that are overdue because they are not executed (2 minutes late). These jobs may be delayed, broken, or failed. For more information, see Jobs (KOE_INST_JOBS).

Tablespaces

Field	Description
Tablespaces	Select this option to monitor the tablespaces. To monitor large and very large tablespaces under separate application classes (Oracle Large Tablespace (KOE_INST_LARGE_TABLESPACE) and Oracle Very Large Tablespace (KOE_INST_VERY_LARGE_TABLESPACE)), configure the minimum size (in MB) of the large and very large tablespaces in the following configuration variables (both are required). Set configuration variables on the Configuration Variables > Add Configuration Variable page. `/KOE/Oracle/<EnvType>/<EnvName>/LargeTablespaceSize` `/KOE/Oracle/<EnvType>/<EnvName>/VeryLargeTablespaceSize` Replace `EnvType` with `DataGuard`, `Standalone`, or `RAC` and `<EnvName>` with the name that you used in the Environmant name field. Restart PATROL Agent after configuring these configuration variables. If you do not configure values in one or any of these variables or if the value in `VeryLargeTablespaceSize` is smaller than the value in `LargeTablespaceSize`, all tablespaces are discovered under the Oracle Tablespace (KOE_INST_TABLESPACE) class. For example, you configure the value of the `LargeTablespaceSize` variable as 200 and the value of the `VeryLargeTablespaceSize` variable as 500. The tablespaces between the sizes 200 and 500 MB are monitored under the Oracle Large Tablespace (KOE_INST_LARGE_TABLESPACE) application class and the tablespaces with sizes more than 500 MB are monitored under the Oracle Very Large Tablespace (KOE_INST_VERY_LARGE_TABLESPACE) application class. Note: These configuration variables are not applicable to UNDO and TEMP tablespaces.
Tablespaces filtering options
Filtering mode	Select the filtering mode: None Include Exclude
Include/Exclude matching criteria	Based on the selection of the filtering mode, specify the tablespaces to include or exclude from monitoring. Use comma (,) only to separate the tablespaces. For example, consider the tablespace name is DB12, you can use any of the following methods to include or exclude tablespaces from monitoring: Starts with: ^DB.* Contains: .B1. Ends with: .*12$ Equals to: DB12 Note Be cautious when you add spaces while entering the regex search string. Extra space is considered as a character by the search string.

Filter for top n queries by total wait time

For more information, see Monitoring of wait time attributes of SQL query.

Field	Description
Total wait time monitoring	Select this option to enable the filter for top n queries by total wait time.
Top Number of queries	Enter the number of queries that you want to filter. This is a mandatory field. The valid input range is 1-100.
Maximun query instances limit	Enter the maximum number of query instances to filter. This is a mandatory field. The valid input range is 1-2500.
Number of days to keep query instances	Enter the number of days to keep the query instance. This is a mandatory field. The valid input range is 1-60.

Environment Settings details

Field	Description
Logging	Select this option to enable detail-level logging for all PostgreSQL databases configured on this environment to get more details about policy configuration and data collection. The log files are created under PATROL_HOME directory: Java: `PATROL_HOME/Patrol3/koekm/logs/java` PSL: `PATROL_HOME/Patrol3/log/koe-*.kmlog` For more information about logging, see Using debug logging in the TrueSight Infrastructure Management operator console.
Device mapping	By default, device mapping on the TrueSight console is enabled. If enabled, all the Oracle Database instances are discovered under the Oracle host device. If it is disabled, all the Oracle Database instances are discovered under the PATROL Agent host device.
Java collector settings
Java path	Enter the path to the JRE directory (also known as $JAVA_HOME environment variable) on the PATROL Agent host. If you use the $JAVA_HOME variable, the Java location depends on the following: No user name and password are entered for the Java collector settings; the KM uses the Java location of the user running the PATROL Agent. A user name and password are entered for the Java collector settings; the KM uses Java location of the user entered in the collector settings. If you enter a path for the JRE directory, you must enter the full path, such as `C:\Program files\Java\jre1.8.0_151`. Do not use the short path format. If you do not enter a value in this field, the KM looks for the Java installed in the PATROL Agent home directory as: Linux: `$PATROL_HOME/../openjdk`; `$PATROL_HOME/../jre64` Windows: `%PATROL_HOME%/../openjdk`; `%PATROL_HOME%/../jre64`
JVM arguments	Enter the optional Java Virtual Machine (JVM) arguments for the Java collector.
User Name	Enter the local user name to start the Java process.
Password Confirm Password	Enter the password to start the Java process. Confirm the password.
Collection settings
Availability collection interval (min)	Enter the time interval used to check for the availability of environment for collecting data. The default interval is one minute.
Data collection interval (min)	Enter the time interval used for data collection from the environment. The default interval is five minutes. Note: Entering a data collection time that is less than 5 minutes will result in a performance issue for the KM.
Long running data collection queries (min)	Enter the time interval used to determine when a running query is blocked. The query is blocked because its execution duration is equal to, or greater than, this interval's threshold. The default interval is 30 minutes.
Wait time sql data collection interval (min)	Enter the total time interval in minutes to collect or report the value for the wait time SQL attributes. This attribute is valid for SQL instances attributes only. The default wait time for SQL data collection time is 5 minutes. The valid input range is 5-60. For more information, see Monitoring of wait time attributes of SQL query.

Where to go from here

Verifying the configuration