Service and Listener for non-RAC Oracle Database

Service - Definition:

A service is a means of grouping sessions by type of work more manageable, measurable, tunable, and recoverable.  It's an abstraction layer allowing clients and applications to access its data from the database.  It's the base for high availability of connections and additional performance tuning dimension.

3 Steps to create a service:

1)  Create a service name and a local listener (if not there) in tnsnames.ora

DBADW_ETL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = srv01.an.com)(PORT = 1972))
    (CONNECT_DATA =
      (SERVICE_NAME = DBADW_ETL)
    )
  )

DBA_L1 =
   (ADDRESS = (PROTOCOL = TCP)(HOST =srv01.an.com)(PORT = 1972))

2)  Create a listener in Listener.ora

ADR_BASE_DBA = /apps/oracle

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_DBA = ON

SID_LIST_DBA =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = DBALT)
      (ORACLE_HOME = /apps/oracle/product/11.2.0/DB)
      (SID_NAME = DBALT)
    )
  )

DBA =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = srv01.an.com)(PORT = 1972))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1972))
    )
  )


3)  Create service and register it via dbms_service

SQL> exec dbms_service.create_service(service_name=>'DBADW_ETL', network_name=>'DBADW_ETL');

SQL> exec dbms_service.start_service('DBADW_ETL');

SQL> alter system set local_listener = DBA_L1 scope=both;

SQL> alter system register;

SQL> show parameter service

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      DBADWLT, DBADW_ETL


SQL> show parameter listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
listener_networks                    string
local_listener                       string      DBA_L1
remote_listener                      string


To drop the service:

SQL> exec dbms_service.stop_service('DBADW_ETL');
PL/SQL procedure successfully completed.

SQL> exec dbms_service.delete_service('DBADW_ETL');
PL/SQL procedure successfully completed.


Create service via srvctl method:

srvctl add service -d MYDB -s DSPOC
srvctl status service -d MYDB -s DSPOC

Service DSPOC is not running.
srvctl start service -d MYDB -s DSPOC

SQL> show parameter service
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      MYDB, DSPOC


Note:  If you get ORA-12514: TNS: listener does not currently know of service required in connect.., check your tnsnames.ora and the local listener.  You might need to set the local_listener = memory or bounce it.