How To Create Service In Oracle 11g Rac

Creating RAC TNS Services

RAC tuning tips October 3, 2015

Creating RAC TNS Services

Before applications can use RAC services, the service must be created. Oracle does not automatically create application services. Which services to create and which instances will operate the services is a decision left to the database administrator.

Out of the box, an Oracle RAC database is created with four services, two of which are internal to the database. Even single instance databases have these four default services. Running services can be discovered in the gv$services view.

SQL> select

2 inst_id,

3 name

4 from

5 gv$services

6 order by

7 inst_id,

8 name;

INST_ID NAME

---------- ------------------------------------------------------------

1 SYS$BACKGROUND

1 SYS$USERS

1 orcl

1 orclXDB

2 SYS$BACKGROUND

2 SYS$USERS

2 orcl

2 orclXDB

The sys$background services is used internally for the instance's background processes. The sys$users service is for all local database connections that did not go through the Oracle Listener. If you sign on to the database host and from that host use SQL*Plus to make a local connection to the instance, you are using the sys$users service. The v$session view shows the service name in use by the session.

SQL> select

2 username,

3 program,

4 service_name

5 from

6 v$session;

USERNAME PROGRAM SERVICE_NAME

---------- ---------------------------------------- ---------------

oracle@host01.localdomain (PMON) SYS$BACKGROUND

oracle@host01.localdomain (DIAG) SYS$BACKGROUND

oracle@host01.localdomain (LMON) SYS$BACKGROUND

oracle@host01.localdomain (DBW0) SYS$BACKGROUND

oracle@host01.localdomain (LGWR) SYS$BACKGROUND

oracle@host01.localdomain (CKPT) SYS$BACKGROUND

oracle@host01.localdomain (SMON) SYS$BACKGROUND

oracle@host01.localdomain (RECO) SYS$BACKGROUND

oracle@host01.localdomain (CJQ0) SYS$BACKGROUND

SCOTT sqlplus@host01.localdomain (TNS V1-V3) SYS$USERS

The output from the query above has been trimmed for brevity. The sessions utilizing the sys$background service are actually background processes most readers should already be familiar with, i.e pmon, lgwr, smon, etc. The last line of output shows one user connected as SCOTT. The program is SQL*Plus running right on the database host and is connected to the sys$users service.

In addition to the two internal services, Oracle RAC also creates two other services. One service has the same name as the database name. The other service has a name of the form db_nameXDB. These last two default services are accessible from applications external to the database servers. The services are registered with the listener. A status of the listener reveals all services and the instance they are registered with.

[oracle@host01 ~]$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 03-JUL-2014 14:00:01

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

STATUS of the LISTENER

------------------------

Alias LISTENER

Version TNSLSNR for Linux: Version 12.1.0.1.0 - Production

Start Date 03-JUL-2014 11:01:31

Uptime 0 days 2 hr. 58 min. 29 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /u01/app/crs12.1.0.1/network/admin/listener.ora

Listener Log File /u01/app/oracle/diag/tnslsnr/host01/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.81)(PORT=1521)))

Services Summary...

Service "+ASM" has 1 instance(s).

Instance "+ASM1", status READY, has 1 handler(s) for this service...

Service "orcl" has 1 instance(s).

Instance "orcl1", status READY, has 1 handler(s) for this service...

Service "orclXDB" has 1 instance(s).

Instance "orcl1", status READY, has 1 handler(s) for this service...

The command completed successfully

In the output above, the orcl and orclXDB services are clearly registered with the orcl1 instance. The status output also shows a service for the ASM database instance. The status command of the listener shows much more information than just the services involved. The lsnrctl utility also includes a services command to focus on that specific status item.

[oracle@host01 ~]$ lsnrctl services

LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 03-JUL-2014 14:00:05

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

Services Summary...

Service "+ASM" has 1 instance(s).

Instance "+ASM1", status READY, has 1 handler(s) for this service...

Handler(s):

"DEDICATED" established:0 refused:0 state:ready

LOCAL SERVER

Service "orcl" has 1 instance(s).

Instance "orcl1", status READY, has 1 handler(s) for this service...

