Skip Headers
Oracle® Enterprise Manager Extensibility Guide
10g Release 5 (10.2.0.5)

Part Number B40007-03
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

8 Defining Policies

Though Enterprise Manager provides a number of out-of-box policies, you may need to define additional policies to meet your monitoring needs. Enterprise Manager provides this capability in the form of the MGMT_USER_DEFINED_POLICY package.

Using the MGMT_USER_DEFINED_POLICY package, you can create and delete policies, as well as add policies to, and remove policies from, targets.

See Also:

This chapter includes the following:

Note:

This chapter assumes that you are familiar with PL/SQL packages and Enterprise Manager policies.

Overview

A policy defines the desired behavior of systems and is associated with one or more targets or groups. A policy rule is a conditional expression that test values from a target against a condition, for example, verifying that database profile limits are set as expected.

A policy tests data retrieved from a query performed against the Oracle Management Repository. A policy is said to be compliant if it is determined that the managed targets do, in fact, meet the desired state; that is, the test of the policy failed to identify any violations. Otherwise, a policy is said to be non-compliant when it has one or more policy violations.

As an extension of the out-of-box policies, Enterprise Manager provides the MGMT_USER_DEFINED_POLICY package which supports user-defined policies in the context of subprograms.

To use these subprograms, use SQL*Plus to connect to the Management Repository database as the Management Repository owner. The default Management Repository owner is SYSMAN.

MGMT_USER_DEFINED_POLICY Package

The following subprograms make up the MGMT_USER_DEFINED_POLICY package:

Subprogram Description
CREATE_POLICY Creates a user-defined policy.
DELETE_POLICY Deletes a user-defined policy.
ADD_POLICY_TO_TARGET Adds a policy to an existing target.
REMOVE_POLICY_FROM_TARGET Removes a policy from an existing target.

Descriptions of the constants and data types used in the subprograms follow.

Constants

The following constants are defined by the MGMT_USER_DEFINED_POLICY package and can be used when calling the supplied subprograms. See Table 8-1.

Table 8-1 Constants Used in the MGMT_USER_DEFINED_POLICY Package

Constant Description

Categories

Different types of policies

G_CATEGORY_FAULT

Breakdown in a component or occurrence of an error that indicates some component or user is unable to successfully complete processing, for example, database down

G_CATEGORY_WORKLOAD_VOL

Workload on a system induced in proportion to the users or batch jobs running against the system, for example, number of user calls

G_CATEGORY_WORKLOAD_TYPE

Type of workload on a system independent of demand, for example, CPU usage

G_CATEGORY_PERFORMANCE

Performance of a system, for example, response time

G_CATEGORY_CAPACITY

Usage of a fixed resource, for example, tablespace usage

G_CATEGORY_CONFIGURATION

Configuration of a target against recommended "best practice" configurations, for example, insufficient number of control files

G_CATEGORY_SECURITY

Security settings and issues, for example, open ports

G_CATEGORY_STORAGE

Storage, for example, permanent and temporary tablespaces

G_CATEGORY_UNCLASSIFIED

Default category to be used if a policy does not fall into a particular category

Severity Levels for Violation

Seriousness of violation

G_SEVERITY_INFORMATIONAL

Provides facts about the violation.

G_SEVERITY_WARNING

Forewarns of serious consequences if the violation is not dealt with in a timely manner.

G_SEVERITY_CRITICAL

Requires immediate attention to the violation.

Parameter Data Types

Data types for policy-related parameters

G_PARAM_TYPE_NUMBER

Number

G_PARAM_TYPE_STRING

String

Target Types

Entities monitored by Enterprise Manager

G_HOST_TARGET_TYPE

Host

G_DATABASE_TARGET_TYPE

Oracle Database Instance

G_LISTENER_TARGET_TYPE

Oracle Listener

G_CLUSTER_TARGET_TYPE

Group of independent servers

G_RAC_DATABASE_TARGET_TYPE

Real Application Cluster (RAC) Database Instance

G_REDUNDANCY_GROUP_TARGET_TYPE

Group containing members of the same type that function collectively as a unit

G_COMPOSITE_TARGET_TYPE

Number of targets grouped together for a purpose, for example, a business function

G_HOST_GROUP_TARGET_TYPE

