Re: OEM Database Target Host

From: Nagaraj S <nagaraj.chk_at_gmail.com>
Date: Wed, 10 Feb 2016 18:27:27 +0530
Message-ID: <CAG6s0C1dVEKAfWRA8ytDy4n3g2GuCLmJ303YY27Ybdu7WOQzdQ_at_mail.gmail.com>



Thank You So much Dave, It was very helpful.

-Naga

On Wed, Feb 10, 2016 at 4:58 AM, Herring, David <HerringD_at_dnb.com> 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_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> 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]
> *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 - 13:57:27 CET

Original text of this message