Database and server details report from OEM

Database and server details Report from OEM:

Database version and Server Details

1- How do I get the number of databases for each category version?
SELECT property_value, COUNT(*)
FROM mgmt$target_properties
WHERE target_type = 'oracle_database'
AND property_name = 'VersionCategory'
GROUP BY property_value;
PROPERTY_VALUE                   COUNT(*)
------------------------------ ----------
12c                                   129
10gR204                                 8
11gR202                               584
 

2 -How do I get the number of databases for each category version and CPU count?
col version format a25
col CPU_Count format a10
SELECT p1.property_value "Version", p2.property_value "CPU_Count", COUNT(*) "Total"
FROM mgmt$target_properties p1, mgmt$target_properties p2
WHERE p1.target_type = 'oracle_database'
AND p1.target_guid = p2.target_guid
AND p1.property_name = 'VersionCategory'
AND p2.property_name = 'CPUCount'
GROUP BY p1.property_value, p2.property_value
ORDER BY p1.property_value, p2.property_value;

Version                   CPU_Count       Total
------------------------- ---------- ----------
10gR204                   8                   8
11gR202                   12                  6
11gR202                   16                 17
11gR202                   2                  48
11gR202                   24                 91
11gR202                   32                134
11gR202                   36                 15
11gR202                   4                  28
11gR202                   72                202
11gR202                   8                  43
12c                       36                  8
12c                       72                121
 
12 rows selected.

3- How do I get the number of databases for each category version and OS platform?

col Platform format a20
SELECT p3.property_value "Platform", p1.property_value "Version", COUNT(*) "Total"
FROM mgmt$target_properties p1, mgmt$target_properties p2, mgmt$target_properties p3
WHERE p1.target_type = 'oracle_database'
AND p1.target_guid = p2.target_guid
AND p3.target_name = p2.property_value
AND p3.target_type = 'host'
AND p1.property_name = 'VersionCategory'
AND p2.property_name = 'MachineName'
AND p3.property_name = 'OS'
GROUP BY p3.property_value, p1.property_value
ORDER BY p3.property_value, p1.property_value;

Platform             Version                        Total
-------------------- ------------------------- ----------
AIX                  10gR204                            8
AIX                  11gR202                           40
Linux                11gR202                          196
Linux                12c                                    30

4 -How do I find the number of hosts grouped by the operating system?

SELECT type_qualifier1, COUNT(*) cnt
FROM mgmt$target
WHERE target_type = 'host'
GROUP BY type_qualifier1;
TYPE_QUALIFIER1                                            CNT
---------------------------------------------------------- ----------
Linux                                                                   121
Windows                                                              35
AIX                                                                      20

 

5- How do I view the details of every Management Agent installation?

col host_name format a50
col home_location format a60
col oh_owner format a15
col oh_group format a20
SELECT host_name, home_location, oh_owner, oh_group
FROM mgmt$oh_home_info
WHERE oui_home_name LIKE 'agent12c%'
;

HOST_NAME                                   HOME_LOCATION                                             OH_OWNER    OH_GROUP
--------------------------------------          ------------------------------------------------------------    ---------------  ------------------
aarav382.oracleworlds.com          /opt/oracle/product/agent12cR3/core/12.1.0.3.0               oracle          dba
redpy369.oracleworlds.com         /opt/oracle/product/agent12cR3/core/12.1.0.3.0               oracle          dba
redxp383.oracleworlds.com         /opt/oracle/product/agent12cR3/core/12.1.0.3.0               oracle          dba
aarav380.oracleworlds.com         /opt/oradb/product/agent12cR3/core/12.1.0.3.0                oracle          dba
aarav101.oracleworlds.com           /opt/oracle/product/agent12cR3/core/12.1.0.3.0               oracle          oinstall
aarav321.oracleworlds.com           /opt/oradb/product/agent12cR3/core/12.1.0.3.0                oracle          dba

Leave a Reply

Your email address will not be published. Required fields are marked *