Oracle DBA – A lifelong learning experience

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’

About these ads

10 Responses to “Producing a grid report”

  1. [...] 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/ [...]

  2. John Wood said

    The first textual syntax for the first script is incomplete.

  3. 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

  4. 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'

  5. 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 184 other followers

%d bloggers like this: