5

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.

Anton Chekmarev
  • 61
  • 1
  • 1
  • 3
  • 192.168.10.110 is not localhost... is that the address you use for ssh? (I can see it in the netstat but it might be NAT'd by the VM). Is there a firewall that's allowing port 22 but not port 1521? – Alex Poole Mar 01 '16 at 16:31
  • Yes, im using this address for ssh connection. I've read that with HOST=localhost it listens only to local machine and this parameter need to be another address. Mostly i've seen it is name of host machine, but i'm not sure DNS is configured properly and put direct address. Turned off firewall to be sure it won't interfere, but still no connection – Anton Chekmarev Mar 02 '16 at 10:33
  • Your netstat output doesn't include port 1521, can you check that something is listening? And can you add the output of `lsnrctl status` to the question? If the listener is up and on that address, and you can connect to that port (e.g. via telnet) from that VM but not from the machine you ssh from, them something in the network is blocking it - a firewall on the server which you've ruled out, or some other network restriction, or even an outbound firewall on your client machine. – Alex Poole Mar 02 '16 at 10:38
  • Your listener.ora doesn't have `HOST=localhost` though. Or have you edited that file to change it from localhost to 192.168.10.110, but not restarted the listener? (It can listen to more than one address, incidentally, in case you have anything else relying on localhost; the database may be configured to register to a specific address for example) – Alex Poole Mar 02 '16 at 10:41
  • I added localhost to config and updated question. lsnrctl status tells .ora files are incorrert, isn't it? – Anton Chekmarev Mar 02 '16 at 11:01
  • The `listener.ora` you're now showing is missing a closing parenthesis. Presumably you are editing it manually, not through netca. Once that's fixed, have you bounced the listener? It doesn't pick up changes in the file while it is running. (The pmon entry in your netstat shows the DB registered against localhost; you aren't showing any LISTEN entries for 1521 or 1530, but that might be because you tried to bounce and got the same error) – Alex Poole Mar 02 '16 at 11:09

1 Answers1

2

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).

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • Couldn't add both 'localhost' and '192.168.10.110', got error that this address already busy. But i left only string with ip address and now i can connect from external. Thanks you much for your help. – Anton Chekmarev Mar 02 '16 at 13:21
  • That implies you already have a listener running on 1521, maybe from another Oracle home? Still check what netstat shows is listening on 1521 on both addresses, including the full path to the binary if you can. And check your DB is still happy registering. – Alex Poole Mar 02 '16 at 13:41