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

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

环境说明

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

零星想起来

中间用了

tnsping 10.60.36.91:1521/orcl

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