Useful queries to retrieve host details (parameters, patches, etc.) from the OEM database.

Managing a large Oracle database fleet, I frequently run into the need to gather loads of info on servers and virtual machines. These reports are used from day-to-day management to handling organizational tasks like licensing. Sure, you can pull some of this stuff from the standard forms, but more often than not, you're left wanting more.

Below I'm about to share some SQL queries for the OEM database that you can use straight away in "sqlplus" console, or even better, use them as a foundation for whipping up some really cool analytical reports in Business Intelligence Publisher or Oracle Analytics Server.

Query #1: hosts hardware information

Let's kick things off from the ground up by gathering hardware information about all our hosts.

SQL:

SELECT
  HOST,
  CPU_SOCKETS,
  CPU_CORES,
  HYPERTHREAD_ENABLED,
  VCORES,
  CPU_MODEL,
  RAM_GB,
  OPERATION_SYSTEM
FROM (
  SELECT
    table1.TARGET_GUID,
    LOWER(table1.TARGET_NAME) as HOST,
    CPU_SOCKETS,
    CPU_CORES,
    HYPERTHREAD_ENABLED,
    VCORES,
    OPERATION_SYSTEM,
    CPU_MODEL
  FROM (
    SELECT 
      TARGET_GUID,
      TARGET_NAME,
      MAX(INSTANCE_COUNT)*COUNT(*) as CPU_SOCKETS,
      MAX(INSTANCE_COUNT)*SUM(NUM_CORES) as CPU_CORES, 
      DECODE(SUM(IS_HYPERTHREAD_ENABLED),
          0,'NO',
          1,'YES', 
          'YES') --When several CPUs assigned with/without HT, could be higher
          as HYPERTHREAD_ENABLED,
      MAX(INSTANCE_COUNT)*SUM(SIBLINGS) as VCORES,
      DECODE(REGEXP_SUBSTR(LISTAGG(IMPL, ' & ') WITHIN GROUP (ORDER BY 1),'Xeon|Opteron|PA[0-9]+|Family [0-9]+ Model [0-9]+|Itanium'),'',LISTAGG(IMPL, ' & ') WITHIN GROUP (ORDER BY 1),REGEXP_SUBSTR(LISTAGG(IMPL, ' & ') WITHIN GROUP (ORDER BY 1),'Xeon|Opteron|PA[0-9]+|Family [0-9]+ Model [0-9]+|Itanium')) as CPU_MODEL
    FROM
      SYSMAN.MGMT$HW_CPU_DETAILS
    GROUP BY
      TARGET_GUID,
      TARGET_NAME
    ) table1
  LEFT JOIN (
    SELECT 
      table1.TARGET_GUID,    
      table2.PROPERTY_VALUE as OPERATION_SYSTEM
    FROM 
      SYSMAN.MGMT$TARGET table1,
      SYSMAN.MGMT$TARGET_PROPERTIES table2
    WHERE 
      table1.TARGET_TYPE(+)='host' AND 
      table2.TARGET_GUID(+)=table1.TARGET_GUID AND 
      table2.PROPERTY_NAME(+)='OS'    
    ) table2
  ON
    table2.TARGET_GUID=table1.TARGET_GUID
) table3
LEFT JOIN (
  SELECT 
    TARGET_GUID,
    ROUND(MEM/1024) as RAM_GB
  FROM
    SYSMAN.MGMT$OS_HW_SUMMARY
) table4
ON
  table3.TARGET_GUID=table4.TARGET_GUID
ORDER BY
  HOST        

Sample output:

Query #2: OS information

Moving up to the next level and collect detailed information about the operating systems running on our servers.

SQL:

SELECT 
  LOWER(table1.TARGET_NAME) AS HOST_NAME,
  table1.PROPERTY_VALUE AS OS_DISTRIBUTION,
  table2.PROPERTY_VALUE AS OS_VERSION,
  table3.PROPERTY_VALUE AS OS_BASE_VERSION,
  table4.PROPERTY_VALUE AS OS_UPDATE_LEVEL
FROM 
  SYSMAN.MGMT$TARGET_PROPERTIES table1,
  SYSMAN.MGMT$TARGET_PROPERTIES table2,
  SYSMAN.MGMT$TARGET_PROPERTIES table3,
  SYSMAN.MGMT$TARGET_PROPERTIES table4
WHERE 
  table1.TARGET_TYPE(+)='host' AND 
  table2.TARGET_TYPE(+)='host' AND 
  table3.TARGET_TYPE(+)='host' AND     
  table4.TARGET_TYPE(+)='host' AND     
  table1.PROPERTY_NAME(+)='Distribution' AND 
  table2.TARGET_GUID(+)=table1.TARGET_GUID AND 
  table2.PROPERTY_NAME(+)='orcl_gtp_target_version' AND
  table3.TARGET_GUID(+)=table1.TARGET_GUID AND 
  table3.PROPERTY_NAME(+)='os.release' AND
  table4.TARGET_GUID(+)=table1.TARGET_GUID AND 
  table4.PROPERTY_NAME(+)='os.version' 
