oracle AC Configuration

This article describes the configuration concepts and procedures to set up the Oracle Database Monitoring (oracle) probe. To configure the probe, you must create a connection to the Oracle database and a monitoring profile. The default checkpoints are automatically added to a new profile. Profile checkpoints are predefined parameters that can be used to monitor the server performance or unwanted events. You can generate alarms and QoS using these checkpoints. Each monitoring profile runs in its own thread, which keeps the monitoring profile in isolation.
uimpga-ga
oracle_AC
This article describes the configuration concepts and procedures to set up the Oracle Database Monitoring (oracle) probe. To configure the probe, you must create a connection to the Oracle database and a monitoring profile. The default checkpoints are automatically added to a new profile. Profile checkpoints are predefined parameters that can be used to monitor the server performance or unwanted events. You can generate alarms and QoS using these checkpoints. Each monitoring profile runs in its own thread, which keeps the monitoring profile in isolation.
The Oracle client or server is mandatory for oracle probe to work.
This article is for probe versions 5.0 and later.
The following diagram outlines the process to configure the probe.
Configuring oracle AC
Configuring oracle AC
Contents
2
Verify Prerequisites
Review the following prerequisites before you configure the probe.
(Optional) Configure General Properties
You can change the default configuration of your probe if these settings do not meet your requirements. After the probe installation, it is active and immediately attempts to publish data. You can configure the following general properties of the probe:
  • Alarm and QoS parameters
  • Logging properties
Follow these steps:
  1. Navigate to the
    oracle
    node.
    The 
    Probe Information 
    section provides information about the probe name, probe version, start time of the probe, and the probe vendor. 
  2. Go to the
    General Config
    section, and set or modify the following information, as required:
    • Clear Alarm On Restart
      : clears all alarms when the probe is restarted.
      Default: Selected
    • Alarm severity filter
      : specifies a severity level filter on events to be considered as alarms. Events matching the selected severity level and higher, generate alarms. If major is selected, then only the messages with severity level as major and above are considered as alarms.
      Default: information
    • Log Size
      : specifies the maximum size of the probe log file in kilobytes. When this size is reached, the existing contents of the file are cleared.
      Default: 1000 
    • Log Level
      : specifies the level of information that is written in the log file.
      Default: 0 - Normal
      Log as little as possible during normal operation to minimize disk consumption, and increase the amount of detail when debugging.
    • QoS V2 compatibility:
      enables backward compatibility to the V2 framework. 
Create a Connection
You can create a connection to the Oracle database that you want to monitor. Specify the user ID, password, and service name you want to use to connect to the database.
To monitor a CDB and PDB in Oracle 12c databases, you must create separate connections for each database type.
Follow these steps:
  1. Click the 
    Options (icon)
    next to the 
    oracle 
    node in the navigation pane and select
    Create new connection
    .
  2. Specify the following information to configure a new connection:
    • Name
      : specifies a unique name of the connection.
    • Description
      : specifies additional information about the connection.
    • User ID
      : defines the user ID with authorization to read the database views.
    • Password:
      defines the password of the specified
      User ID
      .
    • Service Name
      : specifies the service name that is defined in the 
      tnsnames.ora
       file.
    • Retry attempts
      : specifies the number of attempts that the probe makes to connect when there is a failure. The value 0 means only the initial connection is attempted. 
      Default: 0
    • Retry delay
      : specifies the time the probe waits between two connection attempts.
      Default: 0
    • Retry delay unit
      : defines the unit to measure the
      Retry delay
      value.
      Default: sec
    • Skip Privilege Check
      : allows you to create custom checkpoints for users who do not have 
      DBA
       and the 
      Select Catalog Role
       privileges on the database.
      Default: Not selected
      When you select the
      Skip Privilege Check
      field, the
      Version
      and
      Instance
      fields are enabled.
    • Version: 
      defines the version of the database that is used for the connection. For example, mention 12 for an Oracle 12 database.
    • Instance:
      specifies the instance name where the connection is established.
    • (Optional)
      Is RAC
      : allows you to include and configure the Real Application Cluster (RAC) checkpoints.  
      Default: Not selected
      To monitor a RAC profile or connection, create a profile corresponding to the RAC connection. The nodes are auto-discovered and are displayed in the alphabetical order. All RAC-specific checkpoints appear automatically under the nodes. All the common checkpoints fall under the profile at the same level as nodes.
      For more information about RAC monitoring, see Configure RAC Monitoring.
  3. Click 
    Submit
    .
    The new connection is created under the 
    Connections
     node in the navigation pane.
  4. Navigate to the
    Connection-
    Connection Name
    >
    Database Name
    Connection Name
    node.
  5. Click 
    Actions >
     
    Test Connection
     in the right pane to check the connection.
    If successful, it returns the instance name and the version number. If unsuccessful, the probe displays a failure message.
  • To delete a connection, click the
    Options (icon)
    on the
    Connection-
    Connection Name
    node and select
    Delete Connection
    .
  • When you edit a connection for monitoring a CDB or a PDB Oracle instance; test the connection after editing to ensure a successful connection. This test is required as the PDB_Count checkpoint needs connection information before it generates any user-defined alarms or QoS.
