Blog

le 31/01/2018
Jean-François Famechon
Oracle, PostgreSQL

Récupérer des données d’une base Oracle dans une base PostgreSQL

Après les données de fichiers plats, les données d’une base MsSql, on va voir comment récupérer des données de notre base Oracle favorite dans une base de données PostgreSQL.

Pour ce faire, on va une fois de plus passer par un Foreign Data Wrapper.

Ce dernier va nous permettre d’ouvrir une sorte de Db Link vers cette base Oracle à partir de notre base PostgreSQL.
L’installation se fera sur un serveur Linux.

Pré-requis

Pour permettre l’installation du FDW pour Oracle, il faut disposer de librairies particulières que l’on retrouve dans l’installation de l’InstantClient Oracle. Dans notre cas, on installera la version 12.1.0.2 du produit.
Récupérez les packages Basic et SDK de l’instantCLient voulue chez Oracle, déposez-les sur le serveur puis lancez l’installation.
Pour cela, sous root, lancez la commande suivante :

#yum localinstall oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64.rpm oracle-instantclient12.1-devel-12.1.0.2.0-1.x86_64.rpm

Positionnez l’environnement PostgreSQL voulu, dans notre cas la version 9.6.
Assurez-vous que le fichier pg_congig est bien dans le path.

#which pg_config
/usr/pgsql-9.6/bin/pg_config

Positionnez les variables ORACLE_HOME et LD_LIBRARY_PATH.

#export ORACLE_HOME=/usr/share/oracle/12.1/client64
#export LD_LIBRARY_PATH=/usr/lib/oracle/12.1/client64/lib

Positionnez l’ORACLE_HOME dans le path :

#export PATH=$PATH:/usr/share/oracle/12.1/client64

On contrôle :

#echo $PATH
/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/home/postgres/.local/bin:/home/postgres/bin:/usr/local/bin:/u01/app/common:/usr/pgsql-9.6/bin:/usr/share/oracle/12.1/client64:.

Assurez-vous que le package postgresqlXX-devel, correspondant à votre version de postgreSQL, soit bien installé.

#rpm –qa | grep postgres
postgresql96-libs-9.6.1-1PGDG.rhel7.x86_64
postgresql96-server-9.6.1-1PGDG.rhel7.x86_64
postgresql96-contrib-9.6.1-1PGDG.rhel7.x86_64
postgresql96-devel-9.6.1-1PGDG.rhel7.x86_64
postgresql96-9.6.1-1PGDG.rhel7.x86_64
postgresql96-plperl-9.6.1-1PGDG.rhel7.x86_64

Dans le cas contraire, procédez à l’installation du package.

Installation du fdw Oracle

Positionnez-vous dans le répertoire où le fichier oracle_fdw-1.5.0.zip a été déposé.

cd /home/postgres/oracle_fdw

Décompressez le fichier et positionnez-vous dans le répertoire ainsi créé.

#unzip oracle_fdw-1.5.0.zip
#cd oracle_fdw-1.5.0

On peut maintenant lancer la mise en place du fdw.

#make
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -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 -DLINUX_OOM_ADJ=0 -fpic -I/usr/lib/oracle/12.1/client64/sdk/include -I/usr/lib/oracle/12.1/client64/oci/include -I/usr/lib/oracle/12.1/client64/rdbms/public -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-9.6/include/server -I/usr/pgsql-9.6/include/internal -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include -c -o oracle_fdw.o oracle_fdw.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -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 -DLINUX_OOM_ADJ=0 -fpic -I/usr/lib/oracle/12.1/client64/sdk/include -I/usr/lib/oracle/12.1/client64/oci/include -I/usr/lib/oracle/12.1/client64/rdbms/public -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-9.6/include/server -I/usr/pgsql-9.6/include/internal -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include -c -o oracle_utils.o oracle_utils.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -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 -DLINUX_OOM_ADJ=0 -fpic -I/usr/lib/oracle/12.1/client64/sdk/include -I/usr/lib/oracle/12.1/client64/oci/include -I/usr/lib/oracle/12.1/client64/rdbms/public -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-9.6/include/server -I/usr/pgsql-9.6/include/internal -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include -c -o oracle_gis.o oracle_gis.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -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 -DLINUX_OOM_ADJ=0 -fpic -shared -o oracle_fdw.so oracle_fdw.o oracle_utils.o oracle_gis.o -L/usr/pgsql-9.6/lib -Wl,--as-needed -L/usr/lib64 -Wl,--as-needed -Wl,-rpath,'/usr/pgsql-9.6/lib',--enable-new-dtags -L/usr/lib/oracle/12.1/client64 -L/usr/lib/oracle/12.1/client64/bin -L/usr/lib/oracle/12.1/client64/lib -lclntsh -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

