/******************************************************************************
Script Name    : Create_Monthly_Patch_Blackout_Script.sql
Creator        : K. Kriebisch, AMIS Services b.v., Nieuwegein (NL)
Creation date  : 10-04-2020
Purpose        : Due to often forgotten EM Blackouts this SQL-script creates the 
                 content for a shell script called "set_all_patch_blackouts.sh 
		 that creates them in the first week of a month. The script takes
		 into account that PatchTuesday is different each month and that 
		 the blackouts should start a couple of minutes before the 
		 planned patch window.
				 
Usage          : @Create_Monthly_Patch_Blackout_Script.sql
Remarks        : In order to run this script an target property OSPatchDay 
                 has been been added in OMS to every host and the group names
		 correspond with the names of the patch slots Linux uses.
		 This script can only be used on the OEM repository database.
				 
RDBMS versions : n.v.t.
-------------------------------------------------------------------------------
Revision record
Date        Version  Author  Modification
----------  ------   ------  -------------------------------------------------
10-04-2020 1.0      KK      Created
01-06-2020 1.1      KK      switch to Notification BLs
08-06-2020 1.2      KK      return to normal BLs
07-10-2020 1.3      KK	    added BLs for shadow groups
08-10-2020 1.4      KK      corrected logical error when adding shadow groups
6-11-2020  1.5      KK      translated all comments into English
******************************************************************************/
set trimspool on
set serveroutput on
set termout on
set verify off
set lines 300
set pages 100


COLUMN logname new_value new_logname

REM select 'c:\temp\OSPatch_Blackout_Creation_'||to_char(sysdate,'MON_YYYY')||'.log' logname from v$instance;
select '/tmp/OSPatch_Blackout_Creation_'||to_char(sysdate,'MON_YYYY')||'.log' logname from dual;
spool &new_logname


prompt **********************************************************************
prompt *** Creating Notification Blackouts on Hosts for the Month  ***
prompt **********************************************************************
select to_char(sysdate, 'dd-mm-yyyy hh24:mi:ss') "Script executed" from dual;


WHENEVER SQLERROR CONTINUE;
SET TERM OFF;
SPO /home/oracle/set_all_patch_blackouts.sh ;
set term on

PROMPT #!/bin/bash
PROMPT # ******************************************************************************
PROMPT # Script Name    : set_all_patch_blackouts.sh
PROMPT # Creator        : K. Kriebisch, AMIS Services b.v., Nieuwegein (NL)
PROMPT # Creation date  : 01-05-2020
PROMPT # Purpose        : bash script as executable result log to set EM Blackouts
PROMPT #                  
PROMPT # Usage          : set_all_patch_blackouts.sh
PROMPT # Remarks        : script uses EMCLI and is the output of the sql-script
PROMPT # 	          "Create_Monthly_Patch_Blackout_Script.sql", which determines 
PROMPT #                  the correct parameters of the create blackout commands for
PROMPT #                  each group of target hosts.
PROMPT #		            
PROMPT # RDBMS versions : applicable to all Linux versions
PROMPT #------------------------------------------------------------------------------\
PROMPT # Revision record
PROMPT # Date        Version  Author  Modification
PROMPT # ----------  ------   ------  ------------------------------------------------\
PROMPT # 01-05-2020  1.0      KK      Created
PROMPT # 01-06-2020  1.1      KK      switch to Notification BLs
PROMPT # 08-06-2020  1.2      KK      return to normal BLs 
PROMPT # 07-10-2020  1.3      KK      added BLs for shadow groups
PROMPT # 08-10-2020  1.4      KK      corrected logical error when adding shadow groups
PROMPT # 6-11-2020   1.5      KK      translated all comments into English
PROMPT #******************************************************************************
PROMPT #

-- adding commands to login into EMCLI

PROMPT export PATH=/usr/local/bin:$PATH
PROMPT export ORACLE_SID=OMS
PROMPT export ORAENV_ASK=NO
SPO off
set term off

DEF Min_voor = 15;
DEF Duur = 120;


set serveroutput off
set head off feedback off



COLUMN new_PT new_value PT

select to_char(next_day( (trunc(dt,'mm')-1), 'TUE' ) + 7, 'yyyy-mm-dd') as new_PT
from (select add_months( trunc(sysdate-30), rownum ) dt
from all_objects
where rownum = 1 );

--DEF PT = &new_PT;
set serveroutput on
set head on feedback on

WHENEVER SQLERROR CONTINUE;
SPO /home/oracle/set_all_patch_blackouts.sh APP;
set term on

