SQL Script Check Grid Control Blackout Details

Updated: 28-Dec-2005
Version: 0.0

Description

Use this to debug any Grid Control Blackout problems

Parameters

SQL Source

REM Copyright (C) Think Forward.com 1998- 2005. All rights reserved. 

SET verify OFF
SET linesize 255
SET pagesize 128
SET trimout ON
SET trimspool ON

SPOOL em10g_chkblk.log

ALTER SESSION SET nls_date_format='MON-DD-YYYY hh:mi:ss pm';

column target_name format a40
column target_type format a32
column js format a10
column status format a15
column marker_status format a15
column bsts format a10
column bs format 99
column blackout_name format a30
column state format a15
column created_by format a32
column created_thru format a100

PROMPT ****************DUMP FOR BLACKOUT &&BLACKOUT_NAME ************
PROMPT ****************MGMT_BLACKOUTS***************************


SELECT blackout_name,created_by, created_thru,
       decode(blackout_status,
0, 'Scheduled',
                               1, 'StrtPrcg',
                               2, 'StartPtl',
                    3, 'StartFld',
                               4, 'Started',
                               5, 'Partial',
                              6, 'StopFld',
                               7, 'StopPtl',
                               8,'EditFld',
                               9, 'EditPtl',
                               10, 'Stopped',
              11, 'Ended',
                               12, 'EndPtl',
                               13, 'MdfyPndg') "BSTS",
      to_char(scheduled_time, 'DD-MON-YYYY hh:mi pm') "SCHED_TIME",
       to_char(last_start_time, 'DD-MON-YYYY hh:mi pm') "START_TIME",
      to_char(last_end_time, 'DD-MON-YYYY hh:mi pm') "END_TIME" 
FROM MGMT_BLACKOUTS WHERE blackout_name='&&BLACKOUT_NAME';

PROMPT ***************MGMT_BLACKOUT_TARGET_DETAILS*****************


SELECT target_name, target_type, t.load_timestamp,
     decode(edit_state, 0, 'No Edits',
                      1, 'Add',
                      2, 'Remove',
         3, 'Include Add',
                      4, 'Include Rem',
                      'Unknown') "STATE",
        include_members "INCLUDE"
 from MGMT_TARGETS t, MGMT_BLACKOUT_TARGET_DETAILS t1, MGMT_BLACKOUTS b 
WHERE t1.target_guid=t.target_guid AND
     b.blackout_guid=t1.blackout_guid AND
      b.blackout_name='&&BLACKOUT_NAME';


PROMPT ********************MGMT_BLACKOUT_FLAT_TARGETS******************

SELECT
target_name, target_type, 
       decode(job_status, 0, 'StartPndg',
                          1, 'StartFld',    
                 2, 'Started',
                          3, 'StopPndg',
                          4, 'StopFld',
                   5, 'Stopped',
                          'Unknown')"JS", t1.blackout_status "BS",
      decode(edit_state,0, 'No Edits',
                      1, 'Add',
                      2, 'Remove',
                      3, 'Include Add',
                     4, 'Include Rem',
                      'Unknown') "STATE" FROM 
MGMT_TARGETS t, MGMT_BLACKOUT_FLAT_TARGETS
t1, MGMT_BLACKOUTS b WHERE 
         t1.target_guid=t.target_guid AND
         b.blackout_guid=t1.blackout_guid AND
      b.blackout_name='&&BLACKOUT_NAME';


PROMPT ********************MGMT_BLACKOUT_WINDOWS*********************

SELECT
target_name, target_type, to_char(start_time,'DD-MON-YYYY hh:mi:ss pm') "ST", to_char(end_time,'DD-MON-YYYY hh:mi:ss pm') "ET",
w.occurrence_number "ONO",
decode(w.status, 0, 'Scheduled',
                 1, 'StrtPrcg',
                 2, 'StartPtl',
                3, 'StartFld',
                 4, 'Started',
                 5, 'Partial',
                 6, 'StopFld',
                7, 'StopPtl',
                 8, 'EditFld',
                 9, 'EditPtl',
                10, 'Stopped',
               11, 'Ended',
                12, 'EndPtl',
                13, 'MdfyPndg') "STATUS" FROM MGMT_BLACKOUT_WINDOWS
w, MGMT_TARGETS t, MGMT_BLACKOUTS b 
WHERE t.target_guid=w.target_guid AND
      b.blackout_guid=w.blackout_guid AND
    b.blackout_name='&&BLACKOUT_NAME'
ORDER BY start_time;

PROMPT **************** MGMT_BLACKOUT_STATE ******************

SELECT
 target_name, target_type, collection_timestamp "COLL_TS", 
        bs.load_timestamp "LOAD_TS", blackout_code
FROM    MGMT_BLACKOUT_STATE
bs, MGMT_BLACKOUTS b, MGMT_TARGETS t
WHERE   b.blackout_name='&&BLACKOUT_NAME'
AND     b.blackout_guid=bs.blackout_guid
AND bs.target_guid=t.target_guid
ORDER BY collection_timestamp, target_type, target_name;

PROMPT **************** MGMT_AVAILABILITY ******************
SELECT target_name, target_type, EM_SEVERITY.get_avail_string(current_status)  "STATUS"
, start_collection_timestamp "ST", end_collection_timestamp "ET" 
from MGMT_AVAILABILITY a, MGMT_TARGETS t, MGMT_BLACKOUTS b, MGMT_BLACKOUT_FLAT_TARGETS ft
WHERE
a.target_guid=t.target_guid AND
      t.target_guid=ft.target_guid AND
      ft.blackout_guid=b.blackout_guid AND
 b.blackout_name='&&BLACKOUT_NAME'
ORDER BY target_type, target_name, st;
    
PROMPT **************** MGMT_CURRENT_AVAILABILITY ******************
SELECT target_name, target_type, EM_SEVERITY.get_avail_string(current_status)  "STATUS", start_collection_timestamp "ST"
from MGMT_CURRENT_AVAILABILITY a, MGMT_TARGETS t, MGMT_BLACKOUTS b, MGMT_BLACKOUT_FLAT_TARGETS ft
WHERE a.target_guid=t.target_guid
AND
      t.target_guid=ft.target_guid AND
      ft.blackout_guid=b.blackout_guid AND
      b.blackout_name='&&BLACKOUT_NAME'
ORDER
BY target_type, target_name;

PROMPT **************** MGMT_AVAILABILITY_MARKER ******************
SELECT target_name, target_type,
marker_timestamp, EM_SEVERITY.get_avail_string(marker_avail_status)  "MARKER_STATUS" 
from MGMT_AVAILABILITY_MARKER a, MGMT_TARGETS t, MGMT_BLACKOUTS b, MGMT_BLACKOUT_FLAT_TARGETS ft
WHERE a.target_guid=t.target_guid AND
      t.target_guid=ft.target_guid
AND   ft.blackout_guid=b.blackout_guid 
AND   b.blackout_name='&&BLACKOUT_NAME'
ORDER BY target_type, target_name;

PROMPT
**************** MGMT_SEVERITY ******************
SELECT t.target_name, t.target_type, s.collection_timestamp, s.severity_code,
s.load_timestamp
  FROM MGMT_SEVERITY s, MGMT_METRICS m, MGMT_TARGETS t, MGMT_BLACKOUTS b
, MGMT_BLACKOUT_FLAT_TARGETS ft
WHERE s.target_guid=t.target_guid
   and s.metric_guid = m.metric_guid
   and t.target_type = m.target_type
   and t.type_meta_ver
