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@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>> on
behalf of Dba DBA
<oracledbaquestions@xxxxxxxxx<mailto:oracledbaquestions@xxxxxxxxx>>
Reply-To: "oracledbaquestions@xxxxxxxxx<mailto:oracledbaquestions@xxxxxxxxx>"
<oracledbaquestions@xxxxxxxxx<mailto:oracledbaquestions@xxxxxxxxx>>
Date: Tuesday, July 14, 2015 at 10:40 AM
To: ORACLE-L <oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>>
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
