fuzziebrain
commented
6 years ago IIRC, @martindsouza intentionally had the listener listening only to the local interface and it has roots in the deployment of VMs. It was also by intent that users had to connect to the database via an SSH tunnel.
Firstly - thank you for a wonderful environment. It is hugely timesaving. However I was unable to use this connection method listed in 'connect_oracle.md' (oxar docs) on a fresh install on CentOS VM (minimal install + developer tools group 1611) + OXAR) . Note the firewall was set correctly by choosing the options in build script. All other connection mechanisms work. It is only because I am on a local machine and wanted to test AFEB that I needed 1521 access. Other earlier installs but with access via ssh tunnelling work fine.
Opening Port 1521
Though not recommending on public networks, you may want to open port 1521 for a direct connection to the database. This is ideal when using a Virtual Machine (VM) on your system with a local internal network. They're several ways to open port 1521 from the firewall.
In order to resolve the problem I followed this advice (I realise that you have noted an issue in listener.ora and a reference but out of the box it did not work - SJD later edit - note I did need in the end to also comment out the extproc_for_xe line also after trying to test the NAT and back to bridged only setup as the listener wasn't starting properly) http://www.getshifting.com/wiki/oraclelistener 'Oracle Listener Not Listening On Localhost' He notes as a solutiom: "...It comes down to the simple requirement that the oracle listener shouldn't listen on the long hostname or the host's IP-address but on the hosts short hostname."
Here are 4 screenshots. 1st 2 are of the VM with lsnrctl status partially scrolled up but showing the endpoint summary and then netsat output. 1st is as configured:
2nd is after changing the hostname from 'localhost' to 'apexdev' (endpoint reported as '127.0.0.1', now reports as 'apexdev.lan')
SQLcl access from the windows 10 host to the guest Centos VM
Note I originally set up my VirtualBox VM with NAT and used the pt forwarding rules thinking that localhost naming would be fine. However I could not get this to work (both SQLDeveloper and SQLcl had the IO error: 'The Network Adapter could not establish the connection' using localhost), so the solution was done with virtualbox networking mode as 'bridged adaptor' and sqlcl connections from the host machine made using the IP address. Last screenshot shows 2 fails with the VM restarted in bridged mode - 1st fail not unexpected - windows wouldn't be able to resolve the name. 2nd fail was with the assigned IP address but original listener.ora. Final success was with the modifed listener.ora file
Modified listener.ora file.
Note last setting was down to seeing the warning in the listener log file and following the recommendation from this post: https://aprilcsims.wordpress.com/2011/04/28/warning-subscription-for-node-down-event-still-pending-can-cause-hanging-listener/ (note my listener wasn't hung but had this warning)
Thanks, Steve