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

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

2023-07-09 294

版权

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

本文涉及的产品

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

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

RDS MySQL Serverless 高可用系列，价值2615元额度，1个月

简介： 局域网内访问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)
  )
  )
)