All about Oracle RAC clustered database services

 

How to create a database service without dbca or gridcontrol:

srvctl add service -d <db_unique_name> -s <service_name> -r "<preferred_list>" -a "<available_list>" -P <TAF Policy Specification>

example:

The following command will create a database service qadb_oltp for the database qadb.   The service will primarily be for the database instance qadb1 and will connect to qadb2 if qadb1 is not available.  The TAF(transparent application failover) policy which will be used is BASIC.

srvctl add service -d qadb -s qadb_oltp -r "qadb1" -a "qadb2" -P BASIC

Here is the command to remove the following service:

srvctl remove service -d qadb -s qadb_oltp

You maybe asking yourself what really happens when you run this command.  Adding a service modifies your clusterware and database configuration.  

What changes did it make to my clusterware?

The Oracle service get's registerd in Oracle Clusterware.  Oracle Clusterware will manage the service(resource) if something happens to the primary node. 

To find how oracle clusterware see's the service you can run the following command:

crsctl status resource ora.qadb.qadb_oltp.qadb1.srv

NAME=ora.qadb.qadb_oltp.qadb1.srv
TYPE=application
TARGET=ONLINE
STATE=ONLINE on bddb-nm1

 

What changes does it make to my database environment?

When the database service starts it sets your instance configuration parameter service_name dynamically.  If the database service is started you will see the following when your query from your database instance that the service is running on:

SQL> select name, value from v$parameter where name = 'service_names';
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
service_names
qadb_oltp, qadb.company.com

Once the service_name parameter is set inside your database instance it will register with the appropiate listener.


To see if the service registered with your listener you can run the command lsrnctl status

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 29-NOV-2012 16:23:32
Copyright (c) 1991, 2011, Oracle.  All rights reserved.
....
Service "qadb_oltp.company.com" has 1 instance(s).
  Instance "qadb1", status READY, has 2 handler(s) for this service...
....
The command completed successfully

As you can the the service qadb_oltp is now registerd with my Listener.

The database service will be created inside your database using the package DBMS_SERVICES. To find out settings like like the goal and wheter distributed transaction processing has been set you need to look at the data_dictionary view DBA_SERVICES. 

Here is an example query from dba_services:
set linesize 110
column name format a30
column network_name format a30
column enabled format a7
select name, network_name, enabled, dtp, goal, aq_ha_notifications
from dba_services;

For further info on the view DBA_SERVICES(ALL_SERVICES) see:  http://docs.oracle.com/cd/E11882_01/server.112/e25513/statviews_2063.htm#sthref1447

 

Why would you use DBMS_SERVICES package?

Use DBMS_SERVICES package to configure settings that you cannot configure using srvctl.  Example of setting you must use DBMS_SERVICES are AQ_HA_NOTIFICATIONS(advanced queuing high availability notification) and distributed transaction processing.