Create a custom service in single instance Oracle 19c

This post demonstrates how to create a custom service in a single instance Oracle 19c database. As per the Oracle 19c PL/SQL Packages and Types guide, this is the only Oracle database deployment option where you are allowed to use this technique. Anything to do with high availability rules this approach out straight away. The same applies for a database managed by Clusterware (both Oracle Real Application Clusters and Oracle Restart) and Global Data Services (GDS).

Furthermore, the service_name parameter to DBMS_SERVICE.CREATE_SERVICE() is deprecated, I wouldn’t recommend running the code in this blog post on anything newer than Oracle 19c.

If you have a 19c single-instance database where the use of DBMS_SERVICE.CREATE_SERVICE is permitted, you might find this post useful. As always, my example deals with Swingbench. I created a CDB containing a PDB named swingbench1. In addition to the default service I would like to start a custom service, swingbench1_svc. This little SQL*Plus script should do the trick.

To add a little bit of security I added a short prompt to remind you that you can’t use DBMS_SERVICE.START_SERVICE in combination with RAC/Oracle Restart/Global Data Service. It requires you to be logged in a SYS, but that’s easy enough to change if you don’t want to do so.

whenever sqlerror exit

set verify off

define v_service=swingbench1_svc

-- let's try to prevent problems with RAC/Oracle Restart and GDS before we start

prompt This script cannot be run if your database is managed by Clusterware (RAC/Oracle Restart)
prompt or Global Data Services (GDS). 
prompt
accept ok_to_run prompt 'Is this environment a single-instance database [y/n] '

BEGIN
    IF upper('&ok_to_run') != 'Y' THEN
        raise_application_error(-20001, 'you must not use this script with RAC/Oracle Restart/GDS');
    END IF;
END;
/


DECLARE
    v_parameters dbms_service.svc_parameter_array;
    service_exists EXCEPTION;
    service_running EXCEPTION;
    PRAGMA exception_init ( service_exists, -44303 );
    PRAGMA exception_init ( service_running, -44305);
    v_version VARCHAR2(100);
    v_compatibility VARCHAR2(100);
    
BEGIN
    -- must be connected as SYS to a non-CDB or PDB
    IF
        sys_context('userenv', 'cdb_name') IS NOT NULL
        AND sys_context('userenv', 'con_id') <= 2
    THEN
        raise_application_error(-20002, 'you must be connected to a PDB');
    END IF;

    IF sys_context('userenv', 'session_user') != 'SYS' THEN
        raise_application_error(-20002, 'you must by logged in as SYS to run this code');
    END IF;

    -- make sure this is 19c
    dbms_utility.db_version(v_version, v_compatibility);
    if v_version != '19.0.0.0.0' then
        raise_application_error(-20003, 'you must run this script in a 19c database');
    end if;    

    -- create the service, there is no need to provide any parameters
    -- for a single instance database. Ignore the error should the service exist
    BEGIN
        dbms_service.create_service(
            service_name => '&v_service', 
            network_name => '&v_service',
            parameter_array => v_parameters);
    EXCEPTION
        WHEN service_exists THEN
            NULL;
        WHEN others THEN
            raise;
    END;
            
    -- and start it. Ignore an error in case it's running
    BEGIN
        dbms_service.start_service('&v_service');
    EXCEPTION
        WHEN service_running THEN
            NULL;
        WHEN others THEN
            raise;
    END;
END;
/

-- make sure the service starts when the database opens
CREATE OR REPLACE TRIGGER SYS.SERVICES_TRIG
AFTER STARTUP ON DATABASE 
BEGIN
    IF sys_context('userenv','database_role') = 'PRIMARY' THEN
        dbms_service.start_service('&v_service');
    END IF;
END;
/

Once the code is deployed, the service will start with the PDB:

SQL> select name, open_mode, con_id from v$pdbs;

NAME                           OPEN_MODE      CON_ID
------------------------------ ---------- ----------
PDB$SEED                       READ ONLY           2
SWINGBENCH1                    MOUNTED             4

SQL> select con_id, name from v$active_services order by 1,2;

    CON_ID NAME
---------- ------------------------------
         1 CDB
         1 CDBXDB
         1 SYS$BACKGROUND
         1 SYS$USERS
         4 swingbench1

SQL> alter pluggable database SWINGBENCH1 open;

Pluggable database altered.

SQL> select con_id, name from v$active_services order by 1,2;

    CON_ID NAME
---------- ------------------------------
         1 CDB
         1 CDBXDB
         1 SYS$BACKGROUND
         1 SYS$USERS
         4 swingbench1
         4 swingbench1_svc

6 rows selected.

That should do it: if I can’t rely on Clusterware or Global Data Services to manage services for my database, I can use this approach to create an “application” or custom service in my single instance database. And it works:

SQL> conn martin@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = server1.example.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = swingbench1_svc)))
Enter password: 
Connected.
SQL> select sys_context('userenv','service_name') from dual;

SYS_CONTEXT('USERENV','SERVICE_NAME')
----------------------------------------------------------
swingbench1_svc

I like it when a plan comes together.