Oracle Enterprise Manager Cloud Control Information and Queries

 

What Is OEM? 

Oracle Enterprise Manager (OEM) has built-in management capabilities that enable DBAs and Apps DBAs to monitor and manage the complete Oracle IT infrastructure including the Databases and Applications on Cloud as well as On-Premise from a single console.

Oracle Management Server:

In relational database management systems and in the particular context of an Oracle Enterprise Manager (OEM) environment, an Oracle Management Server (OMS) is a software system that functions as a middle tier between Oracle intelligent agents and Oracle management consoles. The system may operate on multiple nodes and by default uses a schema named DBSNMP.^[1] Through this system, database administrators may view and control their OEM domain(s).

An OMS has special links with a repository database, used for storing OEM details.

Oracle Enterprise Manager is Oracle’s solution to manage/monitor various database/applications which may be running on Cloud infrastructure or on-premise. OEM 13c is the latest version at the time of writing this post. In this post we will discuss about the architectural components which are required by Oracle Enterprise Manager.

Below is simple architectural representations of an OEM 13c setup.

 

 

All the required architectural components are marked with numbers and are described below:

 

1) AGENTS

Agent or management agent is a piece of software that runs on every host that you want to monitor. Management agent work in conjunction with plug-ins to monitor the target server. All hosts where you install the agents are then termed as “Managed Hosts”.

Agents can be of two types:

CENTRAL AGENT : When you first install Oracle Management Service (OMS), by default you receive a Management Agent called the Central Agent. It is used for monitoring the OMS host, the OMS, and the other targets running on this OMS.
host.

STANDALONE TARGET AGENT : To monitor other hosts and the targets running on those hosts, you must install a separate Standalone Management Agent on each of those hosts.

 

2) ORACLE MANAGEMENT SERVICE

OMS is the actual brain of OEM. OMS is deployed over Weblogic 13c. It is a web-based application and perform below broad level activities:

• Works with the Management Agents and the plug-ins to discover targets.
• Monitor and manage the agents
• Store the collected information in a repository for future reference and analysis
• Renders the user interface for Enterprise Manager Cloud Control.

 

3) ORACLE MANAGEMENT REPOSITORY

Oracle Management Repository is simply a database/database schema where all the information collected by the Management Agent gets stored. The Management Repository then organizes the data so that it can be retrieved by the OMS and displayed in the Enterprise Manager Cloud Control console.

 

4) PLUG-INS

Plug-ins as name suggest are pluggable entities that offer special management capabilities customized to suit specific target types. Example if your target type is Oracle EBS, you will need specific plug-ins to monitor the Oracle EBS. Plug-ins are deployed to the OMS as well as the Management Agent in OEM 13c.
Good thing about Plug-ins is that they have independent release cycles, so every time you have a new version of an Oracle product released, you will have a new version of the plug-in released to support monitoring of that new product version in Enterprise Manager Cloud Control.

Default Plugins that OEM 13c will install are:

• Oracle Database
• Oracle Fusion Middleware
• Oracle Exadata
• Oracle Cloud Framework
• Oracle System Infrastructure

In addition to these plug-ins, you can optionally install other plug-ins available in the software kit.

 

5) BI PUBLISHER

Oracle Business Intelligence (BI) Publisher is Oracle’s primary reporting tool for authoring, managing, and delivering all your highly formatted documents. Starting with Oracle Enterprise Manager 13c, Oracle BI Publisher is installed and configured by default on the OMS.

 

6) CONSOLE

Console is the GUI Front end of the OEM application. With the help of the console, you can monitor and administer your entire computing environment from one location. All the systems and services including enterprise application systems, databases, hosts, middleware application servers, listeners etc will be visible through Console

 

7) EM CLI

The Enterprise Manager Command Line Interface (EM CLI) is a command-line too that is accessible through classic programming language constructs, enabling tasks t be created and run either from the command-line or programatically.

 

8) TARGETS

A target, or more specifically, a target instance, can be defined as any entity that can be monitored within an enterprise. Managed targets are the entities that Enterprise Manager can monitor and manage. Examples of targets include hosts, databases, application servers, applications, and listeners. As your environment changes, you can add and remove targets from Enterprise Manager as required.

 

