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’

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

  6. fouedgray said

    thank you for sharing your knowledge, very valuable content.
    Foued

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

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 264 other followers

%d bloggers like this: