Re: 11g RAC TAPI/Custom services : how to query in data dictionary
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