LLI Wiki — My Digital Knowledge Base

Oracle: change the service name of an 11g database

Change the service_names parameter in the SPFILE rather than only the tnsnames.ora alias, then restart the instance and check the connection.

On Oracle 11g, changing SERVICE_NAME in the client tnsnames.ora entry does not rename the database service. The database parameter service_names must be changed. The names below are examples.

Check the current service name

SQL> show parameter service

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      dba

Client alias in tnsnames.ora

DBA =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = db01)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = db01)
      (SERVICE_NAME = dba)
    )
  )

Changing only SERVICE_NAME to dba.example.com in this entry and restarting the listener did not work.

Change service_names

  1. Set the parameter in the server parameter file (SPFILE):
SQL> alter system set service_names='dba.example.com' scope=spfile;

System altered.
  1. Restart the instance, because scope=spfile takes effect at startup:
SQL> shut immediate
SQL> startup
  1. Check the new value:
SQL> show parameter service

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      dba.example.com

After this change, connections using the new service name work. Update the SERVICE_NAME of the client aliases that use the new name.

References

Notes

Mastodon LLIMastodon SocialFosstodon