Thursday, June 13, 2013

EM12c Server Report

Ever wanted to see the following type of information you see on the host target homepage in EM12c? Now you can using the select statement I created below.



Below is a sample report I have in my apex application op top of my EM12c repository.







This SQL statement will give you server information from the EM repository tables.

HOST_NAME- server name
HARDWARE- hardware type
OS- os version
FREQ_IN_MHZ- speed of CPU
IMPL- implementation
CPU_CORES- number of cores
CPU_THREADS- number of threads
TOTAL_MEMORY_SIZE_GB- total physical RAM
TOTAL_SWAP_SPACE_IN_GB- total swap space
ROOT_FILESYSTEM_TYPE- type of root file system

-----------SQL Statement-----------------------

SELECT DISTINCT
            a.target_guid,
            CASE
               WHEN host_name LIKE '%.%'
               THEN
                  LOWER (SUBSTR (host_name,
                                 1,
                                   INSTR (host_name,
                                          '.',
                                          2,
                                          1)
                                 - 1))
               ELSE
                  host_name
            END
               host_name,
            system_config hardware,
            OS_summary OS,
            c.freq_in_mhz,
            c.impl,
            cpu_count cpu_cores,
            logical_cpu_count cpu_threads,
            CEIL (mem / 1024) AS total_memory_size_in_gb,
            CEIL (max_swap_space_in_mb / 1024) AS total_swap_space_in_gb,
            d.TYPE AS root_filesystem_type
       FROM MGMT$OS_HW_SUMMARY a,
            MGMT$OS_SUMMARY b,
            (SELECT target_guid, freq_in_mhz, impl
               FROM (SELECT t1.target_guid,
                            T1.FREQ_IN_MHZ,
                            t1.impl,
                            ROW_NUMBER ()
                            OVER (PARTITION BY t1.target_guid ORDER BY t1.impl)
                               rn
                       FROM MGMT$HW_CPU_DETAILS T1)
              WHERE rn = 1) c,
            (SELECT cm_target_guid, TYPE
               FROM CM$MGMT_ECM_OS_FILESYSTEM
              WHERE mount_location IN ('c:\', '/', 'C:\')) d
      WHERE     a.target_guid = b.target_guid
            AND a.target_guid = c.target_guid
            AND a.target_guid = d.cm_target_guid
   ORDER BY 2 ASC;

Note: If you are going to use another account other than SYSMAN to select you will need to have the following privilege.

grant select on MGMT$OS_HW_SUMMARY to <username>;
grant select on MGMT$OS_SUMMARY to <username>;
grant select on MGMT$HW_CPU_DETAILS to <username>;
grant select on CM$MGMT_ECM_OS_FILESYSTEM to <username>;
grant exempt access policy to <username>;

No comments:

Post a Comment

About Me

My photo

Senior DBA with over 16 years experience, specializing in "Database Performance Tuning" and High Availability (RAC, Data Guard & Oracle Golden Gate).