当前位置：首页 > ai >正文

Oracle 11g通过dg4odbc配置dblink连接PostgreSQL

ai 2025/7/27 20:09:50

1、安装unixodbc

2、安装postgresql

yum install -y postgresql17-odbc

3、配置postgresqlodbc数据源

vim  /usr/local/etc/odbcinst.ini##添加如下
[mypg]
Driver = /usr/lib64/psqlodbcw.so
Servername = localhost
Port = 5432
Database = postgres
Username = postgres
Password = password
SocketBufferSize = 4096
FetchBufferSize = 100

4、测试odbc连通性

5、配置透明网关、监听文件以及对应编写的hsodbc的ora文件，我这里是mypg.ora

##对应编写的hsodbc的ora文件
vim $ORACLE_HOME/hs/admin/mypg.ora
##添加如下
HS_FDS_CONNECT_INFO = mypg17
HS_FDS_TRACE_LEVEL = debug
set ODBCINI= /usr/local/etc/odbc.ini
#HS_FDS_SHAREABLE_NAME = /usr/local/psqlodbc/psqlodbcw.so(已注释，使用的psqlodbc驱动未调通，应该是yum默认安装的postgresql-odbc驱动版本有点低，本次使用unixodc驱动)
HS_NLS_NCHAR = UCS2
HS_LANGUAGE=AMERICAN_AMERICA.AL32UTF8
#NLS_LANG=AMERICAN_AMERICA.AL32UTF8
HS_FDS_SHAREABLE_NAME=/usr/lib64/libodbc.so##配置oracle静态监听
vim $ORACLE_HOME/network/admin/listener.ora
##添加如下
SID_LIST_LISTENER=(SID_LIST=(SID_DESC=(PROGRAM = dg4odbc)(ORACLE_HOME = /opt/oracle/app/oracle/product/11.2.0/dbhome_1)(SID_NAME = mypg)))
USE_SID_AS_SERVICE_listener=on##配置tnsnames.ora
vim $ORACLE_HOME/network/admin/tnsnames.ora
##添加如下
mypg=(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))(CONNECT_DATA =(SID = mypg)   )(HS = OK))

6、重启监听

##oracle用户登录的shell下执行重启监听的命令，重启之后你会发现有一个mypg的实例被监听，状态为unknown，不用管它，具体还是要看测试查询的结果
##停止监听
lsnrctl stop
##启动监听 
lsnrctl start
##查看监听状态 
lsnrctl status

7、dblink测试

##postgresql创建表、插入数据
##在Oracle中创建连接postgresql的dblink
create database link mypg_test connect to "postgres" identified by "password" using 'mypg';
##测试在Oracle通过dblink查看postgresql的数据
select * from "public"."t1"@mypg_test;

postgresql