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_PATHLe 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 okIl 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/ora2pgAfin 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 :
- PostgreSQL : planifier une tâche avec pg_cron (Emmanuel RAMI) [Non classéPostgreSQL]
- Cybertec Migrator : une alternative à ora2pg ? (Sarah FAVEERE) [OraclePostgreSQL]
- pg_dirtyread où comment réparer facilement un delete sauvage (Sarah FAVEERE) [PostgreSQL]
- Pyrseas et Postgresql : Comparer facilement des schémas de base de données (Sarah FAVEERE) [PostgreSQL]
- PostgreSQL Anonymizer (Sarah FAVEERE) [PostgreSQL]
Bonjour, Bravo pour cette présentation, claire et riche,.