Adding service to single instance database

Database services (services) are logical abstractions for managing workloads in Oracle Database. Each service represents a workload with common attributes, service-level thresholds, and priorities.

In Real Application Clusters (RAC), a service can span one or more instances and facilitate real workload balancing based on real transaction performance.   RAC also enables you to manage a number of service features with Enterprise Manager, the DBCA, and the Server Control utility (SRVCTL).

The DBMS_SERVICE package lets you create, delete, activate and deactivate services for a single instance. This package allows also the creation, deletion, starting and stopping of services in RAC  Additionally it provides the ability to disconnect all sessions which connect to the instance with a service name.

In addition to application services, Oracle Database also supports two internal services: SYS$BACKGROUND is used by the background processes only and SYS$USERS is the default service for user sessions that are not associated with services.

Adding Service Example:
1-  Login database as sysdba  and create the service
SQL> exec dbms_service.CREATE_SERVICE(SERVICE_NAME=>'MyService', NETWORK_NAME=>'MyServiceNetworkName')

PL/SQL procedure successfully completed.
2- review the listener status , no service registeration
c:\TEMP>lsnrctl status

LSNRCTL for 64-bit Windows: Version 11.2.0.3.0 - Production on 15-OCT-2012 11:59:25

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.3.0 - Production
Start Date                02-OCT-2012 20:18:11
Uptime                    12 days 15 hr. 41 min. 17 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   D:\app\inam\product\11.2.0\dbhome_1\network\admin\listener.ora
Listener Log File         D:\app\inam\diag\tnslsnr\S-HQ-SV-OR-01\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=S-HQ-SV-OR-01.mydomain)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "stage.mydomain" has 1 instance(s).
  Instance "stage", status READY, has 1 handler(s) for this service...
Service "stageXDB.mydomain" has 1 instance(s).
  Instance "stage", status READY, has 1 handler(s) for this service...
The command completed successfully
3- Start the service
SQL> exec dbms_service.START_SERVICE('MyService')
PL/SQL procedure successfully completed.

4-  Review the listener status again, you will observe the service registration now
c:\TEMP>lsnrctl status

LSNRCTL for 64-bit Windows: Version 11.2.0.3.0 - Production on 15-OCT-2012 12:01:05

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.3.0 - Production
Start Date                02-OCT-2012 20:18:11
Uptime                    12 days 15 hr. 42 min. 57 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   D:\app\inam\product\11.2.0\dbhome_1\network\admin\listener.ora
Listener Log File         D:\app\inam\diag\tnslsnr\S-HQ-SV-OR-01\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=OR-01.mydomain)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "MyServiceNetworkName.mydomain" has 1 instance(s).
  Instance "stage", status READY, has 1 handler(s) for this service...
Service "stage.mydomain" has 1 instance(s).
  Instance "stage", status READY, has 1 handler(s) for this service...
Service "stageXDB.mydomain" has 1 instance(s).
  Instance "stage", status READY, has 1 handler(s) for this service...
The command completed successfully


5- Create the tnsentry for the new service in tnsnames.ora
################Service TEST ############################## 
MyService =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = OR-01.mydomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = MyServiceNetworkName.mydomain)
    )
  )

6- Test it
C:\Users\inam>sqlplus scott/tiger@myservice
SQL*Plus: Release 11.2.0.1.0 Production on Mon Oct 15 12:38:29 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

7- Some other activities
-- Start service
exec DBMS_SERVICE.START_SERVICE('MyService','stage'); -- with Instance Name 
-- Stop the Service
exec dbms_service.STOP_SERVICE('MyService')

-- Delete Service
exec dbms_service.DELETE_SERVICE(SERVICE_NAME=>'MyService')
-- Disconnect all session related to service
exec DBMS_SERVICE.DISCONNECT_SESSION('MyService')
-- View all services
SELECT name,network_name, creation_date, clb_goal FROM dba_services;
-- View all active services
SELECT service_id, name, network_name FROM gv$active_services;
 -- view all sessions with specific service
select username, service_name from v$session where service_name like 'My%