配置Oracle侦听器以进行外部侦听 [英] Configuring Oracle listener to listen externally

问题描述

我坚持使用Oracle侦听器配置.它正在监听localhost，但是我无法从外部计算机访问它.网络工作正常，Oracle已安装在虚拟机上，而我正在ssh上工作.

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.

我的listener.ora文件:

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

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

其中，QSYSTEM是数据库SID，而192.168.10.110是主机netstat输出的地址.

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输出:

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

我正在RHEL 7上使用Oracle 11g Standard.

I'm using Oracle 11g Standard on RHEL 7.

解决方案: 当我用IP地址替换localhost并正确更改并格式化listener.ora和tnsnames.ora格式时，我就建立了连接.

Solution: When i replaced localhost with IP address and properly changed and formatted listener.ora and tnsnames.ora i've established connection.

推荐答案

如果您想让监听器使用两个地址来处理内部和外部连接，则需要在listener.ora中列出两者，并添加一个平衡的ADDRESS_LIST级别括号:

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.

然后，您需要停止并启动侦听器以获取更改.您可以使用lsnrctl status和/或netstat验证它正在收听的内容.

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.

您可能还想通过查看LOCAL_LISTENER初始化参数来检查数据库的注册方式.如果未设置该名称或使用主机名，请检查它是否解析为您指定的IP地址之一-通过DNS或/etc/hosts.如果无法正确解析，则它将无法在侦听器中注册，lsnrctl services将不会列出其服务名称，并且您将无法使用服务名称通过SQL * Net连接. (或SID，因为您没有SID_LIST_LISTENER条目).

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

这篇关于配置Oracle侦听器以进行外部侦听的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！