Oracle本地连接与远程连接配置

时间:2021-09-25 08:18:39

由于课程项目需要,要远程连接数据库,弄了两天,终于搞定,写一下心路历程。

环境说明

  • Windows10
  • Oracle 12c

问题说明

用 Sql Developer 和 Java代码两个同时测试,出现了好几个问题

  1. ora-12541:TNS:无监听程序

  2. ora 12514 tns listener does not currently know of service requested in connect descriptor

  3. oracle数据库无法连接 The Network Adapter could not establish

  4. 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中的 服务SIDorcl数据库到底哪个属于哪个范畴还不是很懂。
  • 以后搜东西的时候想达到什么目的就直接去搜解决方法,自己去想当然的试,可能会走弯路。
    比如说这次,就直接搜Oracle远程部署就行了。。

零星想起来

中间用了

tnsping 10.60.36.91:1521/orcl

这个命令测试了下是不是能连上,然后发现了问题一