Can you delete a service still with connections in Oracle database

Service created from srvctl

SQL> select count(*) from gv$session where service_name='test';

  COUNT(*)
----------
         1

SQL> select inst_id,SERVICE_ID,NAME,NAME_HASH,NETWORK_NAME,CREATION_DATE from gv$services  where NAME='test';

 INST_ID SERVICE_ID NAME    NAME_HASH NETWORK_NA CREATION_DATE
---------- ---------- ------ ---------- ---------- --------------------
         1          4 test   3237892936 test       19-APR-2024 13:43:50

SQL> exec dbms_service.DELETE_SERVICE('test');
BEGIN dbms_service.DELETE_SERVICE('test'); END;

*
ERROR at line 1:
ORA-44305: service test is running
ORA-06512: at "SYS.DBMS_SERVICE", line 68
ORA-06512: at "SYS.DBMS_SERVICE", line 458
ORA-06512: at line 1

$ srvctl remove service -s test -d testdb
PRCR-1025 : Resource ora.testdb.test.svc is still running

$ srvctl stop service -s test -d testdb

SQL>  exec dbms_service.DELETE_SERVICE('test');

PL/SQL procedure successfully completed.


SQL> select count(*) from gv$session where service_name='test';

  COUNT(*)
----------
         1

So we can delete a service only when the service is down, but the current connections are still kept.

Service created from dbms_service

SQL>  exec  DBMS_SERVICE.CREATE_SERVICE('test','test');

PL/SQL procedure successfully completed.

SQL>  exec  DBMS_SERVICE.start_service('test');

PL/SQL procedure successfully completed.

SQL>select inst_id,SERVICE_ID,NAME,CREATION_DATE,CON_NAME from gv$active_services;
  INST_ID SERVICE_ID NAME                                                             CREATION_DATE        CON_NAME
---------- ---------- ---------------------------------------------------------------- -------------------- ----------
...
.
 2          4 test                                                             19-APR-2024 14:40:45 pdb1

SQL>  exec dbms_service.DELETE_SERVICE('test');
BEGIN dbms_service.DELETE_SERVICE('test'); END;

*
ERROR at line 1:
ORA-44305: service test is running
ORA-06512: at "SYS.DBMS_SERVICE", line 68
ORA-06512: at "SYS.DBMS_SERVICE", line 458
ORA-06512: at line 1


SQL>  exec dbms_service.STOP_Service('test');
PL/SQL procedure successfully completed.

SQL>  exec dbms_service.DELETE_SERVICE('test');
PL/SQL procedure successfully completed.

SQL> select count(*) from gv$session where service_name='test';

  COUNT(*)
----------
         2

So we can delete a service only when the service is down, but the current connections are still kept.

How to Create Service on Non-RAC PDB

As we know, we can use ‘srvctl’ utility to create a service for a RAC database. Now I demonstrate how to create a service for a standalone CDB/PDB database.

For non CDB/PDB database, we can use ‘service_name’ parameter to specify service names.

SQL> show parameter service_name;

 NAME             TYPE    VALUE
 --------------- ------- ------------------
 service_names   string  HR,STOCK,INVENTORY

Subscribe to get access

Read more of this content when you subscribe today.

CRS-2674 CRS-2632 ORA-01013 CRS-5017 PRCR-1079 PRCD-1084 Srvctl Start Service

One or two services failed to start up, while the rest services are all good to be started up or shutdown.

$ srvctl start service -d TESTDB -s reports
PRCD-1084 : Failed to start service REPORTS
PRCR-1079 : Failed to start resource ora.testdb.reports.svc
CRS-5017: The resource action "ora.testdb.reports.svc start" 
          encountered the following error:
ORA-01013: user requested cancel of current operation
. For details refer to "(:CLSN00107:)" in "/u01/app/grid/diag/crs/
                     racnode1/crs/trace/crsd_oraagent_oracle.trc".
CRS-2674: Start of 'ora.testdb.reports.svc' on 'racnode1' failed
CRS-2632: There are no more servers to try to place resource 
 'ora.testdb.reports.svc' on that would satisfy its placement policy

It seems the information about this database or service is inconsistent in OCR. We can try the following steps one after another until the it is successful.

1)Remove database from OCR, and add it back again.

$srvctl remove database....

$srvctl add database ....

$ srvctl add instance ....

$ srvctl start service -d TESTDB -s reports

2) if not working in previous step, then restart all clusterware.

#crsctl stop crs

#ctsct start crs

$srvctl start service -d TESTDB -s reports

3) For some reason database outage is unavailable, service can be started up manually.

Start up service on instance 1. Users or applications can connect to database through this service, but srvctl still shows service not running

SQL> exec DBMS_SERVICE.START_SERVICE('REPORTS','TESTDB1');

$ srvctl status service -d testdb -s reports 

Service REPORTS is not running.