Oracle Enterprise Manager’s User-Defined Metrics (UDMs) were a very suitable vehicle for enforcing enterprise-wide policies. My NYOUG 2011/VirtaThon presentation and white paper contain a comprehensive example – monitoring forced logging status of all production databases.
Since UDMs are gone in OEM 12c, we’ll see how to use Metric Extensions, the successor of UDMs, to accomplish the same goal.
Metric Extensions (MEs) represent a major improvement over UDMs in terms of code maintenance and ability to deploy to large number of targets. MEs, however, do not allow us to customize the SQL query for a specific target. Even though there is a good reason behind the behavior, it requires us to substantially change the architecture we have previously used.
In the original presentation, I used one UDM ( ForcedLogging ) that had different definitions for different targets.
In OEM 12c, we’ll need to have different Metric Extensions for each of the definitions and target types.
For most single instance DB targets, we’ll use the standard “ForcedLogging” ME that is defined with this query:
select force_logging from v$database
For most RAC DB targets, we’ll use “ForcedLoggingRAC” ME that is defined with the same query:
select force_logging from v$database
And if we want to ignore the “force logging” status of a database, then we can use ForcedLoggingIgnore” ME defined with this query:
select 'YES' from v$database
The query/Metalized View that actually enforces the enterprise-wide force logging policy gets a little more complex:
CREATE MATERIALIZED VIEW "SYSMAN"."UDM_FORCED_LOGGING" ("CNT") ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "MGMT_TABLESPACE" BUILD IMMEDIATE USING INDEX REFRESH COMPLETE ON DEMAND START WITH sysdate+0 NEXT SYSDATE + 1/96 USING DEFAULT LOCAL ROLLBACK SEGMENT USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
AS
SELECT COUNT( member_target_guid ) cnt
FROM SYSMAN.MGMT$GROUP_DERIVED_MEMBERSHIPS O ,
SYSMAN.MGMT$TARGET T ,
sysman.MGMT$AVAILABILITY_CURRENT st
WHERE o.composite_target_name = 'PRODUCTION'
AND o.member_target_type IN ('oracle_database', 'rac_database')
AND ( t.target_type ='rac_database'
OR (t.target_type ='oracle_database'
AND t.type_qualifier3 != 'RACINST'))
AND o. member_target_guid = t.target_guid
AND t.target_guid = st.target_guid
AND st.availability_status = 'Target Up'
AND (NOT EXISTS
(SELECT *
FROM sysman.mgmt$metric_current i
WHERE I.TARGET_GUID = O.MEMBER_TARGET_GUID
AND metric_name = 'ME$ForcedLogging'
AND column_label = 'ForcedLogging'
AND Metric_Column = 'ForcedLogging'
AND collection_timestamp > sysdate - 30/1440
AND value = 'YES'
)
AND NOT EXISTS
(SELECT *
FROM sysman.mgmt$metric_current i
WHERE i.target_guid = o.member_target_guid
AND metric_name = 'ME$ForcedLoggingRAC'
AND column_label = 'ForcedLogging'
AND Metric_Column = 'ForcedLogging'
AND collection_timestamp > sysdate - 30/1440
AND value = 'YES'
)
AND NOT EXISTS
(SELECT *
FROM sysman.mgmt$metric_current i
WHERE i.target_guid = o.member_target_guid
AND metric_name = 'ME$ForcedLoggingIgnore'
AND column_label = 'ForcedLogging'
AND Metric_Column = 'ForcedLogging'
AND collection_timestamp > sysdate - 30/1440
AND value = 'YES'
));
Posted by iiotzov 
