Skip to content

Alter System set Service_Names

  • by

Add a Service to SERVICE_NAMES

Adding a service to the server parameter SERVICE_NAMES is only working for normal, typical databases (non-CDB) or in root containers. This is because they can directly use physical SPFILE to startup their instances, pluggable databases (PDB) cannot.

To add a service to a PDB, you still have other choices.

1. Show Parameter SERVICE_NAMES

Let's see current settings.

SQL> show parameter service_names

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      ORCL

There's only 1 service for the database. Let's add another service name to it.

2. ALTER SYSTEM SET SERVICE_NAMES

Since SERVICE_NAMES can be dynamically changed, we can do it with SCOPE=BOTH.

SQL> alter system set service_names='ORCL,ERP' scope=both sid='*';

System altered.

Here we use commas to separate services.

SQL> show parameter service_names

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      ORCL,ERP

A new service ERP is added. Optionally, we can register it to the listener manually.

SQL> alter system register;

System altered.

Or wait for service update in 60 seconds without doing anything.

3. lsnrctl status

Next, we check the listener.

[oracle@test ~]$ lsnrctl status
...
Services Summary...
Service "ERP" has 1 instance(s).
  Instance "ORCL", status READY, has 1 handler(s) for this service...

Service "ORCL" has 1 instance(s).
  Instance "ORCL", status READY, has 1 handler(s) for this service...
Service "ORCLXDB" has 1 instance(s).
  Instance "ORCL", status READY, has 1 handler(s) for this service...
The command completed successfully

The new service is automatically registered with the listener right after altering system, no need to manually register it.

Please note that, if this is in a root container (CDB$ROOT), you need to re-open all PDB in order to get all services of PDB back to listener. A restart to the entire database is even better.

Leave a Reply

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