Puis

# make install
/usr/bin/mkdir -p '/usr/pgsql-9.6/lib'
/usr/bin/mkdir -p '/usr/pgsql-9.6/share/extension'
/usr/bin/mkdir -p '/usr/pgsql-9.6/share/extension'
/usr/bin/mkdir -p '/usr/pgsql-9.6/doc/extension'
/usr/bin/install -c -m 755 oracle_fdw.so '/usr/pgsql-9.6/lib/oracle_fdw.so'
/usr/bin/install -c -m 644 .//oracle_fdw.control '/usr/pgsql-9.6/share/extension/'
/usr/bin/install -c -m 644 .//oracle_fdw--1.1.sql .//oracle_fdw--1.0--1.1.sql '/usr/pgsql-9.6/share/extension/'
/usr/bin/install -c -m 644 .//README.oracle_fdw '/usr/pgsql-9.6/doc/extension/'

L’installation est terminée, on peut passer maintenant à la configuration du Foreign Data Wrapper dans notre base PostgreSQL.

Mise en place du FWD dans le cluster postgreSQL

Sous le user postgres
Positionnez les variables ORACLE_HOME et LD_LIBRARY_PATH avec les destinations correspondant à l’instantClient précédemment insrallé.

export ORACLE_HOME=/usr/share/oracle/12.1/client64
export LD_LIBRARY_PATH=/usr/lib/oracle/12.1/client64/lib

Dans l’idéal, insérez-les dans le fichier .bash_profile.
Arrêtez puis redémarrez votre cluster postgreSQL pour prendre en compte les nouvelles librairies.
On peut maintenant créer l’extension dans la base.

psql -d mabdd
mabdd=#CREATE EXTENSION oracle_fdw;
CREATE EXTENSION

On passe maintenant à la création du serveur de données. Dans notre exemple, on cherche à se connecter au référentiel RMAN.

CREATE SERVER refrman FOREIGN DATA WRAPPER oracle_fdw
OPTIONS (dbserver '//moserveur.domaine.name/MABDD');
GRANT USAGE ON FOREIGN SERVER reftest TO monpguser;

Dans le cas où le port d’écoute du listener n’est pas le 1521, la chaîne de connexion est la suivante :

CREATE SERVER refrman FOREIGN DATA WRAPPER oracle_fdw
OPTIONS (dbserver '//moserveur.domaine.name:1524/MABDD');
GRANT USAGE ON FOREIGN SERVER reftest TO monpguser;

On crée un mapping entre le user postgreSQL monpguser et le user Oracle rman et son mot de passe.

create user mapping for monpguser server refrman options (user 'rman', password 'rman');
CREATE USER MAPPING

On peut maintenant créer une table externe sous notre user monpguser pour accéder aux données de notre table sous Oracle .

psql -d mabdd -U monpguser
monpgusqer=# CREATE FOREIGN TABLE f_oracle_test (
monpgusqer(#           db_key       numeric ,
monpgusqer(#           dbinc_key     numeric ,
monpgusqer(#           dbid         numeric ,
monpgusqer(#           name         varchar(8),
monpgusqer(#           resetlogs_change numeric,
monpgusqer(#           resetlogs_time timestamp
monpgusqer(#       ) SERVER refrman OPTIONS (schema 'RMAN', table 'RC_DATABASE');
CREATE FOREIGN TABLE
monpgusqer=#
monpgusqer=#
monpgusqer=#
monpgusqer=# select * from f_oracle_test;
db_key   | dbinc_key |   dbid   |   name   | resetlogs_change |   resetlogs_time
-------------+-------------+------------+----------+------------------+---------------------
27979 |       27980 | 2645261322 | KELIOPDH |               1 | 2012-10-10 11:14:18
60895 |       60896 | 2039080018 | NUMPRDH |               1 | 2012-11-02 10:47:46
86792 |       86793 | 1762529405 | VLL1PRD |       281428528 | 2012-09-27 11:27:34

Voilà, on accède maintenat à notre référentiel RMAN à partir de notre base PostgreSQL.

A vous de jouer !

Jean-François Famechon

Voir tous ses articles

Blog

Récupérer des données d’une base Oracle dans une base PostgreSQL

Articles récents