Important Note – when changing a hostname in the listener.ora – and LOCAL_LISTENER is used in the database to be registered with the listener.
The init/spfile parameter LOCAL_LISTENER MUST be reset even if it’s value won’t change as the Oracle database only identifies the host for static registration with a listener to be used when LOCAL_ADMIN is set.
What happens if you don’t reset LOCAL_ADMIN? The database will not register with the database – and you may see this in a lsnrctl status: The listener supports no services
In other words, in addition to changing listener.ora and tnsnames.ora also do this on all databases to be registered:
SELECT * FROM V$LISTENER_NETWORK;
Alter system set LOCAL_ADMIN=MYDB scope=both;
SELECT * FROM V$LISTENER_NETWORK;
Do the same on your DG standby if you have one and that is changing as well.
When changing host names for existing listeners and databases.
Querying the view above ( V$LISTENER_NETWORK ) can be very useful to determine what listener your database thinks should be it’s host.
Also note for thoroughness, there is also a newer INIT/SPFILE parameter named LISTENER_NETWORKS that would impact functionality if used (I am not here).
SQL> desc v$listener_network;
Name Null? Type
—————————————– ——– —————————-
NETWORK VARCHAR2(256)
TYPE VARCHAR2(64)
VALUE VARCHAR2(1024)
CON_ID NUMBER
SQL> select network, type, value from v$listener_network;
NETWORK
——————————————————————————–
TYPE
—————————————————————-
VALUE
——————————————————————————–
LOCAL LISTENER
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myhostsb.localdomain)(PORT=1521))(CONNECT
_DATA=(SERVER=DEDICATED)(SERVICE_NAME=MYDBSBY)(UR=A)))