carloscastilla - Fotolia


Oracle RAC database: A guide to creating helpful services

Oracle RAC database services provide benefits to the database architecture. See how you can use Oracle Listener and the srvctl utility to create these services.

Many Oracle DBAs who work with Oracle Real Application Clusters (RACs) don't use services to their advantage because...

they don't understand why services are beneficial to their overall database architecture.

Typical services for an RAC database

Today's Oracle databases have services. A single-instance database will likely have two services, an XML database service and a service with a name that matches the database. The following is the output of the Listener's status, trimmed for brevity. The Oracle Listener is a process that runs on the database server computer and manages incoming client request traffic. Notice that two services are defined in the Listener, orcl and orclXDB.

Services Summary...
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully

Two other services are not seen by the Listener, but you can see them through the V$SERVICES view

SQL> select name
  2  from v$services;


The SYS$BACKGROUND service is used internally for the instance's background processes. The SYS$USERS service is for all local database connections that do not go through the Oracle Listener.

Using the srvctl utility

You use the srvctl utility to create a service. Alternatively, you can create a service in Enterprise Manager. In the example below, I created a new service named "erp_svc" for my company's enterprise resource planning (ERP) application and defined this service to use the ORCL1 instance. However, in the event that instance is down, the service can be made available on ORCL2, to maintain high availability.

[[email protected] ~]$ srvctl add service -d orcl -service erp_svc \
> -preferred orcl1 -available orcl2

If you are using Oracle 12c's multi-tenant option, you should denote the Pluggable Database for the service with the "-pdb" parameter. The following is an example of creating a service in a multi-tenant configuration.

[[email protected]01 ~]$ srvctl add service -d orcl -s erp_svc \
> -pdb erp_db -serverpool orcl_pool

Now that the service has been created, use srvctl to start it.

[[email protected] ~]$ srvctl start service -d orcl -s erp_svc
[[email protected] ~]$ srvctl status service -d orcl -s erp_svc
Service erp_svc is running on nodes: host02,host01

When creating services, I try to make at least one for each application served by the Oracle RAC database. Let's assume that my Oracle RAC database is used by the company's customer relationship management (CRM) and ERP applications and I want to perform maintenance on the ERP database objects but let the CRM application still have database access. I can stop the ERP application's service and the application won't be able to instantiate any new connections.

[[email protected] ~]$ srvctl stop service -d orcl -s erp_svc

With the CRM service still up and running, the database is still available to CRM users.

Restricted mode workaround for RAC database

Even if your Oracle RAC database only has one application, you should still create a service. There is no Oracle RAC equivalent of "startup restrict" to start an Oracle instance in restricted mode for only DBA access. You can get around this by disabling the service and bouncing the database so as to disconnect all users.

[[email protected] ~]$ srvctl disable service -d orcl -s erp_svc
[[email protected] ~]$ srvctl stop database -d orcl -o immediate
[[email protected] ~]$ srvctl start database -d orcl

Once maintenance is complete, simply enable the service and restart .

[[email protected] ~]$ srvctl enable service -d orcl -s erp_svc
[[email protected] ~]$ srvctl start service -d orcl -s erp_svc

If you aren't creating your own services, you won't be able to use them to restrict database access.

Next Steps

Learn about other tools that can help the database administrator

Find out how real application clusters take advantage of the latest version of SPARC

Dig Deeper on Oracle RAC and database clustering