0

La migration Oracle vers PostgreSQL avec ora2pg

twitterlinkedinmail

Hello

nous avons eu récemment, dans le cadre de notre infogérance, à effectuer une migration base de données Oracle 11g vers une instance PostgreSQL version 10.5 pour un de nos clients. Celui ci souhaitait faire évoluer vers le monde “libre” une de ses base de données applicatives supportant PostgreSQL.

A vrai dire, de telles opérations sont loin d’être nouvelles, et de nombreux clients se sont déjà intéressés à cette problématique. Leur première motivation étant très certainement d’ordre pécuniaire au regard du licensing Oracle.

C’est pourquoi dès 2001, un développeur grenoblois du nom de Gilles Darold, aujourd’hui chez Dalibo,  va se pencher sur un nouvel outil afin de faciliter les diverses tâches de migration SGBD entre Oracle et PostgreSQL, c’est la naissance de “ora2pg”. Un outil qui s’avère en perpétuel évolution, nous en sommes à la version 20.0 depuis janvier 2019 comme l’indique le site Web officiel.

L’idée de cet article n’étant pas uniquement de décrire une installation et l’utilisation de l’outil, le site de Gilles Darold en fait une très bonne illustration, tout comme de nombreux autres posts de DBA que vous pourrez trouver sur le Web, mais de refléter les étapes primordiales à ne pas oublier, et les soucis que nous avons pu rencontrés et comment y remédier. C’est donc un vrai “test-case” que nous présentons à ce sujet.

 

 

Les prérequis d’installation

Comme pour tout outil utilisant des connexions client/serveur, il sera nécessaire d’installer des packages et outils afin d’utiliser ora2pg.
Dans notre exemple, nous utiliserons un serveur Debian GNU/Linux 9 (stretch). Nous avons, au préalable, installer une instance PostgreSQL 10.5 avec le compte OS postgres.

L’utilisation de ora2pg nécessite les packages suivants sur le serveur hébergeant l’instance PostgreSQL :

  •   Perl
  •   instantclient-basic-linux.x64-12.2.0.1.0.zip
  •   instantclient-sqlplus-linux.x64-12.2.0.1.0.zip
  •   instantclient-tools-linux.x64-12.2.0.1.0.zip
  •   instantclient-sdk-linux.x64-12.2.0.1.0.zip
  •   DBI
  •   DBD::Oracle
  •   DBD::Pg (pas utile pour notre exemple, mais nécessaire pour faire du chargement direct Oracle->PG)

Ne pas oublier les librairies liés à sdk pour l’instant client Oracle car nous avons rencontré l’erreur suivante à la compilation de DBD::Oracle

postgres$ make
…….
Oracle.h:37:17: fatal error: oci.h: Aucun fichier ou dossier de ce type
#include <oci.h>

Etant donné que ces librairies sont écrites en C , il faudra disposer d’un compilateur c (type gcc) sur la machine linux.

Il faudra aller télécharger les binaires Oracle directement sur le site .

Installation DBD::Oracle et DBI

Voici un récapitulatif des commandes à lancer pour l’installation de ces packages. Avant tout, il faudra sourcer les variables d’environnement liées au client Oracle, et à la connexion SQL Net vers la base de données. Nous irons donc définir un fichier TNSNAMES.ORA dans lequel les informations de la base Oracle source seront écrites. L’outil “ora2pg” pourra s’y connecter.

postgres@:~/ora2pg/DBD-Oracle-1.80$ export ORACLE_HOME=/opt/oracle/instantclient_12_2/
postgres@:~/ora2pg/DBD-Oracle-1.80$ export TNS_ADMIN=/opt/oracle/instantclient_12_2/
postgres@:~/ora2pg/DBD-Oracle-1.80$ export PATH=$ORACLE_HOME:$PATH
postgres@:~/ora2pg/DBD-Oracle-1.80$ export LD_LIBRARY_PATH=$ORACLE_HOME:$LD_LIBRARY_PATH

Le téléchargement de DBD::Oracle et DBI peut se faire directement via CPAN, qui est un module d’exploration et de téléchargement de packages sous Perl. Une fois installé, il faudra compiler les packages.

root@:~# perl -MCPAN -e shell
Terminal does not support AddHistory.

cpan shell -- CPAN exploration and modules installation (v2.1101)
Enter 'h' for help.

cpan[1]> get DBD::Oracle
Reading '/root/.cpan/Metadata'
  Database was generated on Thu, 01 Aug 2019 07:56:17 GMT
Fetching with LWP:
Writing /root/.cpan/Metadata
Running get for module 'DBD::Oracle'
Checksum for /root/.cpan/sources/authors/id/M/MJ/MJEVANS/DBD-Oracle-1.80.tar.gz ok
Scanning cache /root/.cpan/build for sizes
............................................................................DONE