ORDER BY
  HOST_NAME
;        

Sample output:

Query #3: OS parameters

In many cases, it's crucial to know the values of operating system configuration parameters for monitoring or modifying these values. Below is an example of how to mass extract data from targets for the parameter "fs.file-max", which is essential for the proper functioning of databases. The values are combined with the output of all hosts listed in "MGMT$TARGET" to ensure a complete list of servers, even if the parameter is not found or is irrelevant. Same approach will be used in all following queries.

SQL:

SELECT
  LOWER(table1.TARGET_NAME) as HOST,
  'fs.file-max' as PARAMETER,
  DECODE(table2.PARAM_VALUE, NULL, 'N/A', table2.PARAM_VALUE) AS PARAM_VALUE
FROM
  SYSMAN.MGMT$TARGET table1
LEFT JOIN (
  SELECT 
    TARGET_GUID,
    VALUE as PARAM_VALUE
  FROM
    SYSMAN.MGMT$OS_PROPERTIES
  WHERE
    LOWER(NAME) = 'fs.file-max'
) table2
ON
  table1.TARGET_GUID=table2.TARGET_GUID
WHERE 
  TARGET_TYPE='host'
ORDER BY 
  TARGET_NAME
;        

Sample output:

Query #4: OS packages

For the correct installation and functioning of Oracle components, the presence of specific operating system packages and their versions is crucial. Below, you will find an example of how to gather information on the "libstdc++" package.

SQL:

SELECT
  LOWER(table1.TARGET_NAME) as HOST,
  'libstdc++' as PACKAGE,
  DECODE(table2.VERSION, NULL, 'N/A', table2.VERSION) AS VERSION
FROM
  SYSMAN.MGMT$TARGET table1
LEFT JOIN (
  SELECT 
    VERSION,
    TARGET_GUID
  FROM
    SYSMAN.MGMT$OS_COMPONENTS
  WHERE
    CTYPE='Package' AND
    NAME='libstdc++'
) table2
ON
  table1.TARGET_GUID=table2.TARGET_GUID
WHERE 
  TARGET_TYPE='host'
ORDER BY 
  TARGET_NAME
;        

Sample output:

Query #5: OS modules

The following example could prove invaluable during the migration process from Oracle AFD to ASMLIB, or vice versa. Oracle strongly advises against keeping both libraries on the host, highlighting the importance of this consideration. Let's delve into verifying that we haven't overlooked this crucial step, ensuring our migration process is smooth and compliant with Oracle's best practices.

SQL:

SELECT
  LOWER(table1.TARGET_NAME) as HOST,
  DECODE(table2.MODULES, NULL, 'N/A', table2.MODULES) AS MODULES
FROM
  SYSMAN.MGMT$TARGET table1
LEFT JOIN (
  SELECT 
    TARGET_GUID, 
    LISTAGG(NAME,', ') WITHIN GROUP (ORDER BY NAME) as MODULES
  FROM 
    SYSMAN.MGMT$OS_MODULES 
  WHERE 
    LOWER(NAME) IN ('oracleasm','oracleafd')
  GROUP BY 
    TARGET_GUID
) table2
ON
  table1.TARGET_GUID=table2.TARGET_GUID
WHERE 
  TARGET_TYPE='host'
ORDER BY 
  HOST
;        

Sample output:

Query #6: Nameservers

The final SQL query can be utilized for overseeing server readiness or for detecting potential alterations in a critical area such as the configuration of DNS servers.

SQL:

SELECT
  LOWER(table1.TARGET_NAME) as HOST,
  DECODE(table2.NAMESERVERS, NULL, 'N/A', table2.NAMESERVERS) AS NAMESERVERS
FROM
  SYSMAN.MGMT$TARGET table1
LEFT JOIN (
  SELECT
    TARGET_GUID,
    LISTAGG(VALUE,', ') WITHIN GROUP (ORDER BY VALUE) as NAMESERVERS
  FROM 
    SYSMAN.MGMT$OS_PROPERTIES 
  WHERE 
    SOURCE='/etc/resolv.conf' AND 
    NAME LIKE 'nameserver%'
  GROUP BY 
    TARGET_GUID
) table2
ON
  table1.TARGET_GUID=table2.TARGET_GUID
WHERE 
  TARGET_TYPE='host'
ORDER BY 
  HOST
;        

Sample output:

Conclusion

OEM harvests a treasure trove of valuable insights about the environments where its agents are deployed. Yet, a portion of this rich data is either not showcased through the graphical user interface or is organized in a manner that's less than user-friendly. In this article, we delve into the tactics of navigating the database structure, empowering you to effortlessly uncover and present a wide array of host details.