How to FIX “Metric Collection Error” {Agent Status not UP in Console}

A Common Issue which everyone usually faces, while new installation of Agent on any server.  Agent is up and running but still status is not up in EM Console show “METRIC COLLECTION ERROR”.

Check on the server and perform following steps to resolve this Issue.

D:\oracle\product\agent12c\agent_inst\bin>emctl status agent
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation. All rights reserved.
—————————————————————
Agent Version : 12.1.0.4.0
OMS Version : 12.1.0.4.0
Protocol Version : 12.1.0.1.0
Agent Home : D:/oracle/product/agent12c/agent_inst
Agent Log Directory : D:/oracle/product/agent12c/agent_inst\sysman\log
Agent Binaries : D:\oracle\product\agent12c\core\12.1.0.4.0
Agent Process ID : 5632
Parent Process ID : 10516
Agent URL : https://HANXXX.TEST.com:3872/emd/main/
Local Agent URL in NAT : https://HANXXX.TEST.com:3872/emd/main/
Repository URL : https://HANXXX.TEST.com:4904/empbs/upload

Started at : 2015-05-13 18:35:17
Started by user : HANXXX3$
Operating System : Windows version 6.1 (amd64)
Last Reload : (none)
Last successful upload : 2015-05-13 18:35:45
Last attempted upload : 2015-05-13 18:35:45
Total Megabytes of XML files uploaded so far : 0
Number of XML files pending upload : 0
Size of XML files pending upload(MB) : 0
Available disk space on upload filesystem : 50.84%
Collection Status : Collections enabled
Heartbeat Status : Ok
Last attempted heartbeat to OMS : 2015-05-14 14:17:49
Last successful heartbeat to OMS : 2015-05-14 14:17:49
Next scheduled heartbeat to OMS : 2015-05-14 14:18:49

—————————————————————
Agent is Running and Ready

D:\oracle\product\agent12c\agent_inst\bin>emctl upload agent
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation. All rights reserved.
—————————————————————
EMD upload completed successfully

D:\oracle\product\agent12c\agent_inst\bin>emctl verifykey
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation. All rights reserved.

—————————————————–
verifykey: Successfully Completed communication with agent
EMD pingOMS completed successfully

—————————————————–

======================================================
But Still Agent Status was not showing UP in Console.
=====================================================

d:\oracle\product\agent12c\agent_inst\bin>emctl stop agent
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation. All rights reserved.
The Oracleagent12c1Agent service is stopping………….
The Oracleagent12c1Agent service was stopped successfully.

Deleted files from $ORACLE_HOME/sysman/emd/state and $ORACLE_HOME/sysman/emd/upload
D:\oracle\product\agent12c\agent_inst\bin>emctl clearstate agent
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation. All rights reserved.
EMD clearstate completed successfully

———————————————

D:\oracle\product\agent12c\agent_inst\bin>emctl secure agent
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation. All rights reserved.
Agent successfully stopped… Done.
Securing agent… Started.
Enter Agent Registration Password :
Agent successfully restarted… Done.
Securing agent… Successful.

——————————————-
D:\oracle\product\agent12c\agent_inst\bin>emctl config agent addinternaltargets
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation. All rights reserved.

——————————————-
D:\oracle\product\agent12c\agent_inst\bin>emctl config agent listtargets
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation. All rights reserved.
[agent12c1_2_HANXXX.TEST.com, oracle_home]
[HANXXX.TEST.com, host]
[HANXXX.TEST.com:3872, oracle_emd]

——————————————-
D:\oracle\product\agent12c\agent_inst\bin>emctl upload agent
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation. All rights reserved.
—————————————————————
EMD upload completed successfully

———————————————
D:\oracle\product\agent12c\agent_inst\bin>emctl status agent
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation. All rights reserved.
—————————————————————
Agent Version : 12.1.0.4.0
OMS Version : 12.1.0.4.0
Protocol Version : 12.1.0.1.0
Agent Home : D:/oracle/product/agent12c/agent_inst
Agent Log Directory : D:/oracle/product/agent12c/agent_inst\sysman\log
Agent Binaries : D:\oracle\product\agent12c\core\12.1.0.4.0
Agent Process ID : 7188
Parent Process ID : 3536
Agent URL : https://HANXXX.TEST.com:3872/emd/main/
Local Agent URL in NAT : https://HANXXX.TEST.com:3872/emd/main/
Repository URL : https://HANXXX.TEST.com:4904/empbs/upload

Started at : 2015-05-14 15:27:47
Started by user : HANXXX3$
Operating System : Windows version 6.1 (amd64)
Last Reload : (none)
Last successful upload : 2015-05-14 15:32:38
Last attempted upload : 2015-05-14 15:32:38
Total Megabytes of XML files uploaded so far : 0.12
Number of XML files pending upload : 0
Size of XML files pending upload(MB) : 0
Available disk space on upload filesystem : 50.81%
Collection Status : Collections enabled
Heartbeat Status : Ok
Last attempted heartbeat to OMS : 2015-05-14 15:34:56
Last successful heartbeat to OMS : 2015-05-14 15:34:56
Next scheduled heartbeat to OMS : 2015-05-14 15:35:56

—————————————————————
Agent is Running and Ready

D:\oracle\product\agent12c\agent_inst\bin>

Finally The Agent was having Status up in Console.

Thanks

Deepak

Please follow and like us:
RSS
Follow by Email
Facebook
Google+
http://emdeepaksharma.com/2015/05/how-to-fix-metric-collection-error-agent-status-not-up-in-console/
LinkedIn
Instagram

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

Please follow and like us:
RSS
Follow by Email
Facebook
Google+
http://emdeepaksharma.com/2015/03/how-to-use-em12c-repository-views-to-get-database-storage-details/
LinkedIn
Instagram

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

Please follow and like us:
RSS
Follow by Email
Facebook
Google+
http://emdeepaksharma.com/2015/03/server-generated-alerts-like-metrics-database-time-spent-waiting-response_txn-etc-can-not-be-disabled-in-em12c-2/
LinkedIn
Instagram

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

Please follow and like us:
RSS
Follow by Email
Facebook
Google+
http://emdeepaksharma.com/2015/03/server-generated-alerts-like-metrics-database-time-spent-waiting-response_txn-etc-can-not-be-disabled-in-em12c/
LinkedIn
Instagram

Event 1000, Application Error {opmn.exe} ” Oracle-agent12cR1ProcessManager”

“Event 1000, Application Error {opmn.exe}” One of the Common Issues in Agent/ OMS 12c. Very Often you will find under “Application Error” in Event Viewer of Windows some thing like this:

Faulting application name: opmn.exe, version: 0.0.0.0, time stamp: 0x488507f7
Faulting module name: unknown, version: 0.0.0.0, time stamp: 0x00000000
Exception code: 0xc0000005
Fault offset: 0x00000000
Faulting process id: 0x163c
Faulting application start time: 0x01d050194c4e6030
Faulting application path: C:\app\oracle\product\agent12c\core\12.1.0.2.0\opmn\bin\opmn.exe
Faulting module path: unknown
Report Id: 89fd4e64-bc0c-11e4-987a-0050569857ec

This Issue occur if you install 32-bit agent on 64-bit Windows Server. In Oracle 12c when you install Oracle Agent 12c, there is one extra Service ” Oracle-agent12cR1ProcessManager” which gets install.

This is known Bug in Oracle with BUG ID 14610919. You might not be able to see the contents of this BUG as this is not a PUBLIC Bug.

This Application Error can be easily stopped by stopping this Service and changing its property “Startup Type” to Manual. Stopping this service will not impact functionality of Oracle Agent but this does not means that this is useless or extra service.

