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.