Tags

Targets are routinely blacked out for patching and upgrades.  Most of those blackouts are either of a limited duration or the DBA stops the blackout when they’re done messing with the target.

Blackouts need to be deleted to keep your system tidy.  We toyed with the idea of deleting blackouts programatically, but some blackouts might be reused for recurring tasks.  So we resorted to nagging.  This query pulls a list of blackouts that are eiterh Stopped (by the DBA) or Ended (when their fixed duration expired)

SELECT a.target_name,
       a.blackout_name,
       b.description,
       b.last_end_time,
       TRUNC ( SYSDATE - b.last_end_time ) idle_days
 FROM  mgmt$blackout_history a, mgmt$blackouts b
 WHERE a.blackout_guid = b.blackout_guid
 AND   b.status IN ('Ended', 'Stopped')
 AND   b.schedule_type = 'One Time'
 ORDER BY target_name;

This query was built into a view and the view is called by an Information Publisher report and mailed to the DBA team.

The report includes a second table showing all active blackouts:

SELECT a.target_name,
     a.blackout_name,
     b.description,
     b.status,
     b.last_start_time
FROM mgmt$blackout_history a,
     mgmt$blackouts b
WHERE a.blackout_guid = b.blackout_guid
AND  b.status NOT IN ('Ended', 'Stopped')
AND  b.schedule_type = 'One Time'
ORDER BY target_name;