Handler(s):

"DEDICATED" established:0 refused:0 state:ready

LOCAL SERVER

Service "orclXDB" has 1 instance(s).

Instance "orcl1", status READY, has 1 handler(s) for this service...

Handler(s):

"D000" established:0 refused:0 current:0 max:1022 state:ready

DISPATCHER <machine: host01.localdomain, pid: 3877>

(ADDRESS=(PROTOCOL=tcp)(HOST=host01)(PORT=36099))

The command completed successfully

Most Oracle RAC database administrators use the ?lsnrctl status? command because it is second nature to them.

One way to create a service is to use the srvctl utility. Following the example earlier in this section, two services will be created, hr_svc and accntg_svc. To partition the applications using these services, hr_svc will run on the first instance, orcl1. The service accntg_svc will run on the second instance, orcl2.

[oracle@host01 ~]$ srvctl add service -d orcl -service hr_svc \

> -preferred orcl1 -available orcl2

[oracle@host01 ~]$ srvctl add service -d orcl -service accntg_svc \

> -preferred orcl2 -available orcl1

The -preferred parameter denotes the primary instance for the service. If the preferred instance is down, the service will fail over to any -available instance. After the services are created, the status is checked.

[oracle@host01 ~]$ srvctl status service -d orcl

Service accntg_svc is not running.

Service hr_svc is not running.

The services need to be started after their initial creation.

[oracle@host01 ~]$ srvctl start service -d orcl -s hr_svc

[oracle@host01 ~]$ srvctl start service -d orcl -s accntg_svc

[oracle@host01 ~]$ srvctl status service -d orcl

Service accntg_svc is running on instance(s) orcl2

Service hr_svc is running on instance(s) orcl1

The services are now running, on the preferred instances. When the database is bounced, the services will automatically start.

[oracle@host01 ~]$ srvctl stop database -d orcl -o immediate

[oracle@host01 ~]$ srvctl start database -d orcl

[oracle@host01 ~]$ srvctl status service -d orcl

Service accntg_svc is running on instance(s) orcl2

Service hr_svc is running on instance(s) orcl1

A quick check of gv$services shows the services running in the proper instances.

SQL> select

2 inst_id,

3 name

4 from

5 gv$services

6 order by

7 inst_id,

8 name;

INST_ID NAME

---------- ------------------------------------------

1 SYS$BACKGROUND

1 SYS$USERS

1 hr_svc

1 orcl

1 orclXDB

2 SYS$BACKGROUND

2 SYS$USERS

2 accntg_svc

2 orcl

2 orclXDB

The tnsnames.ora configuration file helps to denote the service the application will connect. Sample entries in the tnsnames.ora file might look like the following.

HR_APPL =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = hr_svc)

)

ACCNTG_APPL =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = accntg_svc)

)

The HR application would make a connection to the hr_appl alias in the tnsnames.ora configuration file. Similarly, the accounting application would use the accntg_appl alias. Notice that both aliases are contacting the same Scan Listeners in the host field, the same Oracle RAC system.

The database administrator has complete control when configuring where a service runs. The service may run as the only service on an instance, or the service may run with other services on that instance. The service may run on multiple instances. By mixing and matching combinations of services to instances, the database administrator has utmost flexibility in segregating applications to reduce cross-instance traffic as well as scaling out an application across multiple database servers. If the Oracle RAC database supports multiple applications, it would be irresponsible to have all applications use the default database service. Using only one service for all applications eliminates flexibility and manageability and significantly reduces the options at the database administrator's disposal. A good practice is to have at least one service per application that will use the Oracle RAC database. A application that is broken down into modules may use one service per module.

Learn RAC Tuning Internals!

This is an excerpt from the landmark book Oracle RAC Performance tuning, a book that provides real world advice for resolving the most difficult RAC performance and tuning issues. Buy it for 30% off directly from the publisher.

Hit Counter

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals. Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata? Oracle technology is changing and we strive to update our BC Oracle support information. If you find an error or have a suggestion for improving our content, we would appreciate your feedback. Just e-mail:

and include the URL for the page.