Wednesday, March 5, 2014

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

1 comment:

  1. Oracle Enterprise Manager Grid Control 12c
    http://www.21cssindia.com/courses/oracle-enterprise-manager-grid-control-12c-228.html
    Introduction
    Course overview
    Key Challenges for Administrators
    What Is Enterprise Manager Cloud Control?
    Built-in and Integrated Manageability
    Configuration Management
    Reviewing Enterprise Manager Core Concepts
    Review Oracle Enterprise Manager architecture
    Agent Installation and Target Discovery
    Describing Different Target Types
    Monitoring Cloud Control
    Security (very high level)
    Managing Securely with Credentials
    Managing and Monitoring Enterprise Manager Cloud Control
    If you are seeking training and support you can reach me on 91-9000444287. Online training by real time Experts. Call us 001-309-200-3848 for online training

    ReplyDelete

About Me

My Photo

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