We can use this Service to start and stop Management Agent, OMS and other related components including “HTTP Server, OC4J_EM, WebCache etc” These Components not only run as Child Processes of this service but also inherit its security settings.  The reason why this service gets installed by default is that in 12C we have got the OPMN component installed with Agent by default.

To avoid this Application Error from occurring every second, just STOP  “Oracle-agent12cR1ProcessManager” service.

Thanks

Deepak

Please follow and like us:
RSS
Follow by Email
Facebook
Google+
http://emdeepaksharma.com/2015/02/event-1000-application-error-opmn-exe-oracle-agent12cr1processmanager/
LinkedIn
Instagram

OMS failed to start “Connection to Repository Failed”

Hi,

Recently one issue blew my mind off, when all of a sudden my OEM test environment failed to startup. Since it was running fine earlier but this time when I start OMS using the command “>emctl start oms” it failed.

When I explored the logs I came across that some has changed the repository credentials for SYSMAN, which was done at the backend and was not synchronized with the OMS. This led the OMS not to connect with the repository. Something like this popped up in the logs.

“failureReason: DB Connection service is invalid or temporarily unavailable” and “ERROR commands.BaseCommand logAndPrint.623 – Oracle Management Server is not functioning because of the following reason:
Connection to the repository failed. Verify that the repository connection information provided is correct.”

Key Point to remember is that “SYSMAN” password should not be changed directly in repository database. The ONLY way to change SYSMAN password is using emctl command. So next thing I tried was reset SYSMAN password using “emctl” command.

[oracle@houoemap1 bin]$ ./emctl config oms -change_repos_pwd -use_sys_pwd -sys_pwd formula123 -new_pwd formula123
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation. All rights reserved.
java.io.IOException
Error occurred. Check the log /app/oracle/product/middleware/gc_inst/em/EMGC_OMS1/sysman/log/secure.log

Which Failed again 🙁

I checked the logs and find that my “Admin Server” was not up and running but even after bringing Admin Server up I was not allowed to change password.

2015-02-10 07:03:32,915 [main] INFO oms.AdminCredsWalletUtil setInstanceHome.177 – Getting credentials from wallet
2015-02-10 07:03:33,218 [main] INFO oms.AdminCredsWalletUtil setInstanceHome.192 – Read the credentials from wallet
2015-02-10 07:03:33,221 [main] INFO util.EmctlUtil logp.251 – Connecting over t3s to: houoemap1.xxxxxx.com/7102 using id: weblogic
2015-02-10 07:03:33,851 [main] INFO util.EmctlUtil logp.251 – Unable to get mbean conn over t3s :null
2015-02-10 07:03:33,854 [main] ERROR oms.ChangeReposPwd logp.251 –

at weblogic.management.remote.common.ClientProviderBase.makeConnection(ClientProviderBase.java:178)
… 7 more
Caused by: java.net.ConnectException: t3s://houoemap1.xxxxxx.com:7102: Destination unreachable; nested exception is:
java.net.ConnectException: Connection refused; No available router to destination

I started Admin server using ” >emctl start oms -admin_only”

Followed by this I again tried to reset the sysman password using “EMCTL” but again no luck 🙁

[oracle@houoemap1 bin]$ ./emctl config oms -change_repos_pwd -use_sys_pwd -sys_pwd formula123 -new_pwd formula1
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation. All rights reserved.
java.lang.NullPointerException
Error occurred. Check the log /app/oracle/product/middleware/gc_inst/em/EMGC_OMS1/sysman/log/secure.log
[oracle@houoemap1 bin]$

Now I had to open case with MOS and they guided me to reset the password of all four dependent users “sysman, sysman_apm, sysman_mds, sysman_opss” to common password and then try to start oms, so I did.

Reset Password—————

[oracle@houoemap1 db_1]$ ss
SQL*Plus: Release 11.2.0.2.0 Production on Fri Feb 13 02:41:33 2015
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> conn sysman/formula1
Connected.
SQL> conn sysman_apm/formula1
Connected.
SQL> conn sysman_mds/formula1
Connected.
SQL> conn sysman_opss/formula1
Connected.