Faire de même pour le téléchargement de DBI

cpan[2]> get DBI
Running get for module 'DBI'
Checksum for /root/.cpan/sources/authors/id/T/TI/TIMB/DBI-1.642.tar.gz ok

Il faudra ensuite, pour DBI et DBD::Oracle , lancer la compilation de ces packages et leur installation (exemple pour DBI).

root@:~# cd /root/.cpan/sources/authors/id/T/TI/TIMB
root@:~/.cpan/sources/authors/id/T/TI/TIMB# ls -l
total 624
-rw-r--r-- 1 root root  30621 août   1 11:42 CHECKSUMS
-rw-r--r-- 1 root root 604581 août   1 11:42 DBI-1.642.tar.gz
root@:~/.cpan/sources/authors/id/T/TI/TIMB# tar xvf DBI-1.642.tar.gz
root@:~/.cpan/sources/authors/id/T/TI/TIMB# cd  DBI-1.642
root@:~/.cpan/sources/authors/id/T/TI/TIMB/DBI-1.642# perl Makefile.PL
root@:~/.cpan/sources/authors/id/T/TI/TIMB/DBI-1.642# make
root@:~/.cpan/sources/authors/id/T/TI/TIMB/DBI-1.642# make install

Installation ora2pg

 

Les sources de la dernière version de ora2pg sont disponibles sur github ici. Il sera possible d’opter pour une version antérieure, mais, pour notre exemple, nous avons choisi la dernière en date qui est la 20.0.

Une fois ces sources poussées sur notre serveur Debian, la suite consistera à lancer le script Perl pour générer les scripts unix, les compiler puis les installer (comme nous l’avons fait pour DBI et DBD::Oracle).

postgres@:~/ora2pg$ cd ora2pg-20.0
postgres@:~/ora2pg/ora2pg-20.0$ perl Makefile.PL
Checking if your kit is complete...
Looks good
Generating a Unix-style Makefile
Writing Makefile for Ora2Pg
Writing MYMETA.yml and MYMETA.json
Done...
------------------------------------------------------------------------------
Please read documentation at http://ora2pg.darold.net/ before asking for help
------------------------------------------------------------------------------
Now type: make && make install

Le script nous demande de passer un make puis un make install.

postgres@:~/ora2pg/ora2pg-20.0$ make
cp lib/Ora2Pg/PLSQL.pm blib/lib/Ora2Pg/PLSQL.pm
cp lib/Ora2Pg.pm blib/lib/Ora2Pg.pm
cp lib/Ora2Pg/GEOM.pm blib/lib/Ora2Pg/GEOM.pm
cp lib/Ora2Pg/MySQL.pm blib/lib/Ora2Pg/MySQL.pm
cp scripts/ora2pg blib/script/ora2pg
"/usr/bin/perl" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/ora2pg
cp scripts/ora2pg_scanner blib/script/ora2pg_scanner
"/usr/bin/perl" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/ora2pg_scanner
Manifying 1 pod document

Attention, le make install devra être passé en “root” car nous avons rencontré des erreurs de type “permission denied” sur certains fichiers (dans /etc notamment).

root@:/var/lib/postgresql/ora2pg/ora2pg-20.0# make install
Manifying 1 pod document
Installing /usr/local/share/perl/5.24.1/Ora2Pg.pm
Installing /usr/local/share/perl/5.24.1/Ora2Pg/PLSQL.pm
Installing /usr/local/share/perl/5.24.1/Ora2Pg/GEOM.pm
Installing /usr/local/share/perl/5.24.1/Ora2Pg/MySQL.pm
Installing /usr/local/man/man3/ora2pg.3
Installing /usr/local/bin/ora2pg
Installing /usr/local/bin/ora2pg_scanner
Installing default configuration file (ora2pg.conf.dist) to /etc/ora2pg
Appending installation info to /usr/local/lib/x86_64-linux-gnu/perl/5.24.1/perllocal.pod

On donne les droits au user postgres pour /etc/ora2pg

root@:/var/lib/postgresql/ora2pg/ora2pg-20.0# chown -R postgres:postgres /etc/ora2pg

Afin de s’assurer de la bonne connexion avec la base Oracle, nous irons faire un test via l’instant client de notre serveur Debian. Attention, ne pas oublier de sourcer les variables comme effectué au 1er paragraphe

