DBA Hub

📋Steps in this guide1/10

How to access oracle database from postgres - DBACLASS DBACLASS

This article explain From postgres, how can we  fetch oracle db data. For this we need to use foreign data wrapper and foreign table concepts. DEMO: 1.Create oracle client on postgres server:     2. Install oracle_fdw extension on postgres server: Download the package from github – https://github.com/laurenz/oracle_fdw   [root]# export ORACLE_HOME=/var/lib/edb/app/enterprisedb/product/19.0.0/clien

postgresql configurationintermediate
by PostgreSQL
12 views
1

DEMO:

1.Create oracle client on postgres server:
Step 1
2

2. Install oracle_fdw extension on postgres server:

Download the package from github – https://github.com/laurenz/oracle_fdw

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
[root]# export ORACLE_HOME=/var/lib/edb/app/enterprisedb/product/19.0.0/client_1
[root]# export PATH=/usr/edb/as12/bin:$PATH
[root]#
[root]# cd /pgdata
[root]# cd oracle_fdw-master/
[root]# 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 -I/usr/libexec/edb-as12-icu53.1/include/ -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -I/usr/include/et -fPIC -I"/var/lib/edb/app/enterprisedb/product/19.0.0/client_2/sdk/include" -I"/var/lib/edb/app/enterprisedb/product/19.0.0/client_2/oci/include" -I"/var/lib/edb/app/enterprisedb/product/19.0.0/client_2/rdbms/public" -I"/var/lib/edb/app/enterprisedb/product/19.0.0/client_2/" -I/usr/include/oracle/21/client64 -I/usr/include/oracle/19.14/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. -I./ -I/usr/edb/as12/include/server -I/usr/edb/as12/include/internal -I/usr/libexec/edb-as12-icu53.1/include/ -I/usr/include/et -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include  -c -o oracle_utils.o oracle_utils.c
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 -I/usr/libexec/edb-as12-icu53.1/include/ -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -I/usr/include/et -fPIC -I"/var/lib/edb/app/enterprisedb/product/19.0.0/client_2/sdk/include" -I"/var/lib/edb/app/enterprisedb/product/19.0.0/client_2/oci/include" -I"/var/lib/edb/app/enterprisedb/product/19.0.0/client_2/rdbms/public" -I"/var/lib/edb/app/enterprisedb/product/19.0.0/client_2/" -I/usr/include/oracle/21/client64 -I/usr/include/oracle/19.14/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. -I./ -I/usr/edb/as12/include/server -I/usr/edb/as12/include/internal -I/usr/libexec/edb-as12-icu53.1/include/ -I/usr/include/et -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include  -c -o oracle_gis.o oracle_gis.c
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 -I/usr/libexec/edb-as12-icu53.1/include/ -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -I/usr/include/et -fPIC -shared -o oracle_fdw.so oracle_fdw.o oracle_utils.o oracle_gis.o -L/usr/edb/as12/lib  -L/usr/libexec/edb-as12-icu53.1/lib/  -L/usr/lib64/llvm5.0/lib  -L/usr/lib64 -Wl,--as-needed -Wl,-rpath,'/usr/edb/as12/lib',--enable-new-dtags  -L"/var/lib/edb/app/enterprisedb/product/19.0.0/client_2/" -L"/var/lib/edb/app/enterprisedb/product/19.0.0/client_2/bin" -L"/var/lib/edb/app/enterprisedb/product/19.0.0/client_2/lib" -L"/var/lib/edb/app/enterprisedb/product/19.0.0/client_2/lib/amd64" -lclntsh -L/usr/lib/oracle/21/client64/lib -L/usr/lib/oracle/19.14/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

[root]# make install
/bin/mkdir -p '/usr/edb/as12/lib'
/bin/mkdir -p '/usr/edb/as12/share/extension'
/bin/mkdir -p '/usr/edb/as12/share/extension'
/bin/mkdir -p '/usr/share/doc/extension'
/bin/install -c -m 755  oracle_fdw.so '/usr/edb/as12/lib/oracle_fdw.so'
/bin/install -c -m 644 .//oracle_fdw.control '/usr/edb/as12/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/edb/as12/share/extension/'
/bin/install -c -m 644 .//README.oracle_fdw '/usr/share/doc/extension/'
3

3. Get postgres system service file.

> NOTE – > If you using pg_ctl command manually to stop and start postgres cluster(instead of service), then you just need to define the variables inside bash_profile. and restart using pg_ctl . NOTE – > If you using pg_ctl command manually to stop and start postgres cluster(instead of service), then you just need to define the variables inside bash_profile. and restart using pg_ctl .

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
[rootdata]# systemctl status edb-as-12.service
● edb-as-12.service - EDB Postgres Advanced Server 12
Loaded: loaded (<span style="color: #993300; font-size: 20px;"><strong>/usr/lib/systemd/system/edb-as-12.service</strong></span>; disabled; vendor preset: disabled)
Active: active (running) since Mon 2022-04-18 18:23:33 +03; 17s ago
Main PID: 13437 (edb-postmaster)
Tasks: 12
CGroup: /system.slice/edb-as-12.service
├─13437 /usr/edb/as12/bin/edb-postmaster -D /var/lib/edb/as12/data
├─13440 postgres: logger
├─13442 postgres: checkpointer
├─13443 postgres: background writer
├─13444 postgres: walwriter
├─13445 postgres: autovacuum launcher
├─13446 postgres: stats collector
├─13447 postgres: dbms_aq launcher
├─13448 postgres: logical replication launcher

.
.
.
.............
--- as it is under /usr/lib/ location. better to copy the file to /etc/ and update the file there itself.

