Nov 25, 2020

Queries on OEM Repository to find some valuable information

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');


1 comment:

Translate >>