This discussion is archived
3 Replies Latest reply: Aug 16, 2013 6:50 AM by JavierFranciscoRuiz RSS

Installed Location from mgmt tables

user13549752 Newbie
Currently Being Moderated

OEM:12.1.0.2

OS:Linux

Repository Database 11.2.0.3

 

From the OEM repository database i want to pull a report about all the database configured, and i am using the below query

 

select TARGET_NAME,TARGET_TYPE,HOST_NAME from mgmt$target ;

 

But the above query doesn't give me the information about Installed Location(ORACLE_HOME). Which mgmt$ table will have the information.

  • 1. Re: Installed Location from mgmt tables
    DK2010 Guru
    Currently Being Moderated

    Hi,

     

    You can Query from  MGMT$OH_HOME_INFO

     

    HTH

  • 2. Re: Installed Location from mgmt tables
    user13549752 Newbie
    Currently Being Moderated

    MGMT$OH_HOME_INFO giving the home location for target type home_name not for all target_type like oracle_database,Listener,rac_database .

  • 3. Re: Installed Location from mgmt tables
    JavierFranciscoRuiz Explorer
    Currently Being Moderated

    Use the following query I created this query to use for check all DBs in my OEM. The first column is the target_guid that way if you want you can create a view and use the view to join to other tables with the target_guid.

     

    Instance name

    Host name

    DB Version

    Total Memory used by DB <- this is a case statement checking for memory target or sga

    Data Guard Status

    Port for listener

    Path to Oracle Home

     

    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;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points