ORACLE EM DBA

Problems, Solutions, Test Cases from my professional and personal experience in technologies like Oracle Enterprise Manager 13 c,Oracle Enterprise Manager 12c, Oracle Configuration Manager and some other stuff.

Month: March 2015

How to use EM12c Repository Views to get Database Storage Details

Hi All,

Recently I was caught in a situation when there was a sudden increase in my PeopleSoft financials database. My Manager and SystemAdmin asks me to provide them
“database storage trends over the last 6 months” and expected “6 to 12 month database future growth report”.

It will be very hectic if you have to do this again and again unless you built some tables, procedures and views based on the data in Enterprise Manager. In this blog
of mine I have created some of the usefull tables using which you can easily provide data to your reporting lead or Infrastructure Team.

In this demo we have divided the storage into three different categories and we will create three separate tables for them. The data will be populated in them using the
procedures which will be calling original tables from “SYSMAN” schema and the final data is selected by using the 3 different views.
Mentioned below are the details of used Tables:
=====================================================
DATABASE_STORAGE_USAGE- will contain the current database storage as of the last run of the stored procedure.
HISTORICAL_STORAGE_USAGE- will contain the historical data for the all months, target database instance has been in EM12c.
FUTURE_STORAGE_USAGE- will contain all the database storage since the database target has been in EM12c.

The output of the future storage is based on a view which you can modify based on your storage requirements.

Mentioned below are the details of used Procedures:
=====================================================
DB_STORAGE- This procedure populates the DATABASE_STORAGE_USAGE table with all the current storage.
HISTORICAL_STORAGE- This procedure populates the HISTORICAL_STORAGE_USAGE table with all the storage since the database instance target was add into Enterprise Manager.
FUTURE_STORAGE- This procedure populates the HISTORICAL_STORAGE_USAGE table with all the storage since the database instance target was add into Enterprise Manager.

Mentioned below are the details of used View:
================================================
CURRENT_DB_STORAGE_USAGE

This view not only shows the current storage usage allocated space, used space and allocated free space but also shows the usage in percent.  DATAPUMP and RMAN space usage has also been included in the last two columns. The RMAN column is allocated_space * 0.30 and the export is based on allocated space * 0.15.  The same can be modified as per the requirements.

HISTORICAL_DB_STORAGE_USAGE

This view shows historical database storage it shows allocated space, used space and allocated free space addition to that, the percent used space can also be viewed where each row is the first of every month for each database instance target in Enterprise Manager.

FUTURE_DB_STORAGE_USAGE

This view shows future database growth based on the max allocated_space -min allocated_space *12 which you can modify as per requirement.  The back 12 month is based on max allocated space -min allocated space *12*0.30. You may ask why 0.30 well I compress my RMAN backups and if your database is not full of lobs “large objects”, RMAN has almost a 70% compression rate. The export 12 months backup is based on {maximum allocated space – minimum allocated space} *12*0.15.

The used space is base on {maximum used space – minimum used space} * 12.

 Before we proceed further grant below mentioned PRIVILEGES to REP_USER user which owns tables, views and procedures

GRANT SELECT ON SYSMAN.MGMT$DB_INIT_PARAMS TO REP_USER;
GRANT SELECT ON SYSMAN.MGMT$DB_TABLESPACES TO REP_USER;
GRANT SELECT ON SYSMAN.MGMT$METRIC_DAILY TO REP_USER;
GRANT SELECT ON SYSMAN.MGMT$TARGET_TYPE TO REP_USER;
GRANT SELECT ON SYSMAN.MGMT_TARGETS TO REP_USER;
GRANT SELECT ON SYSMAN.MGMT_TARGET_PROPERTIES TO REP_USER;

Mentioned below are the scripts to create the tables, procedures and views. The schema being used in this demo is “REP_USER“,  you can modify as per your environment.

——————————————————–
— DDL for Table DATABASE_STORAGE_USAGE
——————————————————–

