Rem Rem $Header: default_notification_rules.sql 06-jun-2007.11:17:36 keiwong Exp $ Rem Rem default_notification_rules.sql Rem Rem Copyright (c) 2002, 2007, Oracle. All rights reserved. Rem Rem NAME Rem default_notification_rules.sql - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem keiwong 06/06/07 - update alert log metrics for 11g Rem ychan 06/01/05 - Fix bug 4376780 Rem rzazueta 11/01/04 - Add EM_REPOS_USER parameter Rem jochen 06/29/04 - Add media failure alert log category Rem jsoule 07/25/03 - remove database-version specific alerts Rem vchao 07/16/03 - Modify defualt notification rule Rem jsoule 04/25/03 - add otthe remaining alert log rollup categories Rem jsoule 04/18/03 - move default notifications from alertLog to alertLogStatus Rem lhan 04/08/03 - split problemTbsp into two Rem yaofeng 12/20/02 - notification API change Rem vshah 11/08/02 - Change segmax Rem ychan 11/05/02 - Change default notification Rem ychan 10/17/02 - Change metric names Rem tjaiswal 10/02/02 - Changes for out of box notification rules Rem tjaiswal 09/14/02 - Changes to default notification rules Rem tjaiswal 09/11/02 - tjaiswal_outofbox_notification_rules Rem tjaiswal 09/10/02 - Created Rem Rem Rem This script requires the following arguments Rem 1. Name of the repository user Rem DEFINE EM_REPOS_USER = "&1" Rem Rem Create default notification rules for types - database, listener Rem BEGIN DECLARE current_user VARCHAR2(128); current_em_user VARCHAR2(128); -- variables for default notification rules rule_name VARCHAR2(64); rule_owner VARCHAR2(128); rule_desc VARCHAR2(256); rule_public NUMBER(1); want_clears NUMBER(1); want_warnings NUMBER(1); want_critical_alerts NUMBER(1); want_tgt_up NUMBER; want_tgt_down NUMBER; want_tgt_unreachable_start NUMBER; want_tgt_unreachable_end NUMBER; want_tgt_metric_err_start NUMBER; want_tgt_metric_err_end NUMBER; want_tgt_blkout_started NUMBER; want_tgt_blkout_ended NUMBER; target_type VARCHAR2(64); rule_targets_array SMP_EMD_STRING_ARRAY; rule_mnames_array SMP_EMD_STRING_ARRAY; rule_mcolumns_array SMP_EMD_STRING_ARRAY; rule_mindexes_array SMP_EMD_NVPAIR_ARRAY; user_email VARCHAR2(128); send_email NUMBER(1); rule_emails SMP_EMD_STRING_ARRAY; rule_device_names SMP_EMD_STRING_ARRAY; rule_device_types SMP_EMD_INTEGER_ARRAY; device_not_avail_list SMP_EMD_STRING_ARRAY; email_gateway_setup NUMBER; BEGIN -- Save the currently logged in user current_user := mgmt_user.get_current_em_user(); -- Set em user context to EM_REPOS_USER. The default notification -- rules will be owned by this user. setemusercontext('&EM_REPOS_USER', MGMT_USER.OP_SET_IDENTIFIER); current_em_user := mgmt_user.get_current_em_user(); dbms_output.put_line( 'current em user: ' || current_em_user ); -- rule email setup will be done in the setup script file -- no email to be assigned with the rules at this stage ... send_email := 0; rule_emails := SMP_EMD_STRING_ARRAY(); -- no devices to be assigned with the rules ... rule_device_names := SMP_EMD_STRING_ARRAY(); rule_device_types := SMP_EMD_INTEGER_ARRAY(); -- create rule "Database Availability and Critical States" for all -- targets of type database BEGIN -- initialize the values rule_name := 'Database Availability and Critical States'; rule_desc := 'System-generated notification rule for monitoring Databases'' availability and critical metric statuses.'; rule_public := 1; target_type := MGMT_GLOBAL.G_DATABASE_TARGET_TYPE; rule_targets_array := SMP_EMD_STRING_ARRAY(); rule_targets_array.extend( 1 ); rule_targets_array( 1 ) := '%'; rule_mnames_array := SMP_EMD_STRING_ARRAY(); rule_mnames_array.extend( 25 ); rule_mnames_array( 1 ) := 'adrAlertLogIncidentErrorStatus'; rule_mnames_array( 2 ) := 'adrAlertLogIncidentErrorStatus'; rule_mnames_array( 3 ) := 'adrAlertLogIncidentErrorStatus'; rule_mnames_array( 4 ) := 'adrAlertLogIncidentErrorStatus'; rule_mnames_array( 5 ) := 'adrAlertLogIncidentErrorStatus'; rule_mnames_array( 6 ) := 'adrAlertLogIncidentErrorStatus'; rule_mnames_array( 7 ) := 'adrAlertLogIncidentErrorStatus'; rule_mnames_array( 8 ) := 'adrAlertLogIncidentErrorStatus'; rule_mnames_array( 9 ) := 'adrAlertLogIncidentErrorStatus'; rule_mnames_array( 10 ) := 'adrAlertLogIncidentErrorStatus'; rule_mnames_array( 11 ) := 'adrAlertLogIncidentErrorStatus'; rule_mnames_array( 12 ) := 'adrAlertLogIncidentErrorStatus'; rule_mnames_array( 13 ) := 'adrAlertLogOperationalErrorStatus'; rule_mnames_array( 14 ) := 'adrAlertLogOperationalErrorStatus'; rule_mnames_array( 15 ) := 'adrAlertLogOperationalErrorStatus'; rule_mnames_array( 16 ) := 'adrAlertLogOperationalErrorStatus'; rule_mnames_array( 17 ) := 'archFull'; rule_mnames_array( 18 ) := 'Database_Resource_Usage'; rule_mnames_array( 19 ) := 'Database_Resource_Usage'; rule_mnames_array( 20 ) := 'problemSegTbsp'; rule_mnames_array( 21 ) := 'problemSegTbsp'; rule_mnames_array( 22 ) := 'problemTbsp'; rule_mnames_array( 23 ) := 'UserBlock'; rule_mnames_array( 24 ) := 'wait_bottlenecks'; rule_mnames_array( 25 ) := 'Response'; rule_mcolumns_array := SMP_EMD_STRING_ARRAY(); rule_mcolumns_array.extend( 25 ); rule_mcolumns_array( 1 ) := 'genericIncidentErrors'; rule_mcolumns_array( 2 ) := 'genericInternalErrors'; rule_mcolumns_array( 3 ) := 'sessTerminateErrors'; rule_mcolumns_array( 4 ) := 'internalSqlErrors'; rule_mcolumns_array( 5 ) := 'accessViolationErrors'; rule_mcolumns_array( 6 ) := 'redoLogCorruptErrors'; rule_mcolumns_array( 7 ) := 'fileAccessErrors'; rule_mcolumns_array( 8 ) := 'inconsistentDbStateErrors'; rule_mcolumns_array( 9 ) := 'oraBlockCorruptErrors'; rule_mcolumns_array( 10 ) := 'deadlockErrors'; rule_mcolumns_array( 11 ) := 'outOfMemoryErrors'; rule_mcolumns_array( 12 ) := 'clusterErrors'; rule_mcolumns_array( 13 ) := 'genericOperationalErrors'; rule_mcolumns_array( 14 ) := 'archiveHungErrors'; rule_mcolumns_array( 15 ) := 'blockCorruptErrors'; rule_mcolumns_array( 16 ) := 'mediaFailureErrors'; rule_mcolumns_array( 17 ) := 'archUsedPercent'; rule_mcolumns_array( 18 ) := 'process_usage'; rule_mcolumns_array( 19 ) := 'session_usage'; rule_mcolumns_array( 20 ) := 'segsChunkSmlCnt'; rule_mcolumns_array( 21 ) := 'segsMaxExtsCnt'; rule_mcolumns_array( 22 ) := 'pctUsed'; rule_mcolumns_array( 23 ) := 'count'; rule_mcolumns_array( 24 ) := 'user_wait_time_pct'; rule_mcolumns_array( 25 ) := 'Status'; rule_mindexes_array := SMP_EMD_NVPAIR_ARRAY(); rule_mindexes_array.extend( rule_mcolumns_array.COUNT ); FOR i in 1..rule_mindexes_array.COUNT LOOP rule_mindexes_array(i) := SMP_EMD_NVPAIR( target_type || '|' || rule_mnames_array(i) || '|' || rule_mcolumns_array(i), '%'); END LOOP; want_clears := 0; want_warnings := 0; want_critical_alerts := 1; want_tgt_up := 0; want_tgt_down := 1; want_tgt_unreachable_start := 0; want_tgt_unreachable_end := 0; want_tgt_metric_err_start := 0; want_tgt_metric_err_end := 0; want_tgt_blkout_started := 0; want_tgt_blkout_ended := 0; dbms_output.put_line( 'Creating rule: ' || rule_name || ' for user: ' || current_em_user ); mgmt_preferences.create_notification_rule( rule_name, rule_desc, rule_public, target_type, rule_targets_array, want_clears, want_critical_alerts, want_warnings, want_tgt_up, want_tgt_down, want_tgt_unreachable_start, want_tgt_unreachable_end, want_tgt_metric_err_start, want_tgt_metric_err_end, want_tgt_blkout_started, want_tgt_blkout_ended, rule_mnames_array, rule_mcolumns_array, rule_mindexes_array, send_email, rule_emails, rule_device_names, rule_device_types, device_not_avail_list, email_gateway_setup ); dbms_output.put_line( 'email gateway setup: ' || email_gateway_setup ); COMMIT; EXCEPTION WHEN OTHERS THEN dbms_output.put_line( 'Exception occurred while creating rule: ' || rule_name || ' for user: ' || current_em_user || ', SQLCODE: ' || SQLCODE || ', SQLERRM: ' || SQLERRM ); END; -- create rule "Listener Availability" for all targets of type listener BEGIN -- initialize the values rule_name := 'Listener Availability'; rule_desc := 'System-generated notification rule for monitoring database Listeners'' availability and critical metric statuses.'; rule_public := 1; target_type := 'oracle_listener'; rule_targets_array := SMP_EMD_STRING_ARRAY(); rule_targets_array.extend( 1 ); rule_targets_array( 1 ) := '%'; rule_mnames_array := SMP_EMD_STRING_ARRAY(); rule_mnames_array.extend( 1 ); rule_mnames_array( 1 ) := 'Response'; rule_mcolumns_array := SMP_EMD_STRING_ARRAY(); rule_mcolumns_array.extend( 1 ); rule_mcolumns_array( 1 ) := 'Status'; rule_mindexes_array := SMP_EMD_NVPAIR_ARRAY(); rule_mindexes_array.extend( rule_mcolumns_array.COUNT ); FOR i in 1..rule_mindexes_array.COUNT LOOP rule_mindexes_array(i) := SMP_EMD_NVPAIR( target_type || '|' || rule_mnames_array(i) || '|' || rule_mcolumns_array(i), '%'); END LOOP; want_clears := 0; want_warnings := 0; want_critical_alerts := 0; want_tgt_up := 0; want_tgt_down := 1; want_tgt_unreachable_start := 0; want_tgt_unreachable_end := 0; want_tgt_metric_err_start := 0; want_tgt_metric_err_end := 0; want_tgt_blkout_started := 0; want_tgt_blkout_ended := 0; dbms_output.put_line( 'Creating rule: ' || rule_name || ' for user: ' || current_em_user ); mgmt_preferences.create_notification_rule( rule_name, rule_desc, rule_public, target_type, rule_targets_array, want_clears, want_critical_alerts, want_warnings, want_tgt_up, want_tgt_down, want_tgt_unreachable_start, want_tgt_unreachable_end, want_tgt_metric_err_start, want_tgt_metric_err_end, want_tgt_blkout_started, want_tgt_blkout_ended, rule_mnames_array, rule_mcolumns_array, rule_mindexes_array, send_email, rule_emails, rule_device_names, rule_device_types, device_not_avail_list, email_gateway_setup ); dbms_output.put_line( 'email gateway setup: ' || email_gateway_setup ); COMMIT; EXCEPTION WHEN OTHERS THEN dbms_output.put_line( 'Exception occurred while creating rule: ' || rule_name || ' for user: ' || current_em_user || ', SQLCODE: ' || SQLCODE || ', SQLERRM: ' || SQLERRM ); END; -- now reset the user setemusercontext(current_user, MGMT_USER.OP_SET_IDENTIFIER); EXCEPTION WHEN OTHERS THEN setemusercontext(current_user, MGMT_USER.OP_SET_IDENTIFIER); END; END; /