Oracle® Enterprise Manager Extensibility Guide 10g Release 5 (10.2.0.5) Part Number B40007-03 |
|
|
PDF · Mobi · ePub |
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:
Oracle Database Concepts for information about packages, SQL, and PL/SQL
Oracle Enterprise Manager Concepts for information on policies
This chapter includes the following:
Note:
This chapter assumes that you are familiar with PL/SQL packages and Enterprise Manager policies.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.
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.
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 |
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.
Use these PL/SQL types to represent the list of parameters used by a user-defined policy.
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;
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 |
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:
Protect your queries from changes to the Management Repository schema that may occur in future releases
Ensure that your policy definition remains functional
A complete listing of the Management Repository views can be found in Chapter 9, "Management Repository Views".
The CREATE_POLICY procedure creates a user-defined policy. As part of a policy, one must identify the:
Name of the policy
Data that is to be evaluated
Test, optionally parameterized, that is used to test the current state of the data
What, if any, default parameter values should be substituted into the test
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:
Threshold condition or simple condition
The value from a single selected column is tested using a specified test operator
SQL expression
A SQL expression that can contain references to literals, selected columns and policy parameters, where the latter two are named as bind variables. In addition, any built-in SQL functions can be used. For example, the following uses the SQL function (length), as well as the selected column (PASSWORD), and policy parameter (MIN_PWD_LENGTH):
length(:PASSWORD) <= :MIN_PWD_LENGTH
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.
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 );
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:
|
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 |
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; /
There are a number of ways to add a user-defined policy to existing targets. These methods include:
To quickly add a user-defined policy to existing targets, use the Metric and Policy Settings UI pages.
On the Grid Control home page, click the Targets tab.
On the resulting page, click the target type of the target to which you want to add the user-defined policy.
Select the name of the target to which you want to add the user-defined policy.
On the resulting Home page for the target, scroll to the Related Links section and click Metric and Policy Settings.
Choose the Policies subtab and click Add Policies.
Select the policy rule from the Policy Library you want to add and click Continue.
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.
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.
PROCEDURE add_policy_to_target ( p_policy_name IN VARCHAR2, p_target_type IN VARCHAR2, p_target_name IN VARCHAR2 );
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 |
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; /
This procedure deletes an existing user-defined policy. As part of deleting a user-defined policy:
All associations of the policy to a target are automatically removed.
All existing evaluations results for the policy are deleted.
All references of this policy in existing monitoring templates are removed.
The policy is deleted from the Policy Library.
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.
PROCEDURE delete_policy( p_policy_name IN VARCHAR2, p_target_type IN VARCHAR2);
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) |
The following example depicts how to delete a policy.
There are two ways to remove a user-defined policy from existing targets:
To quickly remove a user-defined policy from existing targets, use the Metric and Policy Settings UI pages.
On the Grid Control home page, click the Targets tab.
On the resulting page, click the target type of the target from which you want to remove the user-defined policy.
Select the name of the target from which you want to remove the user-defined policy.
On the resulting Home page for the target, scroll to the Related Links section and click Metric and Policy Settings.
Choose the Policies subtab and click Remove.
Select the policy rule you want to remove and click Continue.
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.
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.
PROCEDURE remove_policy_from_target ( p_policy_name IN VARCHAR2, p_target_type IN VARCHAR2, p_target_name IN VARCHAR2 );
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 |
The following example depicts how to remove a user-defined policy from existing targets.