RE: OEM Database Target Host

From: Herring, David <>
Date: Tue, 9 Feb 2016 23:28:34 +0000
Message-ID: <>

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.


   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, '') repo_host_name, t.dbname,, availability_status

     FROM (SELECT NVL(d.database_name, s.instance_name) dbname, s.instance_name,, 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(+) = 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, ''), t.dbname,, availability_status     ORDER BY 1, 2; Regards,


From: [] 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 <<>> 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 [<>] 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


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

Original text of this message