合 在Oracle中通过dblink访问PG数据库
简介
在PG中访问PG可以通过dblink，在PG中访问Oracle可以通过oracle_fdw，访问MySQL可以通过mysql_fdw，具体过程可以参考：https://www.dbaup.com/pgzhongdefile_fdwpostgres_fdwhedblink.html
在Oracle访问SQL server需要配置Oracle Database Gateways透明网关，Oracle中访问SQL Server和MySQL的配置可以参考：https://www.dbaup.com/oracle-database-gatewaystoumingwangguandeanzhuanghepeizhi.html
那么，在Oracle中访问PG该如何配置呢？请看下文。
Oracle dblink的底层是通过ODBC连接PostgreSQL执行SQL的，需安装unixODBC和PostgreSQL ODBC驱动（它们的配置文件是：odbcinst.ini和odbc.ini），还需配置Oracle网络使用这个驱动。
环境准备
Oracle 11.2.0.4 CentOS 6.6 172.17.0.2
PG 13.8 ，Debian GNU/Linux 11 172.17.0.3
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 -itd --name lhrora11204 -h lhrora11204 -p 3394:3389 \ -p 1524:1521 -p 1124:1158 -p 224:22 \ --privileged=true \ lhrbest/oracle_11g_ee_lhr_11.2.0.4:2.0 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驱动包
安装ODBC驱动分成两部分：
1.安装unixODBC
1 2 | -- 可以直接安装 yum install -y unixODBC.x86_64 |
unixODBC相当于Linux中管理所有数据库ODBC驱动的管理器。建议Linux环境安装unixODBC 2.3.7以上版本。可以使用isql --v查询获取安装unixODBC版本。在CentOS 7中，通过yum安装后版本为2.3.7-；在CentOS 6中，通过yum安装后版本为2.2.14，也可以使用，若使用编译安装，则具体安装方法如下，在root用户下进行操作：
Ø 解压文件：tar -xf unixODBC-2.3.7.tar.gz
Ø 配置安装：cd unixODBC-2.3.7;./configure --prefix=/usr/local/odbc
Ø 编译安装：make && make install
Ø 默认安装到/usr/local/odbc下
Ø 退出当前会话，重新登录查询ODBC版本isql --v
2.安装PostgreSQL的ODBC驱动。
安装完成后，在目录/usr/pgsql-12/下生成lib和share相关目录。pg的odbc驱动放在lib下。
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 | -- 通过yum安装的postgresql-odbc包驱动太低，会导致后边的报错 yum install -y unixODBC.x86_64 postgresql-odbc.x86_64 [root@lhrora11204 /]# cat /etc/redhat-release CentOS release 6.6 (Final) [root@lhrora11204 /]# rpm -qa | grep postgres postgresql-libs-8.4.20-8.el6_9.x86_64 postgresql-odbc-08.04.0200-1.el6.x86_64 [root@lhrora11204 /]# rpm -qa | grep unixODBC unixODBC-devel-2.2.14-14.el6.x86_64 unixODBC-2.2.14-14.el6.x86_64 [root@lhr ~]# cat /etc/redhat-release CentOS Linux release 7.6.1810 (Core) [root@lhr ~]# rpm -qa | grep postgres postgresql-odbc-09.03.0100-2.el7.x86_64 postgresql-libs-9.2.24-7.el7_9.x86_64 [root@lhr ~]# rpm -qa | grep unixODBC unixODBC-devel-2.3.7-1.rh.x86_64 unixODBC-2.3.7-1.rh.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 postgresql12-odbc postgresql12-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-12/lib/psqlodbcw.so Setup = /usr/pgsql-12/lib/psqlodbcw.so #数据库名，远程访问的数据库名 Database = lhrdb #数据库所在的主机名或IP Servername = 172.17.0.3 #数据库用户名(可不填，在代码中指定即可) 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 23 24 25 26 27 28 29 30 | [root@lhrora11204 /]# export ODBCINI=/etc/odbc.ini [root@lhrora11204 /]# isql --v unixODBC 2.2.14 [root@lhrora11204 /]# isql PG_LINK -v +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> select 1; +------------+ | ?column? | +------------+ | 1 | +------------+ SQLRowCount returns 1 1 rows fetched SQL> select * from test; +------------+ | id | +------------+ | 1 | | 2 | +------------+ SQLRowCount returns 2 2 rows fetched SQL> |
配置透明网关
在$ORACLE_HOME/hs/admin/下面创建initPG_LINK.ora文件，这个文件名字中的PG_LINK是上面自定义的名字，其中HS_FDS_CONNECT_INFO = PG 这个参数指向隐藏文件.odbc.ini中的ODBC定义数据源名称[PG_LINK]。
在$ORACLE_HOME/hs/admin/下面创建initPG_LINK.ora文件，这个文件名字中的PG_LINK是上面自定义的名字
1 2 3 4 5 6 7 8 | cat > $ORACLE_HOME/hs/admin/initPG_LINK.ora <<"EOF" HS_FDS_CONNECT_INFO = PG_LINK HS_FDS_TRACE_LEVEL = 255 HS_FDS_SHAREABLE_NAME=/usr/pgsql-12/lib/psqlodbcw.so HS_NLS_NCHAR=UCS2 HS_LANGUAGE=AMERICAN_AMERICA.AL32UTF8 set ODBCINI=/home/oracle/.odbc.ini EOF |
后续通过dblink连接PG的时候连接日志文件路径：$ORACLE_HOME/hs/log/，可以进行排错。
配置tnsnames.ora文件
在$ORACLE_HOME/network/admin/下面文件tnsnames.ora添加如下内容。其中PG_LINK是客户端到PostgreSQL实例的连接配置，注意一点：“SID = PG_LINK”中，SID应设置为listener.ora中PostgreSQL的实例名，例如这里就是PG_LINK。PG_LINK这个名称，将在创建PG_LINK时使用。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | cat >> $ORACLE_HOME/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 |
配置监听文件
在$ORACLE_HOME/network/admin/下面文件listener.ora添加如下内容。它代表PostgreSQL提供的数据库服务，Oracle将PostgreSQL也看成一个Oracle实例，SID_NAME = PGINSTANCE是给它定义一个实例名。这个实例名决定了配置透明网关配置文件$ORACLE_HOME/hs/admin/init
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | cat >> $ORACLE_HOME/network/admin/listener.ora <<"EOF" SID_LIST_LISTENER= (SID_LIST= (SID_DESC= (SID_NAME=PG_LINK) (ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_1) (PROGRAM=dg4odbc) (ENVS=LD_LIBRARY_PATH="/usr/pgsql-12/lib:/u01/app/oracle/product/11.2.0.4/dbhome_1/lib") ) ) EOF lsnrctl reload lsnrctl status tnsping PG_LINK |
结果：
SocketBufferSize = 4096
FetchBufferSize = 500
这个参数是什么含义？
增加性能的，可以不用配置，参考：https://docs.oracle.com/en/database/oracle/oracle-database/19/unxar/using-oracle-ODBC-driver.html#GUID-CF96CB6C-43BF-4BD2-919C-EE005B64FEE3
- Fetch Buffer Size
Set the Fetch Buffer Size (FetchBufferSize) in the odbc.ini file to a value specified in bytes. This value is the amount of memory needed that determines how many rows of data Oracle ODBC Driver pre-fetches at a time from an Oracle Database to the client's cache regardless of the number of rows the application program requests in a single query, thus improving performance.
There is an improvement in the response time of applications that typically fetch fewer than 20 rows of data at a time, particularly over slow network connections or from heavily loaded servers. Setting this too high can have an adverse effect on response time or consume large amounts of memory. The default is 64,000 bytes. You should choose an optimal value for the application.
When the LONG and LOB data types are present, the number of rows pre-fetched by Oracle ODBC Driver is not determined by the Fetch Buffer Size. The inclusion of the LONG and LOB data types minimizes the performance improvement and could result in excessive memory use. Oracle ODBC Driver ignores the Fetch Buffer Size and only pre-fetches a set number of rows in the presence of the LONG and LOB data types.
厉害啊，麦苗老师。那个目录是真没想到，按以前版本的目录确实没生效。21c感觉很多地方不一样。不过有个问题，Windows下的管理工具toad，dbeaver显示中文都是乱码，而且看了一下二进制没啥关联，不知道咋形成的。sqlplus看到的中文不是乱码，但插入数据的时候，一个汉字不行，得两个。
SYS@LHRCDB> insert into "public"."t1"@hgdb values(10,'我');
ERROR:
ORA-01756: quoted string not properly terminated
SYS@LHRCDB> insert into "public"."t1"@hgdb values(10,'我我');
1 row created.