EM12c Database Inventory Report

With the select statement below you can have a database inventory report.

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

Instance name- SID
Host name- Host name where database lives
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

-----------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
                  tbl_tar.host_name
            END
               host_name,
            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
       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,
            mgmt_target_properties tbl_main,
            mgmt_targets tbl_tar
      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_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
   ORDER BY 2;

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