RE: OEM Database Target Host

From: Herring, David <HerringD_at_DNB.com>
Date: Tue, 9 Feb 2016 23:28:34 +0000
Message-ID: <CY1PR0201MB1868644B646F06DF3B95EF9BD4D60_at_CY1PR0201MB1868.namprd02.prod.outlook.com>

Maybe overkill for what you want but I use the SQL below to get a complete list with a few more details per install. The snipet sits in a loop where each EM is connected to and then this statement run. The inner-query gathers more than is needed in your case but that's because I use a similar base SQL in different situations.

   SET ECHO off FEEDBACK off HEADING off LINESIZE 220 PAGESIZE 10000 TAB off

   COL availability_status FORMAT A15 HEADING 'Avail.|Status'

   COL repo_host_name FORMAT A15 HEADING 'Repository|Host'
   COL host FORMAT A25 HEADING 'Host Name'
   COL dbname FORMAT A31 HEADING 'DB Name'

   SET LINESIZE 200    SELECT REPLACE(repo.host_name, '.dnb.com') repo_host_name, t.dbname, t.host, availability_status

     FROM (SELECT NVL(d.database_name, s.instance_name) dbname, s.instance_name, s.host, NVL(c.source_target_name, 'N/A') cluster_name


                , p.property_name, p.property_value, NVL(d.availability_status, s.availability_status) availability_status


             FROM (SELECT ta.source_target_name instance_name, ta.source_target_type, ta.assoc_target_name host, ac.availability_status
                     FROM mgmt\$target_associations ta, mgmt\$availability_current ac
                    WHERE ta.assoc_def_name = 'hosted_by'
                      AND ta.assoc_target_type = 'host'
                      AND ta.source_target_type = 'oracle_database'
                      AND ta.source_target_name = ac.target_name) s


                , (SELECT ta.source_target_name database_name, ta.assoc_target_name instance_name, ac.availability_status


                     FROM mgmt\$target_associations ta, mgmt\$availability_current ac
                    WHERE ta.assoc_def_name = 'rac_instance'
                      AND ta.association_type = 'RELATES_TO'
                      AND ta.source_target_name = ac.target_name) d


                , mgmt\$target_associations c


                , mgmt\$target_properties p


            WHERE (s.instance_name = d.instance_name(+))
              AND (    c.association_type(+) = 'contains'
                   AND c.source_target_type(+) = 'cluster'
                   AND c.assoc_target_name(+) = s.host)
              AND (    s.instance_name = p.target_name
                   AND s.source_target_type = p.target_type
                   AND p.property_name IN ('DBVersion', 'DataGuardStatus', 'log_archive_mode', 'OSMInstance', 'RACOption'))) t
        , v\$instance repo

    GROUP BY REPLACE(repo.host_name, '.dnb.com'), t.dbname, t.host, availability_status     ORDER BY 1, 2; Regards,

Dave

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Nagaraj S Sent: Monday, February 08, 2016 8:54 PM To: Courtney Llamas
Cc: oracle-l
Subject: Re: OEM Database Target Host

Alone with it I need to get the status of target like its UP, down or Agent unreachable, I checked in mgmt$target table and there is no status field do i need to build my query with any other table? Please advise

On Mon, Feb 8, 2016 at 8:19 PM, Courtney Llamas <courtney.llamas_at_oracle.com<mailto:courtney.llamas_at_oracle.com>> wrote: You’ll want to select target_type = oracle_database, and get the host_name…

Select distinct host_name from mgmt$target where target_type = 'oracle_database';

From: Nagaraj S [mailto:nagaraj.chk_at_gmail.com<mailto:nagaraj.chk_at_gmail.com>] Sent: Monday, February 08, 2016 4:10 AM To: oracle-l
Subject: OEM Database Target Host

Good Morning All,

We have middleware ,database and application hosts targets monitored in OEM12c and I have a situation to pull the list of database host, I tried by issuing emcli get_targets -noheader -script -targets=host, it shows all the targets(Middleware, Database and Application). Is there any way I we can get only database host using emcli or by querying internal tables? Thanks in Advance

-Naga

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Feb 10 2016 - 00:28:34 CET