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 ####################################################################################### --Database Version and status report-- set lines 200 col DATABASE_NAME for a40 col DATABASE_VERSION for a20 col SERVER for a40 col OS for a20 col OS_VERSION for a60 select db.TARGET_NAME DATABASE_NAME, prop.PROPERTY_VALUE DATABASE_VERSION, os.TARGET_NAME SERVER, av.AVAILABILITY_STATUS from SYSMAN.MGMT$TARGET db, SYSMAN.MGMT$TARGET os, SYSMAN.MGMT$TARGET_PROPERTIES prop , MGMT$AVAILABILITY_CURRENT av where db.HOST_NAME = os.TARGET_NAME and db.TARGET_GUID = prop.TARGET_GUID and db.TARGET_NAME = av.TARGET_NAME and prop.PROPERTY_NAME='Version' and db.target_type='oracle_database' and os.target_type='host' order by 1;