oracle_fdw

作者：瀚高PG实验室（Highgo PG Lab）-Chrisx

oracle_fdw

oracle_fdw是PostgreSQL扩展，它提供了一个第三方外部数据包装器，方便和高效地访问存储在外部数据库oracle中的数据

安装配置oracle客户端

下载instant client

1. 下载三个文件即可
   Basic Package (ZIP)
   SQL*Plus Package (ZIP)
   SDK Package (ZIP)

instantclient-basic-linux.x64-11.2.0.4.0.zip
instantclient-sdk-linux.x64-11.2.0.4.0.zip
instantclient-sqlplus-linux.x64-11.2.0.4.0.zip

2. 创建用户和目录

groupadd oinstall
useradd -G oinstall oracle
passwd oracle
mkdir /opt/oracle
chown oracle:oinstall /opt/oracle

3. 解压3个文件,会将内容解压到一个目录中instantclient_11_2。将文件夹中的内容复制到/opt/oracle

4. 设置环境变量

su - oracle
vi ~/.bash_profile

export ORACLE_HOME=/opt/oracle
export SQLPATH=/opt/oracle
export TNS_ADMIN=/opt/oracle
export LD_LIBRARY_PATH=$ORACLE_HOME:$LD_LIBRARY_PATH;
export PATH=$ORACLE_HOME:$PATH

5. 配置tns

配置网络访问

vi /opt/oracle/tnsnames.ora

FDW =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.6.11)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

测试tns是否正常

sqlplus system/oracle@fdw 

安装oracle_fdw

下载 oracle_fdw
⚠️ windows下载对应版本，linux可使用源码包编译

1. 设置环境变量

将环境变量添加到pg数据库用户下

export ORACLE_HOME=/opt/oracle
export SQLPATH=/opt/oracle
export TNS_ADMIN=/opt/oracle
export LD_LIBRARY_PATH=$ORACLE_HOME:$LD_LIBRARY_PATH;
export PATH=$ORACLE_HOME:$PATH

export HG_BASE=/opt/HighGo5.6.5
export PGHOME=/opt/HighGo5.6.5
export HGDB_HOME=/opt/HighGo5.6.5
export PGDATA=/opt/HighGo5.6.5/data
export LD_LIBRARY_PATH=/opt/HighGo5.6.5/lib:$LD_LIBRARY_PATH
export PGPORT=5966
export PGDATABASE=highgo
export PGUSER=highgo
export PATH=$PATH:/opt/HighGo5.6.5/bin

export ORACLE_HOME=/opt/oracle
export SQLPATH=/opt/oracle
export TNS_ADMIN=/opt/oracle
export LD_LIBRARY_PATH=$ORACLE_HOME:$LD_LIBRARY_PATH;
export PATH=$ORACLE_HOME:$PATH

测试tns

sqlplus system/oracle@fdw 

2. 解压

3. 安装

make PG_CONFIG=/opt/HighGo5.6.5/bin/pg_config

make install PG_CONFIG=/opt/HighGo5.6.5/bin/pg_config

4. 配置

CREATE EXTENSION oracle_fdw;      --创建fdw扩展

CREATE SERVER ser_oracle_fdw  
        FOREIGN DATA WRAPPER oracle_fdw  
        OPTIONS (dbserver '192.168.6.11:1521/orcl'); --创建远程服务

create role ora_fdw with login password 'ora_fdw';

CREATE USER MAPPING FOR highgo  
        SERVER ser_oracle_fdw  
        OPTIONS (user 'hr', password 'hr');      --配置远程访问用户密码-mapping
--ora_fdw，要映射到外部服务器的一个现有用户的名称。也就是本地用户名
--OPTIONS (user 'system', password 'oracle')，定义该映射实际的用户名和 口令，也就是远程连接使用的用户名口令，也就是远程服务器上存在的用户名口令

CREATE FOREIGN TABLE test_oracle_fdw (product_id      CHAR(4)  options(key 'true')    NOT NULL,
 product_name    VARCHAR(100) NOT NULL,
 product_type    VARCHAR(32)  NOT NULL,
 sale_price      INTEGER ,
 purchase_price  INTEGER ,
 regist_date     DATE) SERVER ser_oracle_fdw
OPTIONS (schema 'HR',table 'PRODUCT' );     --创建外部表

--注意，Oracle table names are case sensitive，因此这里的用户名及表名必须为大写

alter FOREIGN TABLE test_oracle_fdw

5. 查询外部表

highgo=# select * from test_oracle_fdw ;
 product_id | product_name | product_type | sale_price | purchase_price |  regist_date
------------+--------------+--------------+------------+----------------+---------------
 0001       | T恤          | 衣服         |       1000 |            500 | 0001-01-01 BC
(1 row)

6. 修改外部表

highgo=# INSERT INTO test_oracle_fdw VALUES ('0002', '打孔器', '办公用品', 500, 320, to_date('yyyy-mm-dd','2009-09-11'));
INSERT 0 1

highgo=# update test_oracle_fdw set sale_price=1111 where product_id='0001';
UPDATE 1

highgo=# select * from test_oracle_fdw ;
 product_id | product_name | product_type | sale_price | purchase_price |  regist_date
------------+--------------+--------------+------------+----------------+---------------
 0001       | T恤          | 衣服         |       1111 |            500 | 0001-01-01 BC
 0002       | 打孔器       | 办公用品     |        500 |            320 | 0001-01-01 BC
(2 rows)

问题

1. oracle_utils.o

报错

oracle_utils.c:22:17: fatal error: oci.h: No such file or directory
 #include <oci.h>
                 ^
compilation terminated.
make: *** [oracle_utils.o] Error 1

解决方案

需在pg数据库用户下设置ORACLE_HOME环境变量
export ORACLE_HOME=/opt/oracle

2. oracle_fdw.so

报错

/bin/ld: cannot find -lclntsh
collect2: error: ld returned 1 exit status
make: *** [oracle_fdw.so] Error 1

解决方案
执行make若出现“/usr/bin/ld: cannot find -lclntsh”；原因是找不到库liblclntsh文件
$ORACLE_HOME下有库文件libclntsh.so.11.1，而需要的库文件是libclntsh.so，创建一个软连接即可
ln -s libclntsh.so.11.1 libclntsh.so

3. ERROR: HV00R

报错

highgo=# select * from test_oracle_fdw ;
2021-02-19 15:55:30.925 CST [6157] ERROR:  HV00R: Oracle table "hr"."product" for foreign table "test_oracle_fdw" does not exist or does not allow read access
2021-02-19 15:55:30.925 CST [6157] DETAIL:  ORA-00942: table or view does not exist
2021-02-19 15:55:30.925 CST [6157] HINT:  Oracle table names are case sensitive (normally all uppercase).
2021-02-19 15:55:30.925 CST [6157] STATEMENT:  select * from test_oracle_fdw ;
ERROR:  HV00R: Oracle table "hr"."product" for foreign table "test_oracle_fdw" does not exist or does not allow read access
DETAIL:  ORA-00942: table or view does not exist
HINT:  Oracle table names are case sensitive (normally all uppercase).
highgo=#

解决方案
创建外部表时，oracle的用户名及表名必须为大写

CREATE FOREIGN TABLE test_oracle_fdw 
......
OPTIONS (schema 'HR',table 'PRODUCT' );     --创建外部表

4. ERROR: 40001

报错

highgo=# INSERT INTO test_oracle_fdw VALUES ('0001', 'T恤' ,'衣服', 1000, 500, '2009-09-20');
2021-02-19 16:02:11.040 CST [6157] ERROR:  40001: error executing query: OCIStmtExecute failed to execute remote query
2021-02-19 16:02:11.040 CST [6157] DETAIL:  ORA-08177: can't serialize access for this transaction
2021-02-19 16:02:11.040 CST [6157] STATEMENT:  INSERT INTO test_oracle_fdw VALUES ('0001', 'T恤' ,'衣服', 1000, 500, '2009-09-20');
ERROR:  40001: error executing query: OCIStmtExecute failed to execute remote query
DETAIL:  ORA-08177: can't serialize access for this transaction

解决方案

数据类型不匹配，日期数据类型需改为兼容oracle

INSERT INTO test_oracle_fdw VALUES ('0001', 'T恤' ,'衣服', 1000, 500, to_date('yyyy-mm-dd','2009-09-20'));

5. ERROR: HV00L

报错

highgo=# delete from test_oracle_fdw where product_id='0001';
2021-02-19 16:09:02.499 CST [6157] ERROR:  HV00L: no primary key column specified for foreign Oracle table
2021-02-19 16:09:02.499 CST [6157] DETAIL:  For UPDATE or DELETE, at least one foreign table column must be marked as primary key column.
2021-02-19 16:09:02.499 CST [6157] HINT:  Set the option "key" on the columns that belong to the primary key.
2021-02-19 16:09:02.499 CST [6157] STATEMENT:  delete from test_oracle_fdw where product_id='0001';
ERROR:  HV00L: no primary key column specified for foreign Oracle table
DETAIL:  For UPDATE or DELETE, at least one foreign table column must be marked as primary key column.
HINT:  Set the option "key" on the columns that belong to the primary key.

解决方案
update或delete一定要设置options(key ‘true’)，就是设置外部表的主键，否则会报错。

CREATE FOREIGN TABLE test_oracle_fdw 
......
OPTIONS (schema 'HR',table 'PRODUCT' );     --创建外部表