Access Oracle database from PostgreSQL using Oracle Foreign Data Wrapper (ORACLE_FDW)

Jamsher Khan 46 mins2
()

Access Oracle database from PostgreSQL using Oracle Foreign Data Wrapper (ORACLE_FDW)

Nowadays many companies are using a mix of RDBMS systems, for example, some instances are running on Oracle and other instances are running on PostgreSQL, and if we want to access data we need to do it using a heterogeneous connection. Heterogeneous connections allow us to query data from non-Oracle databases using SQL.

In the previous 2 blog post we saw 1) Steps to create a Database Link from Oracle to PostgreSQL Click here to read more. 2) Steps to Setup a Foreign Data Wrapper(postgres_fdw) in PostgreSQL Click here to read more.
To access tables present in a remote Oracle database server from a local PostgreSQL database, we can use the oracle_fdw Foreign Data Wrapper.

For this demonstration, we’ll use the PostgreSQL DB local_db and Oracle DB test01. We will access a table employee exist in remote Oracle DB from PostgreSQL local_db.


Below are the high-level steps we will follow to set up ORACLE_FDW
1. Setup Remote Oracle DB
2. Install Oracle Instant Client
3. Download the source code for installing oracle_fdw
4. Install postgresql13-devel package
5. Install missing devtoolset* llvm-toolset-7* llvm5* rpm
6. Configure oracle_fdw package
7. Create the ORACLE_FDW extension
8. Create the Foreign Server
9. Create User Mapping
10. Grant the Local User Access to the Foreign Server
11. Create foreign Table

 

Below are setup details and the same will be used in this demonstration.

Sr. No.DB NameUsernameHostnameIPPort
1Oracle-test01fdw_usertest-machine01192.168.114.1771521
2PostgreSQL-local_dbapp_usertest-machine02192.168.114.1765432


Step 1. Setup Remote Oracle DB: In this step, we will create a user: fdw_user in the existing Oracle DB: test01 then we will create a table employee. This table we will try to access from PostgreSQL Cluster.

[oracle@test-machine01 ~]$
[oracle@test-machine01 ~]$ . oraenv
ORACLE_SID = [test01] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@test-machine01 ~]$
[oracle@test-machine01 ~]$ sqlplus sys as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Oct 1 13:34:27 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> create user fdw_user identified by secret;

User created.

SQL> grant connect,resource to fdw_user;

Grant succeeded.

SQL> grant unlimited tablespace to fdw_user;

Grant succeeded.


SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@test-machine01 ~]$
[oracle@test-machine01 ~]$ sqlplus fdw_user/secret

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Oct 1 13:39:38 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Last Successful login time: Fri Oct 01 2021 13:36:21 +03:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> SQL>
SQL>

SQL> create table employee (id int, first_name varchar(20), last_name varchar(20));

Table created.
SQL> insert into employee values (1,'jobin','augustine');

1 row created.

SQL> insert into employee values (2,'avinash','vallarapu');

1 row created.

SQL> insert into employee values (3,'fernando','camargos');

1 row created.

SQL> commit;

Commit complete.

SQL> select count(*) from employee;

  COUNT(*)
----------
         3

SQL>


Step 2. Install Oracle Instant Client in PostgreSQL: Use the below link to download: Oracle 18.5 version instant client-basic, instantclient-devel https://yum.oracle.com/repo/OracleLinux/OL7/oracle/instantclient/x86_64/index.html

oracle-instantclient18.5-devel-18.5.0.0.0-3.x86_64.rpm
oracle-instantclient18.5-basic-18.5.0.0.0-3.x86_64.rpm

Once download transfer to Server and use yum localinstall command to install both rpms. Ensure that pg_config is in your path, and ORACLE_HOME as well as LD_LIBRARY_PATH are configured. Add both environment variables in .bash_profile of postgres user.

[root@test-machine02 tmp]#
[root@test-machine02 tmp]# ls -ltr
total 55756
-rw-r--r--. 1 root   root   56417852 Oct  1 13:58 oracle-instantclient18.5-devel-18.5.0.0.0-3.x86_64.rpm
-rw-r--r--. 1 root   root     669784 Oct  1 13:58 oracle-instantclient18.5-basic-18.5.0.0.0-3.x86_64.rpm
[root@test-machine02 tmp]#

[root@test-machine02 tmp]#
[root@test-machine02 tmp]# yum localinstall oracle-instantclient18.5-basic-18.5.0.0.0-3.x86_64.rpm oracle-instantclient18.5-devel-18.5.0.0.0-3.x86_64.rpm
Loaded plugins: langpacks, ulninfo
Examining oracle-instantclient18.5-basic-18.5.0.0.0-3.x86_64.rpm: oracle-instantclient18.5-basic-18.5.0.0.0-3.x86_64
Marking oracle-instantclient18.5-basic-18.5.0.0.0-3.x86_64.rpm to be installed
Examining oracle-instantclient18.5-devel-18.5.0.0.0-3.x86_64.rpm: oracle-instantclient18.5-devel-18.5.0.0.0-3.x86_64
Marking oracle-instantclient18.5-devel-18.5.0.0.0-3.x86_64.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package oracle-instantclient18.5-basic.x86_64 0:18.5.0.0.0-3 will be installed
---> Package oracle-instantclient18.5-devel.x86_64 0:18.5.0.0.0-3 will be installed
Dependencies Resolved

=============================================================================================================================================================================
 Package                                       Arch                  Version                        Repository                                                          Size
