Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation







 Oracle Job Services and Instance Stickiness
Oracle Tips by Burleson Consulting

Advanced Oracle Utilities: The Definitive Reference by Rampant TechPress is written by the top Oracle database experts (Bert Scalzo, Donald Burleson, and Steve Callan).  The following is an excerpt from the book.

Services allow the classification or grouping of applications within a database.  This permits application priorities and resource allocation to be managed more effectively.  Services can be defined and utilized in both single-node and Real Application Cluster (RAC) environments.  The RAC is where they are most useful as they facilitate the coordination of grid computing.


A job class can be assigned to a service which affects how jobs associated with the job class are executed.  When using RAC, jobs belonging to a job class will only run in a RAC instance that is assigned to the specific service.  The following rules apply to job classes in relation to services:

  • All job classes are assigned to a service.  If a service is not explicitly specified, the job class is assigned to the default service, meaning it can run on any RAC instance in the cluster.

  • Dropping a service will cause any dependent job classes to be reassigned to the default service.

  • Specifying a nonexistent service will cause the job class creation to fail.

Services can be configured using the Database Configuration Assistant (DBCA), srvctl utility or the dbms_service package.  More information concerning DBCA and its various components in regards to server-side functions can be found in Chapter 4. The dbms_service package is limited to service administration on a single node while the dbca and srcvtl utilities can perform cluster-wide configuration and administration.  Examples of administering services with the dbms_service package are shown below.



  -- Create a new service associated with the specified TSN service name.

  DBMS_SERVICE.create_service (

    service_name => 'test_service',

    network_name => 'DB10G.MYDOMAIN.COM');


  -- Start the specified service.

  DBMS_SERVICE.start_service (

    service_name => ' test_service');


  -- Disconnects all sessions associated with the specified service.

  DBMS_SERVICE.disconnect_session (

    service_name => 'test_service');


  -- Stop the specified service.

  DBMS_SERVICE.stop_service (

    service_name => 'test_service');


  -- Delete the specified service.

  DBMS_SERVICE.delete_service (

    service_name => 'test_service');




Some examples of using the srvctl utility to do similar actions are listed below.


# Create the service on two nodes.

srvctl add service -d DB10G -s TEST_SERVICE -r DB10G1,DB10G2


# Stop and start the service on a single or multiple nodes.

srvctl stop service -d DB10G -s TEST_SERVICE -i DB10G1,DB10G2

srvctl start service -d DB10G -s TEST_SERVICE -i DB10G1


# Disable and enable the service on a single or multiple nodes.

srvctl disable service -d DB10G -s TEST_SERVICE -i DB10G1,DB10G2

srvctl enable service -d DB10G -s TEST_SERVICE -i DB10G1


# Display the current status of the service.

srvctl status service -d DB10G -s TEST_SERVICE -v


# Remove the service from both nodes.

srvctl remove service -d DB10G -s TEST_SERVICE -i DB10G1,DB10G2


Once a service is present, it can be assigned to a job class during creation or subsequently using the set_attribute procedure, as shown below.



  DBMS_SCHEDULER.create_job_class (

    job_class_name => 'test_job_class',

    service        => ‘test_service’);


  DBMS_SCHEDULER.set_attribute (

    name      => 'test_job_class',

    attribute => 'service',

    value     => ‘admin_service’);




The following scenario will explain more specifically how services can be used to partition applications in a three-node RAC environment.


For services to function correctly, the Global Services Daemon (GSD) must be running on each node in the cluster.  The GSDs are started using the gsdctl utility, which is part of the Cluster Ready Services (CRS) installation, so they must be started from that environment.


# Set environment.

export ORACLE_HOME=/u01/app/oracle/product/10.1.0/crs



# Start GSD daemon.

gsdctl start


Once the GSDs are running, the user must check that the cluster configuration is correct.  The following command and output show the expected configuration for a three-node database called ORCL.


srvctl config database -d ORCL

server01 ORCL1 /u01/app/oracle/product/10.1.0/db_1

server02 ORCL2 /u01/app/oracle/product/10.1.0/db_1

server03 ORCL3 /u01/app/oracle/product/10.1.0/db_1


This configuration is typically performed during the cluster database creation, but it can be performed subsequently using the following commands.


srvctl add database -d ORCL -o /u01/app/oracle/product/10.1.0/db_1

srvctl add instance -d ORCL -i ORCL1 -n server01

srvctl add instance -d ORCL -i ORCL2 -n server02

srvctl add instance -d ORCL -i ORCL3 -n server03


Assume that two applications should run in the following way:

  • OLTP - Should run on nodes one and two of the RAC, but is able to run on node three if nodes one and two are not available.

  • BATCH - Should run on node three, but is able to run on nodes one and two if node three is not available.

To meet this requirement, the following services can be created:


# Set environment.

export ORACLE_HOME=/u01/app/oracle/product/10.1.0/db_1



# Create services.

srvctl add service -d ORCL -s OLTP_SERVICE -r ORCL1,ORCL2 -a ORCL1,ORCL2,ORCL3

srvctl add service -d ORCL -s BATCH_SERVICE -r ORCL3 -a ORCL1,ORCL2,ORCL3


The OLTP_SERVICE is able to run on all RAC nodes, indicated by the -a option, but will run in preference on nodes one and two, indicated by the -r option.  The BATCH_SERVICE is able to run on all RAC nodes, indicated by the -a option, but will run in preference on node three, indicated by the -r option.


The services can be started and stopped using the following commands:


srvctl start service -d ORCL -s OLTP_SERVICE

srvctl start service -d ORCL -s BATCH_SERVICE


srvctl stop service -d ORCL -s OLTP_SERVICE

srvctl stop service -d ORCL -s BATCH_SERVICE

Remote DBA Service

Oracle Tuning Book


Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software








r more details on Oracle utilities, see the book "Advanced Oracle Utilities" by Bert Scalzo, Donald K. Burleson, and Steve Callan.

You can buy it direct from the publisher for 30% off directly from Rampant TechPress.



BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.