Thank You So much Dave, It was very helpful.
-Naga
On Wed, Feb 10, 2016 at 4:58 AM, Herring, David <HerringD@xxxxxxx> wrote:
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> 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]
*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
