EM12c Database Data Inventory

Nice SQL statement you can use to create an inventory report from your OEM data. This is similar to the inventory report I blogged about last year but with more improvements.


Column Description
Target GUID- GUID this can be use to join with other table and view under the sysman schema
Instance name- SID
Host name- Host name where database lives
Status- status of database target based on the mgmt_current_availability data
Group- the group that the target belongs to
Version- database version
Total memory- This is the total memory SGA*PGA or if 11g total memory_target
Data Guard Status- Primary or Standby
Port- Listener port
Path- Oracle Home path
Company- this is from the target properties
Location-this is from the target properties
App Contact- this is from the target properties
Cost Center- this is from the target properties
Tier- Department- this is from the target properties
DB Platform- OS platform for database host target
DB Host OS- OS for database host target
Notes- this is from the target properties


-----------SQL Statement-----------------------
SELECT DISTINCT
            tbl_tar.target_guid,
            tbl_sid.sid AS instance_name,
            CASE
               WHEN tbl_tar.host_name LIKE '%.%'
               THEN
                  LOWER (SUBSTR (tbl_tar.host_name,
                                 1,
                                   INSTR (tbl_tar.host_name,
                                          '.',
                                          2,
                                          1)
                                 - 1))
               ELSE
                  LOWER (tbl_tar.host_name)
            END
               host_name,
            DECODE (tbl_ava.current_status,
                    0, 'Down',
                    1, 'Up',
                    2, 'Metric Error',
                    3, 'Agent Down',
                    4, 'Unreachable',
                    5, 'Blackout',
                    6, 'Unknown')
               status,
            tbl_groups.composite_target_name AS "GROUP",
            tbl_ver.version,
            CASE
               WHEN tbl_mem.mem_max > 0
               THEN
                  CEIL (tbl_mem.mem_max / 1024 / 1024)
               ELSE
                  CEIL (tbl_sga.sga / 1024 / 1024 + tbl_pga.pga / 1024 / 1024)
            END
               total_memory,
            tbl_dg.data_guard_status,
            tbl_port.port,
            tbl_home.PATH,
            tbl_company.company,
            tbl_location.location,
            tbl_appcontact.app_contact,
            tbl_costcenter.cost_center,
            tbl_tier.tier,
            tbl_department.department,
            tbl_dbplatform.db_platform,
            tbl_dbhostos.db_host_os,
            tbl_comment.notes
       FROM (SELECT p.target_guid, p.property_value AS port
               FROM mgmt_target_properties p
              WHERE p.property_name = 'Port') tbl_port,
            (SELECT s.target_guid, UPPER (s.property_value) AS sid
               FROM mgmt_target_properties s
              WHERE s.property_name = 'SID') tbl_sid,
            (SELECT s.target_guid, s.property_value AS version
               FROM mgmt_target_properties s
              WHERE s.property_name IN ('Version')) tbl_ver,
            (SELECT s.target_guid, s.property_value AS PATH
               FROM mgmt_target_properties s
              WHERE s.property_name IN ('OracleHome')) tbl_home,
            (SELECT s.target_guid, s.property_value AS data_guard_status
               FROM mgmt_target_properties s
              WHERE s.property_name IN ('DataGuardStatus')) tbl_dg,
            (SELECT s.target_guid, s.VALUE AS PGA
               FROM mgmt$db_init_params s
              WHERE s.name = 'pga_aggregate_target') tbl_pga,
            (SELECT s.target_guid, s.VALUE AS SGA
               FROM mgmt$db_init_params s
              WHERE s.name = 'sga_max_size') tbl_sga,
            (SELECT s.target_guid, s.VALUE AS mem_max
               FROM mgmt$db_init_params s
              WHERE s.name = 'memory_target') tbl_mem,
            (SELECT p.target_guid, p.property_value AS notes
               FROM mgmt_target_properties p
              WHERE p.property_name = 'orcl_gtp_comment') tbl_comment,
            (SELECT p.target_guid, p.property_value AS company
               FROM mgmt_target_properties p
              WHERE p.property_name = 'orcl_gtp_line_of_bus') tbl_company,
            (SELECT p.target_guid, p.property_value AS location
               FROM mgmt_target_properties p
              WHERE p.property_name = 'orcl_gtp_location') tbl_location,
            (SELECT p.target_guid, p.property_value AS app_contact
               FROM mgmt_target_properties p
              WHERE p.property_name = 'orcl_gtp_contact') tbl_appcontact,
            (SELECT p.target_guid, p.property_value AS cost_center
               FROM mgmt_target_properties p
              WHERE p.property_name = 'orcl_gtp_cost_center') tbl_costcenter,
            (SELECT p.target_guid, p.property_value AS tier
               FROM mgmt_target_properties p
              WHERE p.property_name = 'orcl_gtp_lifecycle_status') tbl_tier,
            (SELECT p.target_guid, p.property_value AS department
               FROM mgmt_target_properties p
              WHERE p.property_name = 'orcl_gtp_department') tbl_department,
            (SELECT p.target_guid, p.property_value AS db_platform
               FROM mgmt_target_properties p
              WHERE p.property_name = 'orcl_gtp_platform') tbl_dbplatform,
            (SELECT p.target_guid, p.property_value AS db_host_os
               FROM mgmt_target_properties p
              WHERE p.property_name = 'orcl_gtp_os') tbl_dbhostos,
            mgmt_target_properties tbl_main,
            mgmt_targets tbl_tar,
            mgmt_current_availability tbl_ava,
            (SELECT composite_target_name, member_target_guid
               FROM MGMT_TARGET_MEMBERSHIPS
              WHERE     composite_target_type = 'composite'
                    AND composite_target_name IN
                           ('Production', 'Non-Production', 'SuperCluster')
                    AND member_target_type = 'oracle_database') tbl_groups
      WHERE     tbl_main.target_guid = tbl_port.target_guid(+)
            AND tbl_main.target_guid = tbl_sid.target_guid(+)
            AND tbl_main.target_guid = tbl_tar.target_guid(+)
            AND tbl_main.target_guid = tbl_ver.target_guid(+)
            AND tbl_main.target_guid = tbl_home.target_guid(+)
            AND tbl_main.target_guid = tbl_dg.target_guid(+)
            AND tbl_main.target_guid = tbl_pga.target_guid(+)
            AND tbl_main.target_guid = tbl_sga.target_guid(+)
            AND tbl_main.target_guid = tbl_mem.target_guid(+)
            AND tbl_main.target_guid = tbl_ava.target_guid(+)
            AND tbl_main.target_guid = tbl_comment.target_guid(+)
            AND tbl_main.target_guid = tbl_company.target_guid(+)
            AND tbl_main.target_guid = tbl_location.target_guid(+)
            AND tbl_main.target_guid = tbl_appcontact.target_guid(+)
            AND tbl_main.target_guid = tbl_costcenter.target_guid(+)
            AND tbl_main.target_guid = tbl_tier.target_guid(+)
            AND tbl_main.target_guid = tbl_department.target_guid(+)
            AND tbl_main.target_guid = tbl_dbplatform.target_guid(+)
            AND tbl_main.target_guid = tbl_dbhostos.target_guid(+)
            AND tbl_main.target_guid = tbl_groups.member_target_guid(+)
            AND tbl_tar.target_type = 'oracle_database'
   GROUP BY tbl_tar.target_guid,
            tbl_port.port,
            tbl_sid.sid,
            tbl_tar.host_name,
            tbl_ver.version,
            tbl_home.PATH,
            tbl_dg.data_guard_status,
            tbl_pga.pga,
            tbl_sga.sga,
            tbl_mem.mem_max,
            tbl_ava.current_status,
            tbl_groups.composite_target_name,
            tbl_comment.notes,
            tbl_company.company,
            tbl_location.location,
            tbl_appcontact.app_contact,
            tbl_costcenter.cost_center,
            tbl_tier.tier,
            tbl_department.department,
            tbl_dbplatform.db_platform,
            tbl_dbhostos.db_host_os
   ORDER BY 2;

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

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$storage_report_data to <username>;
grant select on mgmt_target_properties to <username>;
grant select on mgmt_targets to <username>;

grant exempt access policy to <username>;