How to Use DBMS_SERVICE to Create a New Service?
The default service name of a database is the DB_UNIQUE_NAME, if we'd like to add some other entries to service different kinds of user, we have two options, one is to use DBMS_SERVICE to create a service in database, the other is to use srvctl add service in cluster.
To create a new service to a pluggable database (PDB) by DBMS_SERVICE, there's a little different.
Let's see how we add a service to the database by DBMS_SERVICE.
For single-instance, we can only use DBMS_SERVICE to add services to the database. Let's see the life cycle of a database service.
1. DBMS_SERVICE.CREATE_SERVICE
Create a service for the database
SQL> exec dbms_service.create_service('ERP', 'ERP');
PL/SQL procedure successfully completed.
The service is added to the database, no matter a single-instance or a RAC database.
There're more variations on adding a failover service to the cluster.
2. DBMS_SERVICE.START_SERVICE
The new service won't start automatically after creation, you have to start it manually.
Start the service
SQL> exec dbms_service.start_service('ERP');
PL/SQL procedure successfully completed.
DBMS_SERVICE.ALL_INSTANCES
For RAC database, you should start the services on all instances in the cluster:
SQL> exec dbms_service.start_service('ERP', DBMS_SERVICE.ALL_INSTANCES);
PL/SQL procedure successfully completed.
DBMS_SERVICE.START_SERVICE Automatically
Since the running status of service is not persistent across database restarts, so you need a trigger in order to start the service automatically.
create or replace trigger START_SERVICES after startup on database
begin
dbms_service.start_service('ERP');
end;
/
Check services
Let's check online services of the database.
SQL> column name format a30;
SQL> column network_name format a30;
SQL> select name, network_name from v$active_services;
NAME NETWORK_NAME
------------------------------ ------------------------------
ORCL ORCL
ERP ERP
...
For RAC databases, you may see the information like this:
SQL> select inst_id, name, network_name from gv$active_services;
INST_ID NAME NETWORK_NAME
---------- ------------------------------ ------------------------------
1 ORCL ORCL
1 ERP ERP
2 ORCL ORCL
2 ERP ERP
3. DBMS_SERVICE.STOP_SERVICE
If you want to remove service from the database, you have to stop it first.
SQL> exec dbms_service.stop_service('ERP');
PL/SQL procedure successfully completed.
For RAC database, you should stop the services on all instances in the cluster:
SQL> exec dbms_service.stop_service('ERP', DBMS_SERVICE.ALL_INSTANCES);
PL/SQL procedure successfully completed.
4. DBMS_SERVICE.DELETE_SERVICE
Then delete it.
SQL> exec dbms_service.delete_service('ERP');
PL/SQL procedure successfully completed.
If you don't stop it before deleting it, you shall see ORA-44305: service is running.
We have completed the life cycle of a database service by the above 4 steps.
In a multitenant environment, the service adding procedure for pluggable databases (PDB) is almost the same.