9) CONNECTORS

Connector is a very specialized piece of software whose work is to be act like an intermediary between OEM and third party application like BMC Remedy Ticket generation system. Connectors make your life easier in the sense that they give you ready-made solution to connect your OEM system to other famous third party applications.

 

10) JVMD ENGINE

Java Virtual Machine Diagnostics (JVMD) Engine enables you to diagnose performance problems in Java applications. Starting with Oracle Enterprise Manager 13c , as part of the Oracle Fusion Middleware Plug-in deployment, one JVMD Engine is installed and configured by default on the
OMS. You will also need JVMD Agents to be manually deployed on the targeted JVMs.

 

How Grid Repository Stores Data?

The agents upload data at MGMT_METRIC_RAW table which is retained for 7 days.

Raw data are aggregated by Grid Control based on a predefined policy into 1-hour records, stored in MGMT_METRIC_1HOUR.

After 31 days or a month, 1-hour records in MGMT_METRIC_1HOUR are subject for further aggregation that rolls into 1-day records.

These 1-day records are stored in MGMT_METRIC_1Day for 365 days or 1 year before purged by Grid Control.
Modify these retention policies by updating the mgmt_parameters table in the OMR.

Table Name                   Retention Parameter                  Retention Days

MGMT_METRICS_RAW             mgmt_raw_keep_window                    7
MGMT_METRICS_1HOUR           mgmt_hour_keep_window                   31
MGMT_METRICS_1DAY            mgmt_day_keep_window                    365

To query data in above tables, we need to know TARGET_GUID and METRIC_GUID.

For easier access using names "target names" or "metric names" 

we can use 3 views MGMT$METRIC_DETAILS, MGMT$METRIC_HOURLY, and MGMT$METRIC_DAILY.

These views owned by SYSMAN are based on these 3 main tables but having extra columns like 'target_name', 'metric_name' for easier reference.

Following SQL will return definitions of MGMT$METRIC_DETAILS,METRIC_HOURLY & METRIC_DAILY

***********************************************************************************

select VIEW_NAME
, TEXT_LENGTH
, TEXT
from dba_views
where OWNER = 'SYSMAN'
and VIEW_NAME in ( 'MGMT$METRIC_DETAILS'
, 'MGMT$METRIC_HOURLY'
, 'MGMT$METRIC_DAILY' );

List all the metrics available in Enterprise Manager Repository Views
**********************************************************************

SELECT distinct metric_name,
metric_column,
metric_label,
metric_column
FROM MGMT$METRIC_DAILY
ORDER BY 1,2,3;


Let's have a look at attributes stored at these views which we could use for reporting.
The common attributes are "Property_name" & "target_type".

select unique property_name from mgmt$target_properties order by 1;
*******************************************************************
CRSHome
CRSSoftwareVersion
CRSVersion
CRSVersionCategory
ClusterName
DBDomain
DBName
DBVersion
InstanceName
SID
DataGuardStatus
DatabaseName
DatabaseType
DatabaseUser
DbBlockSize
OpenMode
OracleHome
StartTime
StatisticsLevel
background_dump_dest
core_dump_dest
ListenAddress
ListenPort
ListenerOraDir
LsnrName
HARDWARE_TYPE
Machine
MachineName
IP_address
CPUCount
OS
OS_patchlevel
RACInstNum
RACOption


select unique target_type from mgmt$target order by 1;
******************************************************
cluster
composite
host
j2ee_application
metadata_repository
netapp_filer
oracle_csa_collector
oracle_database
oracle_emd
oracle_emrep
oracle_ias_farm
oracle_listener
rac_database
weblogic_domain

weblogic_j2eeserver

Useful OEM Queries to get Target details from OEM Repository

RMAN backup report:

*****************************

select database_name as "Database",
       start_time as "Start",
       end_time as "End",
       status as "Status",
       input_type as "Type",
       output_device_type as "Device",
       output_bytes_display as "Size"
from mgmt$ha_backup
where input_type like '%FULL'
order by database_name ;

 

Database size query:
***********************