=============================================================================================================================================================================
Installing:
 oracle-instantclient18.5-basic                x86_64                18.5.0.0.0-3                   /oracle-instantclient18.5-basic-18.5.0.0.0-3.x86_64                221 M
 oracle-instantclient18.5-devel                x86_64                18.5.0.0.0-3                   /oracle-instantclient18.5-devel-18.5.0.0.0-3.x86_64                2.0 M

Transaction Summary
=============================================================================================================================================================================
Install  2 Packages

Total size: 223 M
Installed size: 223 M
Is this ok [y/d/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : oracle-instantclient18.5-basic-18.5.0.0.0-3.x86_64                                                                                                        1/2
  Installing : oracle-instantclient18.5-devel-18.5.0.0.0-3.x86_64                                                                                                        2/2
  Verifying  : oracle-instantclient18.5-devel-18.5.0.0.0-3.x86_64                                                                                                        1/2
  Verifying  : oracle-instantclient18.5-basic-18.5.0.0.0-3.x86_64                                                                                                        2/2

Installed:
  oracle-instantclient18.5-basic.x86_64 0:18.5.0.0.0-3                                  oracle-instantclient18.5-devel.x86_64 0:18.5.0.0.0-3

Complete!
[root@test-machine02 tmp]# 

[root@test-machine02 ~]#
[root@test-machine02 ~]# su - postgres
Last login: Fri Oct  1 15:49:10 +03 2021 on pts/0
[postgres@test-machine02]
[postgres@test-machine02]vi .bash_profile
PATH=$PATH:/usr/pgsql-13/bin
export PATH
export LD_LIBRARY_PATH=/usr/lib/oracle/18.5/client64/lib
export ORACLE_HOME=/usr/lib/oracle/18.5/client64
:wq!
[postgres@test-machine02]
[postgres@test-machine02]. .bash_profile
[postgres@test-machine02]
[postgres@test-machine02]which pg_config
/usr/pgsql-13/bin/pg_config
[postgres@test-machine02]
[postgres@test-machine02]echo $LD_LIBRARY_PATH
/usr/lib/oracle/18.5/client64/lib
[postgres@test-machine02]
[postgres@test-machine02]echo $ORACLE_HOME
/usr/lib/oracle/18.5/client64
[postgres@test-machine02]


Step 3. Download the source code for installing oracle_fdw in PostgreSQL: Download the source code for installing oracle_fdw. You can download the oracle_fdw code from the below link.

Source code(zip) : https://github.com/laurenz/oracle_fdw/releases/tag/ORACLE_FDW_2_4_0

Once the download is done, transfer to PostgreSQL Server and unzip the file. We are using the make and make install commands to install oracle_fdw. The command make is used to build the software. This runs a series of tasks defined in a Makefile to build the finished program from its source code. The make install command will copy the built program and its libraries and documentation to the correct locations. As you notice while executing make command we received error “Makefile:23: /usr/pgsql-13/lib/pgxs/src/makefiles/pgxs.mk: No such file or directory“. This error is because of the missing packages postgresql13-devel.

[root@test-machine02 ~]# su - postgres
Last login: Fri Oct  1 15:49:10 +03 2021 on pts/0
[postgres@test-machine02]
[postgres@test-machine02]pwd
/tmp
[postgres@test-machine02]ls -ltr
total 55892
-rw-r--r--. 1 root   root   56417852 Oct  1 13:58 oracle-instantclient-basic-21.3.0.0.0-1.x86_64.rpm
-rw-r--r--. 1 root   root     669784 Oct  1 13:58 oracle-instantclient-devel-21.3.0.0.0-1.x86_64.rpm
-rw-r--r--. 1 root   root     137088 Oct  1 16:01 oracle_fdw-ORACLE_FDW_2_4_0.zip
[postgres@test-machine02]unzip oracle_fdw-ORACLE_FDW_2_4_0.zip
Archive:  oracle_fdw-ORACLE_FDW_2_4_0.zip
37bd40a5dd6dfc2e757635298e59de0d598a2a73
   creating: oracle_fdw-ORACLE_FDW_2_4_0/
  inflating: oracle_fdw-ORACLE_FDW_2_4_0/.gitignore
  inflating: oracle_fdw-ORACLE_FDW_2_4_0/CHANGELOG
  inflating: oracle_fdw-ORACLE_FDW_2_4_0/LICENSE
   creating: oracle_fdw-ORACLE_FDW_2_4_0/sql/
  inflating: oracle_fdw-ORACLE_FDW_2_4_0/sql/oracle_fdw.sql
  inflating: oracle_fdw-ORACLE_FDW_2_4_0/sql/oracle_gis.sql
  inflating: oracle_fdw-ORACLE_FDW_2_4_0/sql/oracle_import.sql
  inflating: oracle_fdw-ORACLE_FDW_2_4_0/sql/oracle_join.sql
finishing deferred symbolic links:
  oracle_fdw-ORACLE_FDW_2_4_0/README.md -> README.oracle_fdw
[postgres@test-machine02]
[postgres@test-machine02]ls -ltr
total 55896
drwxr-xr-x. 5 postgres postgres     4096 Sep 24 09:50 oracle_fdw-ORACLE_FDW_2_4_0
-rw-r--r--. 1 root     root     56417852 Oct  1 13:58 oracle-instantclient-basic-21.3.0.0.0-1.x86_64.rpm
-rw-r--r--. 1 root     root       669784 Oct  1 13:58 oracle-instantclient-devel-21.3.0.0.0-1.x86_64.rpm
-rw-r--r--. 1 root     root       137088 Oct  1 16:01 oracle_fdw-ORACLE_FDW_2_4_0.zip
[postgres@test-machine02]cd oracle_fdw-ORACLE_FDW_2_4_0
[postgres@test-machine02]ls -ltr
total 460
-rw-r--r--. 1 postgres postgres    313 Sep 24 09:50 TODO
drwxr-xr-x. 2 postgres postgres     98 Sep 24 09:50 sql
-rw-r--r--. 1 postgres postgres  40498 Sep 24 09:50 README.oracle_fdw
-rw-r--r--. 1 postgres postgres  99627 Sep 24 09:50 oracle_utils.c
w-r--r--. 1 postgres postgres    231 Sep 24 09:50 oracle_fdw--1.0--1.1.sql
drwxr-xr-x. 2 postgres postgres     99 Sep 24 09:50 msvc
-rw-r--r--. 1 postgres postgres   3450 Sep 24 09:50 Makefile
-rw-r--r--. 1 postgres postgres   1086 Sep 24 09:50 LICENSE
drwxr-xr-x. 2 postgres postgres     98 Sep 24 09:50 expected
-rw-r--r--. 1 postgres postgres  21900 Sep 24 09:50 CHANGELOG
lrwxrwxrwx. 1 postgres postgres     17 Oct  1 16:04 README.md -> README.oracle_fdw
[postgres@test-machine02]
postgres@test-machine02]pwd
/tmp/oracle_fdw-ORACLE_FDW_2_4_0
[postgres@test-machine02]make
Makefile:23: /usr/pgsql-13/lib/pgxs/src/makefiles/pgxs.mk: No such file or directory
make: *** No rule to make target `/usr/pgsql-13/lib/pgxs/src/makefiles/pgxs.mk'.  Stop.
[postgres@test-machine02]


Step 4. Install postgresql13-devel package in PostgreSQL: Let try to install package postgresql13-devel. But this package is depend on devtoolset* llvm-toolset-7* llvm5* rpms. So we need to install first missing rpms and then we will install package postgresql13-devel .

[root@test-machine02 ~]# yum install postgresql13-devel
Loaded plugins: langpacks, ulninfo
Resolving Dependencies
--> Running transaction check
---> Package postgresql13-devel.x86_64 0:13.4-1PGDG.rhel7 will be installed
--> Processing Dependency: postgresql13-libs(x86-64) = 13.4-1PGDG.rhel7 for package: postgresql13-devel-13.4-1PGDG.rhel7.x86_64
--> Processing Dependency: postgresql13(x86-64) = 13.4-1PGDG.rhel7 for package: postgresql13-devel-13.4-1PGDG.rhel7.x86_64
---> Package postgresql13-server.x86_64 0:13.2-1PGDG.rhel7 will be updated
---> Package postgresql13-server.x86_64 0:13.4-1PGDG.rhel7 will be an update
--> Finished Dependency Resolution
Error: Package: postgresql13-devel-13.4-1PGDG.rhel7.x86_64 (pgdg13)
           Requires: llvm5.0-devel >= 5.0
Error: Package: postgresql13-devel-13.4-1PGDG.rhel7.x86_64 (pgdg13)
           Requires: llvm-toolset-7-clang >= 4.0.1
 You could try using --skip-broken to work around the problem
 You could try running: rpm -Va --nofiles --nodigest
[root@test-machine02 ~]#


Step 5. Install missing devtoolset* llvm-toolset-7* llvm5* rpm in PostgreSQL: Use OS command wget –no-check-certificate <rpm url> to download required RPMs directly in PosrgreSQL Server. Download all listed rpm and install in the same sequence as shown below. Once all listed RPMs are installed. Try to install a package: postgresql13-devel.

[root@test-machine02 tmp]#
[root@test-machine02 tmp]# wget  --no-check-certificate https://centos.pkgs.org/7/epel-x86_64/llvm5.0-libs-5.0.1-7.el7.x86_64.rpm.html
[root@test-machine02 tmp]# wget  --no-check-certificate https://download-ib01.fedoraproject.org/pub/epel/7/x86_64/Packages/l/llvm5.0-devel-5.0.1-7.el7.x86_64.rpm
[root@test-machine02 tmp]# wget  --no-check-certificate https://download-ib01.fedoraproject.org/pub/epel/7/x86_64/Packages/l/llvm5.0-5.0.1-7.el7.x86_64.rpm
[root@test-machine02 tmp]#
[root@test-machine02 tmp]#  ls -ltr llvm5*
-rw-r--r--. 1 root root  2755155 Mar 30  2018 llvm5.0-5.0.1-7.el7.x86_64.rpm
-rw-r--r--. 1 root root  2424903 Mar 30  2018 llvm5.0-devel-5.0.1-7.el7.x86_64.rpm
-rw-r--r--. 1 root root 13960979 Oct  1 19:37 llvm5.0-libs-5.0.1-7.el7.x86_64.rpm
[root@test-machine02 tmp]#


[root@test-machine02 tmp]# wget --no-check-certificate https://buildlogs.centos.org/centos/7/sclo/x86_64/rh/Packages/l/llvm-toolset-7-clang-5.0.1-4.el7.x86_64.rpm
[root@test-machine02 tmp]# wget --no-check-certificate http://buildlogs.cdn.centos.org/centos/7/sclo/x86_64/rh/Packages/l/llvm-toolset-7-clang-libs-5.0.1-4.el7.x86_64.rpm
[root@test-machine02 tmp]# wget --no-check-certificate https://centos.pkgs.org/7/centos-sclo-rh-x86_64/llvm-toolset-7-runtime-5.0.1-4.el7.x86_64.rpm.html
[root@test-machine02 tmp]# wget --no-check-certificate http://mirror.centos.org/centos/7/sclo/x86_64/rh/Packages/l/llvm-toolset-7-compiler-rt-4.0.1-1.el7.x86_64.rpm
[root@test-machine02 tmp]# wget --no-check-certificate http://mirror.centos.org/centos/7/sclo/x86_64/rh/Packages/l/llvm-toolset-7-libomp-5.0.1-2.el7.x86_64.rpm
[root@test-machine02 tmp]# wget --no-check-certificate http://mirror.centos.org/centos/7/sclo/x86_64/rh/Packages/l/llvm-toolset-7-llvm-libs-5.0.1-8.el7.x86_64.rpm
[root@test-machine02 tmp]#
[root@test-machine02 tmp]#  ls -lr llvm-toolset*
-rw-r--r--. 1 root root  1198572 Oct  1 19:50 llvm-toolset-7-runtime-5.0.1-4.el7.x86_64.rpm
-rw-r--r--. 1 root root 13965916 Mar 12  2020 llvm-toolset-7-llvm-libs-5.0.1-8.el7.x86_64.rpm
-rw-r--r--. 1 root root   252240 Mar 12  2020 llvm-toolset-7-libomp-5.0.1-2.el7.x86_64.rpm
-rw-r--r--. 1 root root  1788824 Mar 12  2020 llvm-toolset-7-compiler-rt-5.0.1-2.el7.x86_64.rpm
-rw-r--r--. 1 root root 13568616 Oct  1 19:42 llvm-toolset-7-clang-libs-5.0.1-4.el7.x86_64.rpm
-rw-r--r--. 1 root root   557892 Oct  1 16:35 llvm-toolset-7-clang-5.0.1-4.el7.x86_64.rpm
[root@test-machine02 tmp]#


[root@test-machine02 tmp]# wget --no-check-certificate https://buildlogs.centos.org/centos/7/sclo/x86_64/rh/Packages/d/devtoolset-7-binutils-2.28-7.el7.x86_64.rpm
[root@test-machine02 tmp]# wget --no-check-certificate https://buildlogs.centos.org/centos/7/sclo/x86_64/rh/Packages/d/devtoolset-7-gcc-7.2.1-1.el7.x86_64.rpm
[root@test-machine02 tmp]# wget --no-check-certificate http://mirror.centos.org/centos/7/sclo/x86_64/rh/Packages/d/devtoolset-7-gcc-c++-7.2.1-1.el7.x86_64.rpm
[root@test-machine02 tmp]# wget --no-check-certificate http://mirror.centos.org/centos/7/sclo/x86_64/rh/Packages/d/devtoolset-7-libstdc++-devel-7.2.1-1.el7.x86_64.rpm
[root@test-machine02 tmp]# wget --no-check-certificate https://buildlogs.centos.org/centos/7/sclo/x86_64/rh/Packages/d/devtoolset-7-runtime-7.0-8.el7.x86_64.rpm
[root@test-machine02 tmp]#
[root@test-machine02 tmp]# ls -ltr devtoolset*
-rw-r--r--. 1 root root  5505848 Jul 30  2017 devtoolset-7-binutils-2.28-7.el7.x86_64.rpm
-rw-r--r--. 1 root root    19836 Oct 24  2017 devtoolset-7-runtime-7.0-8.el7.x86_64.rpm
-rw-r--r--. 1 root root 30450984 Oct 24  2017 devtoolset-7-gcc-7.2.1-1.el7.x86_64.rpm
-rw-r--r--. 1 root root  2704244 Mar 23  2020 devtoolset-7-libstdc++-devel-7.2.1-1.el7.x86_64.rpm
-rw-r--r--. 1 root root 11690356 Oct  5 08:14 devtoolset-7-gcc-c++-7.2.1-1.el7.x86_64.rpm
[root@test-machine02 tmp]#

[root@test-machine02 tmp]# wget --no-check-certificate http://mirror.centos.org/centos/7/os/x86_64/Packages/libedit-devel-3.0-12.20121213cvs.el7.x86_64.rpm
[root@test-machine02 tmp]# wget --no-check-certificate http://mirror.centos.org/centos/7/os/x86_64/Packages/ncurses-devel-5.9-14.20130511.el7_4.x86_64.rpm
[root@test-machine02 tmp]#
[root@test-machine02 tmp]# ls -ltr libedit-devel-3.0-12.20121213cvs.el7.x86_64.rpm
-rw-r--r--. 1 root root 33256 Oct  5 09:11 libedit-devel-3.0-12.20121213cvs.el7.x86_64.rpm
[root@test-machine02 tmp]#
[root@test-machine02 tmp]# ls -ltr ncurses-devel-5.9-14.20130511.el7_4.x86_64.rpm
-rw-r--r--. 1 root root 729508 Sep  7  2017 ncurses-devel-5.9-14.20130511.el7_4.x86_64.rpm
[root@test-machine02 tmp]#
[root@test-machine02 tmp]#

[root@test-machine02 tmp]#  yum localinstall devtoolset-7* llvm-toolset-7*
Loaded plugins: langpacks, ulninfo
Examining devtoolset-7-binutils-2.28-7.el7.x86_64.rpm: devtoolset-7-binutils-2.28-7.el7.x86_64
Marking devtoolset-7-binutils-2.28-7.el7.x86_64.rpm to be installed
Examining devtoolset-7-gcc-7.2.1-1.el7.x86_64.rpm: devtoolset-7-gcc-7.2.1-1.el7.x86_64

  Verifying  : libcgroup-0.41-21.el7.x86_64                                                                                                                            23/25
  Verifying  : glibc-common-2.17-317.0.1.el7.x86_64                                                                                                                    24/25
  Verifying  : glibc-2.17-317.0.1.el7.x86_64                                                                                                                           25/25

Installed:
  devtoolset-7-binutils.x86_64 0:2.28-7.el7                    devtoolset-7-gcc.x86_64 0:7.2.1-1.el7                 devtoolset-7-gcc-c++.x86_64 0:7.2.1-1.el7
  devtoolset-7-libstdc++-devel.x86_64 0:7.2.1-1.el7            devtoolset-7-runtime.x86_64 0:7.0-8.el7               llvm-toolset-7-clang.x86_64 0:5.0.1-4.el7
  llvm-toolset-7-clang-libs.x86_64 0:5.0.1-4.el7               llvm-toolset-7-libomp.x86_64 0:5.0.1-2.el7            llvm-toolset-7-llvm-libs.x86_64 0:5.0.1-8.el7

Dependency Installed:
  audit-libs-python.x86_64 0:2.8.5-4.el7                      checkpolicy.x86_64 0:2.5-8.el7                            glibc-devel.x86_64 0:2.17-324.0.1.el7_9
  glibc-headers.x86_64 0:2.17-324.0.1.el7_9                   kernel-headers.x86_64 0:3.10.0-1160.42.2.el7              libcgroup.x86_64 0:0.41-21.el7
  libmpc.x86_64 0:1.0.1-3.el7                                 libsemanage-python.x86_64 0:2.5-14.el7                    mpfr.x86_64 0:3.1.1-4.el7
  policycoreutils-python.x86_64 0:2.5-34.0.1.el7              python-IPy.noarch 0:0.75-6.el7                            setools-libs.x86_64 0:3.3.8-4.el7

Dependency Updated:
  glibc.x86_64 0:2.17-324.0.1.el7_9                                                 glibc-common.x86_64 0:2.17-324.0.1.el7_9

Complete!
[root@test-machine02 tmp]#

[root@test-machine02 tmp]# rpm -ivh llvm5.0-libs-5.0.1-7.el7.x86_64.rpm
warning: llvm5.0-libs-5.0.1-7.el7.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID 352c64e5: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:llvm5.0-libs-5.0.1-7.el7         ################################# [100%]
[root@test-machine02 tmp]#

[root@test-machine02 tmp]#
[root@test-machine02 tmp]# rpm -ivh ncurses-devel-5.9-14.20130511.el7_4.x86_64.rpm
warning: ncurses-devel-5.9-14.20130511.el7_4.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID f4a80eb5: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:ncurses-devel-5.9-14.20130511.el7################################# [100%]

 [root@test-machine02 tmp]# rpm -ivh libedit-devel-3.0-12.20121213cvs.el7.x86_64.rpm
warning: libedit-devel-3.0-12.20121213cvs.el7.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID f4a80eb5: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:libedit-devel-3.0-12.20121213cvs.################################# [100%]

 [root@test-machine02 tmp]# rpm -ivh llvm5.0-devel-5.0.1-7.el7.x86_64.rpm
warning: llvm5.0-devel-5.0.1-7.el7.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID 352c64e5: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:llvm5.0-devel-5.0.1-7.el7        ################################# [100%]
[root@test-machine02 tmp]#
[root@test-machine02 tmp]# rpm -ivh llvm5.0-5.0.1-7.el7.x86_64.rpm
warning: llvm5.0-5.0.1-7.el7.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID 352c64e5: NOKEY
Preparing...                          ################################# [100%]
        package llvm5.0-5.0.1-7.el7.x86_64 is already installed
[root@test-machine02 tmp]#
[root@test-machine02 tmp]#  yum install postgresql13-devel
Loaded plugins: langpacks, ulninfo
Resolving Dependencies
--> Running transaction check
---> Package postgresql13-devel.x86_64 0:13.4-1PGDG.rhel7 will be installed
--> Processing Dependency: postgresql13-libs(x86-64) = 13.4-1PGDG.rhel7 for package: postgresql13-devel-13.4-1PGDG.rhel7.x86_64
--> Processing Dependency: postgresql13(x86-64) = 13.4-1PGDG.rhel7 for package: postgresql13-devel-13.4-1PGDG.rhel7.x86_64
--> Processing Dependency: libicu-devel for package: postgresql13-devel-13.4-1PGDG.rhel7.x86_64
--> Running transaction check
  Verifying  : postgresql13-devel-13.4-1PGDG.rhel7.x86_64                                                                                                                2/8
  Verifying  : postgresql13-libs-13.4-1PGDG.rhel7.x86_64                                                                                                                 3/8
  Verifying  : postgresql13-13.4-1PGDG.rhel7.x86_64                                                                                                                      4/8
  Verifying  : postgresql13-server-13.4-1PGDG.rhel7.x86_64                                                                                                               5/8
  Verifying  : postgresql13-server-13.2-1PGDG.rhel7.x86_64                                                                                                               6/8
  Verifying  : postgresql13-libs-13.2-1PGDG.rhel7.x86_64                                                                                                                 7/8
  Verifying  : postgresql13-13.2-1PGDG.rhel7.x86_64                                                                                                                      8/8

Installed:
  postgresql13-devel.x86_64 0:13.4-1PGDG.rhel7

Dependency Installed:
  libicu-devel.x86_64 0:50.2-4.el7_7

Dependency Updated:
  postgresql13.x86_64 0:13.4-1PGDG.rhel7               postgresql13-libs.x86_64 0:13.4-1PGDG.rhel7               postgresql13-server.x86_64 0:13.4-1PGDG.rhel7

Complete!
[root@test-machine02 tmp]#


Step 6. Configure oracle_fdw package in PostgreSQL: Once package postgresql13-devel is installed. Again we will try to configure oracle_fdw using make and make install command. But still, make command failed with error “/bin/sh: gcc: command not found“. This is because gcc package is missing from Server. Install gcc package with yum install gcc. Once gcc package is installed try to configure oracle_fdw again using make command. This time make command executed successfully. Grant /usr/pgsql-13 directory with postgres user ownership and then execute make install command.

[root@test-machine02 tmp]# su - postgres
-bash-4.2$ cd /tmp
-bash-4.2$ cd oracle_fdw-ORACLE_FDW_2_4_0
-bash-4.2$ make
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches   -m64 -mtune=generic -fPIC -I"/usr/lib/oracle/21/client64/sdk/include" -I"/usr/lib/oracle/21/client64/oci/include" -I"/usr/lib/oracle/21/client64/rdbms/public" -I"/usr/lib/oracle/21/client64/" -I/usr/include/oracle/21/client64 -I/usr/include/oracle/19.12/client64 -I/usr/include/oracle/19.12/client -I/usr/include/oracle/19.11/client64 -I/usr/include/oracle/19.11/client -I/usr/include/oracle/19.10/client64 -I/usr/include/oracle/19.10/client -I/usr/include/oracle/19.9/client -I/usr/include/oracle/19.9/client64 -I/usr/include/oracle/19.8/client -I/usr/include/oracle/19.8/client64 -I/usr/include/oracle/19.6/client -I/usr/include/oracle/19.6/client64 -I/usr/include/oracle/19.3/client -I/usr/include/oracle/19.3/client64 -I/usr/include/oracle/18.5/client -I/usr/include/oracle/18.5/client64 -I/usr/include/oracle/18.3/client -I/usr/include/oracle/18.3/client64 -I/usr/include/oracle/12.2/client -I/usr/include/oracle/12.2/client64 -I/usr/include/oracle/12.1/client -I/usr/include/oracle/12.1/client64 -I/usr/include/oracle/11.2/client -I/usr/include/oracle/11.2/client64 -I/usr/include/oracle/11.1/client -I/usr/include/oracle/11.1/client64 -I/usr/include/oracle/10.2.0.5/client -I/usr/include/oracle/10.2.0.5/client64 -I/usr/include/oracle/10.2.0.4/client -I/usr/include/oracle/10.2.0.4/client64 -I/usr/include/oracle/10.2.0.3/client -I/usr/include/oracle/10.2.0.3/client64 -I. -I./ -I/usr/pgsql-13/include/server -I/usr/pgsql-13/include/internal  -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include  -c -o oracle_fdw.o oracle_fdw.c
/bin/sh: gcc: command not found
make: *** [oracle_fdw.o] Error 127
-bash-4.2$
-bash-4.2$

[root@test-machine02 tmp]# yum install gcc
Loaded plugins: langpacks, ulninfo
Resolving Dependencies
--> Running transaction check
---> Package gcc.x86_64 0:4.8.5-44.0.3.el7 will be installed
--> Processing Dependency: cpp = 4.8.5-44.0.3.el7 for package: gcc-4.8.5-44.0.3.el7.x86_64
--> Running transaction check
---> Package cpp.x86_64 0:4.8.5-44.0.3.el7 will be installed
--> Finished Dependency Resolution
Dependencies Resolved

=============================================================================================================================================================================
 Package                            Arch                                  Version                                            Repository                                 Size
=============================================================================================================================================================================
Installing:
 gcc                                x86_64                                4.8.5-44.0.3.el7                                   ol7_latest                                 16 M
Installing for dependencies:
 cpp                                x86_64                                4.8.5-44.0.3.el7                                   ol7_latest                                5.9 M

Transaction Summary
=============================================================================================================================================================================
Install  1 Package (+1 Dependent package)
Running transaction
  Installing : cpp-4.8.5-44.0.3.el7.x86_64                                                                                                                               1/2
  Installing : gcc-4.8.5-44.0.3.el7.x86_64                                                                                                                               2/2
  Verifying  : gcc-4.8.5-44.0.3.el7.x86_64                                                                                                                               1/2
  Verifying  : cpp-4.8.5-44.0.3.el7.x86_64                                                                                                                               2/2

Installed:
  gcc.x86_64 0:4.8.5-44.0.3.el7

Dependency Installed:
  cpp.x86_64 0:4.8.5-44.0.3.el7

Complete!
[root@test-machine02 tmp]# 

[root@test-machine02 tmp]# su - postgres
Last login: Tue Oct  5 18:20:08 +03 2021 on pts/0
-bash-4.2$
-bash-4.2$ cd /tmp
-bash-4.2$ cd oracle_fdw-ORACLE_FDW_2_4_0
-bash-4.2$
-bash-4.2$ make
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches   -m64 -mtune=generic -fPIC -I"/usr/lib/oracle/18.5/client64/sdk/include" -I"/usr/lib/oracle/18.5/client64/oci/include" -I"/usr/lib/oracle/18.5/client64/rdbms/public" -I"/usr/lib/oracle/18.5/client64/" -I/usr/include/oracle/21/client64 -I/usr/include/oracle/19.12/client64 -I/usr/include/oracle/19.12/client -I/usr/include/oracle/19.11/client64 -I/usr/include/oracle/19.11/client -I/usr/include/oracle/19.10/client64 -I/usr/include/oracle/19.10/client -I/usr/include/oracle/19.9/client -I/usr/include/oracle/19.9/client64 -I/usr/include/oracle/19.8/client -I/usr/include/oracle/19.8/client64 -I/usr/include/oracle/19.6/client -I/usr/include/oracle/19.6/client64 -I/usr/include/oracle/19.3/client -I/usr/include/oracle/19.3/client64 -I/usr/include/oracle/18.5/client -I/usr/include/oracle/18.5/client64 -I/usr/include/oracle/18.3/client -I/usr/include/oracle/18.3/cl
  -m64 -mtune=generic -fPIC -shared -o oracle_fdw.so oracle_fdw.o oracle_utils.o oracle_gis.o -L/usr/pgsql-13/lib   -L/usr/lib64/llvm5.0/lib  -L/usr/lib64 -Wl,--as-needed -Wl,-rpath,'/usr/pgsql-13/lib',--enable-new-dtags  -L"/usr/lib/oracle/18.5/client64/" -L"/usr/lib/oracle/18.5/client64/bin" -L"/usr/lib/oracle/18.5/client64/lib" -L"/usr/lib/oracle/18.5/client64/lib/amd64" -lclntsh -L/usr/lib/oracle/21/client64/lib -L/usr/lib/oracle/19.12/client64/lib -L/usr/lib/oracle/19.12/client/lib -L/usr/lib/oracle/19.11/client64/lib -L/usr/lib/oracle/19.11/client/lib -L/usr/lib/oracle/19.10/client64/lib -L/usr/lib/oracle/19.10/client/lib -L/usr/lib/oracle/19.9/client/lib -L/usr/lib/oracle/19.9/client64/lib -L/usr/lib/oracle/19.8/client/lib -L/usr/lib/oracle/19.8/client64/lib -L/usr/lib/oracle/19.6/client/lib -L/usr/lib/oracle/19.6/client64/lib -L/usr/lib/oracle/19.3/client/lib -L/usr/lib/oracle/19.3/client64/lib -L/usr/lib/oracle/18.5/client/lib -L/usr/lib/oracle/18.5/client64/lib -L/usr/lib/oracle/18.3/client/lib -L/usr/lib/oracle/18.3/client64/lib -L/usr/lib/oracle/12.2/client/lib -L/usr/lib/oracle/12.2/client64/lib -L/usr/lib/oracle/12.1/client/lib -L/usr/lib/oracle/12.1/client64/lib -L/usr/lib/oracle/11.2/client/lib -L/usr/lib/oracle/11.2/client64/lib -L/usr/lib/oracle/11.1/client/lib -L/usr/lib/oracle/11.1/client64/lib -L/usr/lib/oracle/10.2.0.5/client/lib -L/usr/lib/oracle/10.2.0.5/client64/lib -L/usr/lib/oracle/10.2.0.4/client/lib -L/usr/lib/oracle/10.2.0.4/client64/lib -L/usr/lib/oracle/10.2.0.3/client/lib -L/usr/lib/oracle/10.2.0.3/client64/lib
-bash-4.2$

[root@test-machine02 ~]# cd /usr
[root@test-machine02 usr]# ls -ld pgsql-13
drwxr-xr-x. 7 root root 67 Oct  5 09:21 pgsql-13
[root@test-machine02 usr]#
[root@test-machine02 usr]# chown -R postgres:postgres pgsql-13
[root@test-machine02 usr]# ls -ld pgsql-13
drwxr-xr-x. 7 postgres postgres 67 Oct  5 09:21 pgsql-13
[root@test-machine02 usr]#

[root@test-machine02 tmp]# su - postgres
-bash-4.2$
-bash-4.2$ cd /tmp
-bash-4.2$ cd oracle_fdw-ORACLE_FDW_2_4_0
-bash-4.2$
-bash-4.2$ make install
/bin/mkdir -p '/usr/pgsql-13/lib'
/bin/mkdir -p '/usr/pgsql-13/share/extension'
/bin/mkdir -p '/usr/pgsql-13/share/extension'
/bin/mkdir -p '/usr/pgsql-13/doc/extension'
/bin/install -c -m 755  oracle_fdw.so '/usr/pgsql-13/lib/oracle_fdw.so'
/bin/install -c -m 644 .//oracle_fdw.control '/usr/pgsql-13/share/extension/'
/bin/install -c -m 644 .//oracle_fdw--1.2.sql .//oracle_fdw--1.0--1.1.sql .//oracle_fdw--1.1--1.2.sql  '/usr/pgsql-13/share/extension/'
/bin/install -c -m 644 .//README.oracle_fdw '/usr/pgsql-13/doc/extension/'
-bash-4.2$


Step 7. Create the ORACLE_FDW extension in PostgreSQL: Once all the above steps are done. We are ready to create oracle_fdw extension using create extension command.  Please note all below commands we are executing in DB: local_db as we want to avoid using default postgres DB. But create extension command filed due to error “ERROR: could not load library “/usr/pgsql-13/lib/oracle_fdw.so”: libclntsh.so.18.1: cannot open shared object file: No such file or directory“. This error occurs as OracleClient library is not loaded properly. Follow the below steps to fix this issue.

[root@test-machine02 tmp]# su - postgres
-bash-4.2$
-bash-4.2$ psql local_db
psql (13.4)
Type "help" for help.

local_db=#
local_db=#
local_db=# create extension oracle_fdw;
ERROR:  could not load library "/usr/pgsql-13/lib/oracle_fdw.so": libclntsh.so.18.1: cannot open shared object file: No such file or directory
local_db=#

[root@test-machine02 ~]# vi /etc/ld.so.conf
include ld.so.conf.d/*.conf
/usr/lib/oracle/18.5/client64/lib
/usr/lib/oracle/18.5/client64
:wq!
[root@test-machine02 ~]# ldconfig
[root@test-machine02 ~]#
[root@test-machine02 ~]# ldconfig -p | grep libclntsh.so.18.1
        libclntsh.so.18.1 (libc6,x86-64) => /usr/lib/oracle/18.5/client64/lib/libclntsh.so.18.1
[root@test-machine02 ~]# su - postgres
Last login: Wed Oct  6 19:11:36 +03 2021 on pts/0
-bash-4.2$
-bash-4.2$ ldconfig -p | grep libclntsh.so.18.1
        libclntsh.so.18.1 (libc6,x86-64) => /usr/lib/oracle/18.5/client64/lib/libclntsh.so.18.1
-bash-4.2$
-bash-4.2$ psql local_db
psql (13.4)
Type "help" for help.

local_db=#
local_db=# create extension oracle_fdw;
CREATE EXTENSION
local_db=#
local_db=# \dx
                        List of installed extensions
    Name    | Version |   Schema   |              Description
------------+---------+------------+----------------------------------------
 oracle_fdw | 1.2     | public     | foreign data wrapper for Oracle access
 plpgsql    | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

local_db=# select * from pg_extension;
  oid  |  extname   | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
-------+------------+----------+--------------+----------------+------------+-----------+--------------
 14161 | plpgsql    |       10 |           11 | f              | 1.0        |           |
 34608 | oracle_fdw |       10 |         2200 | t              | 1.2        |           |
(2 rows)

local_db=#


Step 8. Create the Foreign Server in PostgreSQL: Now we can create a server definition. This foreign server is created using the connection details of the remote oracle DB running on host “test-machine01“. Let’s name the foreign server as “testmachine02_oracle “. Use \des to list foreign servers or query view pg_foreign_server.

local_db=#
local_db=# create server testmachine01_oracle foreign data wrapper oracle_fdw options (dbserver '//test-machine01:1521/test01' );
CREATE SERVER
local_db=#
local_db=# \des+
                                                             List of foreign servers
         Name         |  Owner   | Foreign-data wrapper | Access privileges | Type | Version |             FDW options              | Description
----------------------+----------+----------------------+-------------------+------+---------+--------------------------------------+-------------
 testmachine01_oracle | postgres | oracle_fdw           |                   |      |         | (dbserver '//test-machine01:1521/test01') |
(1 row)


local_db=# select * from pg_foreign_server;
  oid  |       srvname        | srvowner | srvfdw | srvtype | srvversion | srvacl |             srvoptions
-------+----------------------+----------+--------+---------+------------+--------+------------------------------------
 34615 | testmachine01_oracle |       10 |  34614 |         |            |        | {dbserver=//test-machine01:1521/test01}
(1 row)

local_db=#


Step 9. Create User Mapping in PostgreSQL: Create a mapping on the PostgreSQL local database: local_db user app_user to remote oracle database: test01 user fdw_user.

local_db=#
local_db=# create user mapping for app_user server testmachine01_oracle options (user 'fdw_user', password 'secret');
CREATE USER MAPPING
local_db=#
local_db=# select * from pg_user_mappings;
 umid  | srvid |       srvname        | umuser | usename  |            umoptions
-------+-------+----------------------+--------+----------+---------------------------------
 34616 | 34615 | testmachine01_oracle |  34597 | app_user | {user=fdw_user,password=secret}
(1 row)

local_db=#


Step 10. Grant the Local User Access to the Foreign Server in PostgreSQL: Grant our local user app_user access to the foreign server testmachine02_oracle. Without the below permission, you will receive the error “ERROR: permission denied for foreign server testmachine02_oracle“ if you try to create Foreign Table as we are doing in Step 12.

local_db=#
local_db=# grant usage on foreign server testmachine01_oracle to app_user;
GRANT
local_db=#


Step 11. Create a foreign table in PostgreSQL: Create a foreign table in the local_db using our app user app_user with the same structure as the remote oracle table, but with OPTIONS specifying target schema_name and table_name. Now we can use the foreign table in local_db to access remote_db table employee.

-bash-4.2$ psql -h test-machine02 -U app_user local_db
Password for user app_user:
psql (13.4)
Type "help" for help.

local_db=>
local_db=> create foreign table employee_oracle (id int, first_name varchar(20), last_name varchar(20)) server testmachine01_oracle options (schema 'fdw_user', table 'employee');
CREATE FOREIGN TABLE
local_db=>
local_db=>  select * from employee_oracle ;
 id | first_name | last_name
----+------------+-----------
  1 | jobin      | augustine
  2 | avinash    | vallarapu
  3 | fernando   | camargos
(3 rows)

local_db=>


Reference: https://www.enterprisedb.com/postgres-tutorials/using-foreign-data-wrappers-access-remote-postgresql-and-oracle-databases


This document is just for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.


Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to understand more about our pursuit.

 

Related Articles

, , , ,


Loading

How useful was this post?

Click on a star to rate it!

Average rating / 5. Vote count:

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

Jamsher Khan

Hello and welcome to DBsGuru,I’m Jamsher Khan working as Senior Oracle DBA based in KSA-Jeddah, I have working experience in Oracle DBA, SQL Server, MySql, PostgreSQL, Linux, Golden Gate, ODA.Thanks for the visits!Share Learn Grow!

2 thoughts on “Access Oracle database from PostgreSQL using Oracle Foreign Data Wrapper (ORACLE_FDW)”

  1. It is very useful. oracle_fdw will support for oracle19c client. While creating extension getting the error as could not load library “/usr/pgsql-12/lib/oracle_fdw.so error. Please suggest for resolving this.

  2. Hi Veera,

    Please make sure make and make install command executed successfully.
    Oracle InstantClient 18.3 should support Oracle 19C DB.

    Thanks & Regards
    Jamsher

Comments are closed.

Related Articles