OPATCH FOR Oracle Agent 12cR2 HIGH CPU UTILIZATION

Hi All,

In this blog of mine I have discussed the remedy for HIGH CPU utilization of Oracle 12cR2 agent. I faced this Issue on most of my Linux servers, where all of a sudden the Oracle Agent used to start utilizing CPU at very high rate.

Again list most of the old Issue this one as well was reported as BUG with MOS.  So If you have Oracle Agent 12cR2 on your Linux machine and you are experiencing Agent High CPU utilization you can get it resolved by applying the patch 13583799.

Listed below are the steps you have to perform in order to apply the patch.

OPATCH FOR HIGH CPU UTILIZATION

{apply patch 13583799 on 12cR2 Agent on Linux}

S.N Status Task
1 Done export ORACLE_HOME=/app/oracle/product/agent12c/core/12.1.0.2.0
2 Done  cd OPatch

./opatch lsinv -details |grep -i jdbc

make sure client Oracle JDBC/OCI Instant Client  is                                   11.1.0.7.0

3 Done using patch 13583799’s 11.1.0.7.0  version  check perl -v > 5.5
4 Done  ./emctl stop agent
5 Done make sure no process run under agent 12c

ps -ef |grep TMMain

ps -ef |grep emagent |grep -v grep

6 Done cd /install_media/ORACLE/OEM/OEM12C/patch/13583799

(for 13583799,cd /install_media/ORACLE/OEM/OEM12C/patch/13583799)

7 Done  opatch apply
8 Done check any error in opatch log  /app/oracle/product/agent12c/core/12.1.0.2.0/cfgtoollogs/opatch/opatch2013-01-07_08-03-49AM.log
9 Done start agent and check status

This will bring down your un-necessary Agent’s CPU utilization.

Thanks

Deepak

Please follow and like us:
RSS
Follow by Email
Facebook
Google+
http://emdeepaksharma.com/2015/05/opatch-for-oracle-agent-12cr2-high-cpu-utilization/
LinkedIn
Instagram

Creating Generic Service using “ATS Transactions” in OEM

Hi Everyone,

In this post, I help you out how we can create Generic Service using “ATS Transactions” in OEM 12c. Well This situation came across me when to monitor one of my application I created a “Generic Service” which use Web Transaction.

The test was created successfully, the only issue was the for every repetition it was occupying one license was not not releasing it at the Logout Step, which ideally it should do. Yes one of the applications in my company acquire one license every time when any user logs into. This license is released only when the user logs out.

So this Web Transaction method started Blocking my licenses for Application Login. I reported this case to Support but they closed it in one day itself telling me that “Web Transaction” method has become obsolete and they do not recommend to use it. MOS suggested me to use OATS {Oracle Application Testing Suite}

I created Service Test using the ATS Transaction Method, created a Service Test and it resolved my Issue of blocking Licenses.

I will show you how create Service Test using ATS Transaction. Follow the below mentioned Steps.

  • Make sure you have Oracle Application Testing Suite, you can install it on you local computer as well.
  • You can download from “http://www.oracle.com/technetwork/oem/app-test/etest-101273.html” link
  • Follow the guidelines in the installation manual and get it installed on your system.
  • Once installed start the Open Script.

Image1

  • Mentioned below is the main page of “Open Script”

Image2

  • Click on “File” in the menu bar and select “New”.

Image3

  • “New Project” wizard will open. Under “Load Testing” select “WebHTTP”

Image4

  • Save you Project under your Repository. Give a name to your project and click on “Finish”.

Image5

  • Once you click on “Finish”, your new project window will open where you can record your Transaction.

Image6

  • Click on highlighted recording button shown in previous screenshot. It will open a recording Toolbar and recording window where you can enter the URL of Application which you want to test.

Image7

  • Perform all the steps which you want to include in your test {Transaction}
  • Once all the steps are included, click on “Stop” button in the Recording Toolbar.
  • It will show the Hierarchy of all the steps which are included in your transaction test.

Image8

  • Save your Project under the “File Menu” and then export the Project Zip file as show in screenshot.

Image9

Image10

  • Save File on you local laptop, in order to upload it at creation time of ATS transaction.
  • Now Login to your EM12c Console and Navigate to “Services” as shown in the screenshot.

Image11

  • Now Click on “Create” button and select “Generic Service – Test Based”

Image12

  • It will navigate  you to the First page, enter the required details like “NAME” & “Time Zone” and Click on “Next”.

Image13

  • Under Test Type, select “ATS Transaction” and enter name for your Service Test. Also enter the Collection Frequency at which you want the test to be executed again n again.

Image14

  • Now Add the saved ZIP file of the transaction recorded using “Oracle Application Testing Suite”.

Image15

  • Once you upload the ZIP file, it will automatically fetch all the details from the file and load it in the Service Test.

Image16

  • Next step is to add a Beacon for your Service Test which will perform Service Test from Geographical location. Beacon Actually help us to know availability of Application from different locations. You can select more than one beacon as well for Service Test.

Image17

  • Final Review of “Generic Service”, review all the details to make sure something wrong did not happen and then click “Finish”.

Image18

Your Service Test is created successfully. You can now monitor you Application without any Issue.

Hope this post will help you out in one or other way.

Will keep posting new Issue & Resolutions.

Regards

Deepak Sharma

Please follow and like us:
RSS
Follow by Email
Facebook
Google+
http://emdeepaksharma.com/2015/05/creating-generic-service-using-ats-transactions-in-oem/
LinkedIn
Instagram

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