Accueil‎ > ‎

SUSE 11.3 : Ora2Pg Installation

posted 4 Apr 2019, 05:26 by Christophe Noël   [ updated 10 Apr 2019, 00:01 ]
Summary of  the Ora2Pg Installation on CentOS 11.3.

Requirements:
- The machine needs to access the internet to download packages
- You need PostgresQL-devel (zypper install postgresql-devel) to get pg_config

PERL Modules Installation


1. Check that perl  5.10+ is installed (otherwise install it, e.g. using zypper):
perl --version
2.a Install DBI Perl Module (> 1.614) which is the DataBase Interface. 

zypper install perl-DBI
Note that this will probably require updating some installed packages such as perl and libstdc. In my case v5.10 was updated to 5.12
.
2.a Manual Installation:
wget http://cpan.metacpan.org/authors/id/T/TI/TIMB/DBI-1.634.tar.gz
tar -xzvf DBI-1.614.tar.gz
cd DBI-1.614/
perl Makefile.PL
make
make install
(ignore warnings)
2.b  ALTERNATIVE: Install DBI Perl Module (> 1.614) using CPANexpor
This requires the installation of make.
perl -MCPAN -e 'install Bundle::DBI'
Would you like me to configure as much as possible automatically? [yes]
3. Verify the perl-DBI version
perl -MDBI -e 'die $DBI::VERSION'
4. Same with DBD-Oracle
wget http://cpan.metacpan.org/authors/id/T/TI/TIMB/DBD-Oracle-1.26.tar.gz
tar -xzvf DBD-Oracle-1.26.tar.gz
cd DBD-Oracle-1.26/
export LD_LIBRARY=/tools/app/oracle/product/11.2.0/dbhome_1 (replace here!!)
perl Makefile.PL
make
make install
(ignore warnings:WARNING: Your LD_LIBRARY_PATH env var doesn't include '/tools/app/oracle/product/11.2.0/dbhome_1/lib' but probably needs to. )
4. Same with DBD-Pg (the perl module that provides access to PG databases)
wget http://cpan.metacpan.org/authors/id/T/TU/TURNSTEP/DBD-Pg-3.7.4.tar.gz
tar -xzvf DBD-Pg-3.7.4.tar.gz
cd DBD-Pg-3.7.4/
perl Makefile.PL
make
make install
(ignore warnings:WARNING: Your LD_LIBRARY_PATH env var doesn't include '/tools/app/oracle/product/11.2.0/dbhome_1/lib' but probably needs to. )



.

Ora2Pg Installation

1. Copy the ora2Pg tar.gz archive and perform the following commands:

tar -xzvf ora2pg-20.0.tar.gz
cd ora2pg-20.0/
perl Makefile.PL
make
make install

2.

Par défaut, l'export des objets ou données de la base Oracle est enregistré dans un fichier. Pour renvoyer le flux des données exportées directement dans la base PostgreSQL de destination il existe 3 directives de configuration : • PG_DSN : chaine de connexion à la base PostgreSQL sous la forme dbi:Pg:dbname=pgdb;host=localhost;port=5432 • PG_USER: utilisateur PostgreSQL utilisé pour la connexion. • PG_PWD : mot de passe de l'utilisateur.

Another useful link (FR): https://easyteam.fr/initiation-a-ora2pg/ 

Use Overview

The general sequence is simple:

1. Generate a migration template (a set of file and directories)
ora2pg --project_base /app/migration/ --init_project test_project
2. Modifcation the configuration options (in test_project tree)
vi config/ora2pg.conf
3.Exports the schemas (typically TABLE PACKAGE VIEW GRANT SEQUENCE TRIGGER FUNCTION PROCEDURE TABLESPACE PARTITION TYPE MVIEW DBLINK SYNONYM DIRECTORY)
./export_schema.sh
4. Import all schemas
./import_all.sh -d database_name -o database_owner -U postgres # (or system account)
5. Copy Data
ora2pg -t COPY -o data.sql -b ./data -c ./config/ora2pg.conf --pg_dsn "dbi:Pg:dbname=xxx;host=localhost;port=5432" --pg_user "xxx" --pg_pwd xxx
In this step, we use the pg_dsn option in order to write directly the export to the postgres database instead of using psql. Theorically this allows to write incrementally the data but also use parallelisation options.

Alternatively you can export to an SQL file and use psql to import the SQL file into your database.

Notes and Comments

- If you do not export the whole library (e.g a set of tables) then take care that the global sequences will not be exported. It may be relevant to set DISABLE_SEQUENCE flag
- When a data entry already exists, the following error is returned "ERROR:  current transaction is aborted, commands ignored until end of transaction block".

* Blob export with COPY:  \\x4749463839615a036302f7ff000000000
* Blob export with INSERT:  ,decode(E'4749463839615a036302f7ff000000000e0e0e1111111815142

Comments