oracle 如何远程连接Oracle 11g数据库

Question

提问by gamosa

I have Oracle 11g XE installed on computer A. I can connect through the sql command line using the command connect username/password. I also can send SQL instructions to the Demo database: Select * from demo_customers;

我在计算机 A 上安装了 Oracle 11g XE。我可以使用命令通过 sql 命令行进行连接connect username/password。我也可以向 Demo 数据库发送 SQL 指令：Select * from demo_customers;

The database is running on localhost of computer A.

数据库在计算机 A 的本地主机上运行。

I want computer B to connect to computer A's database on localhost. How can I do that?

我希望计算机 B 连接到本地主机上计算机 A 的数据库。我怎样才能做到这一点？

Answer 1

回答by DCookie

You will need to run the lsnrctl utility on server A to start the listener. You would then connect from computer B using the following syntax:

您将需要在服务器 A 上运行 lsnrctl 实用程序以启动侦听器。然后，您将使用以下语法从计算机 B 进行连接：

sqlplus username/password@hostA:1521 /XE

The port information is optional if the default of 1521 is used.

如果使用默认值 1521，则端口信息是可选的。

Listener configuration documentation here.Remote connection documentation here.

监听器配置文档在这里。远程连接文档在这里。

Answer 2

回答by Tony

I install the Oracle server and it allows to connect from the local machine with no problem. But from another Maclaptop on my home network, it can't connect using either Sql Developer or Sql Plus. After doing some research, I figured out there is this additional step you have to do:

我安装了 Oracle 服务器，它可以毫无问题地从本地计算机进行连接。但是从我家庭网络上的另一台 Maclaptop 上，它无法使用 Sql Developer 或 Sql Plus 进行连接。在做了一些研究之后，我发现您必须执行以下额外步骤：

Use the Oracle net manager. Select the Listener. Add the IP address (in my case it is 192.168.1.12) besides of the 127.0.0.1 or localhost.

使用 Oracle 网络管理器。选择侦听器。除了 127.0.0.1 或 localhost 之外，添加 IP 地址（在我的例子中是 192.168.1.12）。

This will end up add an entry to the [OracleHome]\product\11.2.0\dbhome_1\network\admin\listener.ora

这最终会向 [OracleHome]\product\11.2.0\dbhome_1\network\admin\listener.ora 添加一个条目

restart the listener service. (note: for me I reboot machine once to make it work)
Use lsnrctl status to verify
Notice the additional HOST=192.168.1.12 shows up and this is what to make remote connection to work.
C:\Windows\System32>lsnrctl status
LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 05-SEP-2015 13:51:43
Copyright (c) 1991, 2010, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
Alias LISTENER
Version TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production
Start Date 05-SEP-2015 13:45:18
Uptime 0 days 0 hr. 6 min. 24 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File
D:\oracle11gr2\product\11.2.0\dbhome_1\network\admin\listener.ora
Listener Log File d:\oracle11gr2\diag\tnslsnr\eagleii\listener\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\.\pipe\EXTPROC1521ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.12)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "xe" has 1 instance(s).
Instance "xe", status READY, has 1 handler(s) for this service... Service "xeXDB" has 1 instance(s).
Instance "xe", status READY, has 1 handler(s) for this service... The command completed successfully
use tnspingto test the connection
ping the IPv4 address, not the localhost or the 127.0.0.1

重启监听服务。（注意：对我来说，我重新启动机器一次以使其工作）
使用 lsnrctl status 来验证
注意额外的 HOST=192.168.1.12 出现了，这是使远程连接工作的原因。
C:\Windows\System32> 64 位 Windows 的lsnrctl 状态
LSNRCTL：版本 11.2.0.1.0 - 生产于 05-SEP-2015 13:51:43
版权所有 (c) 1991, 2010, Oracle。版权所有。
连接到 (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
监听器的状态

用于 64 位 Windows 的别名 LISTENER版本 TNSLSNR：版本 11.2.0.1.0 - 生产
开始日期 05-SEP-2015 13:45:18
正常运行时间 0 天 0 小时。6 分钟。24 秒
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File
D:\oracle11gr2\product\11.2.0\dbhome_1\network\admin\listener.ora
Listener Log File d:\oracle11gr2\diag\tnslsnr\eagleii\ listener\alert\log.xml
侦听端点摘要...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\.\pipe\EXTPROC1521ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.12)(PORT=1521)))
服务摘要...
服务 "CLREExtProc" 有 1 个实例。
实例 "CLRExtProc", 状态 UNKNOWN, 具有此服务的 1 个处理程序...
服务 "xe" 具有 1 个实例。
实例 "xe", 状态 READY, 具有此服务的 1 个处理程序... 服务 "xeXDB" 具有 1 个实例。
实例 "xe", 状态 READY, 具有此服务的 1 个处理程序...命令成功完成
使用tnsping测试连接
ping IPv4 地址，而不是 localhost 或 127.0.0.1

