Tales From A Lazy Fat DBA

Hi All,

Today when I received a call from my team mate where he was facing issues with PostgreSQL when trying to access a remote table that exists on an Oracle database, I I instantly recalled something similar I tried few years back using Oracle Foreign Data Wrappers (oracle_FDW). So, I tried to perform a quick demo showing steps on how to do that using FDW.

Source: EnterpriseDB 10.12.20 on x86_64-pc-linux-gnu
Target: Oracle Enterprise 12c Release 2 (12.2.0.1.0)

Software Used:
Oracle Instant Clients (Need both BASIC and DEVEL packages)
Link: https://www.oracle.com/database/technologies/instant-client/linux-x86-64-downloads.html

Oracle FDW Software: Version 2.2.1)
Link: https://pgxn.org/dist/oracle_fdw/2.2.1/

Below are the files that I have downloaded.

-rw-rw-r--.  1 enterprisedb enterprisedb 143K Aug 27 17:12 oracle_fdw-2.2.1.zip
-rw-rw-r--.  1 enterprisedb enterprisedb  51M Aug 27 17:26 oracle-instantclient12.2-basic-12.2.0.1.0-1.x86_64.rpm
-rw-rw-r--.  1 enterprisedb enterprisedb 593K Aug 27 17:27 oracle-instantclient12.2-devel-12.2.0.1.0-1.x86_64.rpm
drwxrwxr-x.  5 enterprisedb enterprisedb 4.0K Aug 27 22:43 oracle_fdw-2.2.1

[enterprisedb@fatdba ~]$
[enterprisedb@fatdba ~]$
 

Let’s install both if the two instant clients on the server.

[enterprisedb@fatdba ~]$ sudo rpm -Uvh oracle-instantclient12.2-basic-12.2.0.1.0-1.x86_64.rpm
[sudo] password for enterprisedb:
Preparing...                ########################################### [100%]
   1:oracle-instantclient12.########################################### [100%]
[enterprisedb@fatdba ~]$
[enterprisedb@fatdba ~]$
[enterprisedb@fatdba ~]$ sudo -Uvh oracle-instantclient12.2-devel-12.2.0.1.0-1.x86_64.rpm
sudo: unknown user: vh
[enterprisedb@fatdba ~]$ sudo rpm -Uvh oracle-instantclient12.2-devel-12.2.0.1.0-1.x86_64.rpm
Preparing...                ########################################### [100%]
   1:oracle-instantclient12.########################################### [100%]
[enterprisedb@fatdba ~]$
[enterprisedb@fatdba ~]$
[enterprisedb@fatdba ~]$
 

now let’s quickly check if all required libraries are created.

[oracle@fatdba client64]$ cd lib/
[oracle@fatdba lib]$ pwd
/usr/lib/oracle/12.2/client64/lib
[oracle@fatdba lib]$
[oracle@fatdba lib]$
[oracle@fatdba lib]$ ls -ll
total 216568
lrwxrwxrwx. 1 root root        21 Aug 27 22:59 libclntshcore.so -> libclntshcore.so.12.1
-rw-rwxr--. 1 root root   8033199 Jan 26  2017 libclntshcore.so.12.1
lrwxrwxrwx. 1 root root        17 Aug 27 22:59 libclntsh.so -> libclntsh.so.12.1
-rw-rwxr--. 1 root root  71638263 Jan 26  2017 libclntsh.so.12.1
-rw-rwxr--. 1 root root   2981501 Jan 26  2017 libipc1.so
-rw-rwxr--. 1 root root    539065 Jan 26  2017 libmql1.so
-rw-rwxr--. 1 root root   6568149 Jan 26  2017 libnnz12.so
lrwxrwxrwx. 1 root root        15 Aug 27 22:59 libocci.so -> libocci.so.12.1
-rw-rwxr--. 1 root root   2218687 Jan 26  2017 libocci.so.12.1
-rw-rwxr--. 1 root root 124771800 Jan 26  2017 libociei.so
-rw-rwxr--. 1 root root    158543 Jan 26  2017 libocijdbc12.so
-rw-rwxr--. 1 root root    380996 Jan 26  2017 libons.so
-rw-rwxr--. 1 root root    116563 Jan 26  2017 liboramysql12.so
-rw-r--r--. 1 root root   3984814 Jan 26  2017 ojdbc8.jar
-rw-rwxr--. 1 root root    312974 Jan 26  2017 ottclasses.zip
-rw-r--r--. 1 root root     37494 Jan 26  2017 xstreams.jar
[oracle@fatdba lib]$
 

Yup, they all are there, perfect!
Now lets set the installation library path using variable LD_LIBRARY_PATH, same as below.
This is to avoid errors i.e.

ERROR:  could not load library "/opt/edb/as10/lib/oracle_fdw.so": libclntsh.so.12.1: cannot open shared object file: No such file or directory 

