Skip to content

How to Add Service to Database

  • by

There're two ways to add a service to a database (non-CDB):

  1. DBMS_SERVICE Create Service
  2. It's an administration package for managing services of a database.

  3. SRVCTL Add Service
  4. For RAC databases or Oracle Restart, we can use it to manage services.

A. DBMS_SERVICE Create 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.

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.

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.

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', 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 complete the life cycle of a database service by the above 4 steps.

B. SRVCTL Add Service

1. srvctl add service

For a RAC database, you can use srvctl add service command to create a service.

[oracle@primary01 ~]$ srvctl add service -d orcl -s CRM -r ORCL1,ORCL2

Or using the long form for release 12c and later.

[oracle@primary01 ~]$ srvctl add service -database orcl -service CRM -preferred ORCL1,ORCL2

This is the simplest way to create a service for a RAC database. However, the service can be configured as a failover-able one by adding more options to it, which means that it's able to failover sessions to another instance transparently if current instance is down.

2. srvctl start service

[oracle@primary01 ~]$ srvctl start service -d orcl -s crm

3. srvctl status service

[oracle@primary01 ~]$ srvctl status service -d orcl -s crm
Service CRM is running on instance(s) ORCL1,ORCL2

As we can see, we start the service on both nodes.

Now we should check the statuses of both listeners.

Node 1

[oracle@primary01 ~]$ lsnrctl status
...
Service "CRM" has 1 instance(s).
  Instance "ORCL1", status READY, has 1 handler(s) for this service...

Node 2

[oracle@primary02 ~]$ lsnrctl status
...
Service "CRM" has 1 instance(s).
  Instance "ORCL2", status READY, has 1 handler(s) for this service...

The service is ready to accept connections.

In a multitenant environment, the service adding procedure for pluggable databases (PDB) is almost the same.

Leave a Reply

Your email address will not be published.