I'm trying to change the service name of an Oracle 11.2.0.3 installation on a Windows 2003 server.
During installation the service name was defined with the default domain, but we'd like to get rid of that.
What I have done so far (and what has worked before) to change the service name mydb.foo.bar to mydb only:
alter system set service_names = 'mydb' scope = both;
alter database rename global_name to mydb;
Which seems to have worked:
SQL> show parameter name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string mydb db_unique_name string mydb global_names boolean FALSE instance_name string mydb service_names string mydb SQL>
(I removed some properties from the above output which are not relevant)
Then use alter system register to re-register with the listener.
This showed no effect so I restarted the database and the listener, still no luck.
The current situation is as follows:
select name from v$active_services returns:
SERVICE_ID | NAME | NETWORK_NAME -----------+-----------------+-------------------- 1 | SYS$BACKGROUND | 2 | SYS$USERS | 3 | mydb | mydb 5 | mydbXDB | mydbXDB 6 | mydb.foo.bar | mydb.foo.bar
So for some reason the old service name is still there and running.
When trying to stop the service using
SQL> exec dbms_service.stop_service('mydb.foo.bar');
PL/SQL procedure successfully completed.
no error is reported, but when trying to delete the service, Oracle won't let me:
SQL> exec dbms_service.delete_service('mydb.foo.bar');
BEGIN dbms_service.delete_service('mydb.foo.bar'); END;
*
ERROR at line 1:
ORA-44305: service mydb.foo.bar is running
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_SERVICE", line 454
ORA-06512: at "SYS.DBMS_SERVICE", line 343
ORA-06512: at line 1
I verified that I have no open connections using that service:
select count(*)
FROM v$session
where service_name = 'mydb.foo.bar';
returned 0 (zero)
I also re-recreated the Windows service using oradim, but to no avail.
At one point I ran
alter system set service_names = 'mydb,mydb.foo.bar' scope = both;
could that be the reason I have both now?
But after changing service_names to only a single name, the second one should have gone away, shouldn't it?
If I can't make the second service go away, it would be enough to make the instance register with the listener using the short service name, rather than the long one.
I'm sure I'm missing something pretty obvious, but I can't figure out what it is.
1 Answer 1
I'm sure I'm missing something pretty obvious, but I can't figure out what it is.
Yes it was something very obvious...
The parameter DB_DOMAIN still contained foo.bar and therefore the instance registered itself using mydb.foo.bar.
After doing a
alter system set db_domain='' scope=spfile;
and bouncing the database everything is working as expected now.