Create a Profile
You can create profiles to monitor Oracle databases using the connections in the probe. To monitor a CDB and a PDB in Oracle 12c database, create separate profiles for each database type. A CDB profile does not monitor the PDBs linked to the CDB.
Follow these steps:
  1. Click the 
    Options (icon)
    next to the 
    connection name
     
    node in the navigation pane.
  2. Click the 
    Create new profile
     option.
  3. Specify the following information:
    • Name
      : specifies a name of the profile.
    • Connection
      : specifies the connection used by this profile. The connections must exist in the probe before creating a profile.
  4. Click 
    Submit
    .
  5. Navigate to your 
    profile name
     node and specify the following information:
    • Active
      : activates the profile on creation.
      Default: Not selected
    • Description
      : specifies additional information about the profile.
    • Heartbeat
      : specifies the interval after which schedules of all profile checkpoints are tested and executed.
      Default: 5 
      : This number is a common denominator to all check interval values. The higher the value, the lower is the profile overhead.
    • Heartbeat Unit
      : specifies the unit for the heartbeat.
      Default: sec
    • Connection
      : specifies the connection that is used in the profile.
    • Check Interval
      : specifies the interval after which the profiles are scanned.
      Default: For a sample profile, the value is 15 and for a new profile, the value is 5.
      Reduce this interval to generate alarms and QoS frequently. A shorter interval can also increase the system load.
    • Check interval unit
      : specifies the unit for the specified
      Check Interval
      .
      Default: sec
    • Clear message
      : specifies the message that is generated for the timeout clear alarm.
      Default: p_timeout_1
    • SQL Timeout
      : specifies the SQL query timeout. Every checkpoint query runs asynchronously. If the query reaches the SQL timeout, the checkpoint processing is terminated. The probe processes the next checkpoint and generates an alarm. 
      Default: 60
      On every check interval, the SQL timeout alert is first cleared on the basis of query for a particular checkpoint. The SQL timeout alert is thrown again if the issue remains.
    • Sql Timeout Unit
      : specifies the unit for the SQL Timeout.
      Default: min
    • Sql Timeout Message
      : specifies the message for the SQL query timeout alarm.
      Default: Sql_timeout_1
    • Profile Timeout: 
      defines the maximum processing time for all checkpoints in the profile. If this timeout is reached, the interval processing is terminated. The probe waits for next heartbeat to evaluate any checkpoint schedules, and generates an alarm.
      Default: 120
    • Profile Timeout unit
      : specifies the unit of profile timeout.
      Default: sec
    • Profile Timeout Message
      : specifies the message for the profile timeout alarm.
      Default: P_timeout_1
    • Timeout Severity
      : specifies the severity for timeout messages.
      Default: Major
    • Alarm Source
      : overrides the source name of the alarm on the Unified Service Management. If you do not specify a value, robot IP address is used.
    • Connection Failure Message
      : specifies the message that is displayed when the probe is unable to connect to the Oracle database. 
      Default: Connect_failure_1
    • Use Alarm Source As QoS Source
      : specifies the probe to use the
      Alarm Source
       value as the QoS Source.
      Default: Not selected
      CA does not recommend you to change the source fields after the initial configuration. If you change the QoS source later, multiple graphs are displayed on the Unified Service Management (USM) Metrics view (one for every QoS source value). Also, CA recommends you to keep the source identical for both alarm and QoS.
  6. Click
    Save
    to configure the profile.
