环境：Windows 7 + Oracle 11g r2 + Navicat premium 9.15

来源：互联网发布：ubuntu cuda caffe 编辑：程序博客网时间：2024/06/11 18:51

环境：Windows 7 + Oracle 11g r2 + Navicat premium 9.15
Oracle基本是默认安装，安装过程没出现过任何异常
Oracle开启了两个服务：OracleServiceORCL和OracleOraDb11g_home1TNSListener
Navicat一直都连接不上本地的Oracle
网上搜了一大堆资料，大都是改那3个配置文件的

最终的问题是：
Navicat主机那里如果填本机IP地址（192.168.18.8），错误代码是：ORA-12541：TNS：no listener
如果填localhost，错误代码是：ORA-28547：connection to server failed，probable Oracle Net admin error
tnsping localhost 是通的，tnsping 192.168.18.8 则出错 TNS-12541: TNS: 无监听程序

以下是listener.ora文件：
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\Oracle\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\Oracle\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = C:\Oracle

解决方案：

oci.dll的版本不对
    从 http://www.oracle.com 下载instantclient
    里面很多，根据你的版本来下，我下载的是instantclient_11_2。
    在navicat的工具-选项-其他-OCI ,选择刚下的client中的 oci.dll
    设置环境变量：
      1)在环境变量PATH中开头增加C:\Instantclient10_2;
      2)增加用户环境变量SQLPATH，值为C:\Instantclient10_2
      3)增加用户环境变量NLS_LANG，值为AMERICAN_AMERICA.UTF8
    选择 Basic 连接，就大概能连接上了

把Oracle安装目录下oci.dll，替换掉Navicat目录下的oci.dll文件，就解决了

另外需要注意下面的内容：（http://wiki.navicat.com/wiki/index.php/Instant_client_required（

Instant Client package is required for Basic and TNS connection type. To download Instant Client package (Instant Client Package - Basic), please go tohttp://www.oracle.com/technetwork/database/features/instant-client/index-097480.html

Note: Version 9 or above, instant client is bundled in Navicat.

Connection Type

Basic
In Basic mode, Navicat Oracle connects to Oracle through the Oracle Call Interface (OCI). OCI is an application programming interface that allows an application developer to use a third-generation language's native procedure or function calls to access the Oracle database server and control all phases of SQL statement execution. OCI is a library of standard database access and retrieval functions in the form of a dynamic-link library.
TNS
In TNS mode, Navicat Oracle connects to Oracle server using an alias entry from a tnsnames.ora file.

Windows

Installation Instructions

Note: Version 10 or below, Navicat only support 32-bit instant client.

Download the appropriate Instant Client packages for your platform. All installations REQUIRE the Basic or Basic Lite package.

Note:

For Oracle 9i or above, you need Instant Client 11 or below
For Oracle 8 and 8i server, you need Instant Client 10 or below

0 0