解决异类ODBC连接问题的最佳实践是什么？

Question

我正在尝试建立一个从Oracle21cXE数据库到进度OpenEdge 11.7数据库的异构ODBC连接。我已经在Windows2019服务器上安装了一个32位ODBC驱动程序，21c XE DB驻留在该服务器上。我已经建立了一个ODBC系统DSN，并对它进行了测试和工作。

我在C:\app\Administrator\product\21c\homes\OraDB21Home1\hs\admin文件夹中创建了一个initTIMEDATA.ora文件。

它包含；

HS_FDS_CONNECT_INFO = TimeData
HS_FDS_TRACE_LEVEL = 4
HS_FDS_TRACE_FILE_NAME = TimeData.trc

其中，TimeData与ODBC系统DSN名称匹配。

对listener.ora加法进行了改进；

  (SID_DESC=
     (SID_NAME=TimeData)
     (ORACLE_HOME=C:\app\Administrator\product\21c\dbhomeXE)
     (PROGRAM=C:\app\Administrator\product\21c\dbhomeXE\bin\dg4odbc)
     (ENVS = LD_LIBRARY_PATH=C:\Progressx86\OpenEdge\lib:C:\app\Administrator\product\21c\dbhomeXE\bin)
    )

我修改了我的tnsnames.ora文件添加；

TIMEDATA=
  (DESCRIPTION=
   (ADDRESS=
     (PROTOCOL=TCP)
     (HOST=10.254.10.41)
     (PORT=4621)
   )
   (CONNECT_DATA=
     (SID=TimeData))
   (HS=OK))

我创建了一个类似DBLink的；

CREATE PUBLIC DATABASE LINK time_data_link CONNECT TO "sqluser" IDENTIFIED BY "myPassword" USING 'TIMEDATA';

重新启动所有内容之后(DB，侦听器.)听者表现；

Service "TimeData" has 1 instance(s).
 Instance "TimeData", status UNKNOWN, has 1 handler(s) for this service...

当我试图运行一个查询；

SELECT * FROM employee_view@time_data_link;

我明白了；

ORA-28545: error diagnosed by Net8 when connecting to an agent
Unable to retrieve text of NETWORK/NCR message 65535
ORA-02063: preceding 2 lines from TIME_DATA_LINK
28545. 0000 - "error diagnosed by Net8 when connecting to an agent"
*Cause:  An attempt to call an external procedure or to issue SQL
      to a non-Oracle system on a Heterogeneous Services database link
      failed at connection initialization. The error diagnosed
      by Net8 NCR software is reported separately.
*Action:  Refer to the Net8 NCRO error message. If this isn't clear,
      check connection administrative setup in tnsnames.ora
      and listener.ora for the service associated with the
      Heterogeneous Services database link being used, or with
      'extproc_connection_data' for an external procedure call.
Error at Line: 1 Column: 29

我在listener.log中没有看到错误，我找不到TimeData.trc。在查询DB跟踪文件时，我发现了一个与多个副本相关的跟踪文件；

HS connect: nscall returned status ffffffff (-1)
  ns main err code: 12569
  ns (2) err code: 0
  nt main err code: 0
  nt (2) err code: 0
  nt OS  err code: 0

*** 2022-04-03T17:01:07.758909-04:00 (XEPDB1(3))
HS: Unable to establish RPC connection to HS Agent...
HS: ... Agent SID = (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.254.10.41)(PORT=4621))(CONNECT_DATA=(SID=TimeData))), NCR error = 65535 Unable to retrieve text of NETWORK/NCR message 65535

我已经完成了我的故障排除技能，并希望其他人可以指出我的正确方向。

Answer 1

我有两个问题。

1. 在本地主机上运行指向Oracle服务器所需的TNSNAMES.ORA文件(在我的例子中)。一旦我更改了它，错误就会更改为

。

错误；

ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application {IM014}

新TNSNAMES.ORA；

TIMEDATA=
   (DESCRIPTION=
      (ADDRESS=
         (PROTOCOL=TCP)
         (HOST=localhost)
         (PORT=1521)
      )
      (CONNECT_DATA=
         (SID=TimeData))
      (HS=OK))

1. 一看到这个错误，很明显我需要64位版本的ODBC驱动程序，这是我获得的.

。

在做出这些改变之后，一切似乎都正常了。