The probe default checkpoints are automatically added to the profile, however they are inactive, by default. For more information about activating the required checkpoint, see the
Add Profile Checkpoints
section.
(From version 5.20)
You can change the default behavior to automatically enable the probe default checkpoints in your profile. Navigate to the
Raw Configure > setup
section, and set the
apply_template
key to
true
(Default: false). Save and reload the probe configuration.
: To delete a profile, click the
Options (icon)
on the
Profile Name
node and select
Delete profile
.
Add Profile Checkpoints
Profile checkpoints are predefined parameters that can be used to monitor the server performance or unwanted events. The probe default checkpoints use the global checkpoint settings, unless you modify the settings locally for your profile.
Follow these steps:
  1. Click the 
    Options (icon)
     next to your
    profile name
     node in the navigation pane and select 
    Add profile checkpoints
    .
  2. Select the checkpoints in the
    Available
    list and move them to the
    Selected
    list. 
  3. Click 
    Submit
    .
    The checkpoint is added to your profile as a static checkpoint. Any changes made to this checkpoint applies only to your profile.
CA does not recommend you to delete the default checkpoints and the thresholds from the probe to avoid any missing alarms.
Configure Checkpoints
You can configure the properties of a checkpoint in your profile to monitor the server performance or unwanted events. The checkpoint monitors the corresponding real-time object in the server. When the configured profile runs on the server, the set criteria for the checkpoints are scanned against the real-time objects. If any unwanted event occurs, the probe generates alarms or QoS messages. 
CA does not recommend you to delete the default threshold of the following checkpoints, as the probe cannot generate alarms or QoS:
  • locked_users
  • long_queries
  • no_next_extents
  • tablespace_deficit
  • user_locks
For example, if a checkpoint has a count_interval threshold, the threshold and alarm will not work unless the default threshold is breached and an alarm is generated.
Follow these steps:
  1. Click the 
    checkpoint name
     node in your profile.
    You can also configure a checkpoint in the 
    checkpoint name
     node under the oracle node. All changes that are made to a checkpoint here do not apply to the checkpoints in your profiles. If you want to apply these changes to your checkpoints in your profile:
    • Delete the checkpoint from your profile, change the global checkpoint setting and add them again.
    • Change the checkpoint settings before you add them in your profile.
  2. Specify the following field information:
    • Checkpoint Name:
      defines the name of the checkpoint.
    • Active:
      activates the checkpoint for monitoring.
    • Description:
      specifies additional information about the checkpoint.
    • (From version 5.30)
      Enable Alarms
      : specifies the probe to generate alarms when the checkpoint breaches the defined threshold. 
    • Condition:
      specifies the conditional operator to evaluate threshold values.
    • Check Interval:
       specifies the interval after which the database is scanned. This value overrides the interval defined in the profile.
      Default: 1 
    • Check interval unit:
      specifies the unit for the specified
      Check Interval
      .
    • Samples:
       specifies the number of samples to calculate an average value. This average value is compared to the specified alarm threshold.
      Default: 1
      When the probe starts, the average value from the number of available samples is calculated. For example, if you specify the 
      Samples
       value as 3, the probe performs the following actions:
      • uses the first sample value in the first interval
      • uses the average of samples 1 and 2 in the second interval.
    • Clear severity:
       specifies the severity level of the clear message.
      Default: clear
    • Clear message:
      specifies the clear message. 
    • Use Exclude:
       allows you to define objects to be excluded from monitoring. The probe uses regular expressions to search patterns that are excluded from the checkpoint. 
      For more information about excluding objects, see Add Exclude Patterns.
    • (
      From version 5.10
      for the
      tablespace_free
      checkpoint)
      Enable Threshold On Space
      : enables you to define another threshold value for the
      tablespace_free
      checkpoint. If you select this option, the probe evaluates the values specified in both the
      Threshold value
      and
      Threshold on Space (In GB)
      fields before generating an alarm for threshold breach.
  3. Click
    Save
    .
    The checkpoint is configured.
: To delete a checkpoint, click the
Options (icon)
on the
Profile Name
node and select
Delete checkpoint
. A deleted checkpoint becomes available for inclusion again in the
Add profile checkpoints
window.
(Optional) Create Custom Checkpoints
(Version 5.20 and above)
You can define a custom checkpoint to monitor the Oracle database instance. You can delete both default and custom checkpoints at individual profile level.
The custom checkpoints are not available for RAC connection.
Follow these steps:
  1. Navigate to
    oracle > Checkpoints
    , and click the 
    Options (icon)
     next to the 
    Checkpoints 
    node.
    To add custom checkpoints to RAC connections, click the
    Options (icon)
    next to the
    RAC Common Checkpoints
    or
    RAC Specific Checkpoints
    node.
  2. Click 
    Add New Checkpoint 
    option.
  3. Specify the following information:
    • Name: 
      specifies the name of checkpoint.
    • Connection Name: 
      specifies the name of the connection for which this checkpoint is created.
    • Query File: 
      specifies the file name to store the query.
    • Query: 
      specifies the query to retrieve the data for the checkpoint.
    • Interval Modus: 
      subtracts the variable value at the beginning of an interval from the value found at the end to obtain the Interval Modus value. If you do not select the 
      Interval Modus
       check box, the value of variable as returned from the query will be used to check and generate QoS.
  4. Click 
    Submit
    .
    The checkpoint appears under the 
    Checkpoints
     node.
  5. Configure the checkpoint.
  6. Under the 
    Row Identifier 
    section,
     
    pick the rows as variables to set an alarm message and suppression key. If the query returns more than one row, the probe needs a unique identification for each row.
  7. Under the 
    Message Variable 
    section, select the columns as variables used to create alarm messages from a list of all available columns and their possible usage:
    • Data Type
      : specifies the data type of the variable of the new alarm message.
    • Column Use
      : specifies the column use of the variable.
  8. To add this checkpoint to a profile, 
    1. Click the 
      Options
       icon next to <
      connection name>
       node and click 
      Add profile checkpoints
      .
    2. Select the required checkpoint to add from the 
      Available
       list and click 
      Submit
      .
Ensure that you define custom checkpoints with unique targets for the
object
field in the
QoS
section so that separate nodes are created on USM. Otherwise, the data on USM will be reflected under one node. Hence, there could be significant loss of data.
Stored Procedure Support in creating custom checkpoint (Probe versions 5.4 and above):
The probe supports the use of stored procedures in creating custom checkpoints. Checkpoint query can include any stored procedure with one output parameter argument which is of type SYS_REFCURSOR.
Example
 : If the stored procedure name is get_data_resultset with one out parameter of type SYS_REFCURSOR, then the custom check point query using the stored procedure should be “BEGIN  get_data_resultset(:1); END;”
