1. 概述

Oracle_fdw 是一种postgresql外部表插件，可以读取到Oracle数据库的数据。是一种非常方便且常见的pg与Oracle的同步数据的方法。

2. 配置Oracle环境

Oracle_fdw 的编译依赖系统中需要有pg_config和Oracle的环境。必须安装oracle客户端环境、pg数据库环境。文档中涉及安装客户端部分，全部在数据库安装下操作。例如数据库用户atlasdb，以下涉及修改目录属性全部改成atlasdb用户。

2.1. 安装oracle客户端

安装oracle客户端需要以下三个安装包：

Ø instantclient-basic-linux.x64-12.2.0.1.0.zip

Ø instantclient-sdk-linux.x64-12.2.0.1.0.zip

Ø instantclient-sqlplus-linux.x64-12.2.0.1.0.zip

以上三个安装包可以从oracle的官网下载，本文档安装包已经下载见附录。将安装包放到/opt/oraClient目录下。如果目录不存在，创建执行mkdir -p /opt/oraClient，并创建存放实例连接配置文件tnsnames.ora，创建执行mkdir -p /opt/oraClient/network/admin/。

创建目录完成后，将以上三个安装包解压放入/opt/oraClient目录。

Ø unzip instantclient-sqlplus-linux.x64-12.2.0.1.0.zip -d /opt/oraClient

Ø unzip instantclient-basic-linux.x64-12.2.0.1.0.zip -d /opt/oraClient

Ø unzip instantclient-sdk-linux.x64-12.2.0.1.0.zip -d /opt/oraClient

解压完成后，执行如下命令：

Ø cd /opt/oraClient/instantclient_12_2

Ø ln -s libclntsh.so.12.1 libclntsh.so   #oracle_fdw找的是libclntsh.so

Ø ln -s libocci.so.12.1 libocci.so

Ø mv network/ instantclient_12_2/  #创建未在instantclient_12_2，一定注意将编辑完成的配置文件tnsnames.ora放入instantclient_12_2

Ø chown -R atlasdb:atlasdb  /opt/oraClient  #修改oracle客户端目录权限

下图是配置文件tnsnames.ora实例，可以根据实际情况进行修改。

2.2. 用户环境变量配置

安装完客户端以后，进入数据库安装pg用户，su - postgres切换到用户以后，通过vim ~/.bashrc编辑增加如下内容：

Ø export ORACLE_HOME=/opt/oraClient/instantclient_12_2

Ø export LD_LIBRARY_PATH=$ATLASDB_HOME/lib:$ORACLE_HOME:$LD_LIBRARY_PATH

Ø export TNS_ADMIN=$ORACLE_HOME/network/admin

Ø export OCI_INC_DIR=$ORACLE_HOME/sdk/include

Ø export PATH=$ATLASDB_HOME/bin:$ORACLE_HOME:$PATH

其中ORACLE_HOME是oracle数据库客户端安装目录

PG_HOME是PG11.5的数据库安装目录

配置完成环境变量以后，执行source ~/.bashrc使环境变量生效。最后测试sqlplus是否能够成功登陆oracle数据库实例。具体命如下图：

3. 安装oracle_fdw

本次安装oracle_fdw的版本是2.4，软件包需要从github下载。本次安装的软件包见附录。将软件包oracle_fdw-2.4.1.zip服务器后，执行unzip oracle_fdw-2.4.1.zip解压当前目录。

解压完成后，执行如下命令安装oracle_fdw软件。

Ø cd oracle_fdw-2.4.0

Ø make && make install

在数据库用户postgres下安装插件完成后，必须进行动态链接库为系统共享，否则可能会报错如下：

ERROR:  could not load library "/usr/local/pgsql-10.6/lib/oracle_fdw.so": libclntsh.so.12.2: cannot open shared object file: No such file or directory

解决以上报错问题，可以通过以下步骤解决：

Ø  sudo vi /etc/ld.so.conf.d/ oracle-x86_64.conf

#其中sudo权限，需要给数据库配置免密登录root权限，否则此步不能在数据库用户postgres下执行。

具体添加sudo权限，在root下执行visudo命令，打开配置文件添加如下信息保存退出，然后su - postgres用户执行sudo+在root下执行的命令。

Ø postgres    ALL=(ALL)      NOPASSWD: ALL

其中postgres是数据库用户

配置完成sudo权限后，在atlasdb用户下执行sudo vi /etc/ld.so.conf.d/ oracle-x86_64.conf添加如下内容：

/opt/oraClient/instantclient_12_2

添加完成后，在atlasdb用户下执行sudo ldconfig，使之动态库链接生效。否则创建插件环节失败。

4. 创建插件

当安装完成后oracle_fdw插件依赖的环境，下一步登录PG11.5 数据库主机执行创建插件，一定需要在PG11.5的数据库上进行同步oracle表的数据，插件一定要在此数据库上创建。