CREATE TABLE “REP_USER”.”DATABASE_STORAGE_USAGE”
( “TARGET_GUID” VARCHAR2(40 BYTE),
“ALLOCATED_SPACE_GB” VARCHAR2(10 BYTE),
“USED_SPACE_GB” VARCHAR2(10 BYTE),
“ALLOCATED_FREE_SPACE_GB” VARCHAR2(10 BYTE)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 0 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 “REP_USER_DATA” ;
COMMENT ON COLUMN “REP_USER”.”DATABASE_STORAGE_USAGE”.”ALLOCATED_SPACE_GB” IS ‘Total physical space used in GB’;
COMMENT ON COLUMN “REP_USER”.”DATABASE_STORAGE_USAGE”.”USED_SPACE_GB” IS ‘Total logical space used in GB’;
COMMENT ON COLUMN “REP_USER”.”DATABASE_STORAGE_USAGE”.”ALLOCATED_FREE_SPACE_GB” IS ‘Total free physical space in GB’;
——————————————————–
— DDL for Table FUTURE_STORAGE_USAGE
——————————————————–
CREATE TABLE “REP_USER”.”FUTURE_STORAGE_USAGE”
( “TARGET_GUID” VARCHAR2(40 BYTE),
“ALLOCATED_SPACE_GB” NUMBER,
“USED_SPACE_GB” NUMBER,
“ALLOCATED_FREE_SPACE_GB” NUMBER,
“CALENDAR_MONTH” DATE,
“USED_PCT” NUMBER
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 0 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 “REP_USER_DATA” ;
COMMENT ON COLUMN “REP_USER”.”FUTURE_STORAGE_USAGE”.”ALLOCATED_SPACE_GB” IS ‘Total physical space used in GB’;
COMMENT ON COLUMN “REP_USER”.”FUTURE_STORAGE_USAGE”.”USED_SPACE_GB” IS ‘Total logical space used in GB’;
COMMENT ON COLUMN “REP_USER”.”FUTURE_STORAGE_USAGE”.”ALLOCATED_FREE_SPACE_GB” IS ‘Total free physical space in GB’;
——————————————————–
— DDL for Table HISTORICAL_STORAGE_USAGE
——————————————————–
CREATE TABLE “REP_USER”.”HISTORICAL_STORAGE_USAGE”
( “TARGET_GUID” VARCHAR2(40 BYTE),
“ALLOCATED_SPACE_GB” NUMBER,
“USED_SPACE_GB” NUMBER,
“ALLOCATED_FREE_SPACE_GB” NUMBER,
“CALENDAR_MONTH” DATE,
“USED_PCT” NUMBER
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 0 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 “REP_USER_DATA” ;
COMMENT ON COLUMN “REP_USER”.”HISTORICAL_STORAGE_USAGE”.”ALLOCATED_SPACE_GB” IS ‘Total physical space used in GB’;
COMMENT ON COLUMN “REP_USER”.”HISTORICAL_STORAGE_USAGE”.”USED_SPACE_GB” IS ‘Total logical space used in GB’;
COMMENT ON COLUMN “REP_USER”.”HISTORICAL_STORAGE_USAGE”.”ALLOCATED_FREE_SPACE_GB” IS ‘Total free physical space in GB’;
——————————————————–
— DDL for Index TARGET_GUID_PK
——————————————————–
CREATE UNIQUE INDEX “REP_USER”.”TARGET_GUID_PK” ON “REP_USER”.”DATABASE_STORAGE_USAGE” (“TARGET_GUID”)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
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 “REP_USER_DATA” ;
——————————————————–
— Constraints for Table DATABASE_STORAGE_USAGE
——————————————————–
ALTER TABLE “REP_USER”.”DATABASE_STORAGE_USAGE” ADD CONSTRAINT “TARGET_GUID_PK” PRIMARY KEY (“TARGET_GUID”)
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
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 “REP_USER_DATA” ENABLE;
——————————————————–
— DDL for Procedure DB_STORAGE
——————————————————–
set define off;
CREATE OR REPLACE PROCEDURE “REP_USER”.”DB_STORAGE” IS
tmpVar NUMBER;
CURSOR get_target_guid
IS
select target_guid
from mgmt_targets
where target_type = ‘oracle_database’;
CURSOR get_storage (v_target_guid VARCHAR2)
IS
select round (sum (t.tablespace_size / 1024 / 1024 / 1024), 2)
as allocated_gb,
round (sum (t.tablespace_used_size / 1024 / 1024 / 1024), 2)
as used_gb,
round (
sum (
(t.tablespace_size – tablespace_used_size) / 1024 / 1024 / 1024),
2)
as allocated_free_gb
from mgmt$db_tablespaces t,
(select target_guid
from mgmt_targets
where target_guid = v_target_guid
and (target_type = ‘oracle_database’)) tg
where t.target_guid = tg.target_guid;
v_target_guid VARCHAR2 (50);
v_allocated_space VARCHAR2 (20);
v_used_gb VARCHAR2 (20);
v_allocate_free_gb VARCHAR2 (20);
BEGIN
tmpVar := 0;
execute immediate ‘truncate table DATABASE_STORAGE_USAGE’;
FOR c1 IN get_target_guid
LOOP
v_target_guid := c1.target_guid;
FOR c2 IN get_storage (v_target_guid)
LOOP
v_allocated_space:=c2.allocated_gb;
v_used_gb:=c2.used_gb;
v_allocate_free_gb:=c2.allocated_free_gb;
insert into DATABASE_STORAGE_USAGE
values (v_target_guid,v_allocated_space,v_used_gb,v_allocate_free_gb);
commit;
END LOOP;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
RAISE;
END DB_STORAGE;
/
——————————————————–
— DDL for Procedure FUTURE_STORAGE
——————————————————–
set define off;
CREATE OR REPLACE PROCEDURE “REP_USER”.”FUTURE_STORAGE” IS
tmpVar NUMBER;
CURSOR get_target_guid
IS
select target_guid
from mgmt_targets
where target_type = ‘oracle_database’;
CURSOR get_storage (v_target_guid VARCHAR2)
IS
SELECT
TO_DATE(TO_CHAR(a_size.month_timestamp,’MON RR’),’MON RR’) AS CALENDAR_MONTH,
round(avg(a_size.size_gb),2) AS allocated_gb,
round(avg(used.used_gb),2) AS USED_GB,
round(avg(a_size.size_gb – used.used_gb),2) AS allocated_FREE_GB,
round(avg((used.used_gb*100)/
decode(a_size.size_gb,0,1,a_size.size_gb))
,2) AS USED_PCT
FROM
(SELECT
m.rollup_timestamp AS month_timestamp,
sum(m.average/1024) AS size_gb
FROM
mgmt$metric_daily m,
mgmt$target_type t
WHERE
t.target_guid=HEXTORAW(v_target_guid) AND
(t.target_type=’rac_database’ OR
(t.target_type=’oracle_database’ AND t.TYPE_QUALIFIER3 != ‘RACINST’)) AND
m.target_guid=t.target_guid AND
m.metric_guid=t.metric_guid AND
t.metric_name=’tbspAllocation’ AND
(t.metric_column=’spaceAllocated’) AND
m.rollup_timestamp >= sysdate-365 and
m.rollup_timestamp <= sysdate
GROUP BY m.metric_column, m.rollup_timestamp) a_size,
(SELECT
m.rollup_timestamp AS month_timestamp,
sum(m.average/1024) AS used_gb
FROM
mgmt$metric_daily m,
mgmt$target_type t
WHERE
t.target_guid=HEXTORAW(v_target_guid) AND
(t.target_type=’rac_database’ OR
(t.target_type=’oracle_database’ AND t.TYPE_QUALIFIER3 != ‘RACINST’)) AND
m.target_guid=t.target_guid AND
m.metric_guid=t.metric_guid AND
t.metric_name=’tbspAllocation’ AND
(t.metric_column=’spaceUsed’) AND
m.rollup_timestamp >= sysdate-365 and
m.rollup_timestamp <= sysdate
GROUP BY m.metric_column, m.rollup_timestamp) used
WHERE
a_size.month_timestamp =used.month_timestamp
GROUP BY TO_CHAR(a_size.month_timestamp,’MON RR’);
v_target_guid VARCHAR2 (50);
v_sid VARCHAR2(50);
v_allocated_space VARCHAR2 (20);
v_used_gb VARCHAR2 (20);
v_allocate_free_gb VARCHAR2 (20);
v_calendar_month VARCHAR2 (20);
v_used_pct VARCHAR2 (20);
BEGIN
tmpVar := 0;
execute immediate ‘truncate table FUTURE_STORAGE_USAGE’;
FOR c1 IN get_target_guid
LOOP
v_target_guid := c1.target_guid;
FOR c2 IN get_storage (v_target_guid)
LOOP
v_allocated_space:=c2.allocated_gb;
v_used_gb:=c2.used_gb;
v_allocate_free_gb:=c2.allocated_free_gb;
v_calendar_month := c2.calendar_month;
v_used_pct := c2.used_pct;
insert into FUTURE_STORAGE_USAGE
values (v_target_guid,v_allocated_space,v_used_gb,v_allocate_free_gb,v_calendar_month,v_used_pct);
commit;
END LOOP;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
RAISE;
END FUTURE_STORAGE;
/
——————————————————–
— DDL for Procedure HISTORICAL_STORAGE
——————————————————–
set define off;
CREATE OR REPLACE PROCEDURE “REP_USER”.”HISTORICAL_STORAGE” IS
tmpVar NUMBER;
CURSOR get_target_guid
IS
select target_guid
from mgmt_targets
where target_type = ‘oracle_database’;
CURSOR get_storage (v_target_guid VARCHAR2)
IS
SELECT
TO_DATE(TO_CHAR(a_size.month_timestamp,’MON RR’),’MON RR’) AS CALENDAR_MONTH,
round(avg(a_size.size_gb),2) AS allocated_gb,
round(avg(used.used_gb),2) AS USED_GB,
round(avg(a_size.size_gb – used.used_gb),2) AS allocated_FREE_GB,
round(avg((used.used_gb*100)/
decode(a_size.size_gb,0,1,a_size.size_gb))
,2) AS USED_PCT
FROM
(SELECT
m.rollup_timestamp AS month_timestamp,
sum(m.average/1024) AS size_gb
FROM
mgmt$metric_daily m,
mgmt$target_type t
WHERE
t.target_guid=HEXTORAW(v_target_guid) AND
(t.target_type=’rac_database’ OR
(t.target_type=’oracle_database’ AND t.TYPE_QUALIFIER3 != ‘RACINST’)) AND
m.target_guid=t.target_guid AND
m.metric_guid=t.metric_guid AND
t.metric_name=’tbspAllocation’ AND
(t.metric_column=’spaceAllocated’) AND
m.rollup_timestamp >= sysdate-365 and
m.rollup_timestamp <= sysdate
GROUP BY m.metric_column, m.rollup_timestamp) a_size,
(SELECT
m.rollup_timestamp AS month_timestamp,
sum(m.average/1024) AS used_gb
FROM
mgmt$metric_daily m,
mgmt$target_type t
WHERE
t.target_guid=HEXTORAW(v_target_guid) AND
(t.target_type=’rac_database’ OR
(t.target_type=’oracle_database’ AND t.TYPE_QUALIFIER3 != ‘RACINST’)) AND
m.target_guid=t.target_guid AND
m.metric_guid=t.metric_guid AND
t.metric_name=’tbspAllocation’ AND
(t.metric_column=’spaceUsed’) AND
m.rollup_timestamp >= sysdate-365 and
m.rollup_timestamp <= sysdate
GROUP BY m.metric_column, m.rollup_timestamp) used
WHERE
a_size.month_timestamp =used.month_timestamp
GROUP BY TO_CHAR(a_size.month_timestamp,’MON RR’);
v_target_guid VARCHAR2 (50);
v_sid VARCHAR2(50);
v_allocated_space VARCHAR2 (20);
v_used_gb VARCHAR2 (20);
v_allocate_free_gb VARCHAR2 (20);
v_calendar_month VARCHAR2 (20);
v_used_pct VARCHAR2 (20);
BEGIN
tmpVar := 0;
execute immediate ‘truncate table HISTORICAL_STORAGE_USAGE’;
FOR c1 IN get_target_guid
LOOP
v_target_guid := c1.target_guid;
FOR c2 IN get_storage (v_target_guid)
LOOP
v_allocated_space:=c2.allocated_gb;
v_used_gb:=c2.used_gb;
v_allocate_free_gb:=c2.allocated_free_gb;
v_calendar_month := c2.calendar_month;
v_used_pct := c2.used_pct;
insert into HISTORICAL_STORAGE_USAGE
values (v_target_guid,v_allocated_space,v_used_gb,v_allocate_free_gb,v_calendar_month,v_used_pct);
commit;
END LOOP;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
RAISE;
END HISTORICAL_STORAGE;
/

DDL for inolved Views

====================

——————————————————–
— DDL for View CURRENT_DB_STORAGE_USAGE
——————————————————–
CREATE OR REPLACE FORCE VIEW “REP_USER”.”CURRENT_DB_STORAGE_USAGE” (“DB_NAME”, “CALENDAR_MONTH”, “ALLOCATED_SPACE_GB”, “USED_SPACE_GB”, “ALLOCATED_FREE_SPACE_GB”, “USED_PCT”, “backup_space_used_rman”, “backup_space_used_export”) AS
select c.VALUE AS DB_NAME ,to_char(calendar_month,’yyyy-mm-dd’) as calendar_month,
ceil(allocated_space_gb) as allocated_space_gb,
ceil(used_space_gb) as used_space_gb,
ceil(allocated_free_space_gb) as allocated_free_space_gb,
used_pct,
ceil(allocated_space_gb*0.30) as “backup_space_used_rman”,ceil(used_space_gb*0.15) as”backup_space_used_export” from HISTORICAL_STORAGE_USAGE a,
(SELECT value,target_guid
FROM MGMT$DB_INIT_PARAMS
WHERE name = ‘db_name’) c
where a.target_guid = c.target_guid
order by 1 asc,2 asc;
——————————————————–
— DDL for View FUTURE_DB_STORAGE_USAGE
——————————————————–
CREATE OR REPLACE FORCE VIEW “REP_USER”.”FUTURE_DB_STORAGE_USAGE” (“DB_NAME”, “12_month_growth_gb”, “12_month_backup_gb”, “export_12_month_backup_gb”, “used_12_month_growth_gb”) AS
select c.VALUE AS DB_NAME ,
ceil(max(allocated_space_gb)-min(allocated_space_gb))*12as “12_month_growth_gb”,
ceil(max(allocated_space_gb)-min(allocated_space_gb))*12*0.30 as “12_month_backup_gb”,
ceil(max(used_space_gb)-min(used_space_gb))*12*0.15 as “export_12_month_backup_gb”,
ceil(max(used_space_gb)-min(used_space_gb))*12as “used_12_month_growth_gb” from
FUTURE_STORAGE_USAGE a,
(SELECT value,target_guid
FROM MGMT$DB_INIT_PARAMS
WHERE name = ‘db_name’) c
where a.target_guid = c.target_guid
and calendar_month between trunc(to_date(sysdate), ‘MONTH’)-31 and trunc(to_date(sysdate), ‘MONTH’)
group by c.VALUE
ORDER BY 1 ASC;
——————————————————–
— DDL for View HISTORICAL_DB_STORAGE_USAGE
——————————————————–
CREATE OR REPLACE FORCE VIEW “REP_USER”.”HISTORICAL_DB_STORAGE_USAGE”
(“DB_NAME”, “ALLOCATED_SPACE_GB”, “USED_SPACE_GB”, “ALLOCATED_FREE_SPACE_GB”, “CALENDAR_MONTH”, “USED_PCT”) AS
select c.VALUE AS DB_NAME,
allocated_space_gb,
used_space_gb,
allocated_free_space_gb,
calendar_month,used_pct
from HISTORICAL_STORAGE_USAGE a,
(SELECT VALUE, target_guid
FROM MGMT$DB_INIT_PARAMS
WHERE name = ‘db_name’) c
where a.target_guid = c.target_guid
order by 1,4 asc;

Now you can use these views to fetch data as per your requirement. Hope this will save your time and help you to easily provide information.

Thanks

Deepak

Server generated alerts like “Metrics “Database Time Spent Waiting (%)”,” RESPONSE_TXN” etc” can not be disabled in EM12c.

Server generated alerts like "Metrics "Database Time Spent Waiting (%)"," RESPONSE_TXN" etc" can not be disabled in EM12c..

Server generated alerts like “Metrics “Database Time Spent Waiting (%)”,” RESPONSE_TXN” etc” can not be disabled in EM12c.

Hi Everyone.

I this post of mine I am sharing one of the BUGS which I again came across when I faced this issue in my Environment.

I was in process of prioritizing my Monitoring Template by disabling some non-required Alerts for databases in my Organization. I wanted to omit or disable some of the “SYSTEM GENERATED ALERTS”  which was triggered by EM and I was sent alert for same very frequently.

My OMS version was :12.1.0.3.0

Oracle Agent version was: 12.1.0.3.0

I followed simple method, I created a new template and put it under Template Collection for my Administration Group and associate it with Respective group. After this I did the manual Synchronization.

However after doing all this I was still getting email alerts for the metrics which were no longer in my Monitoring template.

The Alert looked like this:
————————–
Host=xxxbidb5.xxx.com
Target type=Database Instance
Target name=BIPRD
Categories=Load
Message=Metrics “Database Time Spent Waiting (%)” is at 76.625 for event class “Concurrency”
Severity=Warning
Event reported time=Mar 8, 2014 4:34:25 PM CST
Target Lifecycle Status=Production
Line of Business=BI
Department=DBA
Operating System=Linux
Platform=x86_64
Associated Incident Id=29735
Associated Incident Status=New
Associated Incident Owner=
Associated Incident Acknowledged By Owner=No
Associated Incident Priority=None
Associated Incident Escalation Level=0
Event Type=Metric Alert
Event name=wait_sess_cls:dbtime_waitclass_pct
Metric Group=Waits by Wait Class
Metric=Database Time Spent Waiting (%)
Metric value=76.6250232644705
Key Value=Concurrency
Key Column 1=Wait Class
Rule Name=RuleSet for all Production Targets,Incident creation Rule for metric alerts.
Rule Owner=DKSHARMA
Update Details:
Metrics “Database Time Spent Waiting (%)” is at 76.625 for event class “Concurrency”
Incident created by rule (Name = RuleSet for all Production Targets, Create incident for critical metric alerts; Owner = DKSHARMA).

So when GUI did not work, I tried to do from the back end by executing below mentioned package:-

exec DBMS_SERVER_ALERT.SET_THRESHOLD(DBMS_SERVER_ALERT.DB_TIME_WAITING, NULL, NULL, NULL, NULL, NULL, NULL, ‘BIPRD’,DBMS_SERVER_ALERT.OBJECT_TYPE_EVENT_CLASS, ‘System I/O’);
exec DBMS_SERVER_ALERT.SET_THRESHOLD(DBMS_SERVER_ALERT.DB_TIME_WAITING, NULL, NULL, NULL, NULL, NULL, NULL, ‘BIPRD’,DBMS_SERVER_ALERT.OBJECT_TYPE_EVENT_CLASS, ‘Concurrency’);
exec DBMS_SERVER_ALERT.SET_THRESHOLD(DBMS_SERVER_ALERT.DB_TIME_WAITING, NULL, NULL, NULL, NULL, NULL, NULL, ‘BIPRD’,DBMS_SERVER_ALERT.OBJECT_TYPE_EVENT_CLASS, ‘Other’);
exec DBMS_SERVER_ALERT.SET_THRESHOLD(DBMS_SERVER_ALERT.DB_TIME_WAITING, NULL, NULL, NULL, NULL, NULL, NULL, ‘BIPRD’,DBMS_SERVER_ALERT.OBJECT_TYPE_EVENT_CLASS, ‘Network’);
exec DBMS_SERVER_ALERT.SET_THRESHOLD(DBMS_SERVER_ALERT.DB_TIME_WAITING, NULL, NULL, NULL, NULL, NULL, NULL, ‘BIPRD’,DBMS_SERVER_ALERT.OBJECT_TYPE_EVENT_CLASS, ‘Configuration’);
exec DBMS_SERVER_ALERT.SET_THRESHOLD(DBMS_SERVER_ALERT.DB_TIME_WAITING, NULL, NULL, NULL, NULL, NULL, NULL, ‘BIPRD’,DBMS_SERVER_ALERT.OBJECT_TYPE_EVENT_CLASS, ‘Commit’);
exec DBMS_SERVER_ALERT.SET_THRESHOLD(DBMS_SERVER_ALERT.DB_TIME_WAITING, NULL, NULL, NULL, NULL, NULL, NULL, ‘BIPRD’,DBMS_SERVER_ALERT.OBJECT_TYPE_EVENT_CLASS, ‘Network’);
exec DBMS_SERVER_ALERT.SET_THRESHOLD(DBMS_SERVER_ALERT.DB_TIME_WAITING, NULL, NULL, NULL, NULL, NULL, NULL, ‘BIPRD’,DBMS_SERVER_ALERT.OBJECT_TYPE_EVENT_CLASS, ‘Cluster’);
exec DBMS_SERVER_ALERT.SET_THRESHOLD(DBMS_SERVER_ALERT.DB_TIME_WAITING, NULL, NULL, NULL, NULL, NULL, NULL, ‘BIPRD’,DBMS_SERVER_ALERT.OBJECT_TYPE_EVENT_CLASS, ‘Application’);
exec DBMS_SERVER_ALERT.SET_THRESHOLD(DBMS_SERVER_ALERT.DB_TIME_WAITING, NULL, NULL, NULL, NULL, NULL, NULL, ‘BIPRD’,DBMS_SERVER_ALERT.OBJECT_TYPE_EVENT_CLASS, ‘Administrative’);

Support asked me to crosscheck the database plugin and registered targets so I did as well.

(/app/oracle/product/agent12c/agent_inst/bin)
oracle@xxxbidb5 $ ./emctl config agent listtargets
Oracle Enterprise Manager Cloud Control 12c Release 3
Copyright (c) 1996, 2013 Oracle Corporation. All rights reserved.
[xxxbidb5.xxx.com, host]
[xxxbidb5.xxx.com:1830, oracle_emd]
[OraDb11g_home1_1_xxxbidb5, oracle_home]
[agent12c2_2_xxxbidb5, oracle_home]
[BIPRD, oracle_database]

() (/app/oracle/product/agent12c/agent_inst/bin)
oracle@xxxbidb5 $ ./emctl listplugins agent
Oracle Enterprise Manager Cloud Control 12c Release 3
Copyright (c) 1996, 2013 Oracle Corporation. All rights reserved.
—————————————————————
oracle.sysman.db 12.1.0.4.0 /app/oracle/product/agent12c/plugins/oracle.sysman.db.agent.plugin_12.1.0.4.0
oracle.sysman.oh 12.1.0.3.0 /app/oracle/product/agent12c/plugins/oracle.sysman.oh.agent.plugin_12.1.0.3.0

Finally they agreed that it is a  Bug 17414360 – “METRIC THRESHOLDS UPDATED ON THE OMS/REPOSITORY SIDE BUT NOT ON DB SIDE” and was fixed in 12.1.0.5.1.

So I did update my database plugin to 12.1.0.5.5 Bundle Patch but all of no use, still my inbox was getting filled with more and more emails. Did some deep trouble shooting enabled the DEBUG mode on agent side but nothing happened.

Finally I was provided with a very strange solution, which MOS could have provided long before I applied three patches and update by plugins to next higher version. The solution was :

I should set the threshold to a very high value of 99% so that it can be rarely reached and the number of email could be reduced. So I did a very quick thing.

The following may need to be run to fully terminate the alerting for dba_thersholds, especially in the case where the value is exceeding the 99% value for the limit: 
exec DBMS_SERVER_ALERT.SET_THRESHOLD(DBMS_SERVER_ALERT.DB_TIME_WAITING, DBMS_SERVER_ALERT.OPERATOR_GE,’10000′, NULL, NULL, 1, 100000, ‘<DB_INSTANCE_NAME>’, DBMS_SERVER_ALERT.OBJECT_TYPE_EVENT_CLASS, ‘System I/O’);
exec DBMS_SERVER_ALERT.SET_THRESHOLD(DBMS_SERVER_ALERT.DB_TIME_WAITING, DBMS_SERVER_ALERT.OPERATOR_GE,’10000′, NULL, NULL, 1, 100000, ‘<DB_INSTANCE_NAME>’, DBMS_SERVER_ALERT.OBJECT_TYPE_EVENT_CLASS, ‘Other’);
exec DBMS_SERVER_ALERT.SET_THRESHOLD(DBMS_SERVER_ALERT.DB_TIME_WAITING, DBMS_SERVER_ALERT.OPERATOR_GE,’10000′, NULL, NULL, 1, 100000, ‘<DB_INSTANCE_NAME>’, DBMS_SERVER_ALERT.OBJECT_TYPE_EVENT_CLASS, ‘Configuration’);
exec DBMS_SERVER_ALERT.SET_THRESHOLD(DBMS_SERVER_ALERT.DB_TIME_WAITING, DBMS_SERVER_ALERT.OPERATOR_GE,’10000′, NULL, NULL, 1, 100000, ‘<DB_INSTANCE_NAME>’, DBMS_SERVER_ALERT.OBJECT_TYPE_EVENT_CLASS, ‘Network’);
exec DBMS_SERVER_ALERT.SET_THRESHOLD(DBMS_SERVER_ALERT.DB_TIME_WAITING, DBMS_SERVER_ALERT.OPERATOR_GE,’10000′, NULL, NULL, 1, 100000, ‘<DB_INSTANCE_NAME>’, DBMS_SERVER_ALERT.OBJECT_TYPE_EVENT_CLASS, ‘Administrative’);
exec DBMS_SERVER_ALERT.SET_THRESHOLD(DBMS_SERVER_ALERT.DB_TIME_WAITING, DBMS_SERVER_ALERT.OPERATOR_GE,’10000′, NULL, NULL, 1, 100000, ‘<DB_INSTANCE_NAME>’, DBMS_SERVER_ALERT.OBJECT_TYPE_EVENT_CLASS, ‘Application’);
exec DBMS_SERVER_ALERT.SET_THRESHOLD(DBMS_SERVER_ALERT.DB_TIME_WAITING, DBMS_SERVER_ALERT.OPERATOR_GE,’10000′, NULL, NULL, 1, 100000, ‘<DB_INSTANCE_NAME>’, DBMS_SERVER_ALERT.OBJECT_TYPE_EVENT_CLASS, ‘Cluster’);
exec DBMS_SERVER_ALERT.SET_THRESHOLD(DBMS_SERVER_ALERT.DB_TIME_WAITING, DBMS_SERVER_ALERT.OPERATOR_GE,’10000′, NULL, NULL, 1, 100000, ‘<DB_INSTANCE_NAME>’, DBMS_SERVER_ALERT.OBJECT_TYPE_EVENT_CLASS, ‘Concurrency’);
exec DBMS_SERVER_ALERT.SET_THRESHOLD(DBMS_SERVER_ALERT.DB_TIME_WAITING, DBMS_SERVER_ALERT.OPERATOR_GE,’10000′, NULL, NULL, 1, 100000, ‘<DB_INSTANCE_NAME>’, DBMS_SERVER_ALERT.OBJECT_TYPE_EVENT_CLASS, ‘Scheduler’);
exec DBMS_SERVER_ALERT.SET_THRESHOLD(DBMS_SERVER_ALERT.DB_TIME_WAITING, DBMS_SERVER_ALERT.OPERATOR_GE,’10000′, NULL, NULL, 1, 100000, ‘<DB_INSTANCE_NAME>’, DBMS_SERVER_ALERT.OBJECT_TYPE_EVENT_CLASS, ‘User I/O’);
exec DBMS_SERVER_ALERT.SET_THRESHOLD(DBMS_SERVER_ALERT.DB_TIME_WAITING, DBMS_SERVER_ALERT.OPERATOR_GE,’10000′, NULL, NULL, 1, 100000, ‘<DB_INSTANCE_NAME>’, DBMS_SERVER_ALERT.OBJECT_TYPE_EVENT_CLASS, ‘Commit’);

This would force the database to alert only if the alerts are being triggered for more than 10000 times.

Well not stretching this topic any longer, it was concluded that OEM was not able to disable all “SERVER GENERATED ALERTS”, you have to finally configure them from backend and set the interval to such a high value which is never met.  🙁

You can sue this document to get list of SERVER GENERATED ALERTS.

http://docs.oracle.com/cd/E18283_01/appdev.112/e16760/d_server_alert.htm#i1007642

CONCLUSION

The metrics for the Database Time Spent Waiting % are Server-generated alerts which means that Enterprise Manager is not responsible for firing the alert. The database server side alert engine fires the alert and send it to Enterprise Manager Agent. 
Disabling collection from Enterprise Manager does not affect Server-Generated alerts. The database server will test thresholds on its side and generate alerts as long as the thresholds are set. Enterprise Manager will faithfully forward along any alerts generated by the server to Enterprise Manager.

Resolution:
PSU patches were applied that include several BUG fixes, including Bug 17414360

[https://bug.oraclecorp.com/pls/bug/webbug_edit.edit_info_top?rptno=17414360] [https://bug.oraclecorp.com/pls/bug/webbug_edit.edit_info_top?rptno=17414360] . This job resolves issues with jobs that are submitted by the OMS to the target databases that updated server generated metrics.
To keep the alerts from happening, we removed the metrics from the incident rule
As some server generated metrics cannot have thresholds changed in EM, we changed them in the database itself. We set the alert threshold very high for the metrics to keep the metric from triggering regularly.

exec DBMS_SERVER_ALERT.SET_THRESHOLD(DBMS_SERVER_ALERT.DB_TIME_WAITING, DBMS_SERVER_ALERT.OPERATOR_GE,’10000′, NULL, NULL, 1, 100000, ‘<DB_INSTANCE_NAME>’, DBMS_SERVER_ALERT.OBJECT_TYPE_EVENT_CLASS, ‘System I/O’);
exec DBMS_SERVER_ALERT.SET_THRESHOLD(DBMS_SERVER_ALERT.DB_TIME_WAITING, DBMS_SERVER_ALERT.OPERATOR_GE,’10000′, NULL, NULL, 1, 100000, ‘<DB_INSTANCE_NAME>’, DBMS_SERVER_ALERT.OBJECT_TYPE_EVENT_CLASS, ‘Other’);
exec DBMS_SERVER_ALERT.SET_THRESHOLD(DBMS_SERVER_ALERT.DB_TIME_WAITING, DBMS_SERVER_ALERT.OPERATOR_GE,’10000′, NULL, NULL, 1, 100000, ‘<DB_INSTANCE_NAME>’, DBMS_SERVER_ALERT.OBJECT_TYPE_EVENT_CLASS, ‘Configuration’);
exec DBMS_SERVER_ALERT.SET_THRESHOLD(DBMS_SERVER_ALERT.DB_TIME_WAITING, DBMS_SERVER_ALERT.OPERATOR_GE,’10000′, NULL, NULL, 1, 100000, ‘<DB_INSTANCE_NAME>’, DBMS_SERVER_ALERT.OBJECT_TYPE_EVENT_CLASS, ‘Network’);
exec DBMS_SERVER_ALERT.SET_THRESHOLD(DBMS_SERVER_ALERT.DB_TIME_WAITING, DBMS_SERVER_ALERT.OPERATOR_GE,’10000′, NULL, NULL, 1, 100000, ‘<DB_INSTANCE_NAME>’, DBMS_SERVER_ALERT.OBJECT_TYPE_EVENT_CLASS, ‘Administrative’);
exec DBMS_SERVER_ALERT.SET_THRESHOLD(DBMS_SERVER_ALERT.DB_TIME_WAITING, DBMS_SERVER_ALERT.OPERATOR_GE,’10000′, NULL, NULL, 1, 100000, ‘<DB_INSTANCE_NAME>’, DBMS_SERVER_ALERT.OBJECT_TYPE_EVENT_CLASS, ‘Application’);
exec DBMS_SERVER_ALERT.SET_THRESHOLD(DBMS_SERVER_ALERT.DB_TIME_WAITING, DBMS_SERVER_ALERT.OPERATOR_GE,’10000′, NULL, NULL, 1, 100000, ‘<DB_INSTANCE_NAME>’, DBMS_SERVER_ALERT.OBJECT_TYPE_EVENT_CLASS, ‘Cluster’);
exec DBMS_SERVER_ALERT.SET_THRESHOLD(DBMS_SERVER_ALERT.DB_TIME_WAITING, DBMS_SERVER_ALERT.OPERATOR_GE,’10000′, NULL, NULL, 1, 100000, ‘<DB_INSTANCE_NAME>’, DBMS_SERVER_ALERT.OBJECT_TYPE_EVENT_CLASS, ‘Concurrency’);
exec DBMS_SERVER_ALERT.SET_THRESHOLD(DBMS_SERVER_ALERT.DB_TIME_WAITING, DBMS_SERVER_ALERT.OPERATOR_GE,’10000′, NULL, NULL, 1, 100000, ‘<DB_INSTANCE_NAME>’, DBMS_SERVER_ALERT.OBJECT_TYPE_EVENT_CLASS, ‘Scheduler’);
exec DBMS_SERVER_ALERT.SET_THRESHOLD(DBMS_SERVER_ALERT.DB_TIME_WAITING, DBMS_SERVER_ALERT.OPERATOR_GE,’10000′, NULL, NULL, 1, 100000, ‘<DB_INSTANCE_NAME>’, DBMS_SERVER_ALERT.OBJECT_TYPE_EVENT_CLASS, ‘User I/O’);
exec DBMS_SERVER_ALERT.SET_THRESHOLD(DBMS_SERVER_ALERT.DB_TIME_WAITING, DBMS_SERVER_ALERT.OPERATOR_GE,’10000′, NULL, NULL, 1, 100000, ‘<DB_INSTANCE_NAME>’, DBMS_SERVER_ALERT.OBJECT_TYPE_EVENT_CLASS, ‘Commit’);

Hope this help you to save your time, which you might waste in updating your plugin or applying random patches.

Thanks

Deepak

© 2017 ORACLE EM DBA

Theme by Anders NorenUp ↑