Group consisting of many hosts

G_DATABASE_GROUP_TARGET_TYPE

Group consisting of many Oracle database instances

G_IAS_TARGET_TYPE

Oracle Application Server

G_WEBSITE_TARGET_TYPE

Web Application

G_FORMSAPP_TARGET_TYPE

Oracle Forms

G_HTTP_SERVER_TARGET_TYPE

Oracle HTTP Server

G_WEBCACHE_TARGET_TYPE

OracleAS Web Cache

G_OC4J_TARGET_TYPE

Oracle Application Server Containers for J2EE

G_BC4J_TARGET_TYPE

ADF Business Components for Java

G_LDAP_TARGET_TYPE

Lightweight Directory Access Protocol (LDAP)

G_PORTAL_TARGET_TYPE

OracleAS Portal

G_APPLICATION_TARGET_TYPE

Oracle Application

G_APPS_SYSTEM_TARGET_TYPE

Oracle Application System

G_ASM_TARGET_TYPE

Automatic Storage Management

Condition Operators

Used when manipulating thresholds which are boundary values against which monitored metric values are compared

G_THRESHOLD_EQ

Equal to ( = )

G_THRESHOLD_LT

Less than ( < )

G_THRESHOLD_GT

Greater than ( > )

G_THRESHOLD_LE

Less than or equal to ( < = )

G_THRESHOLD_GE

Greater than or equal to ( > = )

G_THRESHOLD_NE

Not equal to ( ¼ )

G_THRESHOLD_CONTAINS

Contains at least

G_THRESHOLD_MATCH

Exact match


Data Types

The MGMT_USER_DEFINED_POLICY subprograms use the data types described in Table 8-2.

Table 8-2 Data Types Used in the MGMT_USER_DEFINED_POLICY Package

Type Description

UDP_PARAMETERS

Represents a collection of parameters used in the user-defined policy (UDP).

UDP_PARAMETER

Represents a single parameter.


When creating a user-defined policy, parameter information is passed using the UDP_PARAMETERS object.

UDP_PARAMETERS and UDP_PARAMETER Types

Use these PL/SQL types to represent the list of parameters used by a user-defined policy.

Syntax

TYPE UDP_PARAMETER IS RECORD
(
     param_name          VARCHAR2(64),
     param_type          NUMBER(1),
     threshold_value     VARCHARS(4000)
);

TYPE UDP_PARAMETERS IS TABLE of UDP_PARAMETER;

Parameters

Parameter Description
param_name Name of the parameter to be created
param_type Type of parameter

Example:


G_PARAM_TYPE_NUMBER
G_PARAM_TYPE_STRING
threshold_value Default value of parameter

Creating a User-Defined Policy

As part of creating a user-defined policy, you specify the SQL to use to extract the information to be tested from the Management Repository. Enterprise Manager provides management views with which you can safely extract data from the Management Repository without reading from the base tables.

By using Management Repository views, you:

A complete listing of the Management Repository views can be found in Chapter 9, "Management Repository Views".

CREATE_POLICY Procedure

The CREATE_POLICY procedure creates a user-defined policy. As part of a policy, one must identify the:

Once the script is run, the policy is automatically stored in the Policy Library and is available for viewing.

Tests Supported by CREATE_POLICY Procedure

The following types of tests are supported in the CREATE_POLICY procedure:

The following syntax describes the procedure used to create a policy. If the policy is not created, an error message is raised using the RAISE_APPLICATION_ERROR procedure. The error number and message can be trapped like any Oracle error for processing.

Syntax

Threshold or simple condition

PROCEDURE create_policy
(
    p_policy_name           IN VARCHAR2,
    p_target_type           IN VARCHAR2,
    p_sql_text              IN VARCHAR2,
    p_column_name           IN VARCHAR2,
    p_test_operator         IN VARCHAR2,
    p_threshold_value       IN VARCHAR2,
    p_threshold_data_type   IN NUMBER       DEFAULT G_PARAM_TYPE_NUMBER,
    p_num_keys              IN NUMBER       DEFAULT 1,
    p_description           IN VARCHAR2     DEFAULT ' ',
    p_impact                IN VARCHAR2     DEFAULT ' ',
    p_recommendation        IN VARCHAR2     DEFAULT ' ',
    p_severity_level        IN NUMBER       DEFAULT G_SEVERITY_INFORMATIONAL,
    p_category              IN VARCHAR2     DEFAULT G_CATEGORY_UNCLASSIFIED,
    p_url_link              IN VARCHAR2     DEFAULT NULL,
    p_violation_message     IN VARCHAR2     DEFAULT NULL,
    p_clear_message         IN VARCHAR2     DEFAULT NULL,
    p_eval_interval         IN NUMBER       DEFAULT 24
);

