Database and server details Report from OEM:
1- How do 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 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