4.1. 新建oracle_fdw插件

新建oracle_fdw插件命令如下：

Ø create extension oracle_fdw;

给具体PG11.5数据库创建插件，需要使用超级管理员用户。连接方式pg -d [databasename] -U [superUser]

4.2. 创建Oracle数据库映射

创建Oracle数据库映射有如下两种命令，需要登录超级用户管理员操作：

CREATE SERVER <SERVER名称> FOREIGN DATA WRAPPER postgres_fdw OPTIONS (dbserver '<数据库地址>',port '<数据库端口>',dbname '<数据库名>');

--列出已经创建的foreign server命令是：\des+

Ø CREATE SERVER <SERVER名称> FOREIGN DATA WRAPPER oracle_fdw OPTIONS (host '<Oracle数据库的内网连接地址>', port '<Oracle数据库的内网连接端口>', dbname '<数据库名>');

4.3. 授予用户访问权限

授予test用户访问foreign server执行如下命令，需要登录超级用户管理员操作：

grant usage on foreign server <SERVER名称> to <数据库账号>;

4.4. 创建用户映射

创建用户映射。命令如下，此步一定要进入上一步赋权的用户进行操作，否则创建外部表失败。

Ø CREATE USER MAPPINGFOR <数据库账号> SERVER <映射名> OPTIONS (user '<Oracle数据库用户名>', password '<Oracle数据库用户密码>');

4.5. 创建Oracle的外部表

创建Oracle的外部表。示例如下：

Ø CREATE FOREIGN TABLE <创建Vastbase表名>(<Oracle的表结构信息>) SERVER  <映射名>  OPTIONS (table '<Oracle的表名称>', schema '<Oracle表的模式名称>'  prefetch, '<两个数据库的表之间一次性传输的行数>');

其中prefetch参数代表每次fetch行数。（范围在0到10240之间）；创建外部表时候，Oracle表名称和模式名称一定大写，否则查询不到数据。外部表的结构需要和Oracle中的映射表结构保持一致。

以下是Oracle和pg11.5的查询结果：

4.6. 修改外部表数据

想要对remote table进行数据的修改操作，必须满足外部访问的表是有主键的情况，默认的列选项key设置的是false。需要手工设置对应的列名的key选项为true。

**key** (optional, defaults to "false")

如果没有修改key选项，在进行数据修改时候会出现如下告警，具体创建外部表如下图：

4.7. 删除创建的对象

Ø drop foreign table oratab;

Ø drop user mapping for test server oracle_91;

Ø drop server oracle_91; #创建用户删除

Ø DROP EXTENSION oracle_fdw; #创建用户删除

5. 批量导入Oracle表

PostgreSQL9.5之后，支持import foreign schema特性，可以批量的从oracle的schema中导出对象定义。当你使用该功能的时候需要考虑如下情景:

1) 使用import foreign schema 将会在pg数据库中创建从all_tab_columns表中能够查询到对应schema的所有表、视图、物化视图，但没有同义词。

2) 使用schema import支持两个选项

 **case**: controls case folding for table and column names during import.  

    The possible values are:

    - `keep`: leave the names as they are in Oracle, usually in upper case.

    - `lower`: translate all table and column names to lower case.

    - `smart`: only translate names that are all upper case in Oracle

               (this is the default).

 **readonly** (boolean): controls if imported tables can be modified.  

    If set to `true`, all imported tables are created with the foreign

    table option **readonly** set to `true` (see the [Options](#3-options)

    section).  

    The default is `false`.

3) pg处理schema名字时候默认转换为小写，使用双引号

例如 '"SCOTT"'

4) 使用LIMIT和EXCEPT进行限制或者排除对象进行import

Ø 语法：

Command: IMPORT FOREIGN SCHEMA

Description: import table definitions from a foreign server

Syntax: IMPORT FOREIGN SCHEMA remote_schema [ { LIMIT TO | EXCEPT } ( table_name [, ...] ) ]

FROM SERVER server_name

INTO local_schema [ OPTIONS ( option 'value' [, ... ] ) ]

5.1. IMPORT FOREIGN示例

Ø remote oracle的scott用户有如下对象

Ø 本地创建scott的schema

create schema scott;

import FOREIGN SCHEMA "SCOTT" from server oracle_91 into scott;

Ø 使用limit选择导入外部表

--例如只需要course、emp、dept三张表

import FOREIGN SCHEMA "SCOTT" limit to (course,emp,dept) from server oracle_scott_105 into scott;

Ø 使用except排除相关表

--例如排除course、emp、dept三张表，其余表都导入

import FOREIGN SCHEMA "SCOTT" except (course,emp,dept) from server oracle_scott_105 into scott;