col target_name for a60;
select target_name,min(average) keep ( DENSE_RANK FIRST ORDER BY to_char(rollup_timestamp,'YYYY-MM-DD') ) FIRST,
max(average) keep ( DENSE_RANK  LAST ORDER BY to_char(rollup_timestamp,'YYYY-MM-DD') )   LAST from 
mgmt$metric_daily where target_type in ('rac_database', 'oracle_database' ) and column_label = 'Allocated Space(GB)'
and rollup_timestamp >sysdate -10 group by target_name order by target_name;

select target_name,min(average) keep ( DENSE_RANK FIRST ORDER BY to_char(rollup_timestamp,'YYYY-MM-DD') ) FIRST,
max(average) keep ( DENSE_RANK  LAST ORDER BY to_char(rollup_timestamp,'YYYY-MM-DD') )   LAST from 
mgmt$metric_daily where target_type in ('rac_database', 'oracle_database' ) and column_label = 'Allocated Space(GB)'
and rollup_timestamp >sysdate -30 group by target_name order by target_name;

Tablespace Query
**********************

col DATABASE_NAME for a50;
col TABLESPACE_NAME for a20;
col ENTITY_TYPE for a20;
SELECT entity_name as DATABASE_NAME, entity_type, key_part_1 as TABLESPACE_NAME, 
       sum(case WHEN metric_column_name = 'pctUsed' THEN num_value ELSE 0 END) USED_PERCENTAGE,
       sum(case WHEN metric_column_name = 'bytesFree' THEN num_value/1024 ELSE 0 END) FREE_TS_SIZE_GB,
         (100*sum(case WHEN metric_column_name = 'bytesFree' THEN num_value/1024 ELSE 0 END))/(100-sum(case WHEN metric_column_name = 'pctUsed' THEN num_value ELSE 0 END)+0.0000001) TOTAL_TS_SIZE_GB
FROM sysman.gc$metric_latest
WHERE metric_group_name = 'problemTbsp'
GROUP BY entity_name, entity_type, key_part_1
order by 1,3;


Archive mount point space query:
*********************************

select Target_name,key_value,value,to_char(collection_timestamp,'YYYY-MM-DD HH24:MI:SS')
as Collection_time from sysman.MGMT$METRIC_CURRENT where target_type in ('oracle_database','rac_database') and metric_label in ( 'Archive Area')
and column_label in ('Archive Area Used (%)'); and  collection_timestamp>sysdate-30;




All the metrics for all the database targets
*************************************************


SELECT t.target_name target_name,
       t.metric_name,
       m.metric_column metric_column,
       to_char(m.rollup_timestamp,'YYYY-MM-DD HH24') as TIME,
       sum(m.average/1024) as value
FROM   mgmt$metric_hourly M,
       mgmt$target_type T
WHERE  t.target_type='oracle_database'
       and m.target_guid=t.target_guid
       and m.metric_guid=t.metric_guid
GROUP BY  t.target_name,
          t.metric_name,
          m.metric_column,
          m.rollup_timestamp
ORDER BY 1,2,3;
Current value for the metric iombs_ps
SELECT t.target_name target_name,
     t.metric_name,
     m.metric_column metric_column,
     to_char(m.collection_timestamp,'YYYY-MM-DD HH24:MI') as TIME,
     m.value as value
FROM mgmt$metric_current M,
     mgmt$target_type T
WHERE t.target_type='oracle_database'
      and m.target_guid=t.target_guid
      and m.metric_guid=t.metric_guid
      and t.metric_name='instance_throughput'
      and t.metric_column='iombs_ps'
      ORDER BY 1,2,3;
  
  
Historic data for the metric iombs_ps per hour
****************************************************

SELECT t.target_name target_name,
            t.metric_name,
            m.metric_column metric_column,
            to_char(m.rollup_timestamp,'YYYY-MM-DD HH24') as TIME,
            sum(m.average/1024) as value
FROM        mgmt$metric_hourly M,
            mgmt$target_type T
WHERE       t.target_type='oracle_database'
            and m.target_guid=t.target_guid
            and m.metric_guid=t.metric_guid
            and t.metric_name='instance_throughput'
            and t.metric_column='iombs_ps'
