Database and server details Report from OEM

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

 

 

Leave a Reply

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