The first method in Chad's message (see below) is exactly what my
svc_on_pref.sh script does, available at
http://yong321.freeshell.org/oranotes/Service.txt
(about half page down)
I tried Chad's query. It lists only one of the few services that run on
non-preferred nodes. Why not simply:
select database_unique_name, cluster_name, service_name, preferred_instances,
running_instances
from mgmt_rac_services
where preferred_instances != running_instances
order by 1, 2, 3, 4;
which can list them all.
Yong Huang
--- Original message ---
by Chad Cleveland:
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,and running_instances <> preferred_instances
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)
order by 2 asc;--