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