Check all services and their instance preference in a RAC

To check all services and their instance preference in a RAC (Real Application Clusters) environment, you can use the srvctl or crsctl utility along with SQL queries to gather detailed information.

Method 1: Using srvctl

List all services:

srvctl status service -db <db_unique_name>

Check detailed information about each service:

srvctl config service -db <db_unique_name> -service <service_name>

This command shows you the following details for each service:

• Service Name
• Preferred Instances
• Available Instances
• Service Role
• TAF policy (Transparent Application Failover)

Method 2: Using SQL Query (from DBA_SRVCTL)

You can query the Oracle data dictionary to find services and instance mappings:

SELECT s.name AS service_name,
       i.instance_name,
       s.preferred_instances,
       s.available_instances
FROM   gv$service s,
       gv$instance i
WHERE  s.instance_id = i.instance_id
ORDER BY s.name;

This query returns the list of services and their preferred and available instances, showing which instances are used for each service.

Method 3: Using CRSCTL

To get a more detailed breakdown of services from the Cluster Ready Services:

crsctl stat res -t

This will give you a table-like format showing all cluster services, including preferred and available instances.