Oracle通过Gateways透明网关连接到SQL Server数据库(dblink)
Tags: GatewaysMySQLOracleSQL Server透明网关
简介
在企业里,通常可能有多种数据源,并且他们是异构的。所谓的异构就是说他们是不同的产品,例如: Oracle Database, MS SQL Server, IBM DB2, Sybase ASE, MySQL, Postgre SQL, Excel, XML, Txt或者CSV等用于存放数据的产品或者文件。
oracle 透明网关(Transparent gateways)是Oracle连接异构数据库提供的一种技术。通过Gateway,可以在Oracle里透明的访问其他不同的数据库,如SQL Server, DB2, Sybase等等,就像远程Oracle数据库一样。因此透明网关也是Oracle数据仓库和数据迁移中的一个重要组成部分。透明网关的体系结构也很简单,在ORACLE和SQL SERVER之间使用ORACLE透明网关服务器实现互连互通,其中透明网关服务器可以与ORACLE或SQL SERVER数据库在同一台主机上,也可以是在独立的一台主机上。
配置后的sql查询的处理流程如下:
下载
安装包和数据库的安装包在一块,最新的软件下载:https://www.oracle.com/database/technologies/oracle-database-software-downloads.html
历史版本下载:https://edelivery.oracle.com
历史版本下载需要登录Oracle账号,这个不需要mos即可,自己注册一个就行。
然后搜索“12.2.0.1 gateway”,
然后点击找到的项目,加入下载目录,然后点击Continue:
由于安装网关需要有数据库作为基础,所以这里带出来2个软件,但是我DB已经安装过了,所以,这里只下载Gateways就行:
点击Download会下载如下的下载管理器:
打开即可:
下一步会自动下载:
还能看到下载性能:
还能设置限速:
等待下载完成即可。
安装
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | useradd -g oinstall -G oinstall,dba gateway && echo "lhr" | passwd gateway --stdin cat >> /home/gateway/.bash_profile <<"EOF" export ORACLE_HOME=/u01/app/gateway export TNS_ADMIN=$ORACLE_HOME/dg4msql/admin export PATH=$ORACLE_HOME/bin:$PATH export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH EOF echo xfce4-session > /home/gateway/.xsession chmod +x /home/gateway/.xsession service xrdp restart unzip LINUX.X64_12.2.0.1_gateways.zip cd gateways |
图形界面安装
需要配置网关的自己的监听,注意端口不能重复即可:
执行root.sh脚本:
静默安装
1 2 3 4 5 6 7 8 9 10 11 12 | ./runInstaller -silent -ignorePrereqFailure -ignoreSysPrereqs -showProgress -waitforcompletion \ UNIX_GROUP_NAME=oinstall \ INVENTORY_LOCATION=/u01/app/oraInventory \ ORACLE_BASE=/u01/app/ \ ORACLE_HOME=/u01/app/gateway \ oracle.install.tg.customComponents=oracle.rdbms.tg4msql:12.2.0.1.0,oracle.rdbms.hsodbc:12.2.0.1.0 \ oracle.install.tg.msqlConStr=192.168.66.236,1433,MSSQLSERVER,LHRDB -- root用户 /u01/app/gateway/root.sh netca -silent -responsefile $ORACLE_HOME/assistants/netca/netca.rsp -instype custom -listener LISTENER -lisport 1525 |
执行过程:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 | [gateway@lhrora1221 gateways]$ ./runInstaller -silent -ignorePrereqFailure -ignoreSysPrereqs -showProgress -waitforcompletion \ > UNIX_GROUP_NAME=oinstall \ > INVENTORY_LOCATION=/u01/app/oraInventory \ > ORACLE_BASE=/u01/app/ \ > ORACLE_HOME=/u01/app/gateway \ > oracle.install.tg.customComponents=oracle.rdbms.tg4msql:12.2.0.1.0,oracle.rdbms.hsodbc:12.2.0.1.0 \ > oracle.install.tg.msqlConStr=192.168.66.236,1433,MSSQLSERVER,LHRDB Starting Oracle Universal Installer... Checking Temp space: must be greater than 415 MB. Actual 368199 MB Passed Checking swap space: must be greater than 150 MB. Actual 10546 MB Passed Preparing to launch Oracle Universal Installer from /tmp/OraInstall2022-03-05_03-52-10PM. Please wait ...[WARNING] [INS-32056] The specified Oracle Base contains the existing Central Inventory location: /u01/app/oraInventory. ACTION: Oracle recommends that the Central Inventory location is outside the Oracle Base directory. Specify a different location for the Oracle Base. [WARNING] [INS-13014] Target environment does not meet some optional requirements. CAUSE: Some of the optional prerequisites are not met. See logs for details. /u01/app/oraInventory/logs/installActions2022-03-05_03-52-10PM.log ACTION: Identify the list of failed prerequisite checks from the log: /u01/app/oraInventory/logs/installActions2022-03-05_03-52-10PM.log. Then either from the log file or from installation manual find the appropriate configuration to meet the prerequisites and fix it manually. You can find the log of this install session at: /u01/app/oraInventory/logs/installActions2022-03-05_03-52-10PM.log Prepare in progress. .................................................. 8% Done. Prepare successful. Copy files in progress. .................................................. 14% Done. .................................................. 20% Done. .................................................. 25% Done. .................................................. 30% Done. .................................................. 36% Done. .................................................. 42% Done. .................................................. 47% Done. .................................................. 52% Done. .................................................. 57% Done. .................................................. 64% Done. .................................................. 69% Done. .................................................. 74% Done. .................... Copy files successful. Link binaries in progress. Link binaries successful. Setup files in progress. Setup files successful. Setup Inventory in progress. Setup Inventory successful. Finish Setup successful. The installation of Oracle Database Gateways was successful. Please check '/u01/app/oraInventory/logs/silentInstall2022-03-05_03-52-10PM.log' for more details. Oracle Gateway Configuration in progress. Oracle Net Configuration Assistant in progress. .................................................. 95% Done. Oracle Net Configuration Assistant failed. [WARNING] [INS-32091] Software installation was successful. But some configuration assistants failed, were cancelled or skipped. ACTION: Refer to the logs or contact Oracle Support Services. [gateway@lhrora1221 admin]$ netca -silent -responsefile $ORACLE_HOME/assistants/netca/netca.rsp -instype custom -listener LISTENER -lisport 1525 Parsing command line arguments: Parameter "silent" = true Parameter "responsefile" = /u01/app/gateway/assistants/netca/netca.rsp Parameter "instype" = custom Parameter "listener" = LISTENER Parameter "lisport" = 1525 Done parsing command line arguments. Oracle Net Services Configuration: Configuring Listener:LISTENER Listener configuration complete. Oracle Net Listener Startup: Running Listener Control: /u01/app/gateway/bin/lsnrctl start LISTENER Listener Control complete. Listener started successfully. Profile configuration complete. Oracle Net Services configuration successful. The exit code is 0 [test@lhrora1221 admin]$ lsnrctl status LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 05-MAR-2022 16:02:02 Copyright (c) 1991, 2016, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=lhrora1221)(PORT=1525))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production Start Date 05-MAR-2022 16:01:56 Uptime 0 days 0 hr. 0 min. 6 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/gateway/dg4msql/admin/listener.ora Listener Log File /u01/app/gateway/network/log/listener.log Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=lhrora1221)(PORT=1525))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1525))) The listener supports no services The command completed successfully |
配置
Oracle连接到SQL Server
配置gateway的监听
透明网关目录文件夹$TNS_ADMIN=/u01/app/gateway/dg4msql/admin
其中listener.ora便是监听文件,配置他即可。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 | [gateway@lhrora1221 ~]$ cd $TNS_ADMIN [gateway@lhrora1221 admin]$ echo $TNS_ADMIN /u01/app/gateway/dg4msql/admin [gateway@lhrora1221 admin]$ ll total 40 -rw-r--r-- 1 gateway oinstall 11120 Dec 18 2013 dg4msql_cvw.sql -rw-r--r-- 1 gateway oinstall 746 Jun 9 2007 dg4msql_tx.sql -rw-r--r-- 1 gateway oinstall 365 Mar 5 16:08 initdg4msql.ora -rw-r--r-- 1 gateway oinstall 315 Mar 5 16:18 listener.ora -rw-r--r-- 1 gateway oinstall 388 Mar 5 16:08 listener.ora.sample -rw-r--r-- 1 gateway oinstall 179 Mar 5 16:18 sqlnet2203058AM1818.bak -rw-r--r-- 1 gateway oinstall 179 Mar 5 16:18 sqlnet.ora -rw-r--r-- 1 gateway oinstall 244 Mar 5 16:08 tnsnames.ora.sample -- vi listener.ora LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = lhrora1221)(PORT = 1525)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1525)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = dg4msql) (PROGRAM = dg4msql) (ORACLE_HOME = /u01/app/gateway) ) ) # (PROGRAM = dg4msql) 是固定的,只需要修改 SID_NAME # 注意,配置了透明网关的监听后,需要重启一下监听服务。 [gateway@lhrora1221 admin]$ lsnrctl status LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 05-MAR-2022 16:55:45 Copyright (c) 1991, 2016, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=lhrora1221)(PORT=1525))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production Start Date 05-MAR-2022 16:41:10 Uptime 0 days 0 hr. 14 min. 35 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/gateway/dg4msql/admin/listener.ora Listener Log File /u01/app/diag/tnslsnr/lhrora1221/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=lhrora1221)(PORT=1525))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1525))) Services Summary... Service "dg4msql" has 1 instance(s). Instance "dg4msql", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully |
配置gateway数据库参数
透明网关目录文件夹$TNS_ADMIN=/u01/app/gateway/dg4msql/admin
Admin文件夹下默认存在安装透明网关时的数据库文件initdg4msql.ora。
数据文件命名规则:init + SID(SID就是上一步中配置的SID_NAME),需要多个连接就建多个文件即可。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | [gateway@lhrora1221 admin]$ more initdg4msql.ora # This is a customized agent init file that contains the HS parameters # that are needed for the Database Gateway for Microsoft SQL Server # # HS init parameters # HS_FDS_CONNECT_INFO=[192.168.66.236]:1433//LHRDB # alternate connect format is hostname/serverinstance/databasename HS_FDS_TRACE_LEVEL=OFF HS_FDS_RECOVERY_ACCOUNT=RECOVER HS_FDS_RECOVERY_PWD=RECOVER |
只要修改HS_FDS_CONNECT_INFO参数就可以了。格式是:[hostname:port]/serverinstance/databasename
,其中hostname是机器名称或IP,PORT是SQL Server的端口号,SQL Server2005默认为1433.serverinstance是SQL Server的实例名,一般空着就行。Databasename是SQL Server的数据库名。因为我们在安装过程中指定了主机名和数据库名,这里已经有信息了。
配置oracle服务器的服务名配置文件tnsnames.ora
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | cd /u01/app/oracle/product/12.2.0.1/dbhome_1/network/admin cat >> tnsnames.ora <<"EOF" DG4MSQL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1525)) (CONNECT_DATA = (SERVICE_NAME = dg4msql) ) (HS = OK) ) EOF [oracle@lhrora1221 admin]$ tnsping DG4MSQL TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 05-MAR-2022 16:47:38 Copyright (c) 1997, 2016, Oracle. All rights reserved. Used parameter files: /u01/app/oracle/product/12.2.0.1/dbhome_1/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1525)) (CONNECT_DATA = (SERVICE_NAME = dg4msql)) (HS = OK)) OK (0 msec) |
(HS=OK) #这个很重要表示异构数据库
在oracle数据库中测试:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 | CREATE DATABASE LINK dbl_mssql CONNECT TO "sa" IDENTIFIED BY "lhr" USING 'DG4MSQL'; select COUNT(*) from dbo.test@dbl_mssql; select 1 from dual@dbl_mssql; SYS@lhrsdb> select 1 from dual@dbl_mssql; 1 ---------- 1 SYS@lhrsdb> select COUNT(*) from test@dbl_mssql; select COUNT(*) from test@dbl_mssql * ERROR at line 1: ORA-00942: table or view does not exist [Oracle][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]TEST' 42S02,NativeErr = 208}[Oracle][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]ԤHY000,NativeErr = 8180} ORA-02063: preceding 2 lines from DBL_MSSQL SYS@lhrsdb> select COUNT(*) from "test"@dbl_mssql; COUNT(*) ---------- 5134 SYS@lhrsdb> select name from "test"@dbl_mssql where rownum<=2; select name from "test"@dbl_mssql where rownum<=2 * ERROR at line 1: ORA-00904: "NAME": invalid identifier SYS@lhrsdb> select "name" from "test"@dbl_mssql where rownum<=2; name ----------- GRANTOR GRANTEE |
执行的查询操作,表名需要带双引号,因为MySQL和SQL server默认表名是区分大小写,而oracle是不区分大小写的(oracle默认自动转换成大写)。
Oracle连接到MySQL
参考文档:
http://blog.itpub.net/26736162/viewspace-2144661/
http://blog.itpub.net/26736162/viewspace-2644037/
Detailed Overview of Connecting Oracle to MySQL Using DG4ODBC Database Link (Doc ID 1320645.1)
Oracle使用DG4ODBC数据网关连接其它非Oracle数据库,可以不用安装前边的Gateways网关,其原理图如下:
从上图可知,Oracle连接MySQL需要涉及到如下组件:DG4ODBC, ODBC Driver Manager, ODBC Driver。
配置过程如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 | -- root操作 yum install -y unixODBC unixODBC-devel mysql-connector-odbc -- 升级 mysql-connector-odbc驱动 rpm -Uvh https://repo.mysql.com//mysql80-community-release-el7.rpm yum update -y unixODBC unixODBC-devel mysql-connector-odbc cat >/etc/odbc.ini <<"EOF" [myodbc5] #Driver = /usr/lib64/libmyodbc5w.so Driver = /usr/lib64/libmyodbc8w.so Description = Connector/ODBC 5.2 Driver DSN SERVER = 172.17.0.3 PORT = 3306 USER = root PASSWORD = lhr DATABASE = lhrdb OPTION = 0 TRACE = OFF EOF export ODBCINI=/etc/odbc.ini isql myodbc5 -v -- oracle操作 cd /u01/app/oracle/product/12.2.0.1/dbhome_1/network/admin cat >> tnsnames.ora <<"EOF" myodbc5 = (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=127.0.0.1) (PORT=1521) ) (CONNECT_DATA= (SID=myodbc5) ) (HS=OK) ) EOF -- vi listener.ora SID_LIST_LISTENER= (SID_LIST= (SID_DESC= (SID_NAME=myodbc5) (ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/dbhome_1) (PROGRAM=dg4odbc) (ENVS=LD_LIBRARY_PATH=/usr/lib:/u01/app/oracle/product/12.2.0.1/dbhome_1/lib) ) ) cat > $ORACLE_HOME/hs/admin/initmyodbc5.ora <<"EOF" HS_FDS_CONNECT_INFO=myodbc5 HS_FDS_SHAREABLE_NAME=/usr/lib64/libodbc.so HS_FDS_SUPPORT_STATISTICS=FALSE HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P15 EOF lsnrctl reload lsnrctl status tnsping myodbc5 odbcinst -j create public database link mysqltest connect to "root" identified by "lhr" using 'myodbc5' ; select count(*) from "lhrtest"@mysqltest; |
执行过程:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 | [root@lhrora1221 ~]# rpm -qa | grep unixODBC unixODBC-2.3.1-14.el7.x86_64 unixODBC-devel-2.3.1-14.el7.x86_64 [root@lhrora1221 ~]# rpm -qa | grep mysql-connector-odbc mysql-connector-odbc-5.2.5-8.el7.x86_64 [root@lhrora1221 ~]# [root@lhrora1221 ~]# export ODBCINI=/etc/odbc.ini [root@lhrora1221 ~]# isql myodbc5 -v +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> show databases; +-----------------------------------------------------------------+ | Database | +-----------------------------------------------------------------+ | information_schema | | canal_manager | | lhrdb | | mysql | | performance_schema | | sbtest | | sys | +-----------------------------------------------------------------+ SQLRowCount returns 7 7 rows fetched [oracle@lhrora1221 admin]$ sas SQL*Plus: Release 12.2.0.1.0 Production on Sat Mar 5 17:47:45 2022 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SYS@lhrsdb> select count(*) from "test"@mysqltest; COUNT(*) ---------- 5 |
报错ORA-28511和ORA-02063
若Oracle连接MySQL报错:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 | -- 升级之前 [root@lhrora1221 ~]# rpm -qa | grep mysql-connector-odbc mysql-connector-odbc-5.2.5-8.el7.x86_64 [root@lhroracle21c /]# isql myodbc5 -v +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> select * from test; Segmentation fault SYS@LHRCDB> select count(*) from "test"@mysqltest; select count(*) from "lhrtest"@mysqltest * ERROR at line 1: ORA-28511: lost RPC connection to heterogeneous remote agent using SID=ORA-28511: lost RPC connection to heterogeneous remote agent using SID=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521))(CONNECT_DATA=(SID=myodbc5))) ORA-02063: preceding line from MYSQLTEST Process ID: 1558 Session ID: 872 Serial number: 55665 -- 升级之后 [root@lhroracle21c /]# rpm -qa | grep mysql-connector-odbc mysql-connector-odbc-8.0.31-1.el7.x86_64 mysql-connector-odbc-setup-8.0.31-1.el7.x86_64 [root@lhroracle21c /]# isql myodbc5 -v +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> select * from test; +-----------+ | id | +-----------+ | 1 | | 2 | +-----------+ SQLRowCount returns 2 2 rows fetched SYS@LHRCDB> select * from "test"@mysqltest; id ---------- 1 2 |
原因:mysql-connector-odbc驱动版本太低。
解决:
1 2 3 4 | rpm -Uvh https://repo.mysql.com//mysql80-community-release-el7.rpm yum update -y unixODBC unixODBC-devel mysql-connector-odbc |
总结
1、对于SQL Server, DB2, Sybase等数据库需要安装透明网关软件才能通过dblink访问
2、对于MySQL、PG可以直接进行配置,然后通过dblink访问
3、若Oracle是21c版本,则由于Oracle 21c的默认只读主目录特性,则在配置透明网关文件时,initmyodbc5.ora
文件不应放在$ORACLE_HOME/hs/admin目录下,而应该放在$ORACLE_BASE/homes/OraDB21Home1/hs/admin
目录下:
1 2 3 4 5 6 | cat > /u01/app/oracle/homes/OraDB21Home1/hs/admin/initmyodbc5.ora <<"EOF" HS_FDS_CONNECT_INFO=myodbc5 HS_FDS_SHAREABLE_NAME=/usr/lib64/libodbc.so HS_FDS_SUPPORT_STATISTICS=FALSE HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P15 EOF |
有关只读目录请参考:https://www.xmmup.com/oracle-21czhongdezhiduoraclezhulutexingrooh.html
4、dg4odbc日志文件在hs/log/
目录下。
参考
https://blog.csdn.net/cymm_liu/article/details/38473851
https://www.cnblogs.com/BinBinGo/p/12020246.html
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/gmswn/index.html
http://blog.itpub.net/26736162/viewspace-2144661/
http://blog.itpub.net/26736162/viewspace-2644037/
https://www.cnblogs.com/kerrycode/p/8487671.html
How to Make a Silent Install of Oracle Database Gateway 12c or higher (Doc ID 2639375.1)
How to Resolve Common Errors Encountered while using Database Gateways (DG4IFMX, Dg4MSQL, DG4SYBS), DG4ODBC or Generic Connectivity (文档 ID 234517.1)
Oracle 21c 通过gateway 连接 postgresql研究过没。我这正常配置,总是报错:
ORA-28500: 连接 ORACLE 到非 Oracle 系统时返回此信息:
connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such file or directory
Is the server running locally and accepting connections on that socket?
{08001,NativeErr = 101}
ORA-02063: 紧接着 4 lines (起自 HGDB)
你这个报错看着像是PG连接的问题,可以生成trace来详细分析,参考:https://www.xmmup.com/zaioraclezhongtongguodblinkfangwenpgshujuku.html
同样的配置Oracle11g、12c,直到19c都可以用,就到了21c不行了。不知道是不是对odbc驱动要求高,最新的psqlodbc13.2是不是不能满足了,得等postgresql方出对应的odbc驱动。
若Oracle是21c,那网关的配置文件中应该去掉参数:HS_NLS_NCHAR和HS_LANGUAGE就可以了
Oracle 21c连接PG可以参考:https://www.xmmup.com/zaioraclezhongtongguodblinkfangwenpgshujuku.html