oracle 如何通过网络访问Oracle数据库？

How to access Oracle database over network?

提问by HP's 411

I am trying to accessing my Oracle database over Network. I have Google a lot and found many solutions, but those doesn't work for me.

我正在尝试通过网络访问我的 Oracle 数据库。我有很多谷歌并找到了很多解决方案，但这些对我不起作用。

The problem is that, I want to access my Oracle Database from One Computer to another Computer over my Local Network.

问题是，我想通过本地网络从一台计算机访问我的 Oracle 数据库到另一台计算机。

What I did is....

我所做的是......

1. I found a Solution to modify listener.orafile with

1. 我找到了一个修改listener.ora文件的解决方案

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbp.devProject.com)(PORT=1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbp.devProject.com)(PORT=1521)))

When I modify this file and tried to run and access the Database nothing will happens, and it throws an Error, the port number you have specified is used by another listener, so i have changed port Number but I am not able to access Database. 2. I have configure a new listener with the help of oracle's tool Net Manager, which made a Listener for me, but it does not make accessible database over Network.

当我修改这个文件并尝试运行和访问数据库时，什么都不会发生，它抛出一个错误，你指定的端口号被另一个侦听器使用，所以我更改了端口号，但我无法访问数据库。2.我在oracle的工具Net Manager的帮助下配置了一个新的监听器，它为我做了一个监听器，但它不能通过网络访问数据库。

Here Some required results.

这里有一些需要的结果。

1. lsnrctl status: Listener using listener name LISTENER has already been started.
2. Connection String: jdbc:oracle:thin:@dbp.devProject.com:4541:myDB
3. Database Version:
   1. Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
   2. PL/SQL Release 11.2.0.1.0 - Production
   3. CORE 11.2.0.1.0 Production
   4. TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
   5. NLSRTL Version 11.2.0.1.0 - Production

1. lsnrctl 状态：使用侦听器名称 LISTENER 的侦听器已启动。
2. 连接字符串：jdbc:oracle:thin:@dbp.devProject.com:4541:myDB
3. 数据库版本：
   1. Oracle 数据库 11g 企业版 11.2.0.1.0 版 - 64 位生产
   2. PL/SQL 版本 11.2.0.1.0 - 生产
   3. 核心 11.2.0.1.0 生产
   4. 适用于 64 位 Windows 的 TNS：版本 11.2.0.1.0 - 生产
   5. NLSRTL 版本 11.2.0.1.0 - 生产

status shows me this.

状态向我展示了这一点。

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 : 14-MAY-2015 11:58:05

Uptime : 0 days 2 hr. 29 min. 50 sec

Trace Level : off

Security : ON: Local OS Authentication

SNMP : OFF

Listener Parameter File : product\11.2.0\dbhome_1\network\admin\listener.ora

Listener Log File : \listener\alert\log.xml

Listening Endpoints Summary...

连接到 (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))

听者的状态

别名：听者

版本：适用于 64 位 Windows 的 TNSLSNR：版本 11.2.0.1.0 - 生产

开始日期：2015 年 5 月 14 日 11:58:05

正常运行时间：0 天 2 小时。29 分钟 50 秒

跟踪级别：关闭

安全性：开：本地操作系统身份验证

SNMP : 关闭

监听器参数文件：product\11.2.0\dbhome_1\network\admin\listener.ora

侦听器日志文件：\listener\alert\log.xml

收听端点总结...

1. (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\.\pipe\EXTPROC1521ipc)))
2. (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))

Someone please Help me. Thanks for your Help..

有人请帮助我。感谢您的帮助..

回答by HP's 411

Whenever you are sufering with this kind of errors while creating remote access for you database, do following things before.

每当您在为您的数据库创建远程访问时遇到此类错误时，请先执行以下操作。

1. Create a Listener using Net Managertool of Oracle or create it Manually.
2. After generating Listener, check out "listener.ora" [Located : ORACLE_INSTALLED_DIR\product\11.2.0\dbhome_1\NETWORK\ADMIN]file, for your entry. (is there any Entry for the Listener which you have created just before. If you found your Entry than and than do next steps otherwise do it from FIRST step.)
3. If you found the Entry for your Listener, Open Command Prompt and fire command lsnrctl status. This will give you all currently running Listeners List, find out the one which you created just before.
4. Check out the Host Nameand Port Numberfor your Listener.
5. Generate the Connection String as per the details you found in lsnrctl status
6. Connect with Database, and You can access the Database from anywhere in your Network.

1. 使用Oracle 的Net Manager工具创建侦听器或手动创建。
2. 生成 Listener 后，查看“listener.ora”[位于：ORACLE_INSTALLED_DIR\product\11.2.0\dbhome_1\NETWORK\ADMIN]文件，以供您输入。（是否有您之前为侦听器创建的任何条目。如果您找到了您的条目，然后再执行下一步，否则从第一步开始。）
3. 如果您找到监听器的条目，请打开命令提示符并触发命令lsnrctl status。这将为您提供所有当前正在运行的监听器列表，找出您之前创建的监听器列表。
4. 为您的监听器检查Host Name和Port Number。
5. 根据您在中找到的详细信息生成连接字符串 lsnrctl status
6. 与数据库连接，您可以从网络中的任何位置访问数据库。

Enjoy the Things, Some challenges needs Afforts, Once you fall back from it, it will be never solved.

享受事物，有些挑战需要努力，一旦退缩，就永远无法解决。

I am really thankful to those all Guys who answered and Commented to this Question, this all Material made my Challenge easy..

我真的很感谢所有回答和评论这个问题的人，这些材料让我的挑战变得容易..

Thank You @Alex, @ Lalit, @TenG and @S.Krishna Thank You so Much Guys.

谢谢@Alex、@Lalit、@TenG 和@S.Krishna 非常感谢你们。

回答by Kaspa

If your host name and port number is listener then You could try this option by enabling port number in firewall settings.

如果您的主机名和端口号是侦听器，那么您可以通过在防火墙设置中启用端口号来尝试此选项。

Opening Ports in Windows Firewall

在 Windows 防火墙中打开端口

• Open the Control Panel. ...
• Open Windows Firewall. ...
• Click the Advanced Settings link. ...
• Select "Inbound Rules". ...
• Create "New Rule" under "Inbound Rules". ...
• Select "Port" from the first screen of the wizard. ...
• Choose TCP or UDP. ...
• Enter in the port range. ...
• Finish the wizard.

• 打开控制面板。...
• 打开 Windows 防火墙。...
• 单击高级设置链接。...
• 选择“入站规则”。...
• 在“入站规则”下创建“新规则”。...
• 从向导的第一个屏幕中选择“端口”。...
• 选择 TCP 或 UDP。...
• 输入端口范围。...
• 完成向导。

回答by S.Krishna

Create an entry in TNSNAMES.ORA file on the system from which you are trying to connect to the database. (TNSNAMES.ORA is located in ORACLE_INSTALLATION_PATH/Network/Admin/ folder)

在您尝试连接到数据库的系统上的 TNSNAMES.ORA 文件中创建一个条目。（TNSNAMES.ORA 位于 ORACLE_INSTALLATION_PATH/Network/Admin/ 文件夹中）

Sample entry:

示例条目：

DB_HOST_NAME =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = DB_HOST_NAME)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = XE)

    )

  )

If you have more than one Oracle clients installed, you might need to update this in all those TNSNAMES.ORA

如果您安装了多个 Oracle 客户端，您可能需要在所有这些 TNSNAMES.ORA 中更新它