Oracle DBA – A lifelong learning experience

Valuable BAU OEM reports – some examples

Posted by John Hallas on July 18, 2014

A well configured OEM 12c (or any previous version of your choice) is a fantastic enterprise tool and the reporting capabilities are a major asset.

I am going to share three examples of reports we use and find valuable and if anybody has any  other good ideas I am happy  to hear about them.

I don’t think I wrote any of them but I almost certainly identified the requirement and instigated the creation of each report. I have masked out obvious details so the screen shots do look messy.

For anybody new to OEM reports I blogged a post about creating a new report some time ago

Location of Primary and Standby databases

Like many organisations we have 2 data centres and we use DataGuard extensively. We have a naming convention that has the 8th character of the database name being either A or B which signifies Primary or Standby database. Over time as we do failovers and move things around you cannot always assume that A is the primary database. So I wanted a way of telling quickly what was impacted if we had issues at one site or another.

The report is produced daily and emailed out to the DBA team. In the event of a DR that is our first point of call to see what work is required. The name of each site shows against the server name. This can be achieved because we have a file on a Unix server that contains all the server names and their site location. We create a small table (server_sites)  in the OEM database with the name of the production servers in and the site and then we can join it to the mgmt$target table. We don’t add new production servers that often so it is not too much of an overhead to maintain the table.

a.target_name, a.target_type, a.type_qualifier3, a.type_qualifier4, a.host_name, b.value,
(select *
from mgmt$target
-- type_qualifier2 in('Primary','Physical Standby')
type_qualifier4 in('Primary','Physical Standby')) a,
mgmt$db_init_params b,
where a.target_name = b.target_name
and a.target_name like ('%PRD%')
and = 'dg_broker_start'
and c.server = a.host_name
order by target_name


Guaranteed Restore Points

The availability of guaranteed restore points is a great idea and we use it a lot, especially before data fixes and new code releases. We will create a GRP, apply the code pack, update data and then smoke test. If all fails miserably then we have a quick and easy way to get back, as well as the normal backup and recovery methods. A true belt and braces approach. The downside is that it is easy to forget to remove the GPR and then we have problems with the FRA running out of space.

with db_locations as
(select a.target_guid, a.property_value DB_NAME, b.db_location, c.lifecycle_status
(select target_guid, lower(property_value) db_location from MGMT$TARGET_PROPERTIES 
where property_name = 'orcl_gtp_location') b,
(select target_guid, lower(property_value) lifecycle_status from MGMT$TARGET_PROPERTIES 
where property_name = 'orcl_gtp_lifecycle_status') c
where a.target_guid = b.target_guid
and a.target_guid = c.target_guid
and b.target_guid = c.target_guid
and a.property_name = 'DBName')
m.target_name "Database Name",
t.type_qualifier4  AS Role,
m.key_value     AS "Restore Point Name and Date",
ct.db_location "Location",
ct.lifecycle_status "Lifecycle"
(select target_guid, target_name, metric_label, key_value 
from mgmt$alert_current ac 
where exists (select e.event_id from mgmt$events e 
              where e.event_id = ac.event_instance_id 
              and e.open_status = 1)) m,
mgmt$target t,
(select dbl.*
from db_locations dbl
where dbl.db_location = 'core'
union all
select dbl.*
from db_locations dbl
where dbl.db_location is null 
and substr(dbl.db_name,1,3) in (select distinct substr(db_name,1,3) from db_locations where db_location = 'core')
order by 1,2) ct
WHERE m.metric_label in ('Core_Restore_Point_Check_NON_RAC','Core_Restore_Point_Check_RAC')
AND t.target_name=m.target_name
AND t.target_guid = ct.target_guid
and ct.lifecycle_status like ('%production%')
AND t.target_guid=m.target_guid
order by m.target_name,m.key_value

Databases with no flashback enabled

Reasonably self-evident and all databases should have flashback enabled so it is good to pick up on those that have not got it enabled

t.type_qualifier4  AS Role,
m.column_label     AS Flashback,
m.value            AS Status
mgmt$metric_current m,
mgmt$target t
WHERE m.metric_label = 'Flash Recovery'
AND m.column_label = 'Flashback On'
AND m.value = 'NO'
AND m.target_name like '%PRD%'
AND t.type_qualifier4 in('Primary','Physical Standby')
AND t.target_name=m.target_name
AND t.target_guid=m.target_guid
order by t.type_qualifier4 ,



One Response to “Valuable BAU OEM reports – some examples”

  1. helios said

    Its very useful. Nice work

Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: