Oracle 21c通过dg4odbc配置dblink连接到PostgreSQL
Tags： DBLinkDG4ODBCOracleOracle 21c 新特性PG
👉 本文共约939个字，系统预计阅读时间或需4分钟。
简介
在PG中访问PG可以通过dblink，在PG中访问Oracle可以通过oracle_fdw，访问MySQL可以通过mysql_fdw，具体过程可以参考：https://www.xmmup.com/pgzhongdefile_fdwpostgres_fdwhedblink.html
在Oracle访问SQL server需要配置Oracle Database Gateways透明网关，Oracle中访问SQL Server和MySQL的配置可以参考：https://www.xmmup.com/oracle-database-gatewaystoumingwangguandeanzhuanghepeizhi.html
在Oracle中连接PG的详细内容请参考：https://www.xmmup.com/zaioraclezhongtongguodblinkfangwenpgshujuku.html
本文只简单写出Oracle 21c到PG 13.8的配置过程。
环境准备
Oracle 21.3 CentOS 7.6.1810 172.17.0.2
PG 13.8 ，Debian GNU/Linux 11 172.17.0.4
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | -- 创建Oracle主机，11.2.0.4环境 docker run -d --name lhroracle21c -h lhroracle21c \ -p 5510:5500 -p 55100:5501 -p 1530:1521 -p 3400:3389 \ -v /sys/fs/cgroup:/sys/fs/cgroup \ --privileged=true lhrbest/oracle21c_ee_db_21.3.0.0 \ /usr/sbin/init -- 创建PG主机，已安装PG 13数据库 docker rm -f lhrpg13 docker run --name lhrpg13 -h lhrpg13 -d -p 54326:5432 -e POSTGRES_PASSWORD=lhr -e TZ=Asia/Shanghai postgres:13.8 docker exec -it lhrpg13 bash su - postgres create database lhrdb; \c lhrdb create table test(id int); insert into test values(1),(2); |
安装postgresql的odbc驱动包
1 2 3 4 5 6 7 8 9 | -- 可以直接安装 yum install -y unixODBC.x86_64 -- 正确的安装方式 yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm # yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-6-x86_64/pgdg-redhat-repo-latest.noarch.rpm yum install -y postgresql14-odbc postgresql14-libs |
配置/etc/odbc.ini
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 | cat > /etc/odbc.ini <<"EOF" #[$DSN]定义数据源名称，根据实际情况自定义 [PG_LINK] #数据源说明，根据实际情况自定义 Description = PostgreSQL connection to lhrdb #使用的驱动，上章节安装PostgreSQL的ODBC驱动所在位置 Driver = /usr/pgsql-14/lib/psqlodbcw.so Setup = /usr/pgsql-14/lib/psqlodbcw.so #数据库名，远程访问的数据库名 Database = lhrdb #数据库所在的主机名或IP Servername = 172.17.0.4 #数据库用户名(可不填，在代码中指定即可) UserName = postgres #数据库用户密码(可不填，在代码中指定即可) Password = lhr #数据库端口 Port = 5432 SocketBufferSize = 4096 FetchBufferSize = 500 ReadOnly = Yes RowVersioning = No ShowSystemTables = No #查询结果的字符编码 ConnSettings = set client_encoding to UTF8 EOF odbcinst -j export ODBCINI=/etc/odbc.ini isql --v isql PG_LINK -v select 1; select * from test; ln -sf /etc/odbc.ini /home/oracle/.odbc.ini |
结果如下说明配置正确：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | [oracle@lhroracle21c ~]$ export ODBCINI=/etc/odbc.ini [oracle@lhroracle21c ~]$ isql --v unixODBC 2.3.1 [oracle@lhroracle21c ~]$ isql PG_LINK -v +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> select * from test; +------------+ | id | +------------+ | 1 | | 2 | | 3 | +------------+ SQLRowCount returns 3 3 rows fetched |
配置透明网关
若Oracle是21c之前的版本，请参考：https://www.xmmup.com/zaioraclezhongtongguodblinkfangwenpgshujuku.html
由于Oracle是21c，默认开启了只读主目录特性：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | [oracle@lhroracle21c ~]$ orabasehome /u01/app/oracle/homes/OraDB21Home1 [oracle@lhroracle21c ~]$ cat $ORACLE_HOME/install/orabasetab #orabasetab file is used to track Oracle Home associated with Oracle Base /u01/app/oracle/product/21c/dbhome_1:/u01/app/oracle:OraDB21Home1:Y: [oracle@lhroracle21c ~]$ cd /u01/app/oracle/homes/OraDB21Home1 [oracle@lhroracle21c OraDB21Home1]$ ll total 56 drwxr-x--- 3 oracle oinstall 4096 Aug 16 2021 assistants drwxr-x--- 3 oracle oinstall 4096 Aug 16 2021 cfgtoollogs drwxr-x--- 2 oracle oinstall 4096 Aug 16 2021 dbs drwxr-x--- 3 oracle oinstall 4096 Aug 16 2021 drdaas drwxr-x--- 1 oracle oinstall 4096 Oct 9 15:51 hs drwxr-x--- 2 oracle oinstall 4096 Aug 16 2021 install drwxr-xr-t 1 oracle oinstall 4096 Aug 16 2021 log drwxr-x--- 3 oracle oinstall 4096 Aug 16 2021 mgw drwxr-x--- 5 oracle oinstall 4096 Aug 16 2021 network drwxr-x--- 1 oracle oinstall 4096 Aug 16 2021 rdbms drwxr-x--- 2 oracle oinstall 4096 Aug 16 2021 sqlpatch [oracle@lhroracle21c OraDB21Home1]$ cd hs/admin [oracle@lhroracle21c admin]$ pwd /u01/app/oracle/homes/OraDB21Home1/hs/admin [oracle@lhroracle21c admin]$ |
所以，网关的配置文件应该放在目录/u01/app/oracle/homes/OraDB21Home1/hs/admin，而不是/u01/app/oracle/product/21c/dbhome_1/hs/admin，且不能配置HS_NLS_NCHAR和HS_LANGUAGE变量，否则会报错，这里应特别注意。
1 2 3 4 5 6 7 8 | cat > /u01/app/oracle/homes/OraDB21Home1/hs/admin/initPG_LINK.ora <<"EOF" HS_FDS_CONNECT_INFO = PG_LINK HS_FDS_TRACE_LEVEL = 255 HS_FDS_SHAREABLE_NAME=/usr/pgsql-14/lib/psqlodbcw.so #HS_NLS_NCHAR=UCS2 #HS_LANGUAGE=AMERICAN_AMERICA.AL32UTF8 set ODBCINI=/home/oracle/.odbc.ini EOF |
后续通过dblink连接PG的时候连接日志文件路径：/u01/app/oracle/homes/OraDB21Home1/hs/log/，可以进行排错。
本人提供Oracle、MySQL、PG等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
配置tnsnames.ora文件
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | cat >> /u01/app/oracle/product/21c/dbhome_1/network/admin/tnsnames.ora <<"EOF" PG_LINK = (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=127.0.0.1) (PORT=1521) ) (CONNECT_DATA= (SID=PG_LINK) ) (HS=OK) ) EOF |
配置监听文件
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | cat >> /u01/app/oracle/product/21c/dbhome_1/network/admin/listener.ora <<"EOF" SID_LIST_LISTENER= (SID_LIST= (SID_DESC= (SID_NAME=PG_LINK) (ORACLE_HOME=/u01/app/oracle/product/21c/dbhome_1) (PROGRAM=dg4odbc) (ENVS=LD_LIBRARY_PATH="/usr/pgsql-14/lib:/u01/app/oracle/product/21c/dbhome_1/lib") ) ) EOF lsnrctl reload lsnrctl status tnsping PG_LINK |
结果：
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 | [oracle@lhroracle21c admin]$ lsnrctl status LSNRCTL for Linux: Version 21.0.0.0.0 - Production on 11-OCT-2022 15:38:31 Copyright (c) 1991, 2021, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 21.0.0.0.0 - Production Start Date 11-OCT-2022 14:27:36 Uptime 0 days 1 hr. 10 min. 55 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/21c/dbhome_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/lhroracle21c/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=lhroracle21c)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=lhroracle21c)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/LHRCDB/xdb_wallet))(Presentation=HTTP)(Session=RAW)) Services Summary... Service "LHRCDB" has 1 instance(s). Instance "LHRCDB", status READY, has 1 handler(s) for this service... Service "LHRCDBXDB" has 1 instance(s). Instance "LHRCDB", status READY, has 1 handler(s) for this service... Service "PG_LINK" has 1 instance(s). Instance "PG_LINK", status UNKNOWN, has 1 handler(s) for this service... Service "c9a73ae76f543a73e0530e0011acc4b4" has 1 instance(s). Instance "LHRCDB", status READY, has 1 handler(s) for this service... Service "myodbc5" has 1 instance(s). Instance "myodbc5", status UNKNOWN, has 1 handler(s) for this service... Service "pdb2" has 1 instance(s). Instance "LHRCDB", status READY, has 1 handler(s) for this service... The command completed successfully [oracle@lhroracle21c admin]$ tnsping PG_LINK TNS Ping Utility for Linux: Version 21.0.0.0.0 - Production on 11-OCT-2022 15:38:33 Copyright (c) 1997, 2021, Oracle. All rights reserved. Used parameter files: /u01/app/oracle/product/21c/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=1521)) (CONNECT_DATA= (SID=PG_LINK)) (HS=OK)) OK (0 msec) [oracle@lhroracle21c admin]$ |
创建DBLINK和测试
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | create database link to_pglink connect to "postgres" identified by "lhr" using 'PG_LINK'; -- 访问postgre的数据库表是需要表名字小写并加上双引号 select * from "test"@to_pglink; select * from "public"."test"@to_pglink; SYS@LHRCDB> select * from "test"@to_pglink; id ---------- 1 2 SYS@LHRCDB> select * from "public"."test"@to_pglink; id ---------- 1 2 |
总结
1、若Oracle开启了只读主目录特性，那么需要注意网关文件的配置路径，且不能配置HS_NLS_NCHAR和HS_LANGUAGE变量，正确配置如下：
1 2 3 4 5 6 7 8 | cat > /u01/app/oracle/homes/OraDB21Home1/hs/admin/initPG_LINK.ora <<"EOF" HS_FDS_CONNECT_INFO = PG_LINK HS_FDS_TRACE_LEVEL = 255 HS_FDS_SHAREABLE_NAME=/usr/pgsql-14/lib/psqlodbcw.so #HS_NLS_NCHAR=UCS2 #HS_LANGUAGE=AMERICAN_AMERICA.AL32UTF8 set ODBCINI=/home/oracle/.odbc.ini EOF |
