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.