GROUP BY t.target_name,
         t.metric_name,
         m.metric_column,
         m.rollup_timestamp
ORDER BY 1,2,3; 

Historic data for the metric iombs_ps per day
****************************************************

SELECT t.target_name target_name,
            t.metric_name,
            m.metric_column metric_column,
            to_char(m.rollup_timestamp,'YYYY-MM-DD') as TIME,
            sum(m.average/1024) as value
FROM        mgmt$metric_daily M,
            mgmt$target_type T
WHERE       t.target_type='oracle_database'
            and m.target_guid=t.target_guid
            and m.metric_guid=t.metric_guid
            and t.metric_name='instance_throughput'
            and t.metric_column='iombs_ps'
GROUP BY t.target_name, t.metric_name, m.metric_column, m.rollup_timestamp
ORDER BY 1,2,3;

List Targets with TNS Listener ports configured :
****************************************************

SELECT mgmt$target.host_name
 , mgmt$target.target_name
 , mgmt$target.target_type
 , mgmt$target_properties.property_name
 , mgmt$target_properties.property_value
 FROM mgmt$target
 , mgmt$target_properties
 WHERE ( mgmt$target.target_name = mgmt$target_properties.target_name )
 AND ( mgmt$target.target_type = mgmt$target_properties.target_type )
 and ( mgmt$target.target_type = 'oracle_listener' )
 and ( mgmt$target_properties.property_name = 'Port' );
 
List Machine_Names, CPU Count & Database Verion for Licensing
**************************************************************

SELECT mgmt$target.host_name
 , mgmt$target_properties.property_name
 , mgmt$target_properties.property_value
 FROM mgmt$target
 , mgmt$target_properties
 WHERE ( mgmt$target.target_name = mgmt$target_properties.target_name )
 AND ( mgmt$target.target_type = mgmt$target_properties.target_type )
 AND ( mgmt$target_properties.property_name in ( 'CPUCount','DBVersion' ) )
 GROUP BY mgmt$target.host_name
 , mgmt$target_properties.property_name
 , mgmt$target_properties.property_value
 order by mgmt$target.host_name;
List Dataguard Instances mounted in APPLY mode
SELECT mgmt$target.host_name
 , mgmt$target.target_name
 , mgmt$target.target_type
 , mgmt$target_properties.property_name
 , mgmt$target_properties.property_value
 FROM mgmt$target
 , mgmt$target_properties
 WHERE ( mgmt$target.target_name = mgmt$target_properties.target_name )
 AND ( mgmt$target.target_type = mgmt$target_properties.target_type )
 and ( mgmt$target.target_type = 'oracle_database' )
 and ( mgmt$target_properties.property_name = 'OpenMode' )
 and PROPERTY_VALUE like 'READ%ONLY%WITH%APPLY%';

 
List RAC databases and their Attributes like ClusterName, Dataguard Status
***************************************************************************

Change "property_name" attribute per your need
SELECT mgmt$target.host_name
 , mgmt$target.target_name
 , mgmt$target.target_type
 , mgmt$target_properties.property_name
 , mgmt$target_properties.property_value
 FROM mgmt$target
 , mgmt$target_properties
 WHERE ( mgmt$target.target_name = mgmt$target_properties.target_name )
 AND ( mgmt$target.target_type = mgmt$target_properties.target_type )
 and ( mgmt$target.target_type = 'rac_database' )
 and ( mgmt$target_properties.property_name in ( 'RACOption'
 , 'DBName'
 , 'DBDomain'
 , 'DBVersion'
 , 'ClusterName'
 , 'DataGuardStatus'
 , 'MachineName'
 , 'Role'
 , 'SID' ) )
 order by mgmt$target.host_name, mgmt$target.target_name,
mgmt$target_properties.property_name; 

 
Following SQL will report All Database Target details monitored through OEM
*************************************************************************

select t.host_name
 as     host
 , ip.property_value IP
 , t.target_name
 as     name
 , decode ( t.type_qualifier4
 , ' '
 , 'Normal'
 , t.type_qualifier4 )
 as     type
 , dbv.property_value
 as     version
 , port.property_value port
 , SID.property_value SID
 , logmode.property_value
 as     "Log Mode"
 , oh.property_value
 as     "Oracle Home"
 from mgmt$target t
 , ( select p.target_guid
 , p.property_value
 from mgmt$target_properties p
 where p.property_name = 'DBVersion' ) dbv
 , ( select p.target_guid
 , p.property_value
 from mgmt$target_properties p
 where p.property_name = 'Port' ) port
 , ( select p.target_guid
 , p.property_value
 from mgmt$target_properties p
 where p.property_name = 'SID' ) sid
 , ( select p.target_guid
 , p.property_value
 from mgmt$target_properties p
 where p.property_name = 'log_archive_mode' ) logmode
 , ( select p.target_guid
 , p.property_value
 from mgmt$target_properties p
 where p.property_name = 'OracleHome' ) oh
 , ( select tp.target_name
 as     host_name
 , tp.property_value
 from mgmt$target_properties tp
 where tp.target_type = 'host'
 and tp.property_name = 'IP_address' ) ip
 where t.target_guid = port.target_guid
 and port.target_guid = sid.target_guid
 and sid.target_guid = dbv.target_guid
 and dbv.target_guid = logmode.target_guid
 and logmode.target_guid = oh.target_guid
 and t.host_name = ip.host_name
 order by 1, 3;
 
SQL to report Oracle init parameters for a Target database
***********************************************************
SELECT   target_name,
        target_type,
        name,
        VALUE
 FROM   MGMT$ECM_VISIBLE_SNAPSHOTS A, SYSMAN.MGMT_DB_INIT_PARAMS_ECM B
WHERE       A.ECM_SNAPSHOT_ID = B.ECM_SNAPSHOT_ID
           AND TARGET_TYPE = 'rac_database'  -- Choose TARGET_TYPE
        AND name LIKE 'remote_listener%'     -- Look for a relevant Parameter
GROUP BY   target_name,
           target_type,
           name,
           VALUE
ORDER BY   Target_name, name ;


Below query could provide Month wise database growth.
**********************************************************

SELECT      TARGET_NAME, TRUNC(ROLLUP_TIMESTAMP, 'MON') Month, round(AVG(SIZE_GB),2) Size_GB
FROM
(
SELECT      A.TARGET_NAME, A.rollup_timestamp, round((A.average),2) size_gb,
FIRST_VALUE(A.average) OVER (PARTITION BY a.TARGET_NAME ORDER BY A.rollup_timestamp) first_val,
LAST_VALUE(A.average) OVER (PARTITION BY a.TARGET_NAME ORDER BY A.rollup_timestamp RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) Last_val
FROM        sysman.mgmt$metric_daily A, mgmt$target B
WHERE       A.TARGET_NAME = B.TARGET_NAME
AND         A.TARGET_TYPE=B.TARGET_TYPE
AND         A.column_label = 'Used Space(GB)'
AND         B.target_type IN ('oracle_database','rac_database')
AND         (B.type_qualifier4 = 'Primary'  OR   B.type_qualifier2 = 'Primary' )
AND         B.type_qualifier3 <> 'RACINST'
— AND         A.TARGET_NAME IN ('TEST')  – To Filter based on database
— AND       B.HOST_NAME IN ('SOMEMACHINE')  – Just filter data based on host
— AND TRUNC(A.rollup_timestamp,'MON') BETWEEN ADD_MONTHS(TRUNC(SYSDATE,'MON'),-3) AND TRUNC(SYSDATE,'MON')
ORDER BY    1,2
)
GROUP BY TARGET_NAME, TRUNC(ROLLUP_TIMESTAMP, 'MON')
ORDER BY TARGET_NAME, 2 ASC;


Databse Growth rate:
**********************************************************
 