<strong><span style="color: #993300; font-size: 16px;">cp /usr/lib/systemd/system/edb-as-12.service /etc/systemd/system/edb-as-12.service</span></strong>
4

4.Update the service file with environment variables:

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
-bash-4.2$ cat /etc/systemd/system/edb-as-12.service
# It's not recommended to modify this file in-place, because it will be
# overwritten during package upgrades.  If you want to customize, the
# best way is to create a file "/etc/systemd/system/edb-as-12.service",
# containing
#       .include /lib/systemd/system/edb-as-12.service
#       ...make your changes here...
# For more info about custom unit files, see
# http://fedoraproject.org/wiki/Systemd#How_do_I_customize_a_unit_file.2F_add_a_custom_unit_file.3F

# Note: changing PGDATA will typically require adjusting SELinux
# configuration as well.

[Unit]
Description=EDB Postgres Advanced Server 12
After=syslog.target network.target

<strong>[Service]     
</strong>Type=notify
<span style="color: #993300;"><strong>Environment=ORACLE_HOME=/var/lib/edb/app/enterprisedb/product/19.0.0/client_1
Environment=TNS_ADMIN=/var/lib/edb/app/enterprisedb/product/19.0.0/client_1/bin
Environment=LD_LIBRARY_PATH=/var/lib/edb/app/enterprisedb/product/19.0.0/client_1/lib</strong></span>
User=enterprisedb
Group=enterprisedb
# Location of database directory
Environment=PGDATA=/var/lib/edb/as12/data
PIDFile=/var/lib/edb/as12/data/postmaster.pid

# Where to send early-startup messages from the server (before the logging
# options of postgresql.conf take effect)
# This is normally controlled by the global default set by systemd
# StandardOutput=syslog

# Disable OOM kill on the postmaster
OOMScoreAdjust=-1000

ExecStartPre=/usr/edb/as12/bin/edb-as-12-check-db-dir ${PGDATA}
ExecStart=/usr/edb/as12/bin/edb-postmaster -D ${PGDATA}
ExecReload=/bin/kill -HUP $MAINPID
KillMode=mixed
KillSignal=SIGINT

# Give a reasonable amount of time for the server to start up/shut down
TimeoutSec=300

[Install]
WantedBy=multi-user.target
5

5.Reload the daemon and restart the postgres cluster:

Code/Command (click line numbers to comment):

1
2
3
[root~]# systemctl daemon-reload
[root ~]# systemctl stop edb-as-12.service
[root ~]# systemctl start edb-as-12.service
6

6.Create the extension oracle_fdw;

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
postgres=# create extension oracle_fdw;
CREATE EXTENSION
postgres=#


postgres=# \dx oracle*
                      List of installed extensions
    Name    | Version | Schema |              Description
------------+---------+--------+----------------------------------------
 oracle_fdw | 1.2     | public | foreign data wrapper for Oracle access
(1 row)


postgres=# \dew
                        List of foreign-data wrappers
     Name     |    Owner     |      Handler       |        Validator
--------------+--------------+--------------------+--------------------------
 dblink_fdw   | enterprisedb | -                  | dblink_fdw_validator
 libpq_dblink | enterprisedb | libpq_fdw_handler  | edb_dblink_fdw_validator
 oci_dblink   | enterprisedb | oci_fdw_handler    | edb_dblink_fdw_validator
 oracle_fdw   | enterprisedb | oracle_fdw_handler | oracle_fdw_validator
(4 rows)
7

7.Create foreign server:

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
postgres=# create server ora_gamedb foreign data wrapper oracle_fdw options (dbserver '10.21.45.64:1524/AMIIFDEV');
CREATE SERVER
postgres=# \des+
                                                                   List of foreign servers
    Name    |    Owner     | Foreign-data wrapper | Access privileges | Type | Version |                      FDW options                       |
 Description
------------+--------------+----------------------+-------------------+------+---------+--------------------------------------------------------+
-------------
 ora_gamedb | enterprisedb | oracle_fdw           |                   |      |         | (dbserver '10.21.45.64:1524/AMIIFDEV')                 |

 
(2 rows)
8

8.Create user mapping:

—  provide oracle database username and password

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
postgres=#  create user mapping for enterprisedb server ora_gamedb options (USER 'dba_raj' , password 'dba_raj');
CREATE USER MAPPING


postgres=# \deu+
                       List of user mappings
   Server   |  User name   |              FDW options
------------+--------------+----------------------------------------
 ora_gamedb | enterprisedb | ("user" 'dba_raj', password 'dba_raj')
9

9.Now create the foreign table to access oracle data:

The table (EMP) structure has to be same as that of oracle database

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
postgres=#  create foreign table EMP (EMP_NAME VARCHAR2(20), EMP_ID numeric) server ora_gamedb  options  ( schema 'DBA_RAJ' , table 'EMP');
CREATE FOREIGN TABLE

postgres=# select count(*) from EMP;
 count
-------
   1
                      ^
postgres=# select * from emp;
 emp_name | emp_id
----------+--------
 KALAM    |      1
(1 row)
10

COMMON ISSUES DURING SETUP:

1. If environmental variables are not set properly, then below error. Make sure to set env values as explained in point 4 of this article. 2. If ld_library_path is not set or incorrect , then below error Make sure to set LD_LIBRARY_PATH values as explained in point 4 of this article.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
postgres=# select count(*) from EMP;
ERROR:  error connecting to Oracle: OCIEnvCreate failed to create environment handle
DETAIL:

postgres=# create extension oracle_fdw;
ERROR:  could not load library "/usr/edb/as12/lib/oracle_fdw.so": libclntsh.so.19.1: cannot open shared object file: No such file or directory

Comments (0)

Please to add comments

No comments yet. Be the first to comment!