Oracle DBA – A lifelong learning experience

Archive for the ‘Grid control and agents’ Category

Cloud agent set to DEBUG causing out of memory errors

Posted by John Hallas on June 23, 2015

The following technical detail was put together by a colleague John Evans and have taken it , with his permission, and wrapped some more detail around it as it seemed to be of real value to anybody who might have upgraded an agent to

Following an upgrade of the EM agent from (or to after about 90 days of usage we saw a number of agents failing with out of memory errors.

We traced this down to a line in the properties file where the trace level of parameter Logger.sdklog.level=DEBUG  rather than INFO Read the rest of this entry »

Posted in Grid control and agents, Oracle | Leave a Comment »

Valuable BAU OEM reports – some examples

Posted by John Hallas on July 18, 2014

A well configured OEM 12c (or any previous version of your choice) is a fantastic enterprise tool and the reporting capabilities are a major asset.

I am going to share three examples of reports we use and find valuable and if anybody has any  other good ideas I am happy  to hear about them.

I don’t think I wrote any of them but I almost certainly identified the requirement and instigated the creation of each report. I have masked out obvious details so the screen shots do look messy.

For anybody new to OEM reports I blogged a post about creating a new report some time ago

Location of Primary and Standby databases

Like many organisations we have 2 data centres and we use DataGuard extensively. We have a naming convention that has the 8th character of the database name being either A or B which signifies Primary or Standby database. Over time as we do failovers and move things around you cannot always assume that A is the primary database. So I wanted a way of telling quickly what was impacted if we had issues at one site or another.

The report is produced daily and emailed out to the DBA team. In the event of a DR that is our first point of call to see what work is required. The name of each site shows against the server name. This can be achieved because we have a file on a Unix server that contains all the server names and their site location. We create a small table (server_sites)  in the OEM database with the name of the production servers in and the site and then we can join it to the mgmt$target table. We don’t add new production servers that often so it is not too much of an overhead to maintain the table.

a.target_name, a.target_type, a.type_qualifier3, a.type_qualifier4, a.host_name, b.value,
(select *
from mgmt$target
-- type_qualifier2 in('Primary','Physical Standby')
type_qualifier4 in('Primary','Physical Standby')) a,
mgmt$db_init_params b,
where a.target_name = b.target_name
and a.target_name like ('%PRD%')
and = 'dg_broker_start'
and c.server = a.host_name
order by target_name


Guaranteed Restore Points

The availability of guaranteed restore points is a great idea and we use it a lot, especially before data fixes and new code releases. We will create a GRP, apply the code pack, update data and then smoke test. If all fails miserably then we have a quick and easy way to get back, as well as the normal backup and recovery methods. A true belt and braces approach. The downside is that it is easy to forget to remove the GPR and then we have problems with the FRA running out of space.

with db_locations as
(select a.target_guid, a.property_value DB_NAME, b.db_location, c.lifecycle_status
(select target_guid, lower(property_value) db_location from MGMT$TARGET_PROPERTIES 
where property_name = 'orcl_gtp_location') b,
(select target_guid, lower(property_value) lifecycle_status from MGMT$TARGET_PROPERTIES 
where property_name = 'orcl_gtp_lifecycle_status') c
where a.target_guid = b.target_guid
and a.target_guid = c.target_guid
and b.target_guid = c.target_guid
and a.property_name = 'DBName')
m.target_name "Database Name",
t.type_qualifier4  AS Role,
m.key_value     AS "Restore Point Name and Date",
ct.db_location "Location",
ct.lifecycle_status "Lifecycle"
(select target_guid, target_name, metric_label, key_value 
from mgmt$alert_current ac 
where exists (select e.event_id from mgmt$events e 
              where e.event_id = ac.event_instance_id 
              and e.open_status = 1)) m,
mgmt$target t,
(select dbl.*
from db_locations dbl
where dbl.db_location = 'core'
union all
select dbl.*
from db_locations dbl
where dbl.db_location is null 
and substr(dbl.db_name,1,3) in (select distinct substr(db_name,1,3) from db_locations where db_location = 'core')
order by 1,2) ct
WHERE m.metric_label in ('Core_Restore_Point_Check_NON_RAC','Core_Restore_Point_Check_RAC')
AND t.target_name=m.target_name
AND t.target_guid = ct.target_guid
and ct.lifecycle_status like ('%production%')
AND t.target_guid=m.target_guid
order by m.target_name,m.key_value

Databases with no flashback enabled

Reasonably self-evident and all databases should have flashback enabled so it is good to pick up on those that have not got it enabled

t.type_qualifier4  AS Role,
m.column_label     AS Flashback,
m.value            AS Status
mgmt$metric_current m,
mgmt$target t
WHERE m.metric_label = 'Flash Recovery'
AND m.column_label = 'Flashback On'
AND m.value = 'NO'
AND m.target_name like '%PRD%'
AND t.type_qualifier4 in('Primary','Physical Standby')
AND t.target_name=m.target_name
AND t.target_guid=m.target_guid
order by t.type_qualifier4 ,


Posted in Grid control and agents, Oracle | Tagged: , , | 1 Comment »

Don’t forget the emctl reload agent command

Posted by John Hallas on March 11, 2014

Just a quick entry to show the use of a command that I had forgotten existed but it seemed to work nicely

Problem  – OEM12C agent had been down for a few days on a non-production server and more than the maximum number of files had been created ready to be uploaded.

emctl status agent

Oracle Enterprise Manager Cloud Control 12c Release 2
Copyright (c) 1996, 2012 Oracle Corporation.  All rights reserved.
Agent is Not Running
emctl start agent
Oracle Enterprise Manager Cloud Control 12c Release 2
Copyright (c) 1996, 2012 Oracle Corporation.  All rights reserved.
Starting agent ........................................................................................................................... started but not ready.

emctl status agent
Oracle Enterprise Manager Cloud Control 12c Release 2
Copyright (c) 1996, 2012 Oracle Corporation.  All rights reserved.
Agent Version     :
OMS Version       :
Protocol Version  :
Agent Home        : /app/oracle/product/gc12.1/agent12c
Agent Binaries    : /app/oracle/product/gc12.1/core/
Agent Process ID  : 2930
Parent Process ID : 2806
Agent URL         ::1830/emd/main/
Repository URL   ::4900/empbs/upload
Started at        : 2014-03-11 12:45:03
Started by user   : oracle
Last Reload       : (none)
Last successful upload                       : 2014-03-11 12:48:27
Last attempted upload                        : 2014-03-11 12:48:27
Total Megabytes of XML files uploaded so far : 1.07
Number of XML files pending upload           : 3,118
Size of XML files pending upload(MB)         : 8.51
Available disk space on upload filesystem    : 26.60%
Collection Status                            : [COLLECTIONS_HALTED(
  UPLOAD_SYSTEM Threshold (UploadMaxNumberXML: 5000) exceeded with 5001 files)]
Heartbeat Status                             : Ok
Last attempted heartbeat to OMS              : 2014-03-11 12:48:11
Last successful heartbeat to OMS             : 2014-03-11 12:48:11
Next scheduled heartbeat to OMS              : 2014-03-11 12:49:11

Edit $ORACLE_HOME/sysman/config/ and change the entry to 6000 from 5000 for the line


Then instead of the time-consuming process of stopping and starting the agent

Emctl reload agent

That works very quickly and the files were processed few in a few minutes and then I just re-edited the properties file back again and performed a reload

Posted in Grid control and agents, Oracle | Tagged: , | 3 Comments »

Using EM12C to see bind variables

Posted by John Hallas on July 24, 2013

I have had these images for a few weeks but I was reminded to make a post after seeing Doug Burns present on using the monitoring tool in 12c at a UKOUG SIG meeting last week and he mentioned how you can see bind variables quite easily. Of course you could always work your way through a 10046 trace but I think you might find these options easier.

Firstly how to get them from the command line

select sql_id, sid from v$sql_monitor where sql_exec_id = 16779111;


————- ———-

6jcg0bbfm9z6p 1122

select to_char(BINDS_XML) from v$sql_monitor where sid = 1122 and sql_id = ‘6jcg0bbfm9z6p’



222207/11/2013 00:00:0007/11/2013 00:00:0007/11/2013 00:00:0007/11/2013 00:00:0007/11/2013


Then we go the visual route and you decide which is the most usable method.





Posted in Grid control and agents, Oracle | Tagged: , , , , | 1 Comment »

Grid control sends false alerts with “Agent to OMS communication broken” message

Posted by John Hallas on July 12, 2011

We have been seeing an increasing number of alerts stating that OEM cannot ping an agent. These then generate alerts and incidents and potential callouts. The situation was getting increasingly worse and therefore we started some investigation as we had put it down to a busy network and the fact we have a lot of distributed agents.

The error message is Message=Agent is unable to communicate with the OMS. (REASON = Agent is Unreachable (REASON : Agent to OMS Communication is broken ). Severity=Unreachable Start

We are on GC We came across Note 9276193.8 which highlights bug 9276193 –  gc sends false alerts with “agent to oms communication broken” message

There are two workarounds suggested :-

Turn off alerts notification – which is a bit of a joke really
Increase max_inactive_time in emd_ping table to a large value – the table name is actually mgmt_emd_ping.

Currently the default value is 120 seconds and we upped it to 240 and that resolved our problems.
Below is a test case showing a selection of agents and their target guids and how we proved the fix. Read the rest of this entry »

Posted in Grid control and agents, Oracle | Tagged: , , , , , , | 7 Comments »

Enterprise manager – How To Install An Additional Management Service

Posted by John Hallas on June 7, 2011

How To Install An Additional Management Service

This document will describe how to install an additional management service for Oracle Enterprise Manager Grid Control on HP-UX. It was written by my colleague Carl Holmes.

You must first install the base release of Enterprise Manager ( and patch it to

The recommended installation method is to install the software, patch it to and then configure Enterprise Manager.

To do this, use these installer commands

./runInstaller -noconfig -ignoreSysPreReqs b_skipDBvalidation=TRUE

Explanation of this command

-noconfig – Do not run the configuration assistants after the software is installed

-ignoreSysPreReqs – Skip the system pre req checks as this version of Enterprise Manager is not aware of HP-UX version 11.31

b_skipDBvalidation=TRUE – Use this command to stop the installer checking the repository database version, as it is not aware of version

When the system pre req screen appears, manually verify the OS version to continue.

When the installation is complete you will be prompted to run  $OMS_HOME/

This runs the scripts for the OMS and the management agent that have just been installed Read the rest of this entry »

Posted in Grid control and agents, Oracle | Tagged: , , , , | Leave a Comment »

Using OEM reports to show PSU levels across the estate

Posted by John Hallas on March 30, 2011

The reporting capabilities of OEM are very good, although sometimes it is hard to find  which views the data you want is held in. This post is about sharing how to build a report which details how many databases are at each PSU patch. It will also show how to schedule a repeating report and save the history of each run so that trending can be measured. Most ( if not all ) of the work was performed by Sarabjit Lotay (encouraged by superb leadership !!)

First lets look at the end result. I have had to take out specific detail from our estate but the report looks like this

The red blotches don’t help but you can see the general format and it does prove very useful. The next shot shows how the report is made up

Read the rest of this entry »

Posted in Grid control and agents, Oracle | Tagged: , , , , | 3 Comments »

Overview of OEM management packs

Posted by John Hallas on December 16, 2010

Porus Homi Havewala who produced two of the top ten most downloaded articles on OTN in 2009 has produced an overview of OEM Management Packs

Mostly centred on the Diagnostic and Tuning Packs, which you need to have purchased before you are allowed to use the ASH views, the article briefly covers Configuration Management, Change Management, Data Masking and other management packs.

If you need to get a good overview of the management packs then this article will do for you.

It does not mention a new one that I know is coming out in Mid 2011 – the data subsetting pack (not sure of the actual title) but it will be a pack/toolset to produce cut down referentially complete smaller versions of larger, normally production databases. It is expected that it will be tightly integrated with the Data Masking Pack so that smaller versions of production databases can be used for development and testing purposes with all confidential data masked.

Posted in Grid control and agents, Oracle | Tagged: , , , , , | Leave a Comment »

How to create a User Defined metric(UDM) in Grid

Posted by John Hallas on December 8, 2010

Using Grid it is possible to create user defined metrics that capture information about the state of a database, a host or an application. Once you have a script or sql statement that returns a value then a UDM can be created. Within EM a report can be created to report the metrics or an alert can be raised.

Firstly I will create a UDM that reports when an account is locked and automatically raise an alert. It may be a simple example but I show one or two issues that can occur.

EM/Select the database/User defined Metrics at the bottom of the main page. Create a new UDM and I suggest starting the name off with a business area such as Retail or EBS as you can end up with quite a few metrics once you get the hang of them.

The key choice to make is whether the output will be a number or a string and whether or not you want one or two values output. In my case I want to know the status of an account so that will be string and I want to output two columns, the username and the status. Enter your query and an account to use for the connection. Ensure that the query works and the account has the privilege to run that query. In this case when  I tested the UDM it fails as I had not  created the accounts beforehand. Select a comparison operator (equal, less than MATCH etc) to the sql query you are running and then format the output you want in the alert.

Note the %Key% and the %value% keywords have to be in exactly that case.

Set the test time to be 5 minutes in the schedule team for the purposes of testing it out and then run the test command (or save if fully confident). Remember to reschedule later. Note 5 minutes is the lowest frequency you can use. Read the rest of this entry »

Posted in Grid control and agents, Oracle | Tagged: , , , | 7 Comments »

Using Grid to display database CPU usage

Posted by John Hallas on September 3, 2010

There was a recent post on the Oracle -L list asking  about using Grid Control to  report on a particular databases cpu usage during a certain period of time. A number of answers came in showing  the sql queries that would answer the question but I saw the question being ‘ how can we display the CPU usage in Grid’  or indeed how can we produce a customised metric report on any database in Grid

However for those who are interested in the recommended scripted methods then the the answers that were of most use in my view were from Karl Arao pointing to  a script he has written and Rich Jesse produced the following code

sysman.mgmt$metric_daily mmd
sysman.mgmt$target mt
ON mmd.target_name = mt.target_name
AND mmd.target_type = mt.target_type
AND mmd.target_guid = mt.target_guid
mmd.metric_column like '%cpu%'
AND mt.target_name = :DB_NAME
AND mt.target_type = 'oracle_database';

My method was to create a report that could be used to report on any instance and this is how I did it. Read the rest of this entry »

Posted in Grid control and agents, Oracle, scripts | Tagged: , , | 1 Comment »