SELECT      TARGET_NAME DB_Name,
To_char(min(rollup_timestamp),'MON-YYYY') "First_Collected",
TO_CHAR(max(rollup_timestamp),'MON-YYYY') "Last_Collected",
round(((max(rollup_timestamp)-  min(rollup_timestamp))/30)) "NoOfMonths",
min(first_val) "Initial", Min(last_val) "Current", (min(last_val)-min(first_val)) "Increased_By_GB",
round(((min(last_val)-min(first_val))/min(first_val))*100,2) as "%age_Increase"
FROM
(
SELECT      A.TARGET_NAME, A.rollup_timestamp, round((A.average),2) size_gb,
FIRST_VALUE(A.average) OVER (PARTITION BY a.TARGET_NAME ORDER BY A.rollup_timestamp) first_val,
LAST_VALUE(A.average) OVER (PARTITION BY a.TARGET_NAME ORDER BY A.rollup_timestamp RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) Last_val
FROM        sysman.mgmt$metric_daily A, mgmt$target B
WHERE       A.TARGET_NAME = B.TARGET_NAME
AND         A.TARGET_TYPE=B.TARGET_TYPE
AND         A.column_label = 'Used Space(GB)'
AND         B.target_type IN ('oracle_database','rac_database')
AND         (B.type_qualifier4 = 'Primary'  OR   B.type_qualifier2 = 'Primary' )
AND         B.type_qualifier3 <> 'RACINST'
— AND         A.TARGET_NAME IN ('TEST')  – To Filter based on database
— AND       B.HOST_NAME IN ('SOMEMACHINE')  – Just filter data based on host
— AND TRUNC(A.rollup_timestamp,'MON') BETWEEN ADD_MONTHS(TRUNC(SYSDATE,'MON'),-3) AND TRUNC(SYSDATE,'MON')
ORDER BY    1,2
)
GROUP BY TARGET_NAME
ORDER BY TARGET_NAME;
 
Database Total Used and Allocated Size from OEM Repository
**********************************************************

SELECT Database,
Month_Date,
round(sum(decode(metric_column, 'spaceUsed', maximum))/1024/1024, 3) Used_Size_Tb,
round(sum(decode(metric_column, 'spaceAllocated', maximum))/1024/1024, 3) Allocated_Size_Tb
FROM
(
SELECT target_name Database, trunc(rollup_timestamp, 'MONTH') Month_Date, key_value TB, metric_column, round(max(maximum),0) maximum
FROM mgmt$metric_daily
WHERE target_type = 'rac_database'
and metric_name = 'tbspAllocation'
and metric_column in ('spaceAllocated', 'spaceUsed')
and target_name in ('VERS')
GROUP BY target_name, key_value, trunc(rollup_timestamp, 'MONTH'), metric_column
)
GROUP BY Database, Month_Date ORDER BY Database, Month_Date;

+++++++++++ASM DISK GROUP USAGE DETAILS FOR MUC SYSTEMS +++++++++++++++++++++++++++
 
set lines 200 pages 200;
col TARGET_NAME for a40
col TARGET_TYPE for a15;
col DG_NAME for a20;
select
    target_name,
    target_type,
    key_value dg_name,
    round(max(decode(metric_column,'total_mb',maximum,0))/1024/1024/1024,2) used_space_GB,
    max(decode(metric_column,'usable_total_mb',maximum,0))/1024 total_space_GB,
    round(max(decode(metric_column,'percent_used',maximum,0)),1) percentage_used,
      100-round(max(decode(metric_column,'percent_used',maximum,0)),1) percentage_free,
    trunc(rollup_timestamp) stat_timestamp
  from SYSMAN.MGMT$METRIC_DAILY a
  where target_type in ('osm_instance','osm_cluster')
  and metric_column in ('total_mb','usable_total_mb','percent_used')
  and rollup_timestamp>=trunc(sysdate-1)
  and trunc(rollup_timestamp)=trunc(sysdate-1)
  and not exists
  ( select 'a'
      from SYSMAN.MGMT$TARGET_MEMBERS
        where aggregate_target_type='osm_cluster'
          and member_target_name=a.target_name
        and member_target_type='osm_instance'
  )
  group by target_name, target_type, key_value, rollup_timestamp, trunc(rollup_timestamp)
  order by 1,3;

Use below link for, how to Generate Custom Report from OEM 13c Cloud Control?

https://ora-am.blogspot.com/2020/10/how-to-generate-custom-report-from-oem.html