C:\Windows\System32>tnsping 192.168.1.12
TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 - Production on 05-SEP-2015 14:09:11
Copyright (c) 1997, 2010, Oracle. All rights reserved.
Used parameter files:
D:\oracle11gr2\product\11.2.0\dbhome_1\network\admin\sqlnet.ora
Used EZCONNECT adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.12)(PORT=1521)))
OK (0 msec)

C:\Windows\System32>tnsping 192.168.1.12
用于 64 位 Windows 的 TNS Ping 实用程序：版本 11.2.0.1.0 - 生产于 05-SEP-2015 14:09:11
版权所有 (c) 1997, 2010, Oracle。版权所有。
使用的参数文件：
D:\oracle11gr2\product\11.2.0\dbhome_1\network\admin\sqlnet.ora
使用 EZCONNECT 适配器解析别名
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.12)(PORT=1521)))
OK (0 msec)

Answer 3

回答by Manjit Singh

Its quite easy on computer a you don't need to do anything just make sure both system are on same network if its not internet access(for this you need static ip). Okay now on computer b go to start menu find configuration under oracle folder click Net Configuration Assistant under that folder when window pop up click Local net configuration option it must be third option.

它在计算机上很容易，因为你不需要做任何事情，只要确保两个系统都在同一个网络上，如果它不能访问互联网（为此你需要静态 ip）。好的现在在计算机 b 上转到开始菜单在 oracle 文件夹下找到配置单击该文件夹下的网络配置助手，当窗口弹出时单击本地网络配置选项它必须是第三个选项。

Now click add and click next in next screen it will ask service name here you need to add oracle global database name of computer A(Normally I use oracle86 for my installation) now click next next screen choose protocol normally its tcp click next in host name enter computer A's name you can found that in my computer properties. Click next don't change port untill you have changed that in Computer A click next and choose test connection now here you can check your connection working or not if the error is username and password not correct then click login credential button and fill correct username and password. If its saying unable to reach computer ot target not found than you must add exception in firewall for 1521 port or just disable firewall on computer A.

现在单击添加并在下一个屏幕中单击下一步它会在这里询问服务名称您需要添加计算机 A 的 oracle 全局数据库名称（通常我使用 oracle86 进行我的安装）现在单击下一个屏幕选择协议通常它的 tcp 在主机名中单击下一步输入计算机 A 的名称，您可以在我的计算机属性中找到该名称。单击下一步不要更改端口，直到您在计算机中更改了端口 A 单击下一步并现在选择测试连接，如果错误是用户名和密码不正确，您可以检查您的连接是否正常，然后单击登录凭据按钮并填写正确的用户名和密码。如果它说无法到达未找到目标的计算机，则必须在防火墙中为 1521 端口添加例外或仅禁用计算机 A 上的防火墙。

Answer 4

回答by Rajkaran Mishra

First, make sure the listeneron database server (computer A) that receives client connection requests is running. To do so, run lsnrctl statuscommand.

首先，确保接收客户端连接请求的数据库服务器（计算机 A）上的侦听器正在运行。为此，请运行lsnrctl status命令。

In case, if you get TNS:no listenermessage (see below image), it means listener service is not running. To start it, run lsnrctl startcommand.

如果您收到TNS:no listener消息（见下图），则表示侦听器服务未运行。要启动它，请运行lsnrctl start命令。

Second, for database operations and connectivity from remote clients, the following executables must be added to the Windows Firewall exception list: (see image)

其次，对于远程客户端的数据库操作和连接，必须将以下可执行文件添加到 Windows 防火墙例外列表中：（见图）

Oracle_home\bin\oracle.exe - Oracle Database executable

Oracle_home\bin\oracle.exe - Oracle 数据库可执行文件

Oracle_home\bin\tnslsnr.exe - Oracle Listener

Oracle_home\bin\tnslsnr.exe - Oracle 侦听器

Finally, install oracle instant clienton client machine (computer B) and run:

最后，在客户端机器（计算机 B）上安装oracle 即时客户端并运行：

sqlplus user/password@computerA:port/XE

Answer 5

回答by Vitalie

First.It is necessary add static IP address for Computer A AND B. For example in my case Computer A (172.20.14.13) and B (172.20.14.78).

第一的。有必要为计算机 A 和 B 添加静态 IP 地址。例如，在我的情况下，计算机 A (172.20.14.13) 和 B (172.20.14.78)。