postgres@:~/ora2pg/DBD-Oracle-1.80$ export ORACLE_HOME=/opt/oracle/instantclient_12_2/ 
postgres@:~/ora2pg/DBD-Oracle-1.80$ export TNS_ADMIN=/opt/oracle/instantclient_12_2/ 
postgres@:~/ora2pg/DBD-Oracle-1.80$ export PATH=$ORACLE_HOME:$PATH 
postgres@:~/ora2pg/DBD-Oracle-1.80$ export LD_LIBRARY_PATH=$ORACLE_HOME:$LD_LIBRARY_PATH

Nous avons défini notre base nommée “SRC_DB” dans notre tnsnames.ora

postgres@:~$ sqlplus capdata@SRC_DB
SQL*Plus: Release 12.2.0.1.0 Production on Thu Aug 1 16:29:29 2019
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Enter password: ********
Connecte a :
Oracle Database 11g Release 11.1.0.7.0 - 64bit Production
SQL> exit
Deconnecte de Oracle Database 11g Release 11.1.0.7.0 - 64bit Production

Nous validons la connexion SQL Net vers la base Oracle, nous pouvons donc passer à l’étape configuration de ora2pg

 

Configuration ora2pg

Le fichier ora2pg.conf

Durant l’installation de ora2pg, un fichier a été généré dans /etc/ora2pg nommé “ora2pg.conf.dist”.
Il est important de conserver celui ci et d’en faire une copie que l’on pourra éditer. Il sera question notamment dans ce fichier de renseigner les variables liés à la connexion Oracle.
Nous rappelons que notre base source Oracle se nomme SRC_DB et que notre user est capdata. Nous exportons les données du schéma Oracle nommé REF.

Attention dans le ORACLE_DSN, à bien renseigner le SERVICE_NAME de la base et non le SID.

postgres@:~$ cp /etc/ora2pg/ora2pg.conf.dist /etc/ora2pg/ora2pg.conf.REF
postgres@:~$ vi /etc/ora2pg/ora2pg.conf.REF
...
ORACLE_HOME     /opt/oracle/instantclient_12_2/
ORACLE_DSN      dbi:Oracle:host=HOSTDB;sid=SRC_DB;port=1521
ORACLE_USER     capdata
ORACLE_PWD      ********
SCHEMA  GBMREF
USE_RESERVED_WORDS      1

Un test pour vérifier notre connexion à la base Oracle. Attention, s’assurer que les variables d’environnement propres à Oracle (ORACLE_HOME, TNS_ADMIN…) sont bien configurées.

postgres@:~/ora2pg$ ora2pg -t SHOW_VERSION -c /etc/ora2pg/ora2pg.conf.REF
Oracle Database 11g Release 11.1.0.7.0

Estimation du coût de migration

Il sera possible d’estimer le coût de la migration via l’option SHOW REPORT et –estmate_cost de ora2pg. Cela va nous indiquer si des tâches manuelles devront être effectuées en fin de migration.

postgres@S:~/ora2pg$ ora2pg -t SHOW_REPORT --estimate_cost -c /etc/ora2pg/ora2pg.conf.REF
[========================>] 89/89 tables (100.0%) end of scanning.
[========================>] 11/11 objects types (100.0%) end of objects auditing.
-------------------------------------------------------------------------------
Ora2Pg v20.0 - Database Migration Report
-------------------------------------------------------------------------------
Version Oracle Database 11g Release 11.1.0.7.0
Schema  REF
Size    6351.56 MB
-------------------------------------------------------------------------------
Object  Number  Invalid Estimated cost  Comments        Details
-------------------------------------------------------------------------------
DATABASE LINK   0       0       0       Database links will be exported as SQL/MED PostgreSQL's Foreign Data Wrapper (FDW) extensions using oracle_fdw.
GLOBAL TEMPORARY TABLE  0       0       0       Global temporary table are not supported by PostgreSQL and will not be exported. You will have to rewrite some application code to match the PostgreSQL temporary table behavior.
INDEX   50      0       6.7     17 index(es) are concerned by the export, others are automatically generated and will do so on PostgreSQL. Bitmap will be exported as btree_gin index(es) and hash index(es) will be exported as b-tree index(es) if any. Domain index are exported as b-tree but commented to be edited to mainly use FTS. Cluster, bitmap join and IOT indexes will not be exported at all. Reverse indexes are not exported too, you may use a trigram-based index (see pg_trgm) or a reverse() function based index and search. Use 'varchar_pattern_ops', 'text_pattern_ops' or 'bpchar_pattern_ops' operators in your indexes to improve search with the LIKE operator respectively into varchar, text or char columns.  17 b-tree index(es).
JOB     0       0       0       Job are not exported. You may set external cron job with them.
PACKAGE BODY    1       0       113.6   Total size of package code: 11374 bytes. Number of procedures and functions found inside those packages: 8.    
SEQUENCE        16      0       1.6     Sequences are fully supported, but all call to sequence_name.NEXTVAL or sequence_name.CURRVAL will be transformed into NEXTVAL('sequence_name') or CURRVAL('sequence_name').
SYNONYM 6       0       1       SYNONYMs will be exported as views. SYNONYMs do not exists with PostgreSQL but a common workaround is to use views or set the PostgreSQL search_path in your session to access object outside the current schema.    
TABLE   89      0       8.9             Total number of rows: 23632338. Top 10 of tables sorted by number of rows:. 
TRIGGER 17      0       68.8    Total size of trigger code: 6054 bytes. 
VIEW    10      0       10      Views are fully supported but can use specific functions.
-------------------------------------------------------------------------------
Total   189     0       210.60  210.60 cost migration units means approximatively 3 man-day(s). The migration unit was set to 5 minute(s)
-------------------------------------------------------------------------------
Migration level : B-5
-------------------------------------------------------------------------------
Migration levels:
    A - Migration that might be run automatically
    B - Migration with code rewrite and a human-days cost up to 5 days
    C - Migration with code rewrite and a human-days cost above 5 days
