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
- Set the parameter in the server parameter file (SPFILE):
SQL> alter system set service_names='dba.example.com' scope=spfile;
System altered.
- Restart the instance, because
scope=spfiletakes effect at startup:
SQL> shut immediate
SQL> startup
- 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
- Oracle Database documentation: resolving database link issues
- How to change the service name in Oracle (blog)
Notes
- Host, domain and port values are replaced by examples (db01, dba.example.com, 1521). The source port was different.
- The SGA sizes of the source startup output were removed.