Check

To be used a Service generally exists at a database level and, to accept connections from outside the database server, is registered to a Listener.

You can see all configured Services at the database level using...

set linesize 200

set pagesize 40

column network_name format a40

SELECT name, network_name, creation_date

FROM dba_services;

You can see running Services at the database level using...

set linesize 200

set pagesize 40

column network_name format a40

SELECT name, network_name, creation_date

FROM v$active_services;

You can see running Services at the listener level using...

lsnrctl stat

or, for more detail

lsnrctl services

Further Information

Where you see a Service Instance with a status of UNKNOWN in the output from lsrnctl, this generally means that the Service exists because it was defined in the LISTENER.ORA file (a 'Static Service') rather than being registered from the database instance (a 'Dynamic Service').

For example...

Service "ORCL1" has 2 instance(s).

Instance "ORCL1", status UNKNOWN, has 1 handler(s) for this service...

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

The first Instance, with the status of UNKNOWN is there because of this SID_DESC stanza in the LISTENER.ORA...

(SID_DESC =

(SID_NAME = ORCL1)

(ORACLE_HOME = /u01/app/oracle/product/122010ORCL1)

)

The second Instance, with the status of READY is there because of the parameter SERVICE_NAMES which is set as...

service_names string ORCL1

The lsnrctl stat command may show some other Services...

• There may be a Service to support the Oracle Messaging Gateway (e.g. mgwextproc)

• There may be Services to support the Data Guard Broker (e.g. ORCL1_DGB and ORCL1_DGMGRL)

• There may be some manually created Services

Create Service

exec DBMS_SERVICE.CREATE_SERVICE(service_name => 'SERVICE', network_name => 'SERVICE' );

Start Service

exec DBMS_SERVICE.START_SERVICE('SERVICE');

Stop Service

exec DBMS_SERVICE.STOP_SERVICE('SERVICE');

Delete Service

exec DBMS_SERVICE.DELETE_SERVICE(service_name => 'SERVICE');

If you create and start a Service using DBMS_SERVICE you will need to manually start the Service after every instance restart. There are several ways to get around this...

SERVICE_NAMES

If you add a service name to the SERVICE_NAMES parameter then all Services listed will be started and registered at database startup.

ALTER SYSTEM SET service_names = 'ORCL1, ORCL2, ORCL3...' SCOPE=both;

AFTER STARTUP ON DATABASE Trigger

This method is useful if you only want to start the Service under certain conditions (e.g. only if the database is a Physical Standby)...

CREATE OR REPLACE TRIGGER SERVICE_service_tr

AFTER STARTUP ON DATABASE

DECLARE

role VARCHAR(30);

BEGIN

SELECT database_role INTO role FROM v$database;

IF role = 'PHYSICAL STANDBY' THEN

DBMS_SERVICE.START_SERVICE('SERVICE');

END IF;

END;

/

Use a Pluggable Database

You can save the state of a pluggable database which will include the state of all Services...

ALTER PLUGGABLE DATABASE ALL SAVE STATE;

Disconnect Sessions

exec DBMS_SERVICE.disconnect_session(service_name => 'SERVICE', disconnect_option => DICONNECT_OPTION);

Bibliography