Technical levels:
    1 = trivial: no stored functions and no triggers
    2 = easy: no stored functions but with triggers, no manual rewriting
    3 = simple: stored functions and/or triggers, no manual rewriting
    4 = manual: no stored functions but with triggers or views with code rewriting
    5 = difficult: stored functions and/or triggers with code rewriting
-------------------------------------------------------------------------------

Dans notre exemple, nous sommes dans un niveau B5, nous aurons donc du code à réécrire dans certaines procédures et/ou triggers.

Initialisation de l’arborescence du projet

Afin de classer les différents objets (tables, indexes, triggers….) à exporter, la commande suivante sera à lancer pour créer les répertoires qui constitueront l’arborescence complète du schéma. A noter que le nom du schéma à exporter constitue le nom du projet (ici projet REF)

postgres@:~/ora2pg$ ora2pg --project_base /var/lib/postgresql/ora2pg --init_project REF --conf /etc/ora2pg/ora2pg.conf.REF
Creating project REF.
/var/lib/postgresql/ora2pg/REF/
        schema/
                dblinks/
                directories/
                functions/
                grants/
                mviews/
                packages/
                partitions/
                procedures/
                sequences/
                synonyms/
                tables/
                tablespaces/
                triggers/
                types/
                views/
        sources/
                functions/
                mviews/
                packages/
                partitions/
                procedures/
                triggers/
                types/
                views/
        data/
        config/
        reports/
Generating generic configuration file
Creating script export_schema.sh to automate all exports.
Creating script import_all.sh to automate all imports.

C’est à cette étape que ora2pg crée les 2 fichiers principaux que sont “export_schema.sh” et “import_all.sh” et qui constituent les opérations d’export des données Oracle et l’import des données vers l’instance PostgreSQL.

postgres@:~/ora2pg/REF$ ls -l
total 40
drwxr-xr-x  2 postgres postgres  4096 août   8 14:48 config
drwxr-xr-x  2 postgres postgres  4096 août   8 15:02 data
-rwx------  1 postgres postgres  2010 août   1 16:51 export_schema.sh
-rwx------  1 postgres postgres 16061 août   1 16:51 import_all.sh
drwxr-xr-x  2 postgres postgres  4096 août   5 14:46 reports
drwxr-xr-x 17 postgres postgres  4096 août   1 16:51 schema
drwxr-xr-x 10 postgres postgres  4096 août   1 16:51 sources

Export du schéma Oracle

C’est ce script “export_schema.sh” qui devra être lancé. Ce script se charge de générer les DDL de création des objets du schéma REF avec les conversions Oracle->PosgreSQL adaptées. Aucun paramètre n’est à renseigner.

postgres@:~/ora2pg/GBMREF$ ./export_schema.sh
[========================>] 89/89 tables (100.0%) end of scanning.
[========================>] 11/11 objects types (100.0%) end of objects auditing.
Running: ora2pg -p -t TABLE -o table.sql -b ./schema/tables -c ./config/ora2pg.conf
[========================>] 89/89 tables (100.0%) end of scanning.
[========================>] 89/89 tables (100.0%) end of table export.
Running: ora2pg -p -t PACKAGE -o package.sql -b ./schema/packages -c ./config/ora2pg.conf
[========================>] 1/1 packages (100.0%) end of output.
Running: ora2pg -p -t VIEW -o view.sql -b ./schema/views -c ./config/ora2pg.conf
[========================>] 10/10 views (100.0%) end of output.
Running: ora2pg -p -t GRANT -o grant.sql -b ./schema/grants -c ./config/ora2pg.conf
Running: ora2pg -p -t SEQUENCE -o sequence.sql -b ./schema/sequences -c ./config/ora2pg.conf
[========================>] 16/16 sequences (100.0%) end of output.
.......