Second.In Computer A with Net Manager add for Listener new address (172.20.14.13) or manually add new record in listener.ora

第二。在带有 Net Manager 的计算机 A 中为 Listener 添加新地址 (172.20.14.13) 或在 listener.ora 中手动添加新记录

# listener.ora Network Configuration File: E:\app\user\product.2.0\dbhome_1\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = E:\app\user\product.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:E:\app\user\product.2.0\dbhome_1\bin\oraclr11.dll")
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.20.14.13)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = E:\app\user

Third.With Net Manager create Service Naming with IP address computer B (172.20.14.78) or manually add new record in tnsnames.ora

第三。使用 Net Manager 使用 IP 地址计算机 B (172.20.14.78) 创建服务命名或在 tnsnames.ora 中手动添加新记录

# tnsnames.ora Network Configuration File: E:\app\user\product.2.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.

ALINADB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = alinadb)
    )
  )

LISTENER_ALINADB =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))


LOCAL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.20.14.13)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = alinadb)
    )
  )

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.20.14.78)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

Fourth.In computer B (172.20.14.78) install win64_11gR2_client (For example it is for me in Windows 10 Pro 64 bit )

第四。在计算机 B (172.20.14.78) 中安装 win64_11gR2_client （例如它在 Windows 10 Pro 64 位中适用于我）

Five. Create with Net Configuration Assistant listener (localhost) or manually add record in listener.ora

五。使用 Net Configuration Assistant 侦听器 (localhost) 创建或在 listener.ora 中手动添加记录

# listener.ora Network Configuration File: F:\app\alinasoft\product.2.0\client_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

    LISTENER =
      (DESCRIPTION_LIST =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = myserver)(PORT = 1521))
          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
        )
      )

    ADR_BASE_LISTENER = F:\app\alinasoft

Six.With Net Manager create Service Naming with IP address computer A (172.20.14.13) or manually add new record in tnsnames.ora.

六。使用 Net Manager 使用 IP 地址计算机 A (172.20.14.13) 创建服务命名或在 tnsnames.ora 中手动添加新记录。

SERVER-DB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.20.14.13)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = alinadb)
    )
  )

Seven(Computer A - (172.20.14.13)) for database operations and connectivity from remote clients, the following executables must be added to the Windows Firewall exception list: (see image) Oracle_home\bin\oracle.exe - Oracle Database executable Oracle_home\bin\tnslsnr.exe - Oracle Listener

七（计算机 A - (172.20.14.13)）用于数据库操作和来自远程客户端的连接，必须将以下可执行文件添加到 Windows 防火墙例外列表中：（参见图片）Oracle_home\bin\oracle.exe - Oracle 数据库可执行文件 Oracle_home\ bin\tnslsnr.exe - Oracle 侦听器

EightAllow connections for port 1158 (Computer A - (172.20.14.13)) for Oracle Enterprise Manager (https://172.20.14.13:1158/em/console/logon/logon)

八允许连接端口 1158（计算机 A - (172.20.14.13)）用于 Oracle Enterprise Manager ( https://172.20.14.13:1158/em/console/logon/logon)

NinthAllow connections for port 1521 ( in and out) (Computer A - (172.20.14.17))

第九个允许端口 1521 的连接（输入和输出）（计算机 A - (172.20.14.17)）

TenthIn computer B 172.20.14.78 sqlplus /NOLOG CONNECT system/oracle@//172.20.14.13:1521/alinadb

第十在电脑B 172.20.14.78 sqlplus /NOLOG CONNECT system/oracle@//172.20.14.13:1521/alinadb

If uses Toad, in my case is

如果使用 Toad，在我的情况下是

Answer 6

回答by lakshmikandan

# . /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh  

#  sqlplus /nolog  

SQL> connect sys/password as sysdba                                           

SQL>  EXEC DBMS_XDB.SETLISTENERLOCALACCESS(FALSE);  

SQL> CONNECT sys/password@hostname:1521 as sysdba

oracle 如何远程连接Oracle 11g数据库

提问by gamosa

回答by DCookie

回答by Tony

回答by Manjit Singh

回答by Rajkaran Mishra

回答by Vitalie

回答by lakshmikandan

相关推荐

最近更新

标签

oracle 如何远程连接Oracle 11g数据库

提问by gamosa

回答by DCookie

回答by Tony

回答by Manjit Singh

回答by Rajkaran Mishra

回答by Vitalie

回答by lakshmikandan

相关推荐

在 Oracle 中将 VARCHAR2 转换为 TIMESTAMP

主键列如何在 Oracle 中具有重复值？

列标题不断出现在整个 Oracle 输出中

如果使用 Oracle 钱包，正确的 JDBC URL 语法是什么？

相关推荐

最近更新

标签