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>;