PROMPT
PROMPT . oraenv
PROMPT export PASSWD=$(grep ${ORACLE_SID} /home/oracle/.sec/.passwdP|cut -d':' -f3 )
PROMPT emcli login -username=sysman -password=$PASSWD
PROMPT emcli sync
PROMPT 
PROMPT # Deleting the ended/stopped blackouts of last month/run first
PROMPT #
PROMPT # If there are no commands listed here, then the BLs are possibly stil active
PROMPT # and you have to check first and/or you have to remove them manually before trying again!

SPO off
set term off

WHENEVER SQLERROR CONTINUE;
-- If the following statement fails, this part of the script will stay empty 
set head off verify off feedback off
-- removing the blackouts of last month

SELECT  q'[emcli delete_blackout -name="]'||BLACKOUT_NAME||q'["]'
FROM "SYSMAN"."MGMT$BLACKOUTS" 
where STATUS in ('Ended', 'Stopped')
and BLACKOUT_NAME like 'PT%'
and REASON = 'OS Patch/Maintenance'
and created_BY = 'SYSMAN';

WHENEVER SQLERROR CONTINUE;
SPO /home/oracle/set_all_patch_blackouts.sh APP;
-- repeat last statement, now we know it works
set term on

/

set term off
WHENEVER SQLERROR CONTINUE;
set head on verify on feedback on
SPO /home/oracle/set_all_patch_blackouts.sh APP;
set term on
PROMPT emcli sync
PROMPT
PROMPT # Creating EMCLI-commands for all PT-groups 
PROMPT #
PROMPT # The start op the blackouts is set default to 15 min. before the given patch time,
PROMPT # and the duration is default 120 minutes.
PROMPT # Please adapt the parameter Min_voor and / or Duur (Dutch for duration) at the 
PROMPT # beginning of the script if Oracle patches should be applied before the OS patches 
PROMPT # and if before or after more time is necessary.
PROMPT
PROMPT # PatchTuesday is this month: &PT
SPO off

set term off

WHENEVER SQLERROR CONTINUE;
-- If the following statement fails, this part of the script will stay empty 
set head off verify off feedback off

With newBlackouts as
(select distinct(GROUP_NAME)
,to_char(to_date('&PT', 'yyyy-mm-dd') + to_number(substr(group_name, 4,2)), 'yyyy-mm-dd')||' '||to_char(to_date(substr(GROUP_NAME,-5), 'hh24:mi') - interval '&Min_voor' minute, 'hh24:mi') as PT_start
from SYSMAN.MGMT$GROUP_MEMBERS 
where group_name like ('PT-%')
and target_name not like ('nscloudcontrol%')
order by group_name)
select q'[emcli create_blackout -name="]'|| GROUP_NAME||q'[_BlackOut" -reason="OS Patch/Maintenance" -add_targets="]'||GROUP_NAME||q'[:group" -schedule="frequency:once;start_time:]'||PT_Start||q'[;duration::]'||&Duur||q'[" -full_blackout_all_hosts  -jobs_allowed]' as command
from newBlackouts;

WHENEVER SQLERROR CONTINUE;
SPO /home/oracle/set_all_patch_blackouts.sh APP;
-- repeat last statement, now we know it works
set term on
/

PROMPT 
PROMPT #  *** Creating EMCLI-commands for Blackout of the Shadow Groups (PTS-) ***

set term off
SPO off

WHENEVER SQLERROR CONTINUE;
-- If the following statement fails, this part of the script will stay empty 
set head off verify off feedback off

With newBlackouts as
(select distinct(GROUP_NAME)
,to_char(to_date('&PT', 'yyyy-mm-dd') + to_number(substr(group_name, 5,2)), 'yyyy-mm-dd')||' '||to_char(to_date(substr(GROUP_NAME,-5), 'hh24:mi') - interval '&Min_voor' minute, 'hh24:mi') as PT_start
from SYSMAN.MGMT$GROUP_MEMBERS
where group_name like ('PTS-%')
and target_name not like ('nscloudcontrol%')
order by group_name)
select q'[emcli create_blackout -name="]'|| GROUP_NAME||q'[_BlackOut" -reason="OS Patch/Maintenance" -add_targets="]'||GROUP_NAME||q'[:group" -schedule="frequency:once;start_time:]'||PT_Start||q'[;duration::]'||&Duur||q'[" -full_blackout_all_hosts  -jobs_allowed]' as command
from newBlackouts;

WHENEVER SQLERROR CONTINUE;
SPO /home/oracle/set_all_patch_blackouts.sh APP;
-- repeat last statement, now we know it works
set term on
/

set term off
SPO off

WHENEVER SQLERROR CONTINUE;
set head on verify on feedback on
SPO /home/oracle/set_all_patch_blackouts.sh APP;
set term on
PROMPT emcli sync
PROMPT emcli logout

set term off

SPO off
exit