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
Using OEM reports to show PSU levels across the estate
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.
select
a.target_name, a.target_type, a.type_qualifier3, a.type_qualifier4, a.host_name, b.value, c.site
from
(select *
from mgmt$target
where
-- type_qualifier2 in('Primary','Physical Standby')
type_qualifier4 in('Primary','Physical Standby')) a,
mgmt$db_init_params b,
MORRDBA.SERVER_SITES c
where a.target_name = b.target_name
and a.target_name like ('%PRD%')
and b.name = '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
from MGMT$TARGET_PROPERTIES a,
(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')
SELECT
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"
FROM
(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
SELECT
m.target_name,
t.type_qualifier4 AS Role,
m.column_label AS Flashback,
m.value AS Status
FROM
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 ,
m.value
Oracle DBA - A lifelong learning experience 