SQL expression

PROCEDURE create_policy
(
    p_policy_name           IN VARCHAR2,
    p_target_type           IN VARCHAR2,
    p_sql_text              IN VARCHAR2,
    p_test                  IN VARCHAR2,
    p_parameters            IN UDP_PARAMETERS  DEFAULT NULL,
    p_num_keys              IN NUMBER          DEFAULT 1,     
    p_description           IN VARCHAR2        DEFAULT ' ',
    p_impact                IN VARCHAR2        DEFAULT ' ',
    p_recommendation        IN VARCHAR2        DEFAULT ' ',
    p_severity_level        IN NUMBER          DEFAULT G_SEVERITY_INFORMATIONAL,
    p_category              IN VARCHAR2        DEFAULT G_CATEGORY_UNCLASSIFIED,
    p_url_link              IN VARCHAR2        DEFAULT NULL,
    p_violation_message     IN VARCHAR2        DEFAULT NULL,
    p_clear_message         IN VARCHAR2        DEFAULT NULL,
    p_eval_interval         IN NUMBER          DEFAULT 24    
);

Parameters

Parameter Description
p_policy_name Name of the policy to be created
p_target_type Type of target to which this policy is applicable

Example:


G_HOST_TARGET_TYPE (Host)
G_DATABASE_TARGET_TYPE (Oracle Database Instance)
p_sql_text SQL used to retrieve data to be tested from the Management Repository

The specified SQL needs to satisfy the following requirements:

  • The first n selected columns, where n equals p_num_keys, are columns that are used to uniquely identify a row. The SQL query must not return more than one row with the same key. Furthermore, one of these key columns must be a column called TARGET_GUID which contains the target guid of the target and to which the policy violation should be associated.

  • The remaining selected columns, of which there must be at least one, return the values that are then tested by the policy's test.

p_column_name Name of the column from the select list against which to compare the p_threshold_value
p_test_operator Type of comparison to be performed. Valid values are:
G_THRESHOLD_EQ
G_THRESHOLD_NE
G_THRESHOLD_LT
G_THRESHOLD_LE
G_THRESHOLD_GT
G_THRESHOLD_GE
G_THRESHOLD_CONTAINS
G_THRESHOLD_MATCH
p_threshold_value Value against which comparison is performed
p_threshold_data_type Data type of the threshold value. Valid values are:
G_PARAM_TYPE_NUMBER
G_PARAM_TYPE_STRING
p_test Test to apply to the rows returned by p_sql_text for identifying violations of the policy. The test can be any valid SQL expression. It can also reference columns in the select list from p_sql_text and/or parameters specified in p_parameters. To reference columns in the select column list or a parameter, prefix the name with a colon (:).
p_parameters Tuples containing a list of parameters and the default value to be used when evaluating the policy
p_num_keys Number of columns in the select list that are key columns; that is, they uniquely identify a row returned by p_sql_text
p_description Contains descriptive text for the policy.
p_impact Provides text that states why this policy is important.
p_recommendation Contains information regarding how to bring a target back into compliance with the policy.
p_severity_level Severity level for a violation. Valid values include:
G_SEVERITY_INFORMATIONAL
G_SEVERITY_WARNING
G_SEVERITY_CRITICAL
p_category Policy category. Valid values are:
G_CATEGORY_FAULT
G_CATEGORY_WORKLOAD_VOL
G_CATEGORY_WORKLOAD_TYPE
G_CATEGORY_PERFORMANCE
G_CATEGORY_CAPACITY
G_CATEGORY_CONFIGURATION
G_CATEGORY_SECURITY
G_CATEGORY_STORAGE
G_CATEGORY_UNCLASSIFIED
p_url_link URL to be used for additional detailed information regarding this policy
p_violation_message Message recorded along with the violation. Used for notifications, such as e-mails and paging, that happen as a result of a detection of a new violation. The message can reference columns in the select list from p_sql_text and/or parameters specified in p_parameters. To reference columns in the select column list or a parameter, enclose the name with percent signs (%).
p_clear_message Message recorded with the clearing of a violation. Used for notifications, such as e-mails and paging, that happen as a result of a detection of a new violation. The message can reference columns in the select list from p_sql_text and/or parameters specified in p_parameters. To reference columns in the select column list or a parameter, enclose the name with percent signs (%).
p_eval_interval Evaluation interval expression in number of hours

Examples

The following examples depict how to create a policy.

Example 8-1 Sufficient Control Files Threshold

DECLARE
  l_sql         VARCHAR2(2000);
BEGIN
  l_sql := 'SELECT target_guid, control_file_count ' ||
               'FROM ' ||
                 '(SELECT target_guid, COUNT(file_name) control_file_count ' ||
                 'FROM mgmt$db_controlfiles ' ||
                 'GROUP BY target_guid)';
  MGMT_USER_DEFINED_POLICY.CREATE_POLICY
  (
    p_policy_name          => 'Insufficient Control Files',
    p_target_type          => mgmt_user_defined_policy.G_DATABASE_TARGET_TYPE,
    p_sql_text             => l_sql,
    p_column_name          => 'control_file_count', 
    p_test_operator        => mgmt_user_defined_policy.G_THRESHOLD_LT,
    p_threshold_value      => 2,
    p_threshold_data_type  => mgmt_user_defined_policy.G_PARAM_TYPE_NUMBER,
    p_num_keys             => 1,       -- target_guid is key column
    p_description          => 'Ensures sufficient control files',
    p_impact         => 'The control file is one of the most important files '||
                        'in an Oracle database. It maintains many physical ' ||
                        'characteristics and important recovery information ' ||
                        'about the database. If you lose the only copy of the ' ||
                        'control file due to a media error, there will be ' ||
                        'unnecessary down time and other risks.'
    p_recommendation       => 'Use at least two control files that are ' ||
                              'multiplexed on different disks.',
    p_severity_level       => mgmt_user_defined_policy.G_SEVERITY_CRITICAL,
    p_category             => mgmt_user_defined_policy.G_CATEGORY_CONFIGURATION,
    p_violation_message    => 'Insufficient control files: %control_file_count%',
    p_clear_message        => 'Sufficient control files'
 );
COMMIT;
END;
/

Example 8-2 Sufficient Control Files Using UDP Parameters

DECLARE
  l_sql         VARCHAR2(2000);
  l_test        VARCHAR2(2000);
  l_parameters  mgmt_user_defined_policy.udp_parameters;
BEGIN
  l_sql := 'SELECT target_guid, control_file_count ' ||
           'FROM ' ||
             '(SELECT target_guid, COUNT(file_name) control_file_count ' ||
             'FROM mgmt$db_controlfiles ' ||
             'GROUP BY target_guid)';
 
  l_test := ':control_file_count < :min_control_file_count';
 
  l_parameters := mgmt_user_defined_policy.udp_parameters();
  l_parameters.extend(1);
  l_parameters(1).param_name := 'min_control_file_count';                         
  l_parameters(1).param_type := mgmt_user_defined_policy.G_PARAM_TYPE_NUMBER;
  l_parameters(1).threshold_value :=2;
 
  MGMT_USER_DEFINED_POLICY.CREATE_POLICY
  (
    p_policy_name    => 'Insufficient Control Files',
    p_target_type    => mgmt_user_defined_policy.G_DATABASE_TARGET_TYPE,
    p_sql_text       => l_sql,
    p_test           => l_test,
    p_parameters     => l_parameters, 
    p_num_keys       => 1,   -- target_guid is key column 
    p_description    => 'Ensures sufficient control files',
    p_impact         => 'The control file is one of the most important files ' ||
                        'in an Oracle database. It maintains many physical ' ||
                        'characteristics and important recovery information ' ||
                        'about the database. If you lose the only copy of the ' ||
                        'control file due to a media error, there will be ' ||
                        'unnecessary down time and other risks.',
    p_recommendation      => 'Use at least two control files that are ' ||
                             'multiplexed on different disks.',
    p_severity_level      => mgmt_user_defined_policy.G_SEVERITY_CRITICAL,
    p_category            => mgmt_user_defined_policy.G_CATEGORY_CONFIGURATION,
    p_violation_message   => 'Insufficient control files: %control_file_count%',
    p_clear_message       => 'Sufficient control files'
  );
  COMMIT;
END;
/

Example 8-3 Data Dictionary Protected SQL Expression

DECLARE
  l_sql   VARCHAR2(2000);
  l_test  VARCHAR2(2000);   
BEGIN
  l_sql := 'SELECT target_guid, name, value ' ||
               'FROM mgmt$db_init_params ' ||
               'WHERE name =''O7_DICTIONARY_ACCESSIBILITY''';
  l_test := 'UPPER(NVL(:value, ''TRUE'')) = ''TRUE''';
 MGMT_USER_DEFINED_POLICY.CREATE_POLICY
 (
    p_policy_name      => 'Data Dictionary Protected',
    p_target_type      => mgmt_user_defined_policy.G_DATABASE_TARGET_TYPE,
    p_sql_text         => l_sql,
    p_test             => l_test,   
    p_num_keys         => 1,      -- target_guid is key column
    p_description      => 'Ensures data dictionary protection is enabled',
    p_impact           => 'Setting the 07_DICTIONARY_ACCESSIBILITY to TRUE ' ||
                          'allows users with ANY system privileges to access ' ||
                          'the data dictionary.  As a result, these user ' ||
                          'accounts can be exploited to gain unauthorized ' ||
                          'access to data. Instead the data dictionary should ' ||
                          'be protected such that only those authorized users ' ||
                          'making DBA-privileged connections can use the ANY ' ||
                          'system privilege to access the data dictionary.',
    p_recommendation       => 'Set O7_DICTIONARY_ACCESSIBILITY to TRUE',
    p_severity_level       => mgmt_user_defined_policy.G_SEVERITY_CRITICAL,
    p_category             => mgmt_user_defined_policy.G_CATEGORY_SECURITY,
    p_violation_message    => 'Data dictionary is not protected',
    p_clear_message        => 'Data dictionary is protected',
    p_eval_interval        => 12
 );
 COMMIT;
END;
/

Adding a User-Defined Policy to Existing Targets

There are a number of ways to add a user-defined policy to existing targets. These methods include:

Using the Metric and Policy Settings UI

To quickly add a user-defined policy to existing targets, use the Metric and Policy Settings UI pages.

  1. On the Grid Control home page, click the Targets tab.

  2. On the resulting page, click the target type of the target to which you want to add the user-defined policy.

  3. Select the name of the target to which you want to add the user-defined policy.

  4. On the resulting Home page for the target, scroll to the Related Links section and click Metric and Policy Settings.

  5. Choose the Policies subtab and click Add Policies.

  6. Select the policy rule from the Policy Library you want to add and click Continue.

  7. On the confirmation page, click Yes to add the policy rule to the target. In addition, ensure that you click OK on the Policies page, otherwise the change will not take affect.

Using a PL/SQL Procedure

The following procedure associates an existing user-defined policy with a target that already exists in the Management Repository. If the association is not created, an error message is raised using the RAISE_APPLICATION_ERROR procedure. The error number and message can be trapped like any Oracle error for processing.

Syntax

PROCEDURE add_policy_to_target
(
    p_policy_name       IN VARCHAR2,
    p_target_type       IN VARCHAR2,
    p_target_name       IN VARCHAR2
);

Parameters

Parameter Description
p_policy_name Name of the policy to be added
p_target_type Type of target to which this policy is applicable

Examples:


G_HOST_TARGET_TYPE (Host)
G_DATABASE_TARGET_TYPE (Oracle Database Instance)
p_target_name Name of the target

Examples

The following examples depict how to add policies to targets.

Example 8-4 Add a Policy to One Target

BEGIN
  MGMT_USER_DEFINED_POLICY.ADD_POLICY_TO_TARGET
  (
    p_policy_name     => 'Insufficient Control Files',
    p_target_type     => mgmt_user_defined_policy.G_DATABASE_TARGET_TYPE,
    p_target_name     => 'Finance'
  );
COMMIT;
END;
/

Example 8-5 Add a Policy to All Existing Targets

DECLARE
  l_target_names   MGMT_MEDIUM_STRING_ARRAY;
BEGIN
 SELECT target_name BULK COLLECT INTO l_target_names
 FROM mgmt$target
 WHERE target_type = mgmt_user_defined_policy.G_DATABASE _TARGET_TYPE;

 IF (l_target_names IS NOT NULL) and (l_target_names.COUNT > 0)
 THEN
   FOR i in 1..l_target_names.COUNT LOOP
     mgmt_user_defined_policy.add_policy_to_target
      (
        p_policy_name  => 'Insufficient Control Files',
        p_target_type  => mgmt_user_defined_policy.G_DATABASE_TARGET_TYPE,
        p_target_name  => l_target_names(i)
      );
   END LOOP;
   COMMIT;
 END IF;

END;
/

Using Monitoring Templates

Another way to add a user-defined policy to existing targets is to:

  1. Create a monitoring template.

  2. Add the user-defined policy to the monitoring template.

  3. Apply the monitoring template to the target.

Deleting a User-Defined Policy

This procedure deletes an existing user-defined policy. As part of deleting a user-defined policy:

If the policy is not deleted, an error message is raised using the RAISE_APPLICATION_ERROR procedure. The error number and message can be trapped like any Oracle error for processing.

The Management Repository owner has the permission to delete the policy.

Syntax

PROCEDURE delete_policy(    p_policy_name           IN VARCHAR2,    p_target_type           IN VARCHAR2);

Parameters

Parameter Description
p_policy_name Name of the policy to be deleted
p_target_type Type of target to which this policy is applicable

Examples:


G_HOST_TARGET_TYPE (Host)
G_DATABASE_TARGET_TYPE (Oracle Database Instance)

Example

The following example depicts how to delete a policy.

Example 8-6 Deleting a User-Defined Policy

BEGIN
   MGMT_USER_DEFINED_POLICY.DELETE_POLICY
   (
       p_policy_name  => 'Insufficient Control Files',
       p_target_type  => mgmt_user_defined_policy.G_DATABASE_TARGET_TYPE
    );
COMMIT;
END;
/

Removing a User-Defined Policy from Existing Targets

There are two ways to remove a user-defined policy from existing targets:

Using the Metric and Policy Settings UI

To quickly remove a user-defined policy from existing targets, use the Metric and Policy Settings UI pages.

  1. On the Grid Control home page, click the Targets tab.

  2. On the resulting page, click the target type of the target from which you want to remove the user-defined policy.

  3. Select the name of the target from which you want to remove the user-defined policy.

  4. On the resulting Home page for the target, scroll to the Related Links section and click Metric and Policy Settings.

  5. Choose the Policies subtab and click Remove.

  6. Select the policy rule you want to remove and click Continue.

  7. On the confirmation page, click Yes to remove the policy rule from the target. In addition, ensure that you click OK on the Policies page, otherwise the change will not take affect.

Using a PL/SQL Procedure

The following procedure is used to remove an existing user-defined policy from a target that exists in the Management Repository. If the association is not removed, an error message is raised using the RAISE_APPLICATION_ERROR procedure. The error number and message can be trapped like any Oracle error for processing.

Syntax

PROCEDURE remove_policy_from_target
(
    p_policy_name           IN VARCHAR2,
    p_target_type           IN VARCHAR2,
    p_target_name           IN VARCHAR2
);

Parameters

Parameter Description
p_policy_name Name of the policy to be removed
p_target_type Type of target to which this policy is applicable

Example:


G_HOST_TARGET_TYPE (Host)
G_DATABASE_TARGET_TYPE (Oracle Database Instance)
p_target_name Name of the target

Example

The following example depicts how to remove a user-defined policy from existing targets.

Example 8-7 Remove a Policy from an Existing Target

BEGIN
  MGMT_USER_DEFINED_POLICY.REMOVE_POLICY_FROM_TARGET
  (
    p_policy_name        => 'Insufficient Control Files',
    p_target_type        => mgmt_user_defined_policy.G_DATABASE_TARGET_TYPE,
    p_target_name        => 'Finance'
  );
 COMMIT;
END;
/