What does Oracles 'alter system set local_listener' do exactly

Question

Why I am curious:

I have always had issues when installing an Oracle Express Database. As in I have never had it work right the first time round. The next time I boot my computer after installing Oracle Express I consistently receive the common error:

TNS-12505: TNS:listener does not currently know of SID given in connect descriptor

I have managed to fix this issue using various methods however I have learned that the best method that works for me these days is to Reconfigure the Listener using SQL Plus. When the Listener fails SQL Plus is the only way I can connect to the OracleXE Server and after searching the Internet I'm not alone here.

The DDL that fixes this error - run from SQL Plus:

alter system set local_listener='(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521))' scope=both;

The Question:

Why does re-configuring the Listener work? What does it do? I monitored my listener.ora file along with my TNSNames.ora file and they remain the same before and after I run the above code. So what did it do?

I read this entire Oracle LISTENER Documentation and I did not come out of it enlightened. Can anyone enlighten me?

Answer 1

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.