Oracle Database Services in (Oracle RAC + Oracle Data Guard) Environment

Introduction:

We may classify the database services into two types of services :
1- Database Services
2- Cluster Managed Database Services

Please note that:
– The procedure dbms_service.start_service – used with the first type – is deprecated in databases managed by Oracle Clusterware and Oracle Restart with Release 11.2. While the procedure remains available in the package, Oracle recommends using srvctl to manage services (Type 2).

– But in the case of RAC+DG , we can continue using the first type as we want to stop/start the service upon switching/failing over to the standby.
{Although I’m sure that there is a way to implement the same using clusterware !}

1- Database Services:

– This type of services is created from within the database only
– You cannot use the srvctl utility to manage or monitor it, it is not seen by srvctl
– In RAC env., when you create the service , it will be registered with the current instance only, although it may failover to the other nodes.
– In RAC env., you will find that this service is registered automatically in all the SCAN Listeners in the two nodes but against one instance only
– There is an option to start the service across all RAC nodes.

## The DBMS_service.start_service has a second parameter called instance_name
Name of the instance where the service must be activated (optional).

The instance on which to start the service. NULL results in starting of the service on the local instance. In single instance this can only be the current instance or NULL.

Specify DBMS_SERVICE.ALL_INSTANCES to start the service on all configured instances.

exec DBMS_service.start_service(‘PRODFO’,’prod1′); ==> service be started only in the prod1 instance

So, to start the instance across all instances:

exec DBMS_service.start_service(‘PRODFO’,DBMS_SERVICE.ALL_INSTANCES);

So, the steps to create the HA service and start it across all nodes will be as follows:

a). Create High Availability Service (on the Primary)

b). Create Trigger to start the high availability service for the primary database

c). Modify the High Availability service to handle failover properly

d). Add entry for the High Availability service in the tnsnames.ora on the client side:

Useful Commands:

2- Cluster Managed Database Services

For this type of services, we will use the cluster to create , manage and monitor the services as follows:

srvctl add service -d prod -service sales_service -preferred prod1 -available prod2
srvctl add service -d prod -service hr_service -preferred prod2 -available prod1
srvctl add service -d RAC -pdb PDB -s payroll_service -r inst1 -a inst2 -P BASIC

==> This creates a new service payroll_service with inst1 as a preferred instance and inst2 as an available instance. This means that it will normally run on the inst1 instance but will failover to the inst2 instance if inst1 becomes unavailable.

srvctl relocate service -d RAC -s payroll_service -i inst2 -t RAC
srvctl relocate service -d dbname -s servicename -i instancename -t newinstancename [-f]

srvctl start service -d prod -s sales_service
srvctl start service -d prod -s hr_service
srvctl stop service -d dbname -s sales_service

srvctl status service -d prod
Service hr_service is running on instance(s) prod2
Service sales_service is running on instance(s) prod1

srvctl status service -d prod -s sales_service
srvctl status service -d prod -s hr_service
srvctl config service -d prod
lsnrctl services LISTENER_SCAN1

Thanks
Ahmed