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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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@xxxxxxxxxx<mailto:courtney.llamas@xxxxxxxxxx>> 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@xxxxxxxxx<mailto:nagaraj.chk@xxxxxxxxx>]
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
