Producing a grid report

I mentioned that we have a morning check report that we run from OEM in this post . I was asked about it a couple of days ago so I thought I would post the contents as it may give some ideas on what can be monitored and how we use the report.

We check status on the following events and I have shown the code we use for each of the following reports

Usable space in FRA < 20%
Filesystems over 90% used
Database not backed up within 1 day and not blacked out and not a physical standby
Data Guard Status (targets not blacked out)
Alert Log Errors
Database Parameter Changes

The report can be seen by editing the report definition / elements (base it on one of the standard reports) and then selecting ‘sql parameters’

The sql code is produced below in the same order as the list above

SELECT TARGET_NAME “Database Name”,
NVL2(VALUE, VALUE, ‘No Data Available’) “Usable Flash Recovery Area (%)”,
COLLECTION_TIMESTAMP “Collected”

select “Host”, “Mount Point”, “Size MB”, “Used MB”, “Used %” from sysman.mor_filesystem_usage where “Used %” > 90
and target_guid not in (select distinct member_target_guid from MGMT$TARGET_FLAT_MEMBERS where aggregate_target_name in (‘E-Business’,’PeopleSoft’))
and “Mount Point” not like ‘/var’
and “Mount Point” not like ‘/var/adm/crash’
and “Mount Point” not like ‘/dm’
and “Mount Point” not like ‘/staging’
and “Mount Point” not like ‘/app/oradata/rman_backup_cutover’
and “Mount Point” not like ‘/home’
and “Mount Point” not like ‘/DEV_mrdw’
and “Mount Point” not like ‘/app/peoplesoft/hrws2’

select
host,
database_name,
status,
start_time,
end_time,
input_type,
output_device_type,
collected
from (select * from sysman.mor_all_backups where lower(db_type) <> ‘physical standby’
and database_name not like  —excluded list of databases goes here)
where ((status not in (‘COMPLETED’,’RUNNING’) or (status is null and end_time < SYSDATE -1) or end_time < SYSDATE -1) or end_time is null)
and target_guid not in (select distinct member_target_guid from MGMT$TARGET_FLAT_MEMBERS where aggregate_target_name in (‘E-Business’,’PeopleSoft’))
FROM SYSMAN.MGMT$METRIC_CURRENT
WHERE lower(metric_label) = ‘flash recovery’ and lower(metric_column)=’usable_area’
and value < 20
and target_guid not in (select distinct member_target_guid from MGMT$TARGET_FLAT_MEMBERS where aggregate_target_name in (‘E-Business’,’PeopleSoft’))
and TARGET_NAME not like ‘%ora10g%’

select
  mc.collection_timestamp,
  mab.database_name as primary,
  mc.key_value as standby,
  mc.value as Status
from
  sysman.mor_all_backups mab,
  (select collection_timestamp,target_name, value,key_value from sysman.mgmt$metric_current where lower(metric_name) = ‘dataguard’) mc
where mc.TARGET_NAME = mab.database_name
and lower(mab.db_type) <> ‘physical standby’
and mc.value <>’_$_$’
and target_guid not in (select distinct member_target_guid from MGMT$TARGET_FLAT_MEMBERS where aggregate_target_name in (‘E-Business’,’PeopleSoft’))
and database_name not like ‘DBAPIT1A’

select
maa.TARGET,maa.CATEGORY,maa.TIME,maa.ERROR
from sysman.MOR_ACTIVE_ALERTS maa
where target_guid not in (select distinct member_target_guid from MGMT$TARGET_FLAT_MEMBERS where aggregate_target_name in (‘E-Business’,’PeopleSoft’))

select
deltatime “Date”,
target_name “Database”,
hostname “Host”,
operation “Operation”,
key1 “Parameter”,
attribute “Attribute”,
oldvalue “Old Vlue”,
newvalue “New Value”
from MGMT$ECM_CONFIG_HISTORY_KEY1
where target_name like ‘%PRD%’
and deltatime >= sysdate -1
and collectiontype=’MGMT_DB_INIT_PARAMS_ECM’
and key1 !=’resource_manager_plan’