To extract data use the following command:
ora2pg -t COPY -o data.sql -b ./data -c ./config/ora2pg.conf

On remarque que ce script utilise un nouveau fichier de config qui est “./config/ora2pg.conf“, celui ci est en fait copié depuis la source “/etc/ora2pg/ora2pg.conf.REF“. Chaque commande passée est mentionné au fur et à mesure qu’elle est lancée par le script shell “export_schema.sh“.

A la fin, le script donne la commande que nous devrons lancer pour importer les données dans les tables. Cette partie étant la plus longue, il sera peut être nécessaire de la lancer en “nohup” pour ne pas être dérangé par une éventuelle déconnexion. Le mode COPY pour PostgreSQL est généré (ce qui s’apparente à peu de chose près à du SQL Loader Oracle).

Il sera également possible de paralléliser cette opération en passant le paramètre –parallel dans la commande.

postgres@S:~/ora2pg/REF$ nohup ora2pg -t COPY -o data.sql -b ./data -c ./config/ora2pg.conf --parallel 2 &
postgres@S:~/ora2pg/REF$ tail -f nohup.out
[========================>] 89/89 tables (100.0%) end of scanning.
[========================>] 13174/11605 rows (113.5%) Table JOB (13174 recs/sec)
[>                        ]    13174/23632338 total rows (0.1%) - (1 sec., avg: 13174 recs/sec).
[========================>] 2794/2397 rows (116.6%) Table CHANGE (2794 recs/sec)
[>                        ]    15968/23632338 total rows (0.1%) - (1 sec., avg: 15968 recs/sec).
[========================>] 703/683 rows (102.9%) Table DEFAULT (703 recs/sec)
......

Il faudra bien suivre le résultat de cette log et vérifier les éventuelles erreurs. Pour notre cas, nous avons rencontré l’erreur suivante lors de l’export des données d’une table