(Optional) Add Exclude Patterns
You can define exclude patterns to define objects that you do not want to monitor for the checkpoint. The probe uses regular expressions to search patterns that are excluded from the template checkpoint. The exclude pattern are available only for specific checkpoints.
Follow these steps:
  1. Navigate to your
     
    <
    checkpoint name
    > node.
  2. Select
    Use Exclude 
    under the 
    Use Exclude Configuration
    .
  3. Click
     New 
    in the 
    Exclude List
     section.
  4. Specify the regular expression in the 
    Exclude Pattern
     field to omit the pattern from monitoring. For example, you want to exclude the pattern, no_connection in a given checkpoint. Specify “/no_connection/” in the 
    Exclude Pattern
     field
  5. Click 
    Actions > Test.
    The 
    Response
     dialog displays the list of objects that are excluded. The excluded objects have their Exclude value set to 1.
    This test is possible only for running active profiles and checkpoints that you do not want to monitor on the checkpoint
  6. Click 
    Save
    .
    The exclude pattern is added. 
Configure Monitors
You can configure the monitors of a checkpoint to generate alarms and QoS when specified threshold is breached. 
The threshold list contains the predefined set of monitoring parameters that you can use in your profiles. The probe allows you to modify the thresholds as required. The threshold values can be defined by modifying checkpoints in the respective profile. Every checkpoint must have at least one threshold, but you can define additional thresholds. The probe scans the entire server to verify any matching events. When any given event breaches the set threshold, the probe generates an alarm message.  
Follow these steps:
  1. Navigate to a 
    checkpoint name
     > 
    Monitors
     node. For example, you want to configure the monitor for the database size. 
    The 
    Quality of Service 
    section displays the default QoS. For example, database_size. Similarly, the 
    Threshold 
    section displays the default threshold for this metric.
  2. To generate an existing QoS, select a QoS and select 
    Publish Data
    .
  3. (Optional) To create a QoS, click 
    New 
    and specify the following information:
    • Name: 
      specifies the name of the QoS.
    • Description: 
      specifies additional information about the QoS.
    • Unit: 
      specifies the unit to measure the QoS.
    • Abbreviation: 
      specifies any short name for the QoS.
    • Metric: 
      defines a measuring value of the checkpoint.
      : The values are displayed in the
      Metric
      field only if there is a message variable defined with data type as numeric. 
    • Max value
      : specifies the maximum value of the QoS.
    • Object:
       defines the object for the QoS.
      The
      Object
      field appears only if there is a message variable defined with data type as character and column use as row key. 
  4. To generate an existing alarm, select a threshold and select 
    Publish Alarm
    .
    (From version 5.30) The alarms on threshold values are generated based on the Severity instead of the threshold ID.
    Example: you want to monitor available space in a tablespace using the 
    tablespace_free
     checkpoint. As this checkpoint uses the <= operator, you can define the following threshold conditions when the available space is:
    1. <=10% with the 
      critical
       severity
    2. <=20% with the 
      warning
       severity
    • CA does not recommend you to delete the default checkpoints and the thresholds from the probe to avoid any missing alarms.
  5. (Optional) To create a threshold, click 
    New 
    and specify the following information:
    • Publish Alarms: 
      allows you to generate an alarm on threshold breach.
    • Threshold object name
      : displays the name of the threshold object. The probe uses this field value as default for a checkpoint.
    • Threshold value
      : specifies the value that is used for threshold comparison.
    • (
      From version 5.10
       for 
      tablespace_free
       checkpoint) 
      Threshold on Space (In GB)
      : This field is enabled only if you select 
      Enable Threshold on Space
       field on the tablespace_free node. You can define one more threshold that the probe checks before generating an alarm. 
      Example: you want an alert when the remaining tablespace free size is 20% and 20 GB of the total space. Follow these steps:
      1. Select the 
        Enable Threshold on Space
         field on the tablespace_free node.
      2. Specify the following values:
        • Threshold value:
           20%
        • Threshold on Space (in GB):
           20
      When the tablespace free size reaches both these values, the probe generates an alarm for threshold breach.
    • Severity
      : specifies the severity level of the alarm message that is generated for the threshold.
    • Message
      : specifies the message name used.
    • Message Text
      : specifies the message text containing variables that are replaced at run time.
      The content of the message is displayed only when the message is saved. 
