oracle数据库配置的一些注意事项_oracle 配置host-优快云博客

本文链接：https://blog.youkuaiyun.com/BeginToTiger/article/details/121270904

本文详细介绍了Oracle数据库的listener.ora、tnsnames.ora和sqlnet.ora文件配置，包括SID_LIST_LISTENER、SID_DESC的设置以及解决远程连接和PL/SQL Developer连接报错的问题。同时，提到了32位与64位客户端与服务器端的兼容性问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、listener.ora文件配置

# listener.ora Network Configuration File: D:\ORACLE\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:\ORACLE\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:D:\ORACLE\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
    
    #sid_desc为服务连接的描述名
    #1.如果不设置，使用plsql连接时会报ora-12514:无法识别连接描述符
    #2.如果不设置，在使用sqlplus连接本机时，如：sqlplus system/123456@testorcl 时，也会报ora-12514错误
    #3.如果不设置，在连接本机时，只能使用：sqlplus system/123456 连接到默认的数据库（有多个数据库无法指定数据库）
    #4.有的电脑或者版本可能不用配置也行
    
    (SID_DESC =
      (SID_NAME = ORCL)
      (ORACLE_HOME = D:\ORACLE\product\11.2.0\dbhome_1)
    )
    (SID_DESC =
      (SID_NAME = TESTORCL)
      (ORACLE_HOME = D:\ORACLE\product\11.2.0\dbhome_1)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    
    #下面的host如果只是本机使用可设置为localhost或127.0.0.1，如果提供给局域网的其他电脑远程连接
    #则将host设置成电脑的ip地址
    
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.98)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = D:\ORACLE

二、tnsnames.ora文件配置

# tnsnames.ora Network Configuration File: D:\ORACLE\product\11.2.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_TESTORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.98)(PORT = 1521))


LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.98)(PORT = 1521))


ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(Key = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

TESTORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.98)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = testorcl)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.98)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

三、sql.net文件配置

# sqlnet.ora Network Configuration File: D:\ORACLE\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.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

ADR_BASE = D:\ORACLE

四、主机同时安装oracle服务器端与plsql客户端的问题

windows10系统上安装了oracle服务器端64位，在本机dos窗口使用sqlplus命令能正常进入数据库。安装plsql后，无法使用plsql连接到数据库，报ora-12557错误。
首先查看plsql是32位或64位的，如果是32位的，会与本机的64位服务器端冲突，需要将plsql更换成64位的进行安装。