oracle使用dblink连接postgresql

环境：

oracle        数据库： 192.168.22.201 端口号6666 操作系统：RHEL 7.6
postgresql 数据库： 192.168.22.20   端口号5432 操作系统：RHEL 7.6

pg数据库配置

• 1、修改 pg_hba.conf 文件

host all lanmc 192.168.22.201/24 md5

• 2、重新加载服务

systemctl reload postgresql-15.service

oracle数据库配置

• 1、安装unixODBC，unixODBC相当于Linux中管理所有数据库ODBC驱动的管理器。可以使用isql --v查询获取安装unixODBC版本

yum install -y unixODBC.x86_64
	
[root@t11g2 ~]# isql --version
unixODBC 2.3.1

• 2、安装postgresql的odbc驱动，默认安装位置在/usr/pgsql-11/lib

rpm -ivh postgresql11-libs-11.9-1PGDG.rhel7.x86_64.rpm 
rpm -ivh postgresql11-odbc-13.02.0000-1PGDG.rhel7.x86_64.rpm

###############################   如果不使用新的odbc驱动会出现一下报错   ################
## SQL> create database link PG_LINK connect to "lanmc" identified by "lanmc123" using 'PG';
## 
## Database link created.
## 
## SQL> select * from t1@PG_LINK;
## select * from t1@PG_LINK
##                  *
## ERROR at line 1:
## 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 PG_LINK
## 
## 
## 
## SQL> select * from "t1"@pg ;
## select * from "t1"@pg
##                    *
## ERROR at line 1:
## ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
## ORA-02063: preceding line from PG
## 
## SQL> select * from "t1"@pg;
## select * from "t1"@pg
##               *
## ERROR at line 1:
## ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
## No query has been executed with that handle;
## Could not send Query(connection dead) {HY000,NativeErr = 1}
## ORA-02063: preceding 3 lines from PG
## 
## 
## 
## SQL> select * from "t1"@pg;
## select * from "t1"@pg
##                    *
## ERROR at line 1:
## 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 PG
########################################################################################################

• 3、root用户新建/etc/odbc.ini

vi /etc/odbc.ini

[PG]
Description        = PostgreSQL connection to lanmc
Driver             = /usr/pgsql-11/lib/psqlodbcw.so	
Setup              = /usr/pgsql-11/lib/psqlodbcw.so
Database           = lanmc
Servername         = 192.168.22.20
UserName           = lanmc
Password           = lanmc123
Port               = 5432
SocketBufferSize   = 4096
FetchBufferSize    = 500
ReadOnly           = Yes
RowVersioning      = No
ShowSystemTables   = No
ConnSettings       = set client_encoding to UTF8


测试：
[root@t11g2 lib]# isql pg
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> quit()

############################  如果测试连接失败，则需要检查网络连接是否正常  ###################
## [root@t11g1 opt]# isql pg
## [ISQL]ERROR: Could not SQLConnect
## 
## [root@t11g1 opt]# telnet 192.168.22.20 5432
## Trying 192.168.22.20...
## telnet: connect to address 192.168.22.20: Connection refused
###########################################################

• 4、配置透明网关
  在$ORACLE_HOME/hs/admin目录下创建initPG.ora

su - oracle
vi $ORACLE_HOME/hs/admin/initPG.ora

# HS init parameters
#
HS_FDS_CONNECT_INFO = PG
HS_FDS_TRACE_LEVEL = 255
HS_FDS_SHAREABLE_NAME = /usr/pgsql-11/lib/psqlodbcw.so   ## 这里应该写unixodbc的lib包/usr/lib64/libodbc.so
HS_FDS_TRACE_LEVEL=ON
HS_NLS_NCHAR=UCS2
#HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1
HS_LANGUAGE=AMERICAN_AMERICA.AL32UTF8

#set ODBCINI=/home/oracle/.odbc.ini
set ODBCINI=/etc/odbc.ini

• 5、配置tnsnames.ora文件

vi $ORACLE_HOME/network/admin/tnsnames.ora

PG =
 (DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.22.202)(PORT = 6666))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.22.203)(PORT = 6666))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.22.204)(PORT = 6666))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.22.205)(PORT = 6666))
  (CONNECT_DATA =
  (SID = PG)
  )
 (HS = OK)
 )

• 6、配置监听文件，由于是rac环境在grid用户下

su - grid

vi $ORACLE_HOME/network/admin/listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
      (SID_DESC=
      (SID_NAME=PG)
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db)
      (PROGRAM=dg4odbc)
      (ENVS=LD_LIBRARY_PATH="/usr/pgsq-11/lib:/u01/app/oracle/product/11.2.0/db/lib")
      )
   )
   
## 重启监听
lsnrctl reload


## 创建dblink

SQL> create database link pg_test connect to "lanmc" identified by "lanmc123" using 'PG';

Database link created.

SQL> 
SQL> 
SQL> select * from "t1"@pg_test;

        id
----------
         1
         2