I'm stuck with Oracle listener configuration. It's listening to localhost, but i can't reach it from an external machine. The network works fine, Oracle is installed on a virtual machine and I'm working over ssh.
My listener.ora file:
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST=
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.110)(PORT = 1530))
)
)
ADR_BASE_LISTENER = /home/oracle/app/oracle
and tnsnames.ora:
QSYSTEM =
(DESCRIPTION =
(ADDRESS_LIST=
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.110)(PORT = 1530))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = QSYSTEM)
)
)
where QSYSTEM is the database SID, and 192.168.10.110 is the address of the host netstat output.
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:50904 0.0.0.0:* LISTEN 9650/ora_d000_QSYST
tcp 0 0 192.168.10.110:22 10.48.202.125:63350 ESTABLISHED 2994/sshd: oracle [
tcp 0 0 127.0.0.1:14468 127.0.0.1:1521 ESTABLISHED 9618/ora_pmon_QSYST
udp 0 0 127.0.0.1:60990 0.0.0.0:* 9652/ora_s000_QSYST
udp 0 0 127.0.0.1:20566 0.0.0.0:* 9650/ora_d000_QSYST
udp 0 0 0.0.0.0:21371 0.0.0.0:* 9646/ora_mmon_QSYST
udp 0 0 127.0.0.1:58024 0.0.0.0:* 9618/ora_pmon_QSYST
lsnrctl status output:
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 02-MAR-2016 13:57:16
Copyright (c) 1991, 2011, Oracle. All rights reserved.
TNS-01150: The address of the specified listener name is incorrect
NL-00303: syntax error in NV string
I'm using Oracle 11g Standard on RHEL 7.
Solution:
When i replaced localhost with IP address and properly changed and formatted listener.ora and tnsnames.ora i've established connection.
If you want your listener to handle internal and external connections using both addresses you need to list both in the listener.ora, adding an ADDRESS_LIST level with balanced parentheses:
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST=
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.110)(PORT = 1521))
)
)
)
The same port can be used for both addresses, or they can be different if you prefer. If they are different a separate listener might be less confusing but it will work either way.
You then need to stop and start the listener to pick up the changes. You can verify what it is listening to with lsnrctl status and/or netstat.
You may also want to check how your database is registering by looking at the LOCAL_LISTENER initialisation parameter. If that is not set or is using a host name then check that it resolves to one of the IP addresses you have specified - either through DNS, or in /etc/hosts. If it can't resolve properly then it won't be able to register with the listener, lsnrctl services won't list its service name(s), and you wont' be able to connect over SQL*Net using a service name. (Or a SID, as you don't have SID_LIST_LISTENER entries).