[enterprisedb@fatdba oracle_fdw-2.2.1]$
[enterprisedb@fatdba oracle_fdw-2.2.1]$
[enterprisedb@fatdba oracle_fdw-2.2.1]$ export LD_LIBRARY_PATH=/usr/lib/oracle/12.2/client64/lib
[enterprisedb@fatdba oracle_fdw-2.2.1]$
 

Now let’s do a quick restart of the postgresql instance.

[enterprisedb@fatdba oracle_fdw-2.2.1]$
[enterprisedb@fatdba oracle_fdw-2.2.1]$ pg_ctl -D /opt/edb/as10/data restart -m fast
waiting for server to shut down.... done
server stopped
waiting for server to start....2020-08-27 23:06:33 CEST [3641]: [1-1] user=,db=,remote= LOG:  listening on IPv4 address "0.0.0.0", port 5444
2020-08-27 23:06:33 CEST [3641]: [2-1] user=,db=,remote= LOG:  listening on IPv6 address "::", port 5444
2020-08-27 23:06:33 CEST [3641]: [3-1] user=,db=,remote= LOG:  listening on Unix socket "/tmp/.s.PGSQL.5444"
2020-08-27 23:06:33 CEST [3641]: [4-1] user=,db=,remote= LOG:  redirecting log output to logging collector process
2020-08-27 23:06:33 CEST [3641]: [5-1] user=,db=,remote= HINT:  Future log output will appear in directory "custompg_logdir".
 done
server started
[enterprisedb@fatdba oracle_fdw-2.2.1]$
[enterprisedb@fatdba oracle_fdw-2.2.1]$
[enterprisedb@fatdba oracle_fdw-2.2.1]$ psql -d enterprisedb -U enterprisedb
psql.bin (10.12.20)
Type "help" for help.
 

All good, lets created the EXTENSION in postgresql.

enterprisedb=#
enterprisedb=#
enterprisedb=# create extension oracle_fdw;
CREATE EXTENSION
enterprisedb=#
 

Now next will create the foreign server for the remote database which I need to connect to, Oracle 12.2 in my case.
Note: Here 10.0.0.130 is the oracle_server machine IP address where Oracle is running and ‘fatdb’ is the instance name.

enterprisedb=#
enterprisedb=#
enterprisedb=#
enterprisedb=# create server oracle foreign data wrapper oracle_fdw options (dbserver '//10.0.0.130/fatdb' );
CREATE SERVER
enterprisedb=#
 

Next is to create the USER MAPPING for the specific user which I would like to access, in my case it was ‘migr’ user, and finally pass the password of the user.

enterprisedb=# create user mapping for enterprisedb server oracle options (user 'migr', password 'oracle90');
CREATE USER MAPPING
enterprisedb=#
enterprisedb=#
 

All set, you can now try to query the remote table.

enterprisedb=#
enterprisedb=# select * from migr.bigtab1;
  id  |    created_date    | lookup_id |            data
------+--------------------+-----------+----------------------------
  320 | 19-MAY-19 02:10:38 |         1 | This is some data for 320
  321 | 19-MAY-18 02:10:38 |         2 | This is some data for 321
  322 | 19-MAY-19 02:10:38 |         1 | This is some data for 322
  323 | 19-MAY-20 02:10:38 |         3 | This is some data for 323
  324 | 19-MAY-18 02:10:38 |         2 | This is some data for 324
  325 | 19-MAY-20 02:10:38 |         3 | This is some data for 325
  326 | 19-MAY-19 02:10:38 |         1 | This is some data for 326
  327 | 19-MAY-18 02:10:38 |         2 | This is some data for 327
  328 | 19-MAY-19 02:10:38 |         1 | This is some data for 328
  329 | 19-MAY-20 02:10:38 |         3 | This is some data for 329
  330 | 19-MAY-18 02:10:38 |         2 | This is some data for 330
  331 | 19-MAY-20 02:10:38 |         3 | This is some data for 331
  332 | 19-MAY-19 02:10:38 |         1 | This is some data for 332
 

Let’s do some more fun, lets try to do one insertion.

enterprisedb=#
enterprisedb=# select count(*) from migr.bigtab1;
 count
-------
  1000
(1 row)

enterprisedb=# insert into migr.bigtab1 values(1001, '19-MAY-19 02:10:38', 4, 'This is some data for prashant');
INSERT 0 1
enterprisedb=# select count(*) from migr.bigtab1;
 count
-------
  1001
(1 row)

enterprisedb=# select * from migr.bigtab1 where id=1001;
  id  |    created_date    | lookup_id |              data
------+--------------------+-----------+--------------------------------
 1001 | 19-MAY-19 02:10:38 |         4 | This is some data for prashant
(1 row)

enterprisedb=#
enterprisedb=#
 

Hope It Helps
Prashant Dixit