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

From: Michael McMullen <ganstadba_at_hotmail.com>
Date: Thu, 16 Jul 2015 09:48:13 -0400
Message-ID: <BLU177-W49CDED13CD94E9D90F631FA6990_at_phx.gbl>

thanks for this.
I've been wanting to make a custom report in oem to list all "custom" services in my spare cycles and have been working on a query. This is great.

Mike

From: Chad.Cleveland_at_datavail.com
To: oracledbaquestions_at_gmail.com; oracle-l_at_freelists.org Subject: Re: 11g RAC TAPI/Custom services : how to query in data dictionary Date: Tue, 14 Jul 2015 17:37:22 +0000

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> on behalf of Dba DBA <oracledbaquestions_at_gmail.com> Reply-To: "oracledbaquestions_at_gmail.com" <oracledbaquestions_at_gmail.com> Date: Tuesday, July 14, 2015 at 10:40 AM To: ORACLE-L <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, 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 Thu Jul 16 2015 - 15:48:13 CEST