Why does re-configuring the Listener work?
You are not reconfiguring the listener. That's why you don't see the listener configuration file change. You are changing the database configuration. The spfile is updated because the command you used had scope=both, which means the change is applied immediately - in memory - and written to that file, so it persists on database restart.
From the docs:
LOCAL_LISTENER specifies a network name that resolves to an address or address list of Oracle Net local listeners (that is, listeners that run on the same system as this instance).
and the default is:
(ADDRESS = (PROTOCOL=TCP)(HOST=hostname)(PORT=1521)) where hostname is the network name of the local host.
See also the alter system REGISTER clause:
Specify REGISTER to instruct the PMON background process to register the instance with the listeners immediately. If you do not specify this clause, then registration of the instance does not occur until the next time PMON executes the discovery routine. As a result, clients may not be able to access the services for as long as 60 seconds after the listener is started.
So what this means is that when the database starts, and then periodically, it tries to register its service name(s) with the listener; and it gets the info about the listener from the local_listener parameter. (There's also a remote_listener for data guard, not relevant here.)
Because the default is hostname the PC name is used directly in that parameter, and that will often resolve to the PCs LAN IP address (which can change each reboot to add to the confusion, if the IP is assigned by DHCP), which could be stored instead. If you're lucky the hostname will resolve to the same as localhost, but that isn't the case for you.
So... the database looks up its init parameter, and as a result tries to register with a listener at say 192.168.1.2. But the listener has started on localhost so it is listening on 127.0.0.1. The DB fails to register its service name, as it can't reach a listener; though that is only apparent if you look in the alert log. If you run lsnrctl services it won't show anything.
When you change the init parameter you are telling the DB to try to register against a listener on localhost instead - and as that is where it is actually listening, registration now works, and the listener recognises the service name on subsequent connection attempts. (Your question refers to the error message about SID, which is different, and not solved by your change.) Running lsnrctl services will now show the service name too. But that is a runtime, dynamic thing via the registration - not a change to the permanent configuration of the listener.
It's possible to have the listener listen on more than one address. The main thing is that the listener.ora, tnsnames.ora (if you use TNS aliases) and init parameter use consistent host names or IP addresses, so they are all resolving to and referring to the same thing, whether that is localhost (only reachable from that PC), or a LAN address (reachable across the nwtwork) or both.
You can also have an entry in the tnsnames.ora for the listener itself, rather than just for DBs. You can then use that TNS alias as the local_listener target, instead of spelling put the address and port, possibly making it easier to change later if needed.
