Rem drv:
Rem
Rem $Header: esm_default_notification_rules_setup.sql 13-aug-2006.20:16:54 bmallipe Exp $
Rem
Rem esm_default_notification_rules_setup.sql
Rem
Rem Copyright (c) 2005, 2006, Oracle. All rights reserved.
Rem
Rem NAME
Rem esm_default_notification_rules_setup.sql -
Rem
Rem DESCRIPTION
Rem
Rem
Rem NOTES
Rem
Rem
Rem MODIFIED (MM/DD/YY)
Rem bmallipe 08/13/06 - adding the CENTRAL only condition
Rem rpinnama 10/24/05 -
Rem ychan 10/24/05 - Fix bug 4642958
Rem gsbhatia 07/11/05 - New repmgr header impl
Rem dkjain 04/18/05 - dkjain_enhancement-4296265
Rem dkjain 03/21/05 - Created
Rem
SET ECHO ON
SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100
Rem 1. Name of the repository user
Rem
DEFINE EM_REPOS_USER = "&1"
Rem
Rem Do the user pref email setup, rule emails setup, mail gateway setup for
Rem default notification rules for types - oracle_database & oracle_listener
Rem
BEGIN
DECLARE
current_em_user VARCHAR2(128);
-- variables for default notification rules
rule_name VARCHAR2(64);
rule_owner VARCHAR2(128);
user_email VARCHAR2(128);
send_email NUMBER(1);
rule_emails SMP_EMD_STRING_ARRAY;
rule_email_formats SMP_EMD_INTEGER_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;
rules_subscribed NUMBER;
schedule_setup NUMBER;
mail_gateway_hosts SMP_EMD_STRING_ARRAY;
mail_server_name VARCHAR2(128);
mail_server_sender_name VARCHAR2(64);
mail_server_sender_addr VARCHAR2(128);
schedule_emails SMP_EMD_NVPAIR_ARRAY;
emails_not_available SMP_EMD_STRING_ARRAY;
BEGIN
-- First get the currently logged in user. the default notification
-- rules will be owned by this user
current_em_user := mgmt_user.get_current_em_user();
dbms_output.put_line( 'current em user: ' || current_em_user );
rule_owner := current_em_user;
-- get the user email address if available
-- this will be the same for all the rules
user_email := '&EMAIL_ADDRESS';
dbms_output.put_line( 'user email as obtained from setup: ' || user_email );
IF( user_email IS NULL OR
length(trim(user_email)) = 0 OR
UPPER(user_email) = '_NOT_AVAILABLE_' OR
UPPER(user_email) = '%EMAIL_ADDRESS%' )
THEN
dbms_output.put_line( 'email address of user is not specified' );
send_email := 0;
rule_emails := SMP_EMD_STRING_ARRAY();
rule_email_formats := SMP_EMD_INTEGER_ARRAY();
ELSE
dbms_output.put_line( 'email address of user is specified and is: ' || user_email );
send_email := 1;
rule_emails := SMP_EMD_STRING_ARRAY();
rule_emails.extend( 1 );
rule_emails( 1 ) := user_email;
rule_email_formats := SMP_EMD_INTEGER_ARRAY();
rule_email_formats.extend( 1 );
rule_email_formats( 1 ) := 1;
END IF;
-- note that the mail gateway and the email of the user needs to be
-- setup so as to be able to receive the email notifications. these
-- are part of the core component and so are being setup in core only
-- set up the preference email of the current user
-- note, if the user email was supplied, rule_emails will be a string
-- array of size 1. otherwise, rule_emails will be empty
dbms_output.put_line( 'setting pref emails of user: ' || current_em_user );
BEGIN
mgmt_preferences.update_user_pref_data( '&EM_REPOS_USER',
' ',
0,
rule_emails,
rule_email_formats,
email_gateway_setup,
rules_subscribed,
schedule_setup );
dbms_output.put_line( 'email gateway setup: ' || email_gateway_setup );
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
dbms_output.put_line( 'Exception occurred while setting pref email of user: ' || current_em_user || ', SQLCODE: ' || SQLCODE || ', SQLERRM: ' || SQLERRM );
END;
-- no devices to be assigned with the rules ...
rule_device_names := SMP_EMD_STRING_ARRAY();
rule_device_types := SMP_EMD_INTEGER_ARRAY();
-- now setup the emails for each of the rules of
-- type database and listener
-- Assign email to the database targets rule
rule_name := 'Violation Notification for Database Security Policies';
BEGIN
dbms_output.put_line( 'Assign email: ' || user_email || ' of ' || current_em_user || ' to rule: ' || rule_name || ' of: ' || rule_owner );
mgmt_preferences.update_rule_devices_data( rule_name,
rule_owner,
send_email,
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( 'UNHANDLED EXCEPTION while assigning email: ' || user_email || ' to rule: ' || rule_name );
END;
END;
END;
/
PROMPT Registering metaver callbacks (DB)
Rem NOTE: The following packages are created as part of a last-minute fix
Rem for 10.2 GC. These packages are created here to automatically fix bug 4692595
Rem This is done to avoid re-creating the seed for 10.2 GC, there by
Rem invalidating all the testing done for with the seed.
Rem As we don't have db_outofbox file, the changes are put it here from now
Rem The following lines has to be removed for 10.2.0.2 and should not
Rem be checked into EMDBGC_MAIN branch.
@&EM_SQL_ROOT/db/latest/upgrade/upgrade_pkgdefs.sql
@&EM_SQL_ROOT/db/latest/upgrade/upgrade_pkgbodys.sql
Rem Recompile any invalid objects to avoid leaving the repository in an
Rem inconsistant state.
@&EM_SQL_ROOT/core/latest/admin/admin_recompile_invalid.sql &&EM_REPOS_USER
BEGIN
BEGIN
mgmt_properties.del_property_change_callback(p_target_type=>'oracle_database',
p_procedure_name=>'mgmt_db_upgrade.change_ver_inst',
p_property_name=>'Version',
p_new_property_value=>'%');
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
BEGIN
mgmt_properties.del_property_change_callback(p_target_type=>'rac_database',
p_procedure_name=>'mgmt_db_upgrade.change_ver_rac',
p_property_name=>'Version',
p_new_property_value=>'%');
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
BEGIN
mgmt_target.add_metaver_catprop_callback(p_target_type=>'oracle_database',
p_callback_name=>'mgmt_db_upgrade.change_ver_inst',
p_eval_order=>-1);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
BEGIN
mgmt_target.add_metaver_catprop_callback(p_target_type=>'rac_database',
p_callback_name=>'mgmt_db_upgrade.change_ver_rac',
p_eval_order=>-1);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END;
/
PROMPT Done Registering metaver callbacks (DB)
commit;