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
Like this:
Like Loading...
