数据库和MYSQL 数据库(详细讲述经过)"/>
局域网内访问ORACLE11G 数据库和MYSQL 数据库(详细讲述经过)
公司要求局域网访问oralce 数据库自己之前也没有经验看了网上很多资料也试了很多 也都不行后面经过不断尝试 终于可以了 把自己的解决问题思路及其方法分享下
ORACLE与MYSQL局域网连接
- 1.Oracle解决思路
- 1.我们首先找到oracle的配置文件地址
- 2.配置相关文件
- 3. 踩坑记录
- 3.1 无监听,监听异常(除了恢复配置文件默认,还可以如下操作)
- 4.扩展知识
- 1.文件内容详解
- 2.发现一个好玩的把地址换成电脑的名字也可以的访问 (Oracle 远程访问数据库TNS: 无监听程序解决方法)
- 2.MYSQL解决思路
- 2.1前置条件:
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的(没问题就不要乱玩了)
2.MYSQL解决思路
2.1前置条件:
电脑一定要关闭防火墙和杀毒软件。否则无法ping超时;
A电脑关闭防火墙之后,输入win+R键输入cmd打开dos面板,输入ipconfig查看当前A电脑的ip;
B、C电脑输入win+R键输入cmd打开dos面板,输入ping A的ip 查看是否能ping通
- 在A服务器按win+R键输入cmd打开dos面板,cd进入MySQL的安装bin目录里;
- 输入mysql -u root -p ,然后输入安装mysql时设置的密码,进入mysql;
- 输入use mysql进入mysql数据库;
- 使用grant命令进行授权,(专有账户)
GRANT ALL PRIVILEGES ON zhongdian.* TO 'root'@192.168.0.117 IDENTIFIED BY 'root';
all privileges:表示所有数据库权限;
on *.*:表示在所有表,视图,函数等等,可以是具体的数据库下的某个表,某个视图。(第一个*可以设置特定的允许访问数据库(DB))
'root'@ ip :表示允许root用户从特定的IP主机连接mysql服务器。
identified by '123':表示root用户从ip主机远程连接的密码。
with grant option:表示root从ip主机连接后可以将权限再次使用grant语句将权限授予其他用户。(可不写)
zhongdian.* 我指定的只能访问zhongdian内的全部数据;'root'@192.168.0.117 IDENTIFIED BY 'root'; 账号密码为root Ip为192.168.0.117
- 输入
flush privileges
,进行权限的刷新。
操作此步我本地可以通过ip可以进行访问了,但是局域网内其他人是不行的故进行下面的操作;
-
设置专有账户示例,如下图所示:
-
如果想设置为通用账户,则输入
GRANT ALL PRIVILEGES ON zhongdian.* TO 'root'@"%" IDENTIFIED BY 'root' with grant option;
- 和设置专有账户一样,输入
flush privileges
,进行权限的刷新。
这样局域网其他的电脑就可以访问到你本地了;
哪里有不对的地方需要修改的轻大家多多指教,谢谢;
虚心接受批评;
更多推荐
局域网内访问ORACLE11G 数据库和MYSQL 数据库(详细讲述经过)
发布评论