CODEWITHSUNDEEP
linuxoracle-databaselistener
Anton Chekmarev
Anton Chekmarev

Reputation: 61

Configuring Oracle listener to listen externally

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.

Upvotes: 5

Views: 15474

Answers (1)

Alex Poole
Alex Poole

Reputation: 191335

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

Upvotes: 2

Related Questions