oem 常见视图和应用场景

1 获取主机的IP信息；

select * from mgmt$hw_nic

2 获取19c pdb的配置项信息

with tab_a as (
SELECT A.AGGREGATE_TARGET_NAME racname,a.MEMBER_TARGET_NAME inst_name,c.host_name,c.TYPE_QUALIFIER4 roleinfo,b.TYPE_QUALIFIER2 roleinfo1
FROM MGMT$TARGET_MEMBERS A, MGMT$TARGET B, MGMT$TARGET c
WHERE A.AGGREGATE_TARGET_NAME=B.TARGET_NAME
AND A.AGGREGATE_TARGET_TYPE='rac_database'
and a.MEMBER_TARGET_TYPE='oracle_database'
and a.MEMBER_TARGET_NAME=c.target_name
),
     tab_b as (
SELECT A.AGGREGATE_TARGET_NAME racname,a.MEMBER_TARGET_NAME pdbname
 FROM MGMT$TARGET_MEMBERS A, MGMT$TARGET B
WHERE A.AGGREGATE_TARGET_NAME=B.TARGET_NAME
AND A.AGGREGATE_TARGET_TYPE='rac_database'
and a.MEMBER_TARGET_TYPE='oracle_pdb'
and lower(a.MEMBER_TARGET_NAME) not like '%root%'
     ),
     tab_c as (select * from mgmt$target where target_type='osm_cluster'),
     tab_d as (select distinct target_name,value,ip_addr from MGMT$HW_HOSTS_FILE where lower(target_name||'-vip')=lower(value))
select a.*,b.pdbname,substrb(b.pdbname,instr(b.pdbname,'_')+1) pdbname_jx,c.target_name,d.ip_addr from tab_a a  , tab_b b,tab_c c,tab_d d
where  b.racname =A.racname(+)
   and a.host_name=c.host_name(+)
   and a.host_name=d.target_name(+)
order by substrb(b.pdbname,instr(b.pdbname,'_')+1) ,2 

 3 未关闭告警

SELECT  extract(hour FROM (CURRENT_TIMESTAMP-a.COLLECTION_TIMESTAMP) )+extract(DAY FROM (CURRENT_TIMESTAMP-a.COLLECTION_TIMESTAMP) )*24 duration_hours_alert,
        COLLECTION_TIMESTAMP                                                                                                                                ,
        TARGET_NAME                                                                                                                                         ,
        TARGET_TYPE                                                                                                                                         ,
        METRIC_NAME                                                                                                                                         ,
        MESSAGE
FROM    mgmt$alert_current a
WHERE   COLLECTION_TIMESTAMP>sysdate-7
ORDER BY sysdate-a.COLLECTION_TIMESTAMP DESC