局域网内访问ORACLE11G 数据库和MYSQL 数据库（详细讲述经过）（一）-阿里云开发者社区

局域网内访问ORACLE11G 数据库和MYSQL 数据库（详细讲述经过）（一）

2023-07-09 321

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

本文涉及的产品

云数据库 RDS MySQL，集群系列 2核4GB

RDS MySQL Serverless 基础系列，0.5-2RCU 50GB

云数据库 RDS MySQL，高可用系列 2核4GB

简介： 局域网内访问ORACLE11G 数据库和MYSQL 数据库（详细讲述经过）（一）

1.Oracle解决思路

1.我们首先找到oracle的配置文件地址

....\lenovo\product\10.2.0\db_1\NETWORK\ADMIN

listener.ora：配置监听

tnsnames.ora：配置本地访问及其远程访问

sqlnet.ora：配置访问级别（个人理解）

备份内容：(如果不小心修改错了可复制，但地址还是你们本地的最好自己保存下)

listener.ora

# listener.ora Network Configuration File: D:\app\lenovo\product\11.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 = D:\app\lenovo\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:D:\app\lenovo\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
  )
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
  )
ADR_BASE_LISTENER = D:\app\lenovo

tnsnames.ora

# tnsnames.ora Network Configuration File: D:\app\lenovo\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )
ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

sqlnet.ora

# sqlnet.ora Network Configuration File: D:\app\lenovo\product\11.2.0\dbhome_1\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.
# This file is actually generated by netca. But if customers choose to 
# install "Software Only", this file wont exist and without the native 
# authentication, they will not be able to connect to the database on NT.
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

2.配置相关文件

（切记操作之前先ping 或telenet 是否通不通是肯定访问不到）

操作之前记得先把监听关掉：

我们要把ip配置为本地的ip

win+R 输入 cmd

进入之后输入ipconfig

拿到IP之后下面我们就去配置相关的文件：

# listener.ora Network Configuration File: D:\app\lenovo\product\11.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 = D:\app\lenovo\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:D:\app\lenovo\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
  )
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
   (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.169)(PORT = 1521))
    )
  )
ADR_BASE_LISTENER = D:\app\lenovo

网上很多基本都是操作到一步就结束了我本地是不行的；再网上找了很多相关案例都不太行；

还要再此处加入ip的地址（很重要的）

# tnsnames.ora Network Configuration File: D:\app\lenovo\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )
ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.169)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

保存之后记得

打开监听：

尝试连接之后还是不行报下面的错误：

这个问题就很简单了：

我们打开

sqlnet.ora：配置访问级别（个人理解）

SQLNET.AUTHENTICATION_SERVICES= (NONE)

# sqlnet.ora Network Configuration File: D:\app\lenovo\product\11.2.0\dbhome_1\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.
# This file is actually generated by netca. But if customers choose to 
# install "Software Only", this file wont exist and without the native 
# authentication, they will not be able to connect to the database on NT.
#SQLNET.AUTHENTICATION_SERVICES= (NTS) 高级权限注释掉
SQLNET.AUTHENTICATION_SERVICES= (NONE)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

记得重启监听：上面说了按照顺序

这样远程就可以访问了；本地远程都可以访问 192.168.0.169了

有的访问者需要配置：

tnsnames.ora

名字随意=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 你的ip)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = 服务名)
    )
  )

# tnsnames.ora Network Configuration File: D:\app\lenovo\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )
ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.169)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )
  TEST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ip)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = 服务名)
    )
  )

3. 踩坑记录

3.1 无监听，监听异常（除了恢复配置文件默认，还可以如下操作）

这样就可以了，他会多出几个带今天日期的bak文件；

4.扩展知识

1.文件内容详解

listener.ora文件

listener.ora是服务器端用的，oracle监听程序，就是读的这个文件，里面有oracle服务器端的socket监听地址和端口（

如果要想局域网中的其他人，能够访问我本地的oracle，要把我本机的地址写进去，如10.11.19.19

文件地址：\oracle\product\10.2.0\db_1\NETWORK\ADMIN

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

tnsnames.ora文件

tnsnames.ora是记录客户端访问数据库的本地配置：

客户端是通过tnsnames.ora来识别连接服务器的

如果你没有添加tnsnames.ora,那么你就连接不上服务器的。。

文件地址：\oracle\product\10.2.0\db_1\NETWORK\ADMIN

ORCL =
(DESCRIPTION =
  (ADDRESS_LIST =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 主机地址)(PORT = 端口))
  (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = orcl)
  )
  )
)

其中，每个部分的解释如下所示：

l. PROTOCOL：客户端与服务器端通讯的协议，一般为TCP，该内容一般不用改。

2. HOST：数据库所在的机器的主机名或IP地址。不管用主机名还是IP地址，在客户端一定要用ping命令ping通数据库所在的机器，否则需要在hosts文件中加入数据库所在的机器的主机名和IP地址的对应关系。

3. PORT：数据库监听器的端口，可以查看服务器端的listener.ora文件或在数据库服务器中通过lsnrctl status [listener name]命令来查看。一般为1521端口。

4. SERVICE_NAME：在数据库中使用“SHOW PARAMETER SERVICE_NAME”命令查看，一般情况下和DBNAME相同

2.发现一个好玩的把地址换成电脑的名字也可以的访问（Oracle 远程访问数据库TNS: 无监听程序解决方法）

好像把locahost 换成0.0.0.0. 或者不写都是ok 这些本人没有尝试

1.找到自己电脑的名字；

把我下面红色圈起来的换成计算机的名字；然后重启；本地访问是OK的（没问题就不要乱玩了）

局域网内访问ORACLE11G 数据库和MYSQL 数据库（详细讲述经过）（一）

1.Oracle解决思路

1.我们首先找到oracle的配置文件地址

2.配置相关文件

3. 踩坑记录

4.扩展知识

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

局域网内访问ORACLE11G 数据库和MYSQL 数据库（详细讲述经过）（一）

1.Oracle解决思路

1.我们首先找到oracle的配置文件地址

2.配置相关文件

3. 踩坑记录

4.扩展知识

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像