How to configure an Oracle Listener without SID_LIST_LISTENER in listener.ora?
Bit late to the party on this one...

The database should register with the listener automatically, making the SID_LIST entries redundant, and this seems to be happening with your 192.168.111.111 environment. If the listener is started after the database it can take a while for it to register, and there may be situations where it doesn't do so at all.

You can attempt to make it register with an alter system register command. The database uses the LOCAL_LISTENER parameter to identify the listener it should register with. By default that is null, which according to the documentation is equivalent to hostname:1521.

If alter system register doesn't make the service appear in the lsnrctl status output then I'd suspect it's unable to either identify the hostname or resolve it, or it's resolving to a different address than the one the listener is on, or LOCAL_LISTENER is set to something invalid for the virtual box. You can set the LOCAL_LISTENER to either match the listener.ora directly, e.g:

alter system set LOCAL_LISTENER='(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))' scope=both;
alter system register;


Or you can use an alias that has those details in your tnsnames.ora. For example, if you add a tnsnames.ora entry like:

MY_LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
  )


Then you could:

alter system set local_listener='MY_LISTENER' scope=both;
alter system register;


The only real advantage I can see of using the tnsnames.ora version, apart perhaps from brevity, is that you can change the configuration in the SQL*Net files rather than in the database; and even that only really seems useful if you're cloning databases between machines, or have your listeners and databases running under different accounts (e.g. with a grid user for RAC/HA).

There's more on LOCAL_LISTENER here.

Edit: And this seems quite comprehensive.