Ora2Pg

測試作業系統:Centos 7.6

測試IP     :192.168.112.88

作業系統安裝資訊:最小化安裝

官方網站:

http://ora2pg.darold.net/

Requirement

The Oracle Instant Client or a full Oracle instaaltion must be installed on the system. You can download the RPM from Oracle download center:

    rpm -ivh oracle-instantclient12.2-basic-12.2.0.1.0-1.x86_64.rpm

    rpm -ivh oracle-instantclient12.2-devel-12.2.0.1.0-1.x86_64.rpm

    rpm -ivh oracle-instantclient12.2-jdbc-12.2.0.1.0-1.x86_64.rpm

    rpm -ivh oracle-instantclient12.2-sqlplus-12.2.0.1.0-1.x86_64.rpm

or simply download the corresponding ZIP archives from Oracle download center and install them where you want, for example: /opt/oracle/instantclient_12_2/

You also need a modern Perl distribution (perl 5.10 and more). To connect to a database and proceed to his migration you need the DBI Perl module > 1.614. To migrate an Oracle database you need the DBD::Oracle Perl modules to be installed. To migrate a MySQL database you need the DBD::MySQL Perl modules. These modules are used to connect to the database but they are not mandatory if you want to migrate DDL input files.

To install DBD::Oracle and have it working you need to have the Oracle client libraries installed and the ORACLE_HOME environment variable must be defined.

If you plan to export a MySQL database you need to install the Perl module DBD::mysql which requires that the mysql client libraries are installed.

On some Perl distribution you may need to install the Time::HiRes Perl module.

1.準備 oracle instant client 環境

  1.1 解壓軟體

[root@ora2pg u01]# unzip instantclient-sqlplus-linux.x64-12.2.0.1.0.zip

[root@ora2pg u01]# unzip instantclient-sdk-linux.x64-12.2.0.1.0.zip

[root@ora2pg u01]# unzip instantclient-jdbc-linux.x64-12.2.0.1.0.zip

[root@ora2pg u01]# unzip instantclient-basic-linux.x64-12.2.0.1.0.zip

  1.2 配置環境變數

PATH=$PATH:$HOME/bin:/u01/instantclient_12_2

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/pgsoft/lib:/u01/instantclient_12_2

export PATH

2.準備 DBD::Oracle  ,  DBI   

    安裝依賴包

yum install perl-DBI perl-DBD-Pg perl-ExtUtils-MakeMaker gcc

3. 準備ora2pg 環境

下載檔案:

ora2pg-20.0.zip

下載後解壓執行

perl Makefile.PL

make

make install

4. 配置 /etc/ora2pg.conf

 # oracle connect info

ORACLE_DSN dbi:Oracle:host=192.168.112.77;sid=testdb;port=1521

ORACLE_USER SYSTEM

ORACLE_PWD oracle

# oracle schema

SCHEMA MYUSER

# oracle type

TYPE TABLE COPY DATA

OUTPUT table.sql

 psql -U postgres -d postgres -h 127.0.0.1 -p 5432 -f TABLE_table.sql

 psql -U postgres -d postgres -h 127.0.0.1 -p 5432 -f COPY_table.sql

或者

 psql -U postgres -d postgres -h 127.0.0.1 -p 5432 -f INSERT_table.sql

5. 生成遷移模板

[root@ora2pg ora2pg-20.0]# ora2pg --project_base /u01/migration/ --init_project test_project