If you base LOCAL_LISTENER on SERVICE_NAME and you change the underlying hostname of the service you must reset LOCAL_LISTENER.
This is because, even after creating a new listener, the underlying host that Oracle is looking for is not updated within the database – and as a result – the database cannot register;
desc v$listener_network;
select * from v$listener_network;
NETWORK VALUE
———————— —————————————————————————————————-
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ggstby)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=gggstby)(UR=A)))
GGSTBY
show parameter local_listener
GGSTBY
show parameter listener_networks
If you do not reset local listener – the old host name is still being used and you will like get “The listener supports no services”.
So here’s the fix – connect to the database after updating your listener.ora and tnsnames.ora and starting your new db – and update LOCAL_LISTENER even if your service name did not change – only the underlying hostname changed.
alter system set set LOCAL_LISTENER=GGSTBY;
Ok, so NOTICE I REALLY DIDN’T CHANGE THE VALUE OF LOCAL_LISTENER? It is still GGSTBY – but Oracle (the database instance) behind the scenes is going out to listener.ora and picking up the new hostname… and V$LISTENER_NETWORK showed me that…
Now the database will update internally – to use the new hostname. How do I know – other than the registration now works? Because if you query the internal view V$LISTENER_NETWORK – the value column is updated with the new hostname…
select * from v$listener_network;
NETWORK VALUE
———————— —————————————————————————————————-
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ggnew)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=gggstby)(UR=A)))
GGSTBY
show parameter local_listener
GGSTBY
I’m not showing my tnsnames.ora and listener.ora here but the only thing that changed was the hostname… and for quite a while I could not figure out why a simple hostname change in the listener to use a VIP rather than a physical hostname wouldn’t be picked up…
Why were we changing hostnames? We didn’t change the server hostnames. The server just has a bunch of hostnames 3 virtual / VIPs and 1 what I’ll call physical (the actual hostname). And we had a DG primary & standby pair that were using VIPs and they were the wrong VIPs.