uimpga-ga
Alarm Thresholds
The alarm threshold options that are available can vary depending on the probe versions installed at the hub level. The alarm threshold settings to allow the probe to:
  • Send alarms when threshold criteria is met
  • Indicate to baseline_engine to compute baselines
See Configuring Alarm Thresholds for details.
(Optional) Configure RAC Monitoring
You can configure the probe to monitor an Oracle RAC, which lets multiple computers run the Oracle software simultaneously when accessing a single database, thus providing clustering. 
From NMS 7.5 and PPM version 2.31 onwards, RAC is supported and configured only through Admin Console. 
Follow these steps:
  1. Create a RAC connection using the Create a Connection procedure.
  2. Select the
    Is RAC
    field to enable the probe to perform RAC monitoring.
    A RAC connection cannot be converted to a non-RAC connection.
  3. Create a RAC profile using the Create a Profile procedure to execute the checkpoints for a specific RAC connection.
    On profile creation, all the active nodes are automatically added under the profile. One of the nodes is displayed as primary. This node denotes that the data for a RAC common checkpoint is routed to this node when viewed from UMP. Even when the node is down, the data is still routed to this node (to avoid any data loss).
    When you bind a profile to the RAC connection, all the RAC common and node-specific checkpoints are automatically loaded in the RAC profile. The common checkpoints are added directly in the profile and the node-specific checkpoints under the nodes.
  4. Configure the RAC Common and RAC-<Specific> checkpoints, and monitors, as required. 
    CA does not recommend you to delete the default QoS when you configure the monitors on an AIX platform.
(Optional) View Probe Data
The Unified Management Portal (UMP) comes with a predefined Unified Dashboard you can use to view oracle probe data. The Oracle Unified Dashboard provides predefined list views to monitor Available Tablespace, Database Utilization, Free SGA Memory, Resource Utilization Count, and Server Information. For detailed descriptions and required data sources, see the Oracle Unified Dashboard article.
The ORACLE RAC Unified Dashboard provides predefined list views to monitor RAC Database Health, RAC Instance Node Performance, Access Latency, and Instance Node Health. For detailed descriptions and required data sources, see the Oracle RAC Unified Dashboard article.
 Verify that you are viewing the correct information for your CA UIM version.
(Optional) View Message Configurations
You can view the properties of alarm messages that are configured in the probe.
Follow these steps:
  1. Navigate to the 
    Message pool
    section of the
    oracle
     node.
  2. Select an alarm message from the table to view the following properties:
    • Name
      : indicates the unique name of the message.
    • Text
      : indicates the message text.
    • i18n_Token
      : indicates the predefined alarms retrieved from the database. 
(Optional) Use Regular Expressions
A regular expression (regex for short) is a special text string to describe a search pattern. Constructing regular expression and pattern matching requires meta characters. The probe supports Perl Compatible Regular Expressions (PCRE), which are enclosed within forward slash (/). For example, the expression /[0-9A-C]/ matches any character in the range 0 to 9 in the target string. 
You can also use simple text with some wildcard operators for matching the target string. For example, *test* expression matches the text test in target string. 
The probe uses regular expressions to search patterns to exclude from the checkpoint. For example, you are searching for the pattern, no_connection.  Enter “/no_connection/” in the 
Exclude Pattern
field and click 
OK
. The probe searches for all the matching instances of the specified pattern. 
The following table describes some examples of regular expressions and pattern matching for the probe. 
Regular expression
Type of regular expression
Explanation
[A-Z]
Standard (PCRE)
Matches any uppercase alpha character
.
Standard (PCRE)
Matches against any character
\d*
Custom
Matches for the name which starts from letter d