Producing a grid report
Posted by John Hallas on June 21, 2010
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’
» Best Oracle Peformance Tools? said
[…] https://jhdba.wordpress.com/2010/09/03/using-grid-to-display-database-cpu-usage/ https://jhdba.wordpress.com/2010/06/21/producing-a-grid-report/ http://oracleobserver.com/?q=node/23 http://lianggang.wordpress.com/category/grid-control/ […]
John Wood said
The first textual syntax for the first script is incomplete.
satish said
I am trying to prepare standby log sequence check UDB for 11g Grid, its not throwing error, any post for that..?
John Hallas said
Not tried a specific UDM to check standby log sequences because we rely on the pre-built alert apply lag time.
That can be found from Choose database > Metric and Policy Settings > then first metric is Apply Lag (seconds) In order to see graph, you can go to all metrics > Data Guard Performance > Apply Lag (Seconds)
Hope that helps
John
Sandeep said
Hi John,
I am trying to user script to set up filesystem monitoring but I am not able to do that cos I dont have sysman.mor_filesystem_usage table/view. How do I go ahead with this.
I am using 11g grid control
Regards,
Sandeep
John Hallas said
Hi Sandeep, on my hols at the moment but will post the view DDL when I get back to work next week.
John
John Hallas said
DDL for the view sysman.mor_filesystem_usage listed below
sELECT target_guid,TARGET_NAME,MOUNTPOINT,
round(nvl(SIZEB,0)/1048576,2),round(nvl(USEDB,0)/1048576,2),(nvl(usedb,0)/nvl(sizeb,0))*100
FROM "SYSMAN"."MGMT$STORAGE_REPORT_LOCALFS"
where filesystem_type 'DevFS'
valiky said
Hey,
This is a very useful post, thank you.
I do have one question –
I can’t seem to find sysman.mor_all_backups in my OEM. Was it removed in later versions?
I’m trying to build a report that would show me primary – standby DataGuard pairs based on OEM’s repository views.
John Hallas said
It is a custom view, I will post the sq when I next logon to my works PC
Joe said
Hi John,
can I also ask for SYSMAN.MOR_ALL_BACKUPS view?
Thanks.
fouedgray said
thank you for sharing your knowledge, very valuable content.
Foued
Laura said
Hi
Can you tell me what kind of info you have in the sysman.mor_all_backups table? I assume this is a custom table you created with backup info, but I’d be interested in knowing exactly what info.
John Hallas said
Hi Laura, I wrote that entry in 2010 against a 10g OEM – we now use 12c and did not port some of those views across as we no longer needed them.
I cannot recall exactly what was in the view.
Sorry
John
Raheem said
The GRP script doesn’t work as I tried in the OEM and It doesn’t pull any data ? Any Idea.