由于课程项目需要,要远程连接数据库,弄了两天,终于搞定,写一下心路历程。
环境说明
- Windows10
- Oracle 12c
问题说明
用 Sql Developer 和 Java代码两个同时测试,出现了好几个问题
-
ora-12541:TNS:无监听程序
-
ora 12514 tns listener does not currently know of service requested in connect descriptor
-
oracle数据库无法连接 The Network Adapter could not establish
-
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
反正就很烦,这几个问题来回出现
解决
Google上搜,最终修改了2个代码文件,他们的位置都在
E:\app\Oracle\product\12.1.0\dbhome_1\NETWORK\ADMIN 这个目录下
修改后的2个文件如下:
listener.ora
# listener.ora Network Configuration File: E:\app\Oracle\product\12.1.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\Oracle\product\12.1.0\dbhome_1)
# (PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:E:\app\Oracle\product\12.1.0\dbhome_1\bin\oraclr12.dll")
)
###########################################################
(SID_DESC =
(SID_NAME = orcl)
(ORACLE_HOME = E:\app\Oracle\product\12.1.0\dbhome_1)
# (PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:E:\app\Oracle\product\12.1.0\dbhome_1\bin\oraclr12.dll")
)
)
############################################################
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.60.36.91)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
注意两行“#” 之间的代码为新添加,为了解决上述问题4
还有Listener中的Host记得改
tnsnames.ora
# tnsnames.ora Network Configuration File: E:\app\Oracle\product\12.1.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.microdone.cn)
# )
# )
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.60.36.91)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.microdone.cn)
)
)
被“#”注释掉的几行代码是原本的支持本地连接的代码,下面的一段是重新改过的。
还有问题
- 虽然问题好像解决了,但是对于 Oracle中的 服务、 SID、 orcl和数据库到底哪个属于哪个范畴还不是很懂。
- 以后搜东西的时候想达到什么目的就直接去搜解决方法,自己去想当然的试,可能会走弯路。
比如说这次,就直接搜Oracle远程部署就行了。。
零星想起来
中间用了
tnsping 10.60.36.91:1521/orcl
这个命令测试了下是不是能连上,然后发现了问题一