Queries on OEM Repository to find some valuable information

 

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

Index Fragmentation Query

 set termout off verify off

define owner='DEMO'         -- table owner

define table='DEMOTABLE'    -- table name

define index='DEMOINDEX'    -- index name

define buckets=10           -- number of buckets

define sample=100           -- 100% scans all the index

column "free" format A5

variable c refcursor;

declare

 o all_indexes.owner%TYPE:='&owner';

 t all_indexes.table_name%TYPE:='&table';

 i all_indexes.table_name%TYPE:='&index';

 oid all_objects.object_id%TYPE;

 hsz varchar2(2000);

 n number:=&buckets;

 p number:=&sample;

 s varchar2(2000):='';

 k_min varchar2(2000);

 k_max varchar2(2000);

 k_lst varchar2(2000);

 k_nul varchar2(2000);

 k_vsz varchar2(2000);

 p_sam varchar2(2000):='';

 cursor cols is select i.column_name,i.column_position,case when data_type in ('VARCHAR2','RAW') then 3 else 1 end length_bytes

  from dba_ind_columns i join dba_tab_columns t 

  on (t.owner=i.table_owner and t.table_name=i.table_name and t.column_name=i.column_name)

  where i.table_owner=o and i.table_name=t and i.index_name=i order by column_position;

 procedure add(l in varchar2,i number default 0) is begin s:=s||chr(10)||rpad(' ',i)||l; end;

begin

 select object_id into oid from dba_objects where object_type='INDEX' and owner=o and object_name=i;

 /* Note:10640.1: block header size = fixed header (113 bytes) + variable transaction header (23*initrans) */

 select nvl(to_char(block_size - 113 - ini_trans*23),'null') header_size into hsz 

  from dba_indexes left outer join dba_tablespaces using (tablespace_name) where owner=o and index_name=i;

 for c in cols loop

  if ( c.column_position > 1 ) then k_lst:=k_lst||',' ; k_min:=k_min||',';k_max:=k_max||','; k_nul:=k_nul||' and ' ; k_vsz:=k_vsz||'+' ; end if;

  k_lst:=k_lst||c.column_name;

  k_nul:=k_nul||c.column_name|| ' is not null';

  k_min:=k_min||'min('||c.column_name||') '||c.column_name;

  k_max:=k_max||'max('||c.column_name||') '||c.column_name;

  k_vsz:=k_vsz||'nvl(vsize('||c.column_name||'),1)+'||c.length_bytes;

 end loop;

 if p != 100 then p_sam:='sample block('||p||')'; end if;

 add('with leaf_blocks as (',0);

 add('select /* cursor_sharing_exact dynamic_sampling(0) no_monitoring',1);

 add(' no_expand index_ffs('||t||','||i||') noparallel_index('||t||','||i||') */',10);

 add(k_min||','||1/(p/100)||'*count(rowid) num_rows',1);

 add(','||1/(p/100)||'*sum(1+vsize(rowid)+'||k_vsz||') vsize',1);

 add('from '||o||'.'||t||' '||p_sam||' '||t,1);

 add('where '||k_nul,1);

 add('group by sys_op_lbid('||oid||',''L'',rowid)',1);

 add('),keys as (',0);

 add('select ntile('||n||') over (order by '||k_lst||') bucket,',1);

 add(k_min||',',2);

 add('count(*) leaf_blocks, count(*)*'||hsz||' tsize,',2);

 add('sum(num_rows) num_rows,sum(vsize) vsize',2);

 add('from leaf_blocks group by '||k_lst,1);

 add(')',0);

 add('select '||k_min||',''->'' "->",'||k_max||',round(sum(num_rows)/sum(leaf_blocks)) "rows/block"',0);

 add(',round(sum(vsize)/sum(leaf_blocks)) "bytes/block",',1);

 add('case when sum(vsize)<=sum(tsize) then 100*round(1- sum(vsize) / (sum(tsize)),2) else null end "%free space",',1);

 add(' sum(leaf_blocks) "blocks",');

 --add('case when sum(vsize)<=sum(tsize)/2 then substr(rpad(''o'',5*round(1- sum(vsize) / (sum(tsize)),2),''o''),1,5) end "free"',1);

 add('substr(rpad(''o'',5*round(1- sum(vsize) / (sum(tsize)),2),''o''),1,5) "free"',1);

 add('from keys group by bucket order by bucket',0);

 begin open :c for s ; exception when others then dbms_output.put_line(s); raise; end ;

 dbms_output.put_line(s);

end;

/

set termout on 

print c