= m.type_meta_ver
   and ( (t.category_prop_1 = m.category_prop_1) or (m.category_prop_1 = ' ') )
   and ( (t.category_prop_2 = m.category_prop_2) or (m.category_prop_2 = ' ') )
   and ( (t.category_prop_3 = m.category_prop_3) or (m.category_prop_3 = ' ') )
   and ( (t.category_prop_4 = m.category_prop_4) or (m.category_prop_4 = ' ') )
   and ( (t.category_prop_5 = m.category_prop_5)
or (m.category_prop_5 = ' ') )
   and m.metric_name = 'Response'
   and m.metric_column = 'Status'
   AND t.target_guid=ft.target_guid
   AND ft.blackout_guid=b.blackout_guid 
   AND b.blackout_name='&&BLACKOUT_NAME'
ORDER BY t.target_type, t.target_name,
s.collection_timestamp, s.load_timestamp;

PROMPT **************** MGMT_PURGE_POLICY_TARGET_STATE ******************
SELECT
t.target_name, t.target_type, p.target_retention_hours, p.rolledup_upto_time
  from MGMT_PURGE_POLICY_TARGET_STATE p, MGMT_TARGETS
t, MGMT_BLACKOUTS b, MGMT_BLACKOUT_FLAT_TARGETS ft
 WHERE p.target_guid=t.target_guid 
   AND t.target_guid=ft.target_guid
   AND ft.blackout_guid=b.blackout_guid 
   AND p.policy_name = 'MGMT_AVAILABILITY'
   AND b.blackout_name='&&BLACKOUT_NAME'
ORDER
BY target_type, target_name;


COLUMN STATUS FORMAT 99

PROMPT **************** START JOB INFORMATION *************************
SELECT job_name, j.job_id, e.status, e.scheduled_time
FROM    MGMT_JOB j, MGMT_JOB_EXEC_SUMMARY e, MGMT_BLACKOUTS b
WHERE   b.start_job_id=j.job_id
AND     j.job_id=e.job_id
AND     b.blackout_name='&&BLACKOUT_NAME';

SELECT  step_id, step_name, step_type, step_status,
start_time, 
        output_id, error_id
FROM    MGMT_JOB_EXECUTION e, MGMT_BLACKOUTS b
WHERE   e.job_id=b.start_job_id
AND     b.blackout_name='&&BLACKOUT_NAME'
ORDER BY step_id;

SET LONG 2048

SELECT  output
FROM    MGMT_JOB_OUTPUT
o, MGMT_JOB_EXECUTION e, MGMT_BLACKOUTS b
WHERE   (o.output_id=e.output_id OR
         o.output_id=e.error_id)
AND     b.start_job_id=e.job_id
AND     b.blackout_name='&&BLACKOUT_NAME';

PROMPT **************** END JOB INFORMATIO*************************
SELECT  job_name, j.job_id, e.status, e.scheduled_time
FROM    MGMT_JOB j, MGMT_JOB_EXEC_SUMMARY
e, MGMT_BLACKOUTS b
WHERE   b.end_job_id=j.job_id
AND     j.job_id=e.job_id
AND     b.blackout_name='&&BLACKOUT_NAME';

SELECT
 step_id, step_name, step_type, step_status, start_time,
        output_id, error_id
FROM    MGMT_JOB_EXECUTION e, MGMT_BLACKOUTS
b
WHERE   e.job_id=b.end_job_id
AND     b.blackout_name='&&BLACKOUT_NAME'
ORDER BY step_id;

SELECT  output
FROM
   MGMT_JOB_OUTPUT o, MGMT_JOB_EXECUTION e, MGMT_BLACKOUTS b
WHERE   (o.output_id=e.output_id OR
         o.output_id=e.error_id)
AND
    b.end_job_id=e.job_id
AND     b.blackout_name='&&BLACKOUT_NAME';

UNDEFINE BLACKOUT_NAME
SPOOL OFF

Related Links

Oracle 10g: (SQL Script) Oracle 10G Grid Control Management Repository Health Check

Return to Index of SQL Scripts

Site Map

General Information : Home | Company Profile | Contact Us | Legal
Database Support : Proactive Support | Pay Per Incident Support
Expert Services : High Availability | Creative Solutions | Training
Build Services : Oracle Installations | Revive your Oracle Setup | Low Cost Development
Free Oracle Resources: DBA Scripts | Oracle Links