12c Improvement in Database Service Management

Removing a service via srvctl has not historically resulted in the service being fully removed from the database and it would still be visible in DBA_SERVICES as show below in an 11.2.0.3 database:

Create the service:

$ srvctl add service -d orcl -s demo -r "ORCL1,ORCL2"

Have a look in DBA_SERVICES:

SYS@ORCL1> select name service_name from dba_services;

SERVICE_NAME
----------------------------------------------------------------
SYS$BACKGROUND
SYS$USERS
ORCL

Start the service:

$ srvctl start service -d orcl -s demo

Have another look in DBA_SERVICES:

SYS@ORCL1> select name service_name from dba_services;

SERVICE_NAME
----------------------------------------------------------------
SYS$BACKGROUND
SYS$USERS
ORCL
demo

Stop the service:

$ srvctl stop service -d orcl -s demo

Remove the service:

$ srvctl remove service -d orcl -s demo

Have yet another look in DBA_SERVICES:

SYS@ORCL1> select name service_name from dba_services;

SERVICE_NAME
----------------------------------------------------------------
SYS$BACKGROUND
SYS$USERS
ORCL
demo

So as you can see the service remains in the database and is visible through DBA_SERVICES.

The only way I’m aware of to remove the service from the database is via DBMS_SERVICE.DELETE_SERVICE:

SYS@ORCL1> exec dbms_service.delete_service('demo') 

PL/SQL procedure successfully completed.

SYS@ORCL1>

A final look in DBA_SERVICES:

SYS@ORCL1> select name service_name from dba_services;

SERVICE_NAME
----------------------------------------------------------------
SYS$BACKGROUND
SYS$USERS
ORCL

I can imagine you’re thinking, “So what?”

Well, this can have some knock on implications as I recently demonstrated during my presentation on Fast Connection Failover at UKOUG Tech13.

Anyway, I’m please to report that Oracle have done a bit of tidying up in 12c and now when you remove a service via srvctl it is also removed from the database (most of the time).

Repeating the tests above in a 12.1.0.1 database [Strictly I should be using the new syntax for srvctl in the 12c example that follows, but as the old syntax still works and is consistent with the 11.2.0.3 example above I’ve stuck with it here]:

Create the service:

$ srvctl add service -d orcl -s demo -r "ORCL1,ORCL2"

Have a look in DBA_SERVICES:

SYS@ORCL1> select name service_name from dba_services;

SERVICE_NAME
----------------------------------------------------------------
SYS$BACKGROUND
SYS$USERS
ORCLXDB
ORCL

Start the service:

$ srvctl start service -d orcl -s demo

Have another look in DBA_SERVICES:

SYS@ORCL1> select name service_name from dba_services;

SERVICE_NAME
----------------------------------------------------------------
SYS$BACKGROUND
SYS$USERS
demo
ORCLXDB
ORCL

Stop the service:

$ srvctl stop service -d orcl -s demo

Have another look in DBA_SERVICES:

SYS@ORCL1> select name service_name from dba_services;

SERVICE_NAME
----------------------------------------------------------------
SYS$BACKGROUND
SYS$USERS
demo
ORCLXDB
ORCL

It’s still there.

Remove the service:

$ srvctl remove service -d orcl -s demo

Have yet another look in DBA_SERVICES:

SYS@ORCL1> select name service_name from dba_services;

SERVICE_NAME
----------------------------------------------------------------
SYS$BACKGROUND
SYS$USERS
ORCLXDB
ORCL

SYS@ORCL1> 

It’s gone.

As I write this I realise most people (even those working with Oracle databases) aren’t going to be that trilled by this minor improvement, but it pleases me.

The question that immediately occurred to me is, “So what happens in the database is down when the service is removed?”

Add and start the service:

$ srvctl add service -d orcl -s demo -r "ORCL1,ORCL2"
$ srvctl start service -d orcl -s demo

Verify that it exists in DBA_SERVICES:

SYS@ORCL1> select name service_name from dba_services;

SERVICE_NAME
----------------------------------------------------------------
SYS$BACKGROUND
SYS$USERS
demo
ORCLXDB
ORCL

Stop the database, remove the service and start the database:

$ srvctl stop database -d orcl 
$ srvctl remove service -d orcl -s demo
$ srvctl start database -d orcl

Reconnect to the database and have a look in DBA_SERVICES:

SYS@ORCL1> conn / as sysdba
Connected.
SYS@ORCL1> select name service_name from dba_services;

SERVICE_NAME
----------------------------------------------------------------
SYS$BACKGROUND
SYS$USERS
demo
ORCLXDB
ORCL

SYS@ORCL1> 

So in this situation you’re left to clear things up with DBMS_SERVICE yourself.

At the risk of going overboard with testing scenarios I also wondered what would happen if only the local instance was down. I’ll save you the output and simply stated that if the local instance is down then the service is still cleaned up in the database (by oraagent.bin).

2 thoughts on “12c Improvement in Database Service Management

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>