DBD::Oracle::st fetchall_arrayref failed: ORA-24345: A Truncation or null fetch error occurred (DBD SUCCESS_WITH_INFO: OCIStmtFetch, LongReadLen too small and/or LongTruncOk no
ERROR no statement executing (perhaps you need to call execute first) [for Statement "SELECT "MOMENT","PID","ROOT_PID","FATHER_PID","PROJECT","JOB","CONTEXT","PRIORITY","TYPE",",
"CODE","MESSAGE" FROM "REF"."LOGS" a"] at /usr/local/share/perl/5.24.1/Ora2Pg.pm line 14110.
ERROR: ORA-24345: A Truncation or null fetch error occurred (DBD SUCCESS_WITH_INFO: OCIStmtFetch, LongReadLen too small and/or LongTruncOk not set)
ERROR no statement executing (perhaps you need to call execute first)

Après recherches, nous nous sommes aperçus que c’est le type CLOB d’un champ de cette table LOGS qui pose souci :

SQL> DESC REF.LOGS
 Nom                                       NULL ?   Type
 ----------------------------------------- -------- ----------------------------
 MOMENT                                             DATE
 PID                                                VARCHAR2(20)
 ROOT_PID                                           VARCHAR2(20)
 FATHER_PID                                         VARCHAR2(20)
 PROJECT                                            VARCHAR2(50)
 JOB                                                VARCHAR2(255)
 CONTEXT                                            VARCHAR2(50)
 PRIORITY                                           NUMBER(38)
 TYPE                                               VARCHAR2(255)
 ORIGIN                                             VARCHAR2(255)
 OLD_MESSAGE                                        VARCHAR2(4000)
 CODE                                               NUMBER(38)
 MESSAGE                                            CLOB


C’est la que les spécificités du fichier “ora2pg.conf” rentrent en jeu. En suivant la note de Gilles Darold à ce sujet, nous pourrons adapter le fichier de conf afin de modifier la taille maximale de la colonne à exporter pour cette table :

# Taille max de la colonne sur le schéma Oracle : 

SQL> select max(length(MESSAGE)) from REF.LOGS ;
MAX(LENGTH(MESSAGE))
--------------------
20514748

# La taille maximale de cette colonne est de 20Mo. On modifie le ora2pg.conf pour n'exporter que la table qui nous intéresse.

postgres@:~/ora2pg/REF$ vi /var/lib/postgresql/ora2pg/REF/config/ora2pg.conf
...
ALLOW           LOGS
...
LONGREADLEN     20971520

Puis on relance l’export uniquement pour cette table dans un autre fichier sql nommé “data_LOGS.sql”

postgres@:~/ora2pg/REF$ ora2pg -t COPY -o data_LOGS.sql -b ./data -c ./config/ora2pg.conf
[========================>] 1/1 tables (100.0%) end of scanning.
[========================>] 96090/66680 rows (144.1%) Table LOGS (154 recs/sec)
[========================>] 96090/66680 total rows (144.1%) - (623 sec., avg: 154 recs/sec).
[========================>] 66680/66680 rows (100.0%) on total estimated data (623 sec., avg: 107 recs/sec)

Les DDL ainsi générés pour les objets seront classés dans leur répertoire:

postgres@:~/ora2pg/GBMREF/schema$ tree -a
.
├── dblinks
├── directories
├── functions
├── grants
│   └── grant.sql
├── mviews
├── packages
│   └── package.sql
├── partitions
├── procedures
├── sequences
│   └── sequence.sql
├── synonyms
│   └── synonym.sql
├── tables
│   └── table.sql
├── tablespaces
│   └── tablespace.sql
├── triggers
│   └── trigger.sql
├── types
└── views
    └── view.sql
15 directories, 8 files

Post export

Nous pouvons voir que les noms des objets ne sont pas préfixés dans les SQL générés.
Dans le fichier “table.sql”, il est fortement recommandé de sortir les ordres de création des index et des contraintes.

postgres@S:~/ora2pg/REF$ cp schema/tables/table.sql schema/tables/table.SAV
postgres@S:~/ora2pg/REF$ cat schema/tables/table.SAV | grep -vE '^ALTER | INDEX ' > schema/tables/tables_ONLY.sql
postgres@S:~/ora2pg/REF$ cat schema/tables/table.SAV | grep ' INDEX ' > schema/tables/indexes_ONLY.sql
postgres@S:~/ora2pg/REF$ cat schema/tables/table.SAV | grep '^ALTER ' > schema/tables/constraints_ONLY.sql
postgres@S:~/ora2pg/REF$ cp schema/tables/tables_ONLY.sql schema/tables/table.sql


Import vers PostgreSQL

Pour la partie import des données, il faut lancer le script  “import_all.sh” auquel nous répondrons par Y ou N aux différentes actions demandées.
L’avantage de ce script est qu’il est séquentiel, les ordres sont lancées de façon indépendante, en fonction de la réponse à la question.
Il sera donc possible de repasser celui ci plusieurs fois au cas ou on souhaite passer une étape, il suffira de passer une question en répondant N à celle ci.

L’autre avantage est que l’ordre SQL, encapsulé dans l’outil “psql”, nous est affiché  à chacune des étapes.
Le script “import_all.sh” prend en paramètre 3 variables :

  • -d pour le nom de la base PostgreSQL vers laquelle on souhaite importer
  • -o pour définir le owner des objets
  • -n pour définir le schéma pour les objets (PostgreSQL fait une différence schema et owner contrairement à Oracle)

Au premier lancement, on suppose que notre base n’existe pas encore, nous aurons donc des messages d’erreurs à ce sujet.

postgres@S:~/ora2pg/REF$ ./import_all.sh -d CAPDATA_REF -o REF_USER -n REF_USER
psql: FATAL:  la base de données « CAPDATA_REF » n'existe pas

Would you like to create the owner of the database REF_USER ? [y/N/q] y
Running: createuser --no-superuser --no-createrole --no-createdb REF_USER 
psql: FATAL:  la base de données « CAPDATA_REF » n'existe pas

Would you like to create the database CAPDATA_REF ? [y/N/q] y
Running: createdb -E UTF8 --owner REF_USER CAPDATA_REF 

Would you like to create schema REF_USER in database CAPDATA_REF ? [y/N/q] y
Running: psql -U REF_USER -d CAPDATA_REF -c "CREATE SCHEMA REF_USER ;"
CREATE SCHEMA

Would you like to change search_path of the database owner? [y/N/q] y
Running: psql -d CAPDATA_REF -c "ALTER ROLE REF_USER SET search_path TO REF_USER ,public;"
ALTER ROLE

Les premières questions portant sur la structure physique “database/owner” étant passées, l’outil “import_all.sh” va alors continuer sur la partie objets bases de données à importer dans les questions suivantes.

Would you like to import TABLE from ./schema/tables/table.sql? [y/N/q] y
Running: psql --single-transaction  -U REF_USER -d CAPDATA_REF -f ./schema/tables/table.sql
SET
CREATE TABLE
CREATE TABLE
CREATE TABLE
.....

Attention, nous ne créerons pas les packages et les triggers étant donné que l’on sait que l’on aura des corrections à effectuer au niveau du code (cf : estimation avant export).
Pas de grants ni de synonyms car nous utiliserons uniquement notre user/schema nommé REF_USER. Nous ne créerons pas non plus les index et contraintes, nous utiliserons les scripts générés en “post export”.

 

Would you like to import PACKAGE from ./schema/packages/package.sql? [y/N/q] n

Would you like to import VIEW from ./schema/views/view.sql? [y/N/q] y
Running: psql --single-transaction  -U REF_USER -d CAPDATA_REF -f ./schema/views/view.sql
SET
CREATE VIEW
CREATE VIEW
CREATE VIEW
....

Would you like to import SEQUENCE from ./schema/sequences/sequence.sql? [y/N/q] y
Running: psql --single-transaction  -U REF_USER -d CAPDATA_REF -f ./schema/sequences/sequence.sql
SET
CREATE SEQUENCE
CREATE SEQUENCE
CREATE SEQUENCE
CREATE SEQUENCE
CREATE SEQUENCE
CREATE SEQUENCE
CREATE SEQUENCE
....

Would you like to import TRIGGER from ./schema/triggers/trigger.sql? [y/N/q] n

Would you like to import SYNONYM from ./schema/synonyms/synonym.sql? [y/N/q] n
-
Would you like to process indexes and constraints before loading data? [y/N/q] n

Would you like to process GRANTS from ./schema/grants/grant.sql? [y/N/q] n

Would you like to import TABLESPACE from ./schema/tablespaces/tablespace.sql? [y/N/q] n

 

A partir de là, nous attaquons la partie data. Il est impératif de bien formater l’outil “psql”, le plus simple pour le script “import_all.sh” étant de définir un fichier nommé “.psqlrc” dans le $HOME du user “postgres” linux. Ajouter les valeurs suivantes :

\set ON_ERROR_STOP on
\pset null ‘NULL’
\set VERBOSITY verbose

Nous avons rencontré au premier lancement d’un chargement du fichier “data.sql” de très nombreuses erreurs “”erreur \N invalide“” car certains codes “\N” indiquant une valeur null sont mal interprétés par psql si les valeurs ci dessus ne sont pas positionnées.
Durant cette opération, les données pures de nos tables seront réimportées en blocs (COPY) sauf notre table LOGS car nous l’avons exporté dans un autre fichier nommé “data_LOGS.sql”. Nous pourrons donc l’importer dans un second temps.

A noter que les données sont contenues dans un bloc BEGIN , c’est à dire une seule transaction, il faut donc bien prendre en considération le fait que toutes les données ne seront présentes dans les tables, qu’une fois le bloc terminé avec succès et l’ordre COMMIT enregistré.

Would you like to import data from ./data/data.sql? [y/N/q] y
Running: psql -U REF_USER -d CAPDATA_REF -f ./data/data.sql
BEGIN
COPY 10000
COPY 3174
COPY 2794
COPY 703
COPY 275
COPY 359
COPY 2106
COPY 155
COPY 320
COPY 465
COPY 125
COPY 4801
COPY 209
COPY 0
COPY 36
COPY 14
COPY 104
COPY 696
....
COMMIT

Cas de la table LOGS

On passe au traitement des données de la table LOGS. Celle ci sera également traité par ora2pg (même s’il l’on aurait pu directement envoyé le “data_LOGS.sql” dans la commande psql), avant cela nous irons renommer le fichier “data.sql”  pour n’intégrer que cette table.

postgres@:~/ora2pg $ cd REF/data
postgres@:~/ora2pg/REF/data$ ls -l
-rw-r--r-- 1 postgres postgres 3822319020 août   1 17:47 data.sql
-rw-r--r-- 1 postgres postgres  254962470 août   2 09:22 data_LOGS.sql
postgres@:~/ora2pg/REF/data$ mv data.sql data.sql.save
postgres@:~/ora2pg/REF/data$ mv data_LOGS.sql data.sql
postgres@:~/ora2pg/REF/data$ ls -l
-rw-r--r-- 1 postgres postgres  254962470 août   2 09:22 data.sql
-rw-r--r-- 1 postgres postgres 3822319020 août   1 17:47 data.sql.save

 

Relancer l’opération via le script “import_all.sh” avec les mêmes paramètres en prenant soin de répondre N à toutes les questions, sauf pour la partie data :

Would you like to import data from ./data/data.sql? [y/N/q] y
Running: psql -U REF_USER -d CAPDATA_REF -f ./data/data.sql
BEGIN
COPY 10000
COPY 10000
COPY 10000
COPY 10000
COPY 10000
COPY 10000
COPY 10000
COPY 10000
....
COMMIT

Comparaison des données

Une fois ces opérations de chargement effectuées, on pourra faire les comparatifs adéquats afin de savoir si le nombre de lignes sur la base Oracle et la base PostgreSQL sont identiques.

— Pour Oracle , les commandes suivantes seront à passer sous SQLPlus.

postgres@:~$ sqlplus capdata@SRC_DB
SQL> exec dbms_stats.gather_schema_stats(ownname=>'GBMREF', degree=>4);
Puis on regarde les lignes des tables
SQL> select owner, table_name, num_rows from dba_tables where owner ='REF' order by 3 desc;

 

— Pour PostgreSQL, on passera la commande “vacuum (analyze)” sur chacune des tables dans psql.

postgres@:~/ora2pg $ psql -U REF_USER -d CAPDATA_REF
(REF_USER@[local]:5432) [CAPDATA_REF] > select 'vacuum (analyze) '||relname||';' FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)      
inner join pg_user u on (u.usesysid=c.relowner) WHERE  nspname NOT IN ('pg_catalog', 'information_schema') 
AND relkind='r' ORDER BY relname;

Les index et contraintes

Pour la création des index et contraintes, nous utiliserons les scripts générés en “post export”. Pas de souci sur la partie index.

postgres@:~/ora2pg/REF/schema/tables$ psql -d CAPDATA_REF -U REF_USER -f indexes_ONLY.sql
L'affichage de null est « NULL ».
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
......

Puis la création des contraintes

postgres@:~/ora2pg/REF/schema/tables$ psql -d CAPDATA_REF -U REF_USER -f constraints_ONLY.sql
L'affichage de null est « NULL ».
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
....


Les fonctions/procédures et triggers

Suite au premier passage du script “package.sql” voici les erreurs que nous avons rencontré, ainsi que les suggestions que nous pouvons apporter.

La syntaxe sera à adapter et à tester au niveau applicatif. Dans notre exemple de migration de schéma, nous avons 8 fonctions à migrer pour la base CAPDATA_REF.

Le script “package.sql” généré par “ora2pg” transforme le nom du package Oracle  en nom de schéma.
Exemple avec cet appel Oracle suivant : exec DBMS_OUTPUT.PUT_LINE ou l’application “ora2pg” va alors interpréter DBMS_OUTPUT comme un nouveau schéma à créer. Nous pourrons donc supprimer cette ordre de création de schéma ainsi que le préfixe inscrit sur chacune des fonctions.

On comprend donc qu’une analyse approfondie devra être menée sur le fichier “package.sql” avant de lancer celui ci.

Durant cette analyse nous avons pu relever 2 erreurs fondamentales qui seront à corriger en accord avec les développeurs et les équipes applicatives métier.

-- ERREUR à l'execution -- >
ERREUR:  42601: erreur de syntaxe sur ou près de « dbms_job »
LIGNE 12 :   dbms_job.submit( l_jobno, l_script);
             ^

-- SUGGESTION --> 
Le "dbms_job" n'existe pas sous PostgreSQL , il faudrait utiliser l'extension "pg_cron" qui est une extension à charger au démarrage de la base pour effectuer des tâches schedulés.

et

 

-- ERREUR à l'execution -- >
ERREUR:  3F000: le schéma « utl_http » n'existe pas
LIGNE 4 : p_http_req     UTL_HTTP.req;
                         ^

-- SUGGESTION --> 
"UTL_HTTP n'existe pas sous PostgreSQL , il existe également une extension "pgsql-http" qui est à charger au démarrage de la base pour effectuer des appels POST et GET via une base PostgreSQL.

 

Pour ces 2 erreurs, nous avons proposé à notre client de faire évoluer le code de leurs fonctions  en utilisant 2 extensions pour PostgreSQL qui sont “pg_cron” en remplacement de l’appel au package DBMS_JOB sur Oracle. Ainsi que l’extension “pgsql-hhtp” que l’on peut utiliser pour les appels Web qui étaient traités par UTL_HTTP sur Oracle.

Ces 2 extensions seront à télécharger, compiler et installer sur les librairies partagées dans les binaires PostgreSQL de notre instance.
Une modification du fichier “postgresql.conf” avec la variable “shared_preload_libraries” devra être effectuée afin de pouvoir utiliser ces nouvelles librairies.
Ce procédé fera l’objet d’un prochain article détaillé  sur ce blog.

N’hésitez pas à laisser vos commentaires si besoin.

Emmanuel RAMI

 

 

Continuez votre lecture sur le blog :

twitterlinkedinmail

Emmanuel RAMI

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.