Re: 11g RAC TAPI/Custom services : how to query in data dictionary

From: Chad Cleveland <Chad.Cleveland_at_datavail.com>
Date: Tue, 14 Jul 2015 17:37:22 +0000
Message-ID: <D1CAA34A.60F4E%chad.cleveland_at_datavail.com>



You can get this information with SRVCTL inside a shell script. Loop through each of the sids and report back.

ALTERNATIVELY you can hit the OEM repository for this info:

Databases with Services running on non-preferred Node:

select distinct(lower(service_name)) Service_Name, database_unique_name, cluster_name, preferred_instances, available_instances, running_instances from sysman.mgmt_rac_services a
where ecm_snapshot_id = (select max(b.ecm_snapshot_id) from sysman.mgmt_rac_services b where b.service_name = a.service_name) and running_instances <> preferred_instances order by 2 asc;

RAC Cluster Services with Preferred and Available Instances:

select distinct(lower(service_name)) Service_Name, database_unique_name, cluster_name, preferred_instances, available_instances from sysman.mgmt_rac_services where (lower(service_name)) <> (lower(database_unique_name)) order by 2, 1 ASC;

Complete list of Services to Database Mappings

SELECT lower(srv.name) "Service Name" , lower(srv.target_name) "Database Name"   FROM mgmt$db_services_ecm srv

 WHERE     srv.name NOT LIKE 'SYS$%'
       AND srv.name NOT LIKE '%XDB'
       AND LOWER (SUBSTR (target_name, 1, 5)) <> LOWER (SUBSTR (name, 1, 5))
       AND srv.target_name NOT IN
              (SELECT mem.member_target_name
                 FROM mgmt$target_members mem
                WHERE     mem.member_target_name = srv.target_name
                      AND mem.aggregate_target_type = 'rac_database')
order by 2 ASC;

From: <oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>> on behalf of Dba DBA <oracledbaquestions_at_gmail.com<mailto:oracledbaquestions_at_gmail.com>> Reply-To: "oracledbaquestions_at_gmail.com<mailto:oracledbaquestions_at_gmail.com>" <oracledbaquestions_at_gmail.com<mailto:oracledbaquestions_at_gmail.com>> Date: Tuesday, July 14, 2015 at 10:40 AM To: ORACLE-L <oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>> Subject: 11g RAC TAPI/Custom services : how to query in data dictionary

3 Node cluster
11.2.0.4

I used srvctl add services to create a number of custom services that run on one node and failover to another node. I am trying to find a data dictionary query to report on it. I have most of it below. I cant find the right view/field that tells me what the primary and failover nodes are. Docs call them by slightly different names.

gv$services.inst_id tells me where they are 'right now'. However, I also need which node is priamry and failover so I can keep track of these. We have alot of them in several different instances. srvctl status service is not a practical way to report on this. i would have to take the long output run it repeatedly for each service and stick it in a spreadsheet.

I am thinking this has to be in the data dictionary. I have hunted through any views with the word 'SERVICE' in it. Anyone know?

Below are the srvctl add service flags I am trying to report on.

-r : primary (preferred_list)
-a: failover (available_list)

I need this to keep track of them and to verify they are correct. select b.inst_id,

a.name<http://a.name>,
a.network_name,
a.creation_date,
a.failover_method,
a.failover_type,
a.failover_retries,
a.failover_delay,
a.enabled

from dbA_services a, gv$services b
where a.service_id=b.service_id

--

http://www.freelists.org/webpage/oracle-l Received on Tue Jul 14 2015 - 19:37:22 CEST

Original text of this message