Start OMS —————-

[oracle@houoemap1 bin]$ ./emctl start oms
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation. All rights reserved.
Starting Oracle Management Server…
Starting WebTier…
WebTier Successfully Started
Oracle Management Server is not functioning because of the following reason:
Unexpected error occurred. Check error and log files.
Check the following log files:
EM log files: /app/oracle/product/middleware/gc_inst/em/EMGC_OMS1/sysman/log/emctl.log, emoms.trc, emoms_pbs.trc
[oracle@houoemap1 bin]$

Providing logs to Oracle Support we came to know that there is mismatch of the information in JDBC xml files.

Used “/app/oracle/product/middleware/gc_inst/user_projects/domains/GCDomain/config/jdbc/*” to find details.

Finally below action plan worked.

—————————————————————————-

1. Take a backup of the directory /app/oracle/product/middleware/gc_inst/user_projects/domains/GCDomain/config/jdbc
2. Access weblogic admin console. https://houoemap1.xxxxxx.com/7102/console
Enter ‘weblogic’ as the username and its password.
If the weblogic admin server is not up, then start it using:
OMS_HOME/bin>./emctl start oms -admin_only
3. From the admin console, Leftpanel > Services > Datasources
Here you will have list of all datasources. Click on “Lock and Edit” on top corner left side. Click on Datasources one by one.
For example: Click on emgc-sysman-pool > Connection Pool tab > Enter Password and Confirm Password as formula1
And Save each change.
Please repeat this for all the datasources.
4. Click on “Release Configuration” (Under Lock and Edit) after the changes are done.
5. Now restart the OMS with
$emctl stop oms -all -force
$emctl start oms

If you face this Issue ever, this Action plan will help you to get the Issue resolved.

Regards

Deepak

Please follow and like us:
RSS
Follow by Email
Facebook
Google+
http://emdeepaksharma.com/2015/02/oms-failed-to-start-connection-to-repository-failed/
LinkedIn
Instagram

How to add Additional target properties in Oracle Enterprise Manager12c

In Oracle Enterprise Manager we have the option set properties of targets which in-turn we can later use to categorize them.

The Administration Group option in Oracle Enterprise Manager 12c is one of the good features introduced. This feature help us categorize our targets in different hierarchy sub groups. The hierarchy was decided on the number of target properties having values set.

Up to recently I was under the assumption that this was a fixed list of target properties like “Department, LifeCycle Status, Contact etc”. However I was caught in a scenario
where I have to increase the hierarchy in my Administration Group to meet my business need. This was the point when I thought what If I can add something more to this property set. This gave me reason to explore some more feature of EMCLI.

I’ll take example of my test case in this Blog.

I wanted to add a property “ENVIRONMENT” using which I can differentiate all targets for my PeopleSoft Environments “Financials, HR, Brazil and Portal”
This came as a solution of the enhancement request that was already in place. MOS provided the solution to this problem. We can add additional target property fields by using EMCLI.

The parameter to add additional target properties for a target in Oracle Enterprise Manager is “add_target_property”.
By using this parameter with EMCLI command we can extend the number of fields per target type.

For example, if you wants to add new target property “ENVIRONMENT” for all targets it can be easily done using below mentioned command.

>emcli add_target_property -target_type=”*” -property=”ENVIRONMENT”

In case you wanted to add the ENVIRONMENT property to only oracle_database targets you could do so by executing the below command:

>emcli add_target_property -target_type=”oracle_database” – property=”ENVIRONMENT”

This option provides you a versatile Oracle Enterprise Manager with extended capability to store more information on a target level.

Hope you have find this post interesting and useful in your business.

Thanks

Deepak

Please follow and like us:
RSS
Follow by Email
Facebook
Google+
http://emdeepaksharma.com/2015/02/how-to-add-additional-target-properties-in-oracle-enterprise-manager12c/
LinkedIn
Instagram

Avoid Failure while creating blackouts in OEM

Hi Everyone,

In this video I have explained how we can avoid a common error which one can easily get while creating blackout in OEM12c.  I got complaint from my team members that when ever they create blackout for some maintenance activity it is getting failed every time.

I tried to find out the reason for the same and asked my team mate show me how he is creating blackout. I created using the same method which he did and the strange thing was that I did not get that error.

I took some time to analyze the root cause and found that there was something un common in the drop down menu to select targets. The whole thing is explained in this video.

Link to Video

——————

https://plus.google.com/u/1/photos/104998264808723427631/albums/5878592103991194177/5878592103328450706?pid=5878592103328450706&oid=104998264808723427631

Thanks & Regards

Deepak Sharma

Please follow and like us:
RSS
Follow by Email
Facebook
Google+
http://emdeepaksharma.com/2015/01/avoid-failure-while-creating-blackouts-in-oem/
LinkedIn
Instagram

Create Service Request for E-Business Suite on MOS through Oracle Configuration Manager

Hi All,

In this post I have shared a link to my video. In this video I have explained, how efficiently we can open a case with MOS if we have Oracle Configuration Manager Configured on our system.

Well this video is specifically used for E-Business Suite. By using this strategy it become much easier for end users to not only open case but provide required information in prior to Oracle Support.

Oracle Configuration Manager can not only be used for EBS but for other applications like PeopleSoft, OEM, Business Intelligence and many more. I’ll be soon uploading video for PeopleSoft and OEM.

Link for Video

—————————

Thanks & Regards

Deepak Sharma

Please follow and like us:
RSS
Follow by Email
Facebook
Google+
http://emdeepaksharma.com/2015/01/create-service-request-for-e-business-suite-on-mos-through-oracle-configuration-manager/
LinkedIn
Instagram

EM12c {Target Type “Generic Service”} — Flaws in “Web Transaction” (Replacement with ATS Transaction)

Hi Everyone,

In this blog I’ll be discussing about “Services” as target in EM12c Cloud Control. We have Services as one of the targets, in which we can create a Service Test for any of our applications, our tomcat servers, etc.. This can be done by for several “Test Types” like “Web Transaction, ATS Transaction, Web Service, SMTP etc.”

Usually I used to create Service Test to record the behavior of application using “Web Transaction” in which I used to record few step like “login page, home page and finally logout page”. I used to perform this by using the test credentials for different applications. This technique was in used since 11g and it was running fine in 11g and in 12c as well.

Last week I created a Service Test for my new application, the strange thing about this Application was that for each login one licence will be occupied. I used the same “Web Transaction” technique, created service test for same “login, homepage and sign out page” with collection frequency set to 5 minutes.

In next one hour I got complaint from Application Manager that there are 12 licences being occupied and not released for this test user. “STRANGE & ANNOYING THING”.

I worked with support and after doing debugging and checking the logs we found that the last sign-out page was not even being hit. It was then I came to know in 12c “Web Transaction” was having some flaws for different kind of applications.

Then I replaced “Web Transaction” with “ATS Transaction” with some extra effort. I have to setup “Oracle Testing Suite” for this, which have “Open Script” application that is used to do the recording of the transaction.

Advantage of “Open Script” is that if you have some knowledge of java you can actually check the code running behind and can double crosscheck it. Then this whole transaction can be exported to a zip file which is used by EM to perform test.

This solved my problem this time the Test was successful and the licence was getting released after every sign out.

I have started using “ATS Transactions” over “Web Transactions”, in my next blog I’ll be publishing the document for how to use “Oracle Application Testing Suite” for ATS transaction.

Please follow and like us:
RSS
Follow by Email
Facebook
Google+
http://emdeepaksharma.com/2014/12/em12c-target-type-generic-service-flaws-in-web-transaction-replacement-with-ats-transaction/
LinkedIn
Instagram