oracle数据库配置的一些注意事项

本文详细介绍了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位的进行安装。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值