Querying Oracle Management Repository (OEM repository) to dig out for a valuable information. Here we can see very few examples.
How to find Available Target Types in OEM?
SQL> select distinct target_type,type_display_name
from mgmt_targets order by 1;
TARGET_TYPE TYPE_DISPLAY_NAME
------------------------------ ----------------------------------------
cluster Cluster
composite Group
has Oracle High Availability Service
host Host
j2ee_application Application Deployment
j2ee_application_cluster Clustered Application Deployment
j2ee_application_domain Domain Application Deployment
metadata_repository Metadata Repository
oracle_apache Oracle HTTP Server
oracle_beacon Beacon
oracle_bi_publisher Oracle BI Publisher
oracle_cloud Cloud
oracle_coherence Oracle Coherence Cluster
oracle_coherence_cache Oracle Coherence Cache
oracle_coherence_node Oracle Coherence Node
oracle_database Database Instance
oracle_dbsys Database System
oracle_em_service EM Service
oracle_emd Agent
oracle_emrep OMS and Repository
oracle_emsvrs_sys EM Servers System
oracle_home Oracle Home
oracle_listener Listener
oracle_oms Oracle Management Service
oracle_oms_console OMS Console
oracle_oms_pbs OMS Platform
oracle_pdb Pluggable Database
oracle_si_network Systems Infrastructure Network
oracle_si_server_map Systems Infrastructure Server
osm_cluster Cluster ASM
osm_instance Automatic Storage Management
osm_proxy Asm Proxy
rac_database Cluster Database
weblogic_cluster Oracle WebLogic Cluster
weblogic_domain Oracle WebLogic Domain
weblogic_j2eeserver Oracle WebLogic Server
weblogic_nodemanager Oracle WebLogic Node Manager
How to find all registered targets in OEM for a particular target type?
So I want to find all the targets registered in OEM repository whose target type is "rac_database"
in other words, basically I want to find all the rac databases registered in my OEM.
SQL> select target_name,target_type,target_guid
from mgmt_targets where target_type='rac_database';
TARGET_NAME TARGET_TYPE
------------------------------ ------------------------------
HCMPRD rac_database
HCMSTG rac_database
How to find a particular target information registered in OEM?
SQL> select * from mgmt_targets where target_name='HCMPRD';
How to find Important Matrics related to a particular Target?
SQL> select * from mgmt$metric_daily
where target_name = 'HCMPRD' and trunc(rollup_timestamp) >= trunc(sysdate-2);
How to find the daily growth of a database(last 7 days) from OEM repository?
SQL> select rollup_timestamp, average
from sysman.mgmt$metric_daily
where target_name = 'HCMPRD'
and column_label = 'Used Space(GB)'
and trunc(rollup_timestamp) >= trunc(sysdate-7)
order by rollup_timestamp;
ROLLUP_TIME AVERAGE
--------- ----------
18-NOV-20 2794.44
19-NOV-20 2794.45
20-NOV-20 2794.50
21-NOV-20 2794.54
22-NOV-20 2794.65
23-NOV-20 2794.74
How to find database user accounts password is going to expire in next 15 days?
select distinct Target_name "Target_Database",host_name,username,profile,expiry_date
from sysman.mgmt$db_users
where expiry_date is not null and username not like '%SYS%'
and trunc(expiry_date-sysdate) between 0 and 15;
Target_Database HOST_NAME USERNAME PROFILE EXPIRY_DATE
-------------------- ----------------------------- ----------- ------------ ------------
HCMDE2 hcmdev2.example.com U_KMPSMST SECURE 10-DEC-20
HCMDE2 hcmde2.example.com U_SEBUAU SECURE 26-NOV-20
HCMST1 hcmtst1.example.com U_VANSHUI SECURE 26-NOV-20
HCMST1 hcmtst1.example.com U_SDHAGAY SECURE 10-DEC-20
How to find perticular file system usage details from all target hosts:
with FSsize as
(select rollup_timestamp, lower(target_name) hostname, round((average/1024),0) size_gb , key_value filesystem
from SYSMAN.mgmt$metric_daily
where metric_name ='Filesystems'
and rollup_timestamp > trunc(sysdate-2)
and metric_column = 'size'),
FSAvailable as
(select rollup_timestamp, lower(target_name) hostname, round((average/1024),0) size_gb , key_value filesystem
from SYSMAN.mgmt$metric_daily
where metric_name ='Filesystems'
and rollup_timestamp > trunc(sysdate-2)
and metric_column = 'available'),
FSpctAvailable as
(select rollup_timestamp, lower(target_name) hostname, round(average,2) pctAvailable , key_value filesystem
from SYSMAN.mgmt$metric_daily
where metric_name ='Filesystems'
and rollup_timestamp > trunc(sysdate-2)
and metric_column = 'pctAvailable'
)
select
a.rollup_timestamp ts#
, a.hostname
, a.filesystem "Mounted on"
, a.size_gb "FS_Size_GB"
, (a.size_gb-b.size_gb) "FS_Used_GB"
, b.size_gb "FS_Available_GB"
, c.pctavailable "Fs_Free_%"
, 100-c.pctavailable "Fs_Used_%"
from FSsize a, FSAvailable b, FSpctAvailable c
where c.hostname = a.hostname and c.hostname = b.hostname
and c.rollup_timestamp = a.rollup_timestamp and c.rollup_timestamp = b.rollup_timestamp
and c.filesystem = a.filesystem and c.filesystem = b.filesystem
and c.filesystem = '/u01';
How to find CPU Utilization of one host for last 24 hours?
SELECT a.collection_time, a.value
FROM sysman.gc_metric_values a
WHERE TRUNC(a.collection_time) between TRUNC(SYSDATE-1) AND TRUNC(SYSDATE)
AND a.entity_type = 'host'
AND metric_group_name = 'Load'
AND metric_column_name = 'cpuUtil'
AND a.entity_name like 'oratesthost.example.com' order by a.collection_time;
COLLECTION_TIME VALUE
---------------------- ----------
24-NOV-2020 16:57:06 4.149
24-NOV-2020 17:02:06 4.317
24-NOV-2020 17:07:06 4.003
24-NOV-2020 17:12:06 3.093
24-NOV-2020 17:17:06 2.897
24-NOV-2020 17:22:06 3.117
24-NOV-2020 17:27:06 2.782
24-NOV-2020 17:32:06 2.546
24-NOV-2020 17:37:06 2.061
...
How to find Memory Utilization of one host for last 24 hours?
SELECT a.collection_time, a.value
FROM sysman.gc_metric_values a WHERE TRUNC(a.collection_time) between TRUNC(SYSDATE-1) AND TRUNC(SYSDATE)
AND a.entity_type = 'host'
AND metric_group_name = 'Load'
AND metric_column_name = 'memUsedPct'
AND a.entity_name like 'oratesthost.example.com' order by a.collection_time;
COLLECTION_TIME VALUE
-------------------- ----------
...
24-NOV-2020 23:52:06 27.879
24-NOV-2020 23:57:06 27.841
25-NOV-2020 00:02:06 27.865
25-NOV-2020 00:07:06 27.874
25-NOV-2020 00:12:06 27.877
25-NOV-2020 00:17:06 27.878
25-NOV-2020 00:22:06 27.881
25-NOV-2020 00:27:06 27.876
25-NOV-2020 00:32:06 27.877
..
How to find Requests Per Minute for any java application?
SELECT a.collection_time, a.value
FROM sysman.gc_metric_values a WHERE TRUNC(a.collection_time) between TRUNC(SYSDATE-1) AND TRUNC(SYSDATE)
AND a.entity_type = 'weblogic_j2eeserver'
AND metric_group_name = 'server_servlet_jsp'
AND metric_column_name = 'service.throughput'
AND a.entity_name like '%/TST_WEB_webdomain/testwebdomain/hcmapp1%' order by a.collection_time;
COLLECTION_TIME VALUE
-------------------- ----------
24-NOV-2020 16:37:03 707.332
24-NOV-2020 16:52:03 468.336
24-NOV-2020 17:07:03 560.999
24-NOV-2020 17:22:03 421.933
24-NOV-2020 17:37:03 385.066
...
How to find Archive log summary for all Oracle databases from oem?
select distinct database_name "DATABASE_NAME",instance_name "INSTANCE_NAME",log_mode "LOG_MODE",host_name "HOST_NAME"
from mgmt$db_dbninstanceinfo order by LOG_MODE;
DATABASE_NAME INSTANCE_NAME LOG_MODE HOST_NAME
-------------------- ---------------- ------------ --------------------------------
EBSPRD EBSPRD ARCHIVELOG oraprdhost.example.com
EBSVAL EBSVAL ARCHIVELOG oravalhost.example.com
HCMPRD HCMPRD ARCHIVELOG oraprdhost.example.com
....
How to find Backout information details?
select blackout_name "BLACKOUT_NAME",created_by "CREATED_BY",start_time "START_TIME",target_name "TARGET_NAME",
target_type "TARGET_TYPE",status "STATUS"
from SYSMAN.MGMT$BLACKOUT_HISTORY